Basis of Selection 1

Basis SQL Query Syntax

SELECT operator: get data samples from DBMS.

Output of all data from the table:

SELECT * FROM Table_name

Output of data from certain columns of the table

SELECT Table_field1, Table_field2, ... FROM Table_name

Aliases: In case we want to output some columns of the table.

SELECT Table_field1, Table_field2 AS alias FROM Table_name

OUTPUT:

Table_field1	alias
data_1	data_2

Literals

A literal is an explicitly specified fixed value, such as the number 12 or the string `“SQL”‘.

The main types of literals in MySQL are:

string
numeric
logical
NULL
date and time literal

String Literals

A string is a sequence of characters enclosed in single (’) or double (”) quotation marks. For example, this is a string” and "this is a string".

Strings can contain special sequences of characters starting with "\".

SELECT "Line \n Another line" as String

Numeric Literals

Numeric literals can have

Integers and floating point : 1, 2.8, 0.01
Only integer part, fractional part or both: .2, 1.2, 20
Positive or negative numbers: +1, -10, -2.2
Exponential notation: 1e3, 1e-3

Arithemetic Operators

For numeric literals, SQL has all arithimetic Operators

%, MOD = *
/
DIV

USAGE

SELECT (5 * 2 - 6) / 2 AS Result;

Date and Time literals.

Date and time values can be represented as string or numbers.

SELECT * FROM Table_name WHERE birthday > '1970-12-30'

where 1970-12-30 is a string.

Logical Literals

A logical literal is a value of TRUE or FALSE

NULL

The value NULL means “no data” or “no value.”

Function usage

When creating SQL queries, we can use built-in functions. For example, if we want to output a string in uppercase, we can use the UPPER function.

SELECT UPPER("Hello world")

functions reference page

Applying functions over table field values

Functions can be used not only on literals, but also on values taken from a table.

SELECT member_name,
	LENGTH(member_name) AS fullname_length
FROM FamilyMembers;

Operations on the result of the function

Since we know that each function must return any of the possible literals, its result can also be used in further calculations and transformations.

SELECT UPPER(LEFT('sql-academy', 3)) AS str;

Duplication elimination, DISTINCT

In some situations, an SQL query for selecting data may return duplicate rows. To avoid such duplication when selecting data, there is the DISTINCT operator.

SELECT [DISTINCT] table_fields FROM table_name;

Conditional WHERE operator

The situation where a selection needs to be made based on a specific condition is very common. For this, the WHERE operator exists in the SELECT statement, which is followed by conditions for limiting rows.

SELECT [DISTINCT] table_fields FROM table_name
WHERE row_limit_conditions
[logical_operator other_row_limit_conditions];

Operators

Comparision Operators

SELECT
  2 = 1 as Numerical_equality_test,
	'a' = 'a' as String_equality_test,
	NULL <=> NULL as Equivalence_test,
	2 <> 2 as Inequality_test,
	3 < 4 as Less_than_test,
	10 <= 10 as Less_than_or_equal_test,
	7 > 1  as Greater_than_test,
	8 >= 10 as Greater_than_or_equal_test;

Logical Operators

NOT
OR
AND
XOR

Operators IS NULL, BETWEEN, IN

IS NULL

The IS NULL operator allows you to find out if the checked value is equal to NULL, i.e. if the value is empty.

SELECT * FROM Teacher
WHERE middle_name IS NULL;

BETWEEN

The BETWEEN min AND max operator allows you to find out if the checked value of the column is located in the interval between min and max, including the values min and max themselves

SELECT * FROM Payments
WHERE unit_price BETWEEN 100 AND 500;

is equivalent to:

SELECT * FROM Payments
WHERE unit_price >= 100 AND unit_price <= 500;

IN

The IN operator allows you to find out if the checked value of the column is included in a list of certain values.

SELECT * FROM FamilyMembers
WHERE status IN ('father', 'mother');

Operators LIKE

The LIKE operator is used in conditional queries when we want to find out whether a string matches a certain pattern.

Syntax:

... WHERE table_field [NOT] LIKE string_pattern