Basic SQL Tutorial

SELECT * FROM read_csv('IN.txt', header = false, delim = ',');
-- columns: column0, column1, column2, ...   
-- delim not needed for , ; | and \t.

COPY (
SELECT species, island, sex, year
FROM 'penguins.csv')
TO 'penguins.parquet' (FORMAT PARQUET);

COPY (
SELECT column00 as "ID", column01 as "Name", column08 as "Admin"
FROM 'IN.txt')
TO 'cities.csv' (HEADER true, DELIMITER ',');
-- Use DELIMITER '|' or '\t' for pipe or tab separated values.

INSTALL excel;
LOAD excel;

COPY (
SELECT species, island, bill_length_mm
FROM 'penguins.csv') 
TO 'penguins.xlsx' (FORMAT xlsx, HEADER true, SHEET 'Penguins1');   

COPY (
SELECT species, island, sex, year
FROM 'penguins.csv') 
TO 'penguins.xlsx' (FORMAT xlsx, HEADER true, SHEET 'Penguins2', MODE 'append'); 

-- New table with constraints
CREATE TABLE IF NOT EXISTS review (
    review_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id    INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    rating      INTEGER CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT,
    created_at  TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (track_id) REFERENCES track(track_id),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- A useful view
CREATE VIEW artist_track_count AS
SELECT a.artist_id, a.name, COUNT(t.track_id) AS track_count
FROM artist a
JOIN album al ON a.artist_id = al.artist_id
JOIN track t  ON al.album_id = t.album_id
GROUP BY a.artist_id;   

-- Insert multiple rows
INSERT INTO review (track_id, customer_id, rating, comment) 
VALUES (2, 2, 4, 'Great opener'), 
       (3, 1, 5, NULL), 
       (4, 3, 3, 'A bit long');
       
-- From a query 
INSERT INTO artist_track_count_snapshot (artist_id, name, track_count) 
SELECT artist_id, name, track_count 
FROM artist_track_count 
WHERE track_count > 10;

-- Update specific rows
UPDATE customer
SET country = 'USA'
WHERE country = 'United States';

-- Update with a subquery
UPDATE invoice
SET total = (
    SELECT SUM(unit_price * quantity)
    FROM invoiceline
    WHERE invoiceline.invoice_id = invoice.invoice_id
)
WHERE invoice_id IN (1, 2, 3);

-- Bulk update
UPDATE track
SET unit_price = unit_price * 1.10
WHERE genre_id = 1;  -- 10% price increase for Rock   

-- Delete specific rows
DELETE FROM review
WHERE rating = 1 AND comment IS NULL;

-- Delete with a subquery (orphan cleanup)
DELETE FROM playlisttrack
WHERE track_id NOT IN (SELECT track_id FROM track);

-- Delete all rows (see TRUNCATE below)
DELETE FROM review;   

-- Add a column
ALTER TABLE customer
ADD COLUMN loyalty_points INTEGER DEFAULT 0;

-- Rename a table
ALTER TABLE review RENAME TO track_review;

-- Rename a column (SQLite 3.25+)
ALTER TABLE track_review
RENAME COLUMN comment TO review_comment;

-- Drop a column (SQLite 3.35+)
ALTER TABLE track_review
DROP COLUMN review_comment;   

TRUNCATE track_review;
-- or
TRUNCATE TABLE track_review;   

-- Drop a table (and its data, indexes, triggers)
DROP TABLE IF EXISTS review;

-- Drop a view
DROP VIEW IF EXISTS artist_track_count;

-- Drop an index
DROP INDEX IF EXISTS idx_track_genre;   

SELECT 
    species, island, COUNT(*) AS penguin_count, AVG(body_mass_g) AS avg_mass_g
FROM 
    penguins
WHERE 
    body_mass_g IS NOT NULL
GROUP BY 
    species, island
HAVING 
    COUNT(*) > 20
ORDER BY 
    avg_mass_g DESC;   

SELECT a.Name AS Artist, al.Title AS Album, t.Name AS Track
FROM Artist a
INNER JOIN Album al ON a.ArtistId = al.ArtistId
INNER JOIN Track t ON al.AlbumId = t.AlbumId
WHERE a.Name = 'AC/DC';   

SELECT a.Name AS Artist, al.Title AS Album
FROM Artist a
LEFT JOIN Album al ON a.ArtistId = al.ArtistId
WHERE al.AlbumId IS NULL;   

SELECT g.Name AS Genre, t.Name AS Track
FROM Genre g
FULL OUTER JOIN Track t ON g.GenreId = t.GenreId
WHERE g.GenreId IS NULL OR t.GenreId IS NULL;   

SELECT c.FirstName, c.LastName, g.Name AS Genre
FROM Customer c
CROSS JOIN Genre g;   

SELECT e.FirstName || ' ' || e.LastName AS Employee,
       m.FirstName || ' ' || m.LastName AS Manager
FROM Employee e
LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId;   

-- Find all tracks in the "Rock" or "Metal" genres
SELECT t.Name, g.Name AS Genre
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name IN ('Rock', 'Metal', 'Jazz');   

-- Find customers who have placed an invoice
SELECT c.FirstName, c.LastName
FROM Customer c
WHERE c.CustomerId IN (SELECT DISTINCT CustomerId FROM Invoice);   

-- Tracks priced higher than ANY track in the "Jazz" genre
-- (i.e., more expensive than at least one Jazz track)
SELECT t.Name, t.UnitPrice, g.Name AS Genre
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
WHERE t.UnitPrice > ANY (
    SELECT t2.UnitPrice
    FROM Track t2
    JOIN Genre g2 ON t2.GenreId = g2.GenreId
    WHERE g2.Name = 'Jazz'
)
ORDER BY t.UnitPrice DESC;   


-- Employees hired later than ANY employee in the "Sales" department
SELECT e.FirstName, e.LastName, e.HireDate
FROM Employee e
WHERE e.HireDate > ANY (
    SELECT e2.HireDate
    FROM Employee e2
    WHERE e2.Title = 'Sales Agent'
);   

-- Tracks priced higher than ALL tracks in the "Jazz" genre
-- (i.e., more expensive than every single Jazz track)
SELECT t.Name, t.UnitPrice, g.Name AS Genre
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
WHERE t.UnitPrice > ALL (
    SELECT t2.UnitPrice
    FROM Track t2
    JOIN Genre g2 ON t2.GenreId = g2.GenreId
    WHERE g2.Name = 'Jazz'
)
ORDER BY t.UnitPrice DESC;   

-- Customers who live in a city that ALL employees also live in
SELECT c.FirstName, c.LastName, c.City
FROM Customer c
WHERE c.City <> ALL (
    SELECT DISTINCT e.City
    FROM Employee e
);
-- Returns customers NOT living in any employee's city   

-- Build a full name
SELECT
    CONCAT(FirstName, ' ', LastName) AS Full_Name
FROM Customer
LIMIT 5;

-- With a separator (MySQL / DuckDB)
SELECT
    CONCAT_WS(' - ', FirstName, LastName, City) AS Customer_Info
FROM Customer
LIMIT 5;   

-- Extract the first 3 characters of a track name (e.g., "Boh" from "Bohemian Rhapsody")
SELECT
    Name,
    SUBSTRING(Name, 1, 3) AS First_3_Chars
FROM Track
LIMIT 5;
-- SUBSTRING(string, start, length)

-- Extract characters from position 5 onward (skip first 4)
SELECT
    Name,
    SUBSTRING(Name, 5) AS From_5th_Char
FROM Track
WHERE Name = 'Bohemian Rhapsody';
-- Result: 'mian Rhapsody'   

-- Standardize artist names to uppercase
SELECT
    Name AS Original,
    UPPER(Name) AS Uppercase,
    LOWER(Name) AS Lowercase
FROM Artist
WHERE Name IN ('AC/DC', 'Queen', 'Metallica');   

-- Trim spaces from both sides
SELECT
    '  Bohemian Rhapsody  ' AS Original,
    TRIM('  Bohemian Rhapsody  ') AS Trimmed,
    LTRIM('  Bohemian Rhapsody  ') AS Left_Trimmed,
    RTRIM('  Bohemian Rhapsody  ') AS Right_Trimmed;    


-- Remove spaces from customer city names before grouping
SELECT
    TRIM(City) AS Clean_City,
    COUNT(*) AS Customer_Count
FROM Customer
GROUP BY TRIM(City)
ORDER BY Customer_Count DESC;   


-- Remove leading/trailing dashes from a track name
SELECT
    '---Bohemian Rhapsody---' AS Original,
    TRIM('---Bohemian Rhapsody---', '-') AS Trimmed;
-- Result: 'Bohemian Rhapsody'   

-- First 3 characters of a track name
SELECT
    Name,
    LEFT(Name, 3) AS First_3
FROM Track
WHERE Name IN ('Bohemian Rhapsody', 'Thunderstruck', 'Stairway to Heaven');   


-- Last 4 characters (e.g., file extension or ending)
SELECT
    Name,
    RIGHT(Name, 4) AS Last_4
FROM Track
WHERE Name IN ('Bohemian Rhapsody', 'Thunderstruck', 'Stairway to Heaven');   


SELECT
    Country,
    LEFT(Country, 2) AS Code
FROM Customer
GROUP BY Country
ORDER BY Country;   

-- Replace a substring within artist names
SELECT
    Name AS Original,
    REPLACE(Name, '/', '-') AS Modified
FROM Artist
WHERE Name LIKE '%/%';   


-- Replace all spaces with underscores (slug-style)
SELECT
    Name,
    REPLACE(Name, ' ', '_') AS Slug
FROM Track
WHERE Name IN ('Bohemian Rhapsody', 'Stairway to Heaven');   


-- Remove all occurrences of a character
SELECT
    Name,
    REPLACE(Name, 'e', '') AS No_E
FROM Artist
WHERE Name = 'Queen';
-- Result: 'Qun'   

-- Track names starting with "Bo"
SELECT Name
FROM Track
WHERE Name LIKE 'Bo%';
-- Bohemian Rhapsody, Boston, ...   


-- Track names ending with "s"
SELECT Name
FROM Track
WHERE Name LIKE '%s';   


-- Track names containing "love"
SELECT Name
FROM Track
WHERE Name LIKE '%love%';   


-- Exactly 5 characters long
SELECT Name
FROM Track
WHERE Name LIKE '_____'
LIMIT 5;   


-- Artist names where the 3rd character is a space
SELECT Name
FROM Artist
WHERE Name LIKE '__ %';   


-- All tracks NOT starting with "A"
SELECT Name
FROM Track
WHERE Name NOT LIKE 'A%';   

SELECT
    Name,
    CASE GenreId
        WHEN 1  THEN 'Rock'
        WHEN 2  THEN 'Blues'
        WHEN 17 THEN 'Jazz'
        ELSE 'Other'
    END AS Genre_Name
FROM Track
LIMIT 10;   


SELECT
    Name,
    UnitPrice,
    CASE
        WHEN UnitPrice < 0.50  THEN 'Budget'
        WHEN UnitPrice < 1.00  THEN 'Standard'
        ELSE 'Premium'
    END AS Price_Tier
FROM Track;   


SELECT
    CustomerId,
    FirstName,
    CASE
        WHEN Email IS NULL THEN 'No Email'
        WHEN Country IS NULL THEN 'No Country'
        ELSE 'Complete'
    END AS Data_Quality
FROM Customer;   

-- DuckDB / MySQL
SELECT
    Name,
    UnitPrice,
    IF(UnitPrice >= 1.00, 'Expensive', 'Cheap') AS Price_Label
FROM Track
LIMIT 10;   


-- With a NULL fallback (third argument)
SELECT
    FirstName,
    IF(Country IS NULL, 'Unknown', Country) AS Safe_Country
FROM Customer;   

-- Convert 0 to NULL (avoid division by zero)
SELECT
    CustomerId,
    NULLIF(COUNT(*), 0) AS Invoice_Count
FROM Invoice
GROUP BY CustomerId;   


-- Neutralize a "default" value
SELECT
    FirstName,
    NULLIF(City, 'Unknown') AS City
FROM Customer
WHERE City = 'Unknown';
-- Result: City column becomes NULL for those rows   


-- Average invoice amount per customer (avoid divide-by-zero)
SELECT
    c.FirstName,
    c.LastName,
    SUM(i.Total) / NULLIF(COUNT(i.InvoiceId), 0) AS Avg_Invoice
FROM Customer c
LEFT JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName;   

-- All unique cities from customers AND employees
SELECT City FROM Customer
UNION
SELECT City FROM Employee;   


-- With duplicates (UNION ALL)
SELECT City FROM Customer
UNION ALL
SELECT City FROM Employee;   

-- Cities where both customers AND employees live
SELECT City FROM Customer
INTERSECT
SELECT City FROM Employee;   


-- Artists who have tracks in both "Rock" and "Metal" genres
SELECT a.ArtistId
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name = 'Rock'
INTERSECT
SELECT a.ArtistId
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name = 'Metal';   

-- Customers who have never placed an invoice
SELECT CustomerId FROM Customer
EXCEPT
SELECT CustomerId FROM Invoice;   


-- Genres that have no tracks
SELECT GenreId FROM Genre
EXCEPT
SELECT DISTINCT GenreId FROM Track;   

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');
-- Result: 'third_value'   


SELECT
    EmployeeId,
    FirstName,
    LastName,
    COALESCE(Email, 'No Email') AS Email,
    COALESCE(Phone, 'No Phone') AS Phone
FROM Employee;   


-- Use the first available contact method
SELECT
    CustomerId,
    FirstName,
    COALESCE(Email, Phone, 'No Contact') AS Contact
FROM Customer;   


SELECT
    i.InvoiceId,
    i.Total,
    COALESCE(il.UnitPrice, 0) AS UnitPrice,
    COALESCE(il.Quantity, 0) * COALESCE(il.UnitPrice, 0) AS Line_Total
FROM Invoice i
LEFT JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId;   


SELECT
    FirstName,
    LastName,
    FirstName || ' ' || COALESCE(MiddleName, '') || ' ' || LastName AS Full_Name
FROM Employee;   


SELECT
    g.Name AS Genre,
    COUNT(t.TrackId) AS Track_Count,
    COALESCE(AVG(t.UnitPrice), 0) AS Avg_Price
FROM Genre g
LEFT JOIN Track t ON g.GenreId = t.GenreId
GROUP BY g.Name;   

COPY (
    WITH cleaned AS (
        SELECT
            ROW_NUMBER() OVER () AS observation_id,

            NULLIF(TRIM(species), 'NA') AS species,

            NULLIF(TRIM(island), 'NA') AS island,

            CAST(NULLIF(TRIM(bill_length_mm), 'NA') AS DOUBLE)
                AS bill_length_mm,

            CAST(NULLIF(TRIM(bill_depth_mm), 'NA') AS DOUBLE)
                AS bill_depth_mm,

            CAST(NULLIF(TRIM(flipper_length_mm), 'NA') AS INTEGER)
                AS flipper_length_mm,

            CAST(NULLIF(TRIM(body_mass_g), 'NA') AS INTEGER)
                AS body_mass_g,

            NULLIF(TRIM(sex), 'NA') AS sex,

            CAST(year AS INTEGER) AS year

        FROM 'Raw/penguins.csv'
    )

    SELECT
        observation_id,
        species,
        island,
        bill_length_mm,
        bill_depth_mm,
        flipper_length_mm,
        body_mass_g,
        sex,
        year,

        bill_length_mm / bill_depth_mm AS bill_ratio,

        CASE
            WHEN species IS NOT NULL
             AND island IS NOT NULL
             AND bill_length_mm IS NOT NULL
             AND bill_depth_mm IS NOT NULL
             AND flipper_length_mm IS NOT NULL
             AND body_mass_g IS NOT NULL
             AND sex IS NOT NULL
             AND year IS NOT NULL
            THEN TRUE
            ELSE FALSE
        END AS is_ok

    FROM cleaned
)
TO 'Processed/penguins.parquet'
(FORMAT PARQUET);