Queries (SQL)

SELECT [DISTINCT | TOP <n>]
    <column expression> [EXCLUDE(<column exclusion list>)],...
FROM [<schema name>.]<table name> [<table alias>]
    [<join type> JOIN <join table name> [<join alias>] ON <join expr>],...
[WHERE <filtering expression list>]
[GROUP BY <grouping expression list>]
[HAVING <group filtering expression list>]
[ORDER BY <ordering expression list>]
[LIMIT [<offset>, ]<num rows>]

SELECT
    e.last_name || ', ' || e.first_name AS "Employee_Name",
    m.last_name || ', ' || m.first_name AS "Manager_Name"
FROM
    example.employee e
    LEFT JOIN example.employee m ON e.manager_id = m.id
WHERE
    e.dept_id IN (1, 2, 3)
ORDER BY
    m.id ASC NULLS FIRST,
    e.hire_date

SELECT DAYNAME(NOW()) AS "Today"

SELECT
    e.last_name || ', ' || e.first_name AS "Employee_Name",
    m.last_name || ', ' || m.first_name AS "Manager_Name"
FROM
    example.employee e,
    example.employee m
WHERE
    e.manager_id = m.id
ORDER BY
    e.last_name,
    e.first_name

SELECT
    e.last_name || ', ' || e.first_name AS "Employee_Name",
    m.last_name || ', ' || m.first_name AS "Manager_Name"
FROM
    example.employee e
    LEFT JOIN example.employee m ON e.manager_id = m.id
ORDER BY
    e.last_name,
    e.first_name

ASOF(<left_column>, <right_column>, <rel_range_begin>, <rel_range_end>, <MIN|MAX>)

ASOF(inbound.eta, outbound.etd, INTERVAL '30' MINUTE, INTERVAL '90' MINUTE, MIN)

ASOF(b.x, n.x, .00001, 5, MIN)

SELECT
    t.id,
    t.dt AS execution_dt,
    q.open_dt AS quote_dt,
    t.price AS execution_price,
    q.open_price
FROM
    example.trades t
    LEFT JOIN example.quotes q ON
        t.ticker = q.symbol AND
        ASOF(t.dt, q.open_dt, INTERVAL '-1' DAY, INTERVAL '0' DAY, MAX)

SELECT
    t.ticker,
    t.asof_dt,
    q.open_dt,
    q.open_price
FROM
    (SELECT 'EBAY' AS ticker, DATETIME('2006-12-15 12:34:56') AS asof_dt) t
    LEFT JOIN example.quotes q ON
        t.ticker = q.symbol AND
        ASOF(t.asof_dt, q.open_dt, INTERVAL '-1' DAY, INTERVAL '0' DAY, MAX)

SELECT ROUND(AVG(total_amount),2) AS "Average_Fare"
FROM demo.nyctaxi

SELECT
    vendor_id AS Vendor_ID,
    YEAR(pickup_datetime) AS Year,
    MAX(trip_distance) AS Max_Trip,
    MIN(trip_distance) AS Min_Trip,
    ROUND(AVG(trip_distance),2) AS Avg_Trip,
    INT(AVG(passenger_count)) AS Avg_Passenger_Count
FROM demo.nyctaxi
WHERE
    trip_distance > 0 AND
    trip_distance < 100
GROUP BY vendor_id, 2
ORDER BY Vendor_ID, Year

CASE
    WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
    ELSE NVL(Sector, '<UNKNOWN SECTOR>')
END AS SectorGroup,

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS SectorGroup,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS SymbolGroup,
    DECIMAL(AVG("Open"), 7, 2) AS AvgOpen
FROM demo.Stocks
GROUP BY ROLLUP(Sector, Symbol)
ORDER BY SectorGroup, SymbolGroup

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS SectorGroup,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS SymbolGroup,
    DECIMAL(AVG("Open"), 7, 2) AS AvgOpen
FROM demo.Stocks
GROUP BY CUBE(Sector, Symbol)
ORDER BY SectorGroup, SymbolGroup

SELECT
    CASE
        WHEN (GROUPING(Sector) = 1) THEN '<ALL SECTORS>'
        ELSE NVL(Sector, '<UNKNOWN SECTOR>')
    END AS SectorGroup,
    CASE
        WHEN (GROUPING(Symbol) = 1) THEN '<ALL SYMBOLS>'
        ELSE NVL(Symbol, '<UNKNOWN SYMBOL>')
    END AS SymbolGroup,
    DECIMAL(AVG("Open"), 7, 2) AS AvgOpen
FROM demo.Stocks
GROUP BY GROUPING SETS((Sector), (Symbol), ())
ORDER BY SectorGroup, SymbolGroup

SELECT
    <window function> OVER (
        [PARTITION BY <column expression list>]
        [ORDER BY <ordering expression list>]
        [
            <RANGE | ROWS>
            <
                <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
                |
                BETWEEN <UNBOUNDED PRECEDING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
                    AND <UNBOUNDED FOLLOWING | <number> PRECEDING | CURRENT ROW | <number> FOLLOWING>
            >
        ]
    ) [AS <alias>]

SELECT
    vendor_id,
    pickup_datetime,
    total_amount,
    passenger_count,
    DECIMAL
    (
        SUM(total_amount) OVER
            (
                PARTITION BY vendor_id
                ORDER BY pickup_datetime
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
    ) AS growing_sum,
    COUNT(*) OVER
        (
            PARTITION BY vendor_id
            ORDER BY LONG(pickup_datetime)
            RANGE BETWEEN 300000 PRECEDING AND 300000 FOLLOWING
        ) AS trip_demand
FROM demo.nyctaxi
WHERE pickup_datetime >= '2015-01-01' AND pickup_datetime < '2015-01-01 02:00:00'
ORDER BY
    vendor_id,
    pickup_datetime

SELECT
    vendor_id,
    pickup_datetime,
    trip_distance,
    AVG(trip_distance) OVER
        (
            PARTITION BY vendor_id
            ORDER BY pickup_datetime
            ROWS BETWEEN 5 PRECEDING AND 10 FOLLOWING
        ) AS local_avg_dist
FROM demo.nyctaxi
WHERE
    passenger_count = 4 AND
    pickup_datetime >= '2015-01-01' AND pickup_datetime < '2015-01-02'
ORDER BY
    vendor_id,
    pickup_datetime

SELECT
    vendor_id,
    pickup_datetime,
    dropoff_datetime,
    total_amount AS fare,
    RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount) AS ranked_fare,
    DECIMAL(PERCENT_RANK() OVER (PARTITION BY vendor_id ORDER BY total_amount)) * 100 AS percent_ranked_fare
FROM demo.nyctaxi
WHERE
    passenger_count = 3 AND
    pickup_datetime >= '2015-01-11' AND pickup_datetime < '2015-01-12'
ORDER BY
    vendor_id,
    pickup_datetime

SELECT
    vendor_id,
    pickup_datetime,
    tip_amount,
    tip_amount -
        FIRST_VALUE(tip_amount) IGNORE NULLS OVER
            (PARTITION BY vendor_id ORDER BY tip_amount) AS vs_lowest_tip,
    tip_amount -
        DECIMAL
        (
            AVG(tip_amount) OVER
                (
                    PARTITION BY vendor_id
                    ORDER BY tip_amount
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                )
        ) AS vs_average_tip,
    tip_amount -
        FIRST_VALUE(tip_amount) IGNORE NULLS OVER
            (PARTITION BY vendor_id ORDER BY tip_amount DESC) AS vs_highest_tip
FROM demo.nyctaxi
WHERE
    passenger_count = 5 AND trip_distance > 0 AND tip_amount > 0 AND
    pickup_datetime >= '2015-04-17' AND pickup_datetime < '2015-04-18'
ORDER BY
    vendor_id,
    pickup_datetime

SELECT
    vendor_id,
    DECIMAL(AVG(total_amount)) AS average_total_amount,
    DECIMAL(AVG(IF(quartile IN (2,3), total_amount, null))) AS average_interq_range_total_amount
FROM
(
    SELECT
        vendor_id,
        total_amount,
        NTILE(4) OVER (PARTITION BY vendor_id ORDER BY total_amount) quartile
    FROM
        demo.nyctaxi
)
GROUP BY vendor_id
ORDER BY vendor_id

<select statement>
PIVOT
(
    <aggregate expression [AS <alias>]>[,...]
    FOR <column> IN (<column list>)
)

+--------+--------------+----------------+
| name   | phone_type   | phone_number   |
+--------+--------------+----------------+
| Jane   | Home         | 123-456-7890   |
| Jane   | Work         | 111-222-3333   |
| John   | Home         | 123-456-7890   |
| John   | Cell         | 333-222-1111   |
+--------+--------------+----------------+

SELECT
    name,
    Home_Phone,
    Work_Phone,
    Cell_Phone
FROM
    example.phone_list
PIVOT
(
    MAX(phone_number) AS Phone
    FOR phone_type IN ('Home', 'Work', 'Cell')
)

+--------+----------------+----------------+----------------+
| name   | Home_Phone     | Work_Phone     | Cell_Phone     |
+--------+----------------+----------------+----------------+
| Jane   | 123-456-7890   | 111-222-3333   |                |
| John   | 123-456-7890   |                | 333-222-1111   |
+--------+----------------+----------------+----------------+

<select statement>
UNPIVOT
(
    <value_column> FOR <var_column> IN (<column list>)
)

+--------+----------------+----------------+----------------+
| name   | home_phone     | work_phone     | cell_phone     |
+--------+----------------+----------------+----------------+
| Jane   | 123-456-7890   | 111-222-3333   |                |
| John   | 123-456-7890   |                | 333-222-1111   |
+--------+----------------+----------------+----------------+

SELECT *
FROM
(
    SELECT
        name,
        Home_Phone AS Home,
        Work_Phone AS Work,
        Cell_Phone AS Cell
    FROM
        example.customer_contact
)
UNPIVOT (
    phone_number FOR phone_type IN (Home, Work, Cell)
)

+--------+----------------+--------------+
| name   | phone_number   | phone_type   |
+--------+----------------+--------------+
| Jane   | 123-456-7890   | Home         |
| John   | 123-456-7890   | Home         |
| Jane   | 111-222-3333   | Work         |
| John   | 333-222-1111   | Cell         |
+--------+----------------+--------------+

SELECT *
FROM example.student_grade
UNPIVOT
(
    grade FOR quarter IN (q1_grade, q2_grade, q3_grade, q4_grade)
)

+--------------+-------------+------------+
|   student_id |       grade | quarter    |
+--------------+-------------+------------+
|            1 |   80.0      | q1_grade   |
|            2 |   82.0      | q1_grade   |
|            3 |   73.0      | q1_grade   |
|            1 |   90.0      | q2_grade   |
|            3 |   77.0      | q2_grade   |
|            1 |   85.0      | q3_grade   |
|            2 |   87.0      | q3_grade   |
|            3 |   97.0      | q3_grade   |
|            1 |   95.0      | q4_grade   |
|            2 |   92.0      | q4_grade   |
+--------------+-------------+------------+

<select statement>
UNION [ALL]
<select statement>

SELECT
    food_name,
    category,
    price
FROM
    example.lunch_menu
UNION
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

SELECT
    food_name,
    category,
    price
FROM
    example.lunch_menu
UNION ALL
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

<select statement>
INTERSECT [ALL]
<select statement>

SELECT
    food_name,
    category,
    price
FROM
    example.lunch_menu
INTERSECT
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

<select statement>
EXCEPT [ALL]
<select statement>

SELECT
    food_name,
    category,
    price
FROM
    example.lunch_menu
EXCEPT
SELECT
    food_name,
    category,
    price
FROM
    example.dinner_menu

WITH [RECURSIVE]
	<cte definition>[,...]
<select statement>

WITH
    dept2_emp_sal_by_mgr (manager_id, sal) AS
    (
        SELECT manager_id, salary
        FROM example.employee
        WHERE dept_id = 2
    )
SELECT
    manager_id dept2_mgr_id,
    MAX(sal) dept2_highest_emp_sal_per_mgr,
    COUNT(*) as dept2_total_emp_per_mgr
FROM dept2_emp_sal_by_mgr
GROUP BY manager_id

INSERT INTO example.dept2_emp_mgr_roster (emp_first_name, emp_last_name, mgr_first_name, mgr_last_name)
WITH
    dept2_emp AS
    (
        SELECT first_name, last_name, manager_id
        FROM example.employee
        WHERE dept_id = 2
    ),
    dept2_mgr AS
    (
        SELECT first_name, last_name, id
        FROM example.employee
        WHERE dept_id = 2
    )
SELECT d2emp.first_name, d2emp.last_name, d2mgr.first_name, d2mgr.last_name
FROM
    dept2_emp as d2emp
    JOIN dept2_mgr as d2mgr ON d2emp.manager_id = d2mgr.id

WITH RECURSIVE
	<cte name> AS
	(
    	<non-recursive query>
    	UNION [ALL]
    	<recursive query>
    )
<select statement>

WITH RECURSIVE subordinates AS
(
    SELECT
        id,
        manager_id,
        first_name,
        last_name
    FROM
        example.employee
    WHERE
        id = 6
    UNION ALL
    SELECT
        e.id,
        e.manager_id,
        e.first_name,
        e.last_name
    FROM
        example.employee e
        INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT *
FROM subordinates
ORDER BY manager_id, id

WITH RECURSIVE subordinates AS
(
    SELECT
        id,
        manager_id,
        first_name,
        last_name,
        JSON_ARRAY(first_name || ',' || last_name) AS hierarchy
    FROM
        example.employee
    WHERE
        id = 1
    UNION ALL
    SELECT
        e.id,
        e.manager_id,
        e.first_name,
        e.last_name,
        JSON_ARRAY_APPEND(s.hierarchy, e.first_name || ',' || e.last_name) AS hierarchy
    FROM
        example.employee e
        INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT *
FROM subordinates
ORDER BY manager_id, id

SELECT *
FROM table, ITER
WHERE ITER.i < <column expression>

SELECT id, word, i, SUBSTR(word, i + 1, 1) AS letter
FROM example.dictionary
JOIN ITER ON i < LENGTH(word)
ORDER BY id, i

SELECT i, id, word
FROM example.dictionary, ITER
WHERE i < 5
ORDER BY id, i

INSERT INTO example.numeric_types (int_type1, int_type2, float_type1, float_type2)
VALUES
(
    1,
    '2',
    3.4,
    '5.6'
)

INSERT INTO example.string_types (varchar_type, charn_type, ipv4_type, wkt_type)
VALUES
(
    'varchar value',
    'charN value',
    '12.34.56.78',
    'POINT(0 0)'
)

INSERT INTO example.byte_types (bytes_type)
VALUES
    (12345678901234567890),
    ('12345678901234567890'),
    ('0x00AB54A98CEB1F0AD2')

INSERT INTO example.date_time_types (date_type, time_type, datetime_type, timestamp_type)
VALUES
(
    '2000-01-02',
    '12:34:56.789',
    '2000-01-02 12:34:56.789',
    '2000-01-02 12:34:56.789'
),
(
    '2000-1-02',
    '1:23:45.678',
    '2000-01-02T12:34:56.789Z',
    '2000-1-02 1:23:45.678'
)

INSERT INTO example.date_time_types (date_type, time_type, datetime_type, timestamp_type)
VALUES
(
    DATE '2000-01-02',
    TIME '12:34:56.789',
    TIMESTAMP '2000-01-02 12:34:56.789',
    TIMESTAMP '2000-01-02 12:34:56.789'
)

INSERT INTO example.date_time_types (date_type, time_type, datetime_type, timestamp_type)
VALUES
(
    {d '2000-01-02'},
    {t '12:34:56.789'},
    {ts '2000-01-02 12:34:56.789'},
    {ts '2000-01-02 12:34:56.789'}
)

CASE
    WHEN <cond_expr_a> THEN <value_expr_a>
    ...
    WHEN <cond_expr_n> THEN <value_expr_n>
    ELSE <value_expr>
END

CASE <expr>
    WHEN <match_expr_a> THEN <value_expr_a>
    ...
    WHEN <match_expr_n> THEN <value_expr_n>
    ELSE <value_expr>
END

CASE
    WHEN color = 1 THEN 'Red'
    WHEN color >= 2 THEN 'Green'
    ELSE 'Blue'
END

CASE MOD(LENGTH(text), 2)
    WHEN 0 THEN 'Even'
    WHEN 1 THEN 'Odd'
    ELSE null
END