SQL Live 05

SQL Code

SELECT
invoicedate,
-- STRing Format (Date)Time
STRFTIME("%Y", invoicedate) AS year,
STRFTIME("%m", invoicedate) AS month,
STRFTIME("%d", invoicedate) AS day,
STRFTIME("%Y-%m", invoicedate) AS monthid
FROM invoices
WHERE monthid = "2009-09"

Create table eu_customers

CREATE TABLE eu_customers AS

SELECT firstname, country, email
FROM customers
WHERE country IN ('Belgium','France','Italy');

DROP TABLE eu_customers;

Aggregate Functions

-- aggregate functions
SELECT
COUNT(*) AS total_songs,
ROUND(AVG(bytes),2) AS avg_bytes,
ROUND(SUM(bytes/(1024*1024)),2) AS sum_mb,
MIN(bytes) AS min_bytes,
MAX(bytes) AS max_bytes
FROM tracks;

Clean NULL values

SELECT
company,
COALESCE(company, "B2C") AS clean_company,

SQL Live 05 1
 CASE
WHEN company IS NULL THEN "B2C"
ELSE "B2B"
END AS segment
FROM customers;

Having vs. Where

SELECT
CASE
WHEN company IS NULL THEN "B2C"
ELSE "B2B"
END AS segment,
country,
COUNT(*) AS num_customers
FROM customers
WHERE country IN ("Belgium", "France", "Italy")
GROUP BY 1,2
HAVING num_customers > 1

JOIN Tables

-- join syntax
SELECT
[Link] AS artist_name,
[Link] AS album_name,
[Link] AS track_name
FROM artists AS ar
INNER JOIN albums AS al
ON [Link] = [Link] -- pk=fk
INNER JOIN tracks AS tr
ON [Link] = [Link];

Aggregate + JOIN

-- join syntax
-- virtual table (VIEW)
CREATE VIEW genre_stats AS
SELECT
[Link],
COUNT(*) as count_tracks,
AVG(milliseconds) AS avg_milliseconds

SQL Live 05 2
 FROM artists AS ar
JOIN albums AS al ON [Link] = [Link]
JOIN tracks AS tr ON [Link] = [Link]
JOIN genres AS ge ON [Link] = [Link]
GROUP BY 1
ORDER BY 3 DESC
LIMIT 5;

Subqueries (Select ซ้อน Select)

breakdown our long query into steps

-- subqueries
SELECT firstname, country
FROM (SELECT * FROM customers) AS sub
WHERE country = 'United Kingdom';

-- WITH : common table expression

WITH sub AS (SELECT * FROM customers)

SELECT firstname, country

FROM sub
WHERE country = 'United Kingdom';

Example subqueries + with clause

😛 Query American customers who purchase our products in 2009-10 (invoices)

-- basic query
SELECT
firstname,
lastname,
email,
COUNT(*) count_order
FROM customers c
JOIN invoices i ON [Link] = [Link]
WHERE [Link] = 'USA' AND STRFTIME("%Y-%m",[Link]) = "2009-10"
GROUP BY 1,2,3;

-- with clauses
WITH usa_customers AS (
SELECT * FROM customers

SQL Live 05 3
 WHERE country = 'USA'
), invoice_2009 AS (
SELECT * FROM invoices
WHERE STRFTIME("%Y-%m",invoicedate) = "2009-10"
)

SELECT firstname, lastname, email, COUNT(*)

FROM usa_customers t1
JOIN invoice_2009 t2
ON [Link] = [Link]
GROUP BY 1,2,3;

-- standard subqueries
SELECT firstname, lastname, email, COUNT(*)
FROM (
SELECT * FROM customers
WHERE country = 'USA'
) AS t1
JOIN (
SELECT * FROM invoices
WHERE STRFTIME("%Y-%m",invoicedate) = "2009-10"
) AS t2
ON [Link] = [Link]
GROUP BY 1,2,3;

SQL Live 05 4