Code sample · SQL

Schema → analytics → security

A self-contained relational database built from scratch. It walks through the full lifecycle, designing the schema, manipulating data, writing analytical queries, tuning for performance, generating reports, and locking it down with roles and access control. The point is to show how I think in SQL, not just that I know the syntax.

Design & creation

A normalized schema for a music store: artists, albums, tracks, customers, orders, and reviews, with foreign keys enforcing referential integrity.

-- Create tables
CREATE TABLE Artists (
    ArtistID INT PRIMARY KEY,
    ArtistName VARCHAR(100) NOT NULL,
    Genre VARCHAR(50)
);

CREATE TABLE Albums (
    AlbumID INT PRIMARY KEY,
    AlbumTitle VARCHAR(100) NOT NULL,
    ReleaseYear INT,
    ArtistID INT,
    CONSTRAINT fk_artist FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
);

CREATE TABLE Tracks (
    TrackID INT PRIMARY KEY,
    TrackTitle VARCHAR(100) NOT NULL,
    Duration TIME,
    AlbumID INT,
    TrackNumber INT,
    Lyrics TEXT,
    CONSTRAINT fk_album FOREIGN KEY (AlbumID) REFERENCES Albums(AlbumID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Birthdate DATE
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE DEFAULT CURRENT_DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    TrackID INT,
    Quantity INT,
    UnitPrice DECIMAL(6, 2),
    CONSTRAINT fk_order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    CONSTRAINT fk_track FOREIGN KEY (TrackID) REFERENCES Tracks(TrackID)
);

CREATE TABLE Reviews (
    ReviewID INT PRIMARY KEY,
    TrackID INT,
    CustomerID INT,
    Rating INT CHECK (Rating >= 1 AND Rating <= 5),
    ReviewText TEXT,
    CONSTRAINT fk_review_track FOREIGN KEY (TrackID) REFERENCES Tracks(TrackID),
    CONSTRAINT fk_review_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Data manipulation

Inserting records and retrieving data across joins, the everyday operations the schema needs to support cleanly.

-- Insert a new artist
INSERT INTO Artists (ArtistID, ArtistName, Genre)
VALUES (1, 'The Exclusive', 'indie-pop');

-- Insert a new customer
INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Birthdate)
VALUES (1, 'Shane', 'Draper', 'shane@example.com', '1990-05-15');

-- Retrieve all tracks from a specific album
SELECT TrackTitle, Duration
FROM Tracks
WHERE AlbumID = 1;

-- Retrieve customers who placed orders
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

-- Calculate the total order amount for a specific customer
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM Orders
WHERE CustomerID = 1
GROUP BY CustomerID;

-- Retrieve customer info with the track and album they ordered
SELECT c.FirstName, c.LastName, t.TrackTitle, a.AlbumTitle
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Tracks t ON oi.TrackID = t.TrackID
JOIN Albums a ON t.AlbumID = a.AlbumID
WHERE c.CustomerID = 1;

Complex queries

Multi-table joins and aggregation to answer questions the business would actually ask.

-- Albums and their average ratings
SELECT a.AlbumTitle, AVG(r.Rating) AS AvgRating
FROM Albums a
LEFT JOIN Tracks t ON a.AlbumID = t.AlbumID
LEFT JOIN Reviews r ON t.TrackID = r.TrackID
GROUP BY a.AlbumID, a.AlbumTitle
ORDER BY AvgRating DESC;

-- Customers who purchased albums from a specific genre
SELECT DISTINCT c.FirstName, c.LastName
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Tracks t ON oi.TrackID = t.TrackID
JOIN Albums a ON t.AlbumID = a.AlbumID
JOIN Artists ar ON a.ArtistID = ar.ArtistID
WHERE ar.Genre = 'Rock';

-- Most popular tracks by total number of orders
SELECT t.TrackTitle, COUNT(oi.OrderID) AS NumOrders
FROM Tracks t
LEFT JOIN OrderItems oi ON t.TrackID = oi.TrackID
GROUP BY t.TrackID, t.TrackTitle
ORDER BY NumOrders DESC
LIMIT 5;

-- Customers who haven't made any orders
SELECT c.FirstName, c.LastName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

Performance optimization

Indexing hot lookup paths, normalizing genre into its own table, and partitioning orders by year so the system stays fast as data grows.

-- Index the Albums table
CREATE INDEX idx_album_artist ON Albums (ArtistID);
CREATE INDEX idx_album_title ON Albums (AlbumTitle);

-- Index the Tracks table
CREATE INDEX idx_track_album ON Tracks (AlbumID);
CREATE INDEX idx_track_title ON Tracks (TrackTitle);

-- Normalize genre into a separate table
CREATE TABLE Genres (
    GenreID INT PRIMARY KEY,
    GenreName VARCHAR(50) NOT NULL
);

CREATE TABLE Artists (
    ArtistID INT PRIMARY KEY,
    ArtistName VARCHAR(100) NOT NULL,
    GenreID INT,
    FOREIGN KEY (GenreID) REFERENCES Genres(GenreID)
);

-- Partition the Orders table by year
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(OrderDate)) (
    PARTITION p_2010 VALUES LESS THAN (2011),
    PARTITION p_2011 VALUES LESS THAN (2012),
    PARTITION p_2012 VALUES LESS THAN (2013)
);

Reporting queries

Aggregations that turn transactional data into the numbers leadership cares about, revenue by genre and order trends over time.

-- Total sales revenue by genre
SELECT ar.Genre, SUM(oi.Quantity * oi.UnitPrice) AS TotalRevenue
FROM Artists ar
JOIN Albums a ON ar.ArtistID = a.ArtistID
JOIN Tracks t ON a.AlbumID = t.AlbumID
JOIN OrderItems oi ON t.TrackID = oi.TrackID
GROUP BY ar.Genre
ORDER BY TotalRevenue DESC;

-- Average order amount and number of orders per year
SELECT YEAR(o.OrderDate) AS Year,
       AVG(o.TotalAmount) AS AvgOrderAmount,
       COUNT(o.OrderID) AS NumOrders
FROM Orders o
GROUP BY Year
ORDER BY Year;

Subqueries & aggregation

Nested queries for higher-order questions: top spenders, customer lifetime value, and artists who span multiple genres.

-- Customer with the highest total amount spent
SELECT c.FirstName, c.LastName, MAX(TotalAmountSpent) AS MaxAmountSpent
FROM (
    SELECT c.CustomerID, SUM(o.TotalAmount) AS TotalAmountSpent
    FROM Customers c
    JOIN Orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID
) AS CustomerTotalAmount
JOIN Customers c ON CustomerTotalAmount.CustomerID = c.CustomerID;

-- Customer lifetime value based on average order amount
SELECT c.FirstName, c.LastName,
       AVG(o.TotalAmount) * COUNT(o.OrderID) AS CLV
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName;

-- Artists with albums in different genres
SELECT ar.ArtistName, GROUP_CONCAT(DISTINCT a.Genre) AS DifferentGenres
FROM Artists ar
JOIN Albums a ON ar.ArtistID = a.ArtistID
GROUP BY ar.ArtistName
HAVING COUNT(DISTINCT a.Genre) > 1;

Stored procedures

Encapsulating frequently used logic into procedures and functions for maintainability and reuse.

-- Calculate total revenue for an artist
DELIMITER //
CREATE PROCEDURE CalculateArtistRevenue(IN artistID INT)
BEGIN
    DECLARE totalRevenue DECIMAL(10, 2);

    SELECT SUM(oi.Quantity * oi.UnitPrice) INTO totalRevenue
    FROM OrderItems oi
    JOIN Tracks t ON oi.TrackID = t.TrackID
    JOIN Albums a ON t.AlbumID = a.AlbumID
    WHERE a.ArtistID = artistID;

    SELECT totalRevenue;
END //
DELIMITER ;

CALL CalculateArtistRevenue(1);

-- Update track prices (increase by 10%)
DELIMITER //
CREATE PROCEDURE UpdateTrackPrices()
BEGIN
    UPDATE Tracks
    SET UnitPrice = UnitPrice * 1.1;
END //
DELIMITER ;

-- Function: number of reviews for a customer
DELIMITER //
CREATE FUNCTION GetNumReviewsForCustomer(customerID INT) RETURNS INT
BEGIN
    DECLARE numReviews INT;

    SELECT COUNT(*) INTO numReviews
    FROM Reviews
    WHERE CustomerID = customerID;

    RETURN numReviews;
END //
DELIMITER ;

SELECT FirstName, LastName, GetNumReviewsForCustomer(CustomerID) AS NumReviews
FROM Customers;

Security & access control

Roles, granular permissions, row-level security, and password policy, because a database is only as trustworthy as its access model.

-- Roles for different types of users
CREATE ROLE admin;
CREATE ROLE employee;
CREATE ROLE customer;

-- Grant permissions to roles
GRANT SELECT, INSERT, UPDATE, DELETE ON Artists TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON Albums TO admin;
GRANT SELECT ON Tracks TO admin, employee, customer;
GRANT SELECT ON Customers TO admin, employee, customer;
GRANT SELECT ON Orders TO admin, employee, customer;
GRANT SELECT ON OrderItems TO admin, employee, customer;

-- Create users and assign roles
CREATE USER 'admin_user' IDENTIFIED BY 'admin_password';
GRANT admin TO 'admin_user';

CREATE USER 'employee_user' IDENTIFIED BY 'employee_password';
GRANT employee TO 'employee_user';

-- Row-level access control for customers
CREATE POLICY customer_order_policy
  ON Orders
  USING (CustomerID = current_setting('app.current_customer_id')::INT);

ALTER TABLE Orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE Orders FORCE ROW LEVEL SECURITY;

-- Enforce strong password policy
ALTER USER 'admin_user' PASSWORD EXPIRE;

Back to Tech