TRAINING (SQL-6) - Data Query Language (DQL) Keywords & Clauses

SQL Keywords & Clauses

1.) SELECT Statement
SELECT statement contains list of columns or all columns which needs to select/display after the query execution competes.

Syntax:
SELECT <Column1, Column1, ......., ColumnN)
SELECT <*>
Example:
SELECT *
SELECT Ename, Job

1.1) DISTINCT
Used with SELECT to club/group the result.
When NULL is used in DISTINCT clause, SQL will consider all NULL as same

Syntax:
SELECT DISTINCT <Column1>
Example:
SELECT DISTNCT Job

2.) FROM Clause
FROM clause is used to mention the table/tables going to be used for selection.

Syntax:
SELECT <Column1, Column2, ......., ColumnN)
FROM <Table 1, TABLE2,.....TableN>

Example:
SELECT EMPNO, ENAME
FROM EMP

3.) WHERE Clause
WHERE clause is used to narrow down/filter the data retrieved using SELECT statement. In-order to  narrow down/filter the data retrieved different comparison, logical and other operators are used.  mention the table/tables going to be used for selection.

3.1.1) RELATIONAL COMPARISON OPERATORS
Below are the list of relational operators used to compare any datatype (number, character, string or combination of these datatypes) and expression (formula).

• EQUALS TO (=)
• NOT EQUALS TO (<>)
• GREATER THAN ( >)
• LESSER THAN (<)
• GREATER THAN or EQUALS TO (>=)
• LESSER THAN or EQUALS TO (<=)

Syntax:
SELECT <Column1, Column2, ......., ColumnN)
FROM <Table 1, TABLE2,.....TableN>
WHERE <Column/Expression> = <FILTER CRITERIA>

Example:
SELECT EMPNO, ENAME
FROM EMP
WHERE EMPNO = 7900;

3.1.2) BETWEEN
BETWEEN is inclusive of both side of values around it.
Example:
WHERE Salary BETWEEN 4000 AND 5000;

3.1.3) IN
Example:
WHERE Cost IN (200, 400, 500);

3.1.4) LIKE
Example:
WHERE Last_Name LIKE 'S%';
WHERE Last_Name LIKE '_HAR%';

3.1.5) IS NULL

• By definition a NULL value is an unknown value.
• One NULL value never equals to another NULL value. There is an exception though ( When NULL is used in DISTINCT clause, SQL will consider all NULL as same).

Example:
WHERE Commision IS NULL;

3.1.6) NOT
For NEGATING/REVERTING the select result.
Example:
WHERE Salary NOT BETWEEN 4000 AND 5000;
WHERE Cost NOT IN (200, 400, 500);
WHERE Commision IS NOT NULL;

3.2) LOGICAL or BOOLEAN OPERATORS
Comparison operators can be combined with help of Logical or Boolean operators AND and OR.

3.2.1) AND
Conditions on both side of AND logical operator must be true (0) to fetch the results.
Example:
SELECT *
FROM emp 
WHERE empno = 7900 AND deptno = 30;

3.2.2) OR
Conditions on either side of OR logical operator must be true (0) to fetch the results.
Example:
SELECT *
FROM emp 
WHERE deptno = 20 OR deptno = 30;

PRECEDENCE OF LOGICAL OPERATORS

• When AND and OR are used together in a WHERE clause, the AND operator always take precedence over the OR operator, meaning the AND condition are evaluated first.
• If there are multiple operators of same precedence, the left operator is executed before right.
• You can manipulate the precedence in WHERE clause using parentheses.
• For example below queries aare going to give you different results
• SELECT description, cost, prerequisite
  FROM course
  WHERE cost = 1195
  AND prerequisite = 20
  OR prerequisite =2;
• SELECT description, cost, prerequisite
  FROM course
  WHERE cost = 1195
  AND (prerequisite = 20
  OR prerequisite =25);

4.) ORDER BY Clause

Result of SQLs are displayed in the order they are returned from database, however to display data in a specific order (ascending or descending) ORDER BY clause is used.

• By Default when ORDER BY clause is used results are displayed in Ascending order.
• If you want to display in specific order use the abbreviation DESC (descending) or ASC (ascending).

Syntax:
SELECT <Column1, Column2, ......., ColumnN)
FROM <Table 1, TABLE2,.....TableN>
WHERE <Column/Expression> = <FILTER CRITERIA>
ORDER BY <Column1, Column2, ......., ColumnN) 

Example:
SELECT empno, ename, deptno
FROM emp
WHERE ename LIKE 'J%'
ORDER BY deptno DESC;

5.) GROUP BY Clause

• GROUP BY clause is similar to DISTINCT but only work on groups.
• Used to club/group the results filtered on the basis of WHERE clause.

Syntax:
SELECT <Column1, Column2, ......., ColumnN)
FROM <Table 1, TABLE2,.....TableN>
WHERE <Column/Expression> = <FILTER CRITERIA>
GROUP BY <Column>
ORDER BY <Column1, Column2, ......., ColumnN) 

Example:
SELECT empno, ename, deptno
FROM emp
WHERE ename LIKE 'J%'
GROUP BY deptno
ORDER BY deptno DESC;

6.) HAVING Clause

HAVING clause is similar to WHERE clause to eliminate/filter the results, but it only works on groups/aggregate.

Syntax:
SELECT <Column1, Column2, ......., ColumnN)
FROM <Table 1, TABLE2,.....TableN>
WHERE <Column/Expression> = <FILTER CRITERIA>
GROUP BY <Column>
HAVING <Column/Expression> = <FILTER CRITERIA> 
ORDER BY <Column1, Column2, ......., ColumnN) 

Example:
SELECT location, count(location) "Total Locations", capacity, sum(capacity) "Total Capacity"
FROM section
WHERE section_no=3
GROUP BY location, capacity
HAVING sum(capacity) > 75
ORDER BY deptno DESC;