In this blog we will covers all query related to SQLite database, Below the details of database tables:
albums are the albums our store is selling
artists are all the artists
tracks are all the tracks, containing a column that tells us which album the track belongs to.
media_types tell us what format a track is in, e.g., MP3, Apple Audio, etc.
genres are a list of genres.
playlists are, well, playlists. A play list has many tracks and a single track can belong to many playlists.
playlist_tracks tells us which tracks belong to which playlists
customers are our customer records and they may or may not have an assigned "customer support representative"
invoices are a list of specific customer orders
invoice_lines are the line-items on specific invoices
employees contains information about our company's employees. This is mostly used to assign support representatives to customers.
Creating Tables:
CREATE TABLE playlist_tracks
(
playlist_id INTEGER NOT NULL,
track_id INTEGER NOT NULL,
CONSTRAINT PK_PlaylistTrack PRIMARY KEY (playlist_id, track_id),
FOREIGN KEY (playlist_id) REFERENCES playlists (id)
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (track_id) REFERENCES tracks (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE playlists
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(120)
)
CREATE TABLE invoice_lines
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
invoice_id INTEGER NOT NULL,
track_id INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
quantity INTEGER NOT NULL,
FOREIGN KEY (invoice_id) REFERENCES invoices (id)
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (track_id) REFERENCES tracks (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE tracks
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(200) NOT NULL,
album_id INTEGER,
media_type_id INTEGER NOT NULL,
genre_id INTEGER,
composer VARCHAR(220),
milliseconds INTEGER NOT NULL,
bytes INTEGER,
unit_price NUMERIC(10,2) NOT NULL,
FOREIGN KEY (album_id) REFERENCES albums (id)
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (genre_id) REFERENCES genres (id)
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (media_type_id) REFERENCES media_types (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE media_types
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(120)
)
CREATE TABLE invoices
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
invoice_date TIMESTAMP NOT NULL,
billing_address VARCHAR(70),
billing_city VARCHAR(40),
billing_state VARCHAR(40),
billing_country VARCHAR(40),
billing_postal_code VARCHAR(10),
total NUMERIC(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE genres
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(120)
)
CREATE TABLE customers
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(20) NOT NULL,
company VARCHAR(80),
address VARCHAR(70),
city VARCHAR(40),
state VARCHAR(40),
country VARCHAR(40),
postal_code VARCHAR(10),
phone VARCHAR(24),
fax VARCHAR(24),
email VARCHAR(60) NOT NULL,
support_rep_id INTEGER,
FOREIGN KEY (support_rep_id) REFERENCES employees (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE employees
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
title VARCHAR(30),
reports_to INTEGER,
birth_date TIMESTAMP,
hire_date TIMESTAMP,
address VARCHAR(70),
city VARCHAR(40),
state VARCHAR(40),
country VARCHAR(40),
postal_code VARCHAR(10),
phone VARCHAR(24),
fax VARCHAR(24),
email VARCHAR(60),
FOREIGN KEY (reports_to) REFERENCES employees (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE albums
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(160) NOT NULL,
artist_id INTEGER NOT NULL,
FOREIGN KEY (artist_id) REFERENCES artists (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE artists
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(120)
)
Working With Queries:
-- Every album
SELECT*FROM albums;
-- Every album title (and only the title)
SELECT title FROM albums;
-- Every album ordered by album title in ascending order (A-Z)-- Note: ORDER BY orders by lowest-to-highest by default
SELECT*FROM albums ORDER BY title;
-- Every album ordered by album title in descending order (Z-A)
SELECT*FROM albums ORDER BY title DESC;
-- Every album whose title starts with A in alphabetical order
SELECT*FROM albums WHERE title LIKE'A%'ORDER BY title;
-- "invoices" table-- Remember: run ".schema invoices" to see the structure of the "invoices" table-- Every invoice-- Every invoice ordered by total invoice amount ("total")-- Every invoice with a total greater than 10-- The 10 least expensive invoices-- Remember: ORDER BY orders from lowest-to-highest by default
SELECT*FROM invoices ORDER BY total LIMIT10;
-- The 10 most expensive invoices-- The 15 most recent invoices-- The 15 oldest invoices-- The 10 most expensive invoices from the US-- The 10 least expensive invoices from the US-- The 10 most expensive invoices from outside the US-- Hint: If "=" means equal, use "!=" to mean "not equal"-- Every invoice from Chicago, IL
SELECT*FROM invoices WHERE billing_city ="Chicago"AND billing_state ="IL"AND billing_country="USA";
-- A list of all the invoices worth more than $5.00 from Chicago, IL-- The billing addresses of the 5 most valuable invoices from Mountain View CA-- Gotta reward those big spenders!-- A list of the 10 most valuable invoices made before January 1st, 2010-- Hint: Dates are formatted like 'YYYY-MM-DD' and you can compare them using '<', '>', '<=' and '>='-- The number of invoices from Chicago, IL
SELECTCOUNT(*) FROM invoices WHERE billing_city ="Chicago"AND billing_state ="IL"AND billing_country ="USA";
-- The number of invoices from the US, grouped by state
SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country ="USA"GROUP BY billing_state;
-- The state in the US with the most invoices
SELECT billing_state, COUNT(*) FROM invoices WHERE billing_country ="USA"GROUP BY billing_state ORDER BYCOUNT(*) DESCLIMIT1;
-- The total invoice value from California
SELECT billing_state, SUM(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";
-- The number of invoices and the invoice total from California
SELECT billing_state, COUNT(*), SUM(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";
-- The count, total, and average of invoice totals from California
SELECT billing_state, COUNT(*), SUM(total), AVG(total) FROM invoices WHERE billing_country ="USA"and billing_state ="CA";
-- The count, total, and average of invoice totals, grouped by state, ordered by average invoice total from highest-to-lowest
-- A list of the top 5 countries by number of invoices
-- A list of the top 5 countries by gross/total invoice size-- A list of the top 5 countries by average invoice size
Comments