SQL intro

intro.sql

-- SQL: Structured Query Language for communicating with relational (tabular) databases
-- RDBMS: Relational Database Management Systems
-- CRUD: Create, Read, Update, and Delete
-- Databases contain tables, plus potentially Views and Queries too

-- Once connected to the server database, the following example snippets work

------------------
-- Querying data -
------------------

-- All columns and rows from a table
SELECT
    *
FROM
    nspl24feb

-- Certain columns and all rows from a table
SELECT
    forename AS first_name,
    last_name
FROM
    my_table

-- Conditionals: AND operator to specifiy multiple conditions
SELECT
    *
FROM
    my_table
WHERE
    height>=170 AND age>=30

-- Conditional: = operator --> exact match
SELECT
    *
FROM
    my_table
WHERE
    last_name='Smith'

-- Conditional: LIKE operator --> exact match
SELECT
    *
FROM
    my_table
WHERE
    last_name LIKE 'Smith'

-- Conditional: % operator as N-characters wildcard --> any starting or ending with Smith (Smith, Smithson, McSmith, etc)
SELECT
    *
FROM
    my_table
WHERE
    last_name LIKE '%Smith%'

-- Conditional: % operator as N-characters wildcard --> any starting with A & ending with n (Aaron, Arton, etc)
SELECT
    *
FROM
    my_table
WHERE
    last_name LIKE 'A%n'

-- Conditional: _ operator as a single-character wildcard (Tim, Tom, etc)
SELECT
    *
FROM
    my_table
WHERE
    last_name LIKE 'T_m'

-- Conditional: exact match from list
SELECT
    *
FROM
    my_table
WHERE
    last_name IN ('Sally', 'Ang', 'Bart')

-- Conditional: between operator
SELECT
    *
FROM
    my_table
WHERE
    age BETWEEN 30 AND 50

-- Conditional: IS null, IS NOT null
SELECT
    *
FROM
    my_table
WHERE
    age IS null

-- Order (asc by default)
SELECT
    *
FROM
    my_table
ORDER BY
    age

-- Order (desc)
SELECT
    *
FROM
    my_table
ORDER BY
    age DESC

-- Join
SELECT
    my_table_1.name_column,
    my_table_1.age,
    my_table_2.weight
FROM
    my_table_1
    INNER JOIN my_table_2 ON my_table_1.name_column=my_table_2.name_column

-- Join with aliases (a and b)
SELECT
    a.name_column,
    a.age,
    b.weight
FROM
    my_table_1 a
    INNER JOIN my_table_2 b ON a.name_column=b.name_column

-- Aggregation: group by sum, count, avg
SELECT
    a.full_name,
    a.age,
    b.height,
    sum(b.wage) AS total_wages,
    count(b.wage) AS qty_wages,
    avg(b.wage) AS avg_wage
FROM
    my_table_1 a
    INNER JOIN my_table_2 b ON a.full_name=b.full_name
GROUP BY
    a.full_name
HAVING
    total_wages > 10000
ORDER BY
    total_wages DESC