SQL

Queries

Subqueries

A Subquery is a SQL query embedded within the WHERE clause of another SQL query

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id IN (
        SELECT location_id
        FROM locations
        WHERE city = 'Seattle'
    )
)
ORDER BY employee_id 

Here there are 2 subqueries.

location_id is common between tables locations and departments
department_id is common between tables employees and departments

WHERE

BETWEEN

Between is inclusive of the upper & lower limit of the range specified.

IN

WHERE & LIKE

• WHERE clause is used for filtering of records.

IS NULL

The absence of any value in a cell is represented by the NULL keyword.

TRIM() function

removes excess spaces from the beginning or end of a string value

IFNULL() function

 check for null values in a column and if found, replace them with some other value.

SELECT employeeNumber,firstName,lastName,
IFNULL(email,"N/A") AS email,
IFNULL(phone,"N/A") AS phone
FROM employees

in email column if there are any null values it will be replaced by N/A and again show the column email

CASE statement

What if you want to take some action based on a certain condition?

IF() function

IF(condition, true_value, false_value)

Wildcards

 partially matched strings using a comparison operator called LIKE, and wildcard characters, which serve as a placeholder for unknown characters in a string.

SUBSTR().

Syntax: SUBSTR(value, position, length)

position tells us which character to start from.

length tells us up to which character we should include.

OFFSET

omit a specified number of rows before the beginning of the result set

ROUND() function.

The ROUND() function can also accept negative numbers for the second parameter, to round digits that are to the left of the decimal point. 

For example, SELECT ROUND(1245, -2) will return a value of 1200.

CEIL,FLOOR

CEIL(5.4) -> 6
FLOOR (5.7) -> 5