SQL5

INSTRUCTOR
VINAY NOAH

Select Query

+

Comparison Operators

For numbers (INT, DECIMAL, FLOAT), you could use comparison operators: '=' (equal to), '<>' or '!=' (not equal to), '>' (greater than), '<' (less than), '>=' (greater than or equal to), '<=' (less than or equal to), to compare two numbers. For example, price > 1.0, quantity <= 500.CAUTION: Do not compare FLOATs (real numbers) for equality ('=' or '<>'), as they are not precise. On the other hand, DECIMAL are precise. For strings, you could also use '=', '<>', '>', '<', '>=', '<=' to compare two strings (e.g., productCode = 'PEC'). The ordering of string depends on the so-called collation chosen. For example,+

+

String Pattern Matching – LIKE and NOT LIKE

For strings, in addition to full matching using operators like '=' and '<>', we can perform pattern matching using operator LIKE (or NOT LIKE) with wildcard characters. The wildcard '_' matches any single character; '%' matches any number of characters (including zero). For example,+

• 'abc%' matches strings beginning with 'abc';
• '%xyz' matches strings ending with 'xyz';
• '%aaa%' matches strings containing 'aaa';
• '___' matches strings containing exactly three characters; and
• 'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.

+

 +

Note : MySQL also support regular expression matching via the REGEXE operator.+

Logical Operators – AND, OR, NOT, XOR

You can combine multiple conditions with boolean operators AND, OR, XOR. You can also invert a condition using operator NOT. For example,+

+

IN, NOT IN

BETWEEN, NOT BETWEEN

IS NULL, IS NOT NULL

+

ORDER BY Clause & LIMIT Clause

+

 +

AS – Alias

You could use the keyword AS to define an alias for an identifier (such as column name, table name). The alias will be used in displaying the name. It can also be used as reference. For example,+

+

 +

Function CONCAT()

+

 +

Producing Summary Reports

To produce a summary report, we often need to aggregate related rows.+

DISTINCT

A column may have duplicate values, we could use keyword DISTINCT to select only distinct values. We can also apply DISTINCT to several columns to select distinct combinations of these columns. For example,+

+

 +

GROUP BY Clause

The GROUP BY clause allows you to collapse multiple records with a common value into groups. For example,+

+

 +

GROUP BY by itself is not meaningful. It is used together with GROUP BY aggregate functions (such as COUNT(), AVG(), SUM()) to produce group summary.+

GROUP BY Aggregate Functions: COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT

+

 +

Besides COUNT(), there are many other GROUP BY aggregate functions such as AVG(), MAX(), MIN() and SUM(). For example,+

+

HAVING clause  &  WITH ROLLUP

HAVING is similar to WHERE, but it can operate on the GROUP BY aggregate functions; whereas WHERE operates only on columns.+

+

PREVIOUS UNIT