Dataset 1
The dataset consists of four tables: author, book, adaptation,
and book_review.
The first table shows the author data in the following columns:
id – The author’s unique ID within the database.
name – The author’s name.
birth_year – The year when that author was born.
death_year – The year when that author died (the field is empty if
they are still alive).
Here are the table’s first few rows:
id name birth_year death_year
1 Marcella Cole 1983 NULL
2 Lisa Mullins 1891 1950
3 Dennis Stokes 1935 1994
4 Randolph Vasquez 1957 2004
5 Daniel Branson 1965 1990
… … … …
The second table, book, shows details about books. The columns are:
id – The ID of a given book.
author_id – The ID of the author who wrote that book.
title – The book’s title.
publish_year – The year when the book was published.
publishing_house – The name of the publishing house that printed
the book.
rating – The average rating for the book.
These are the first five rows:
author_i publish_ye publishing_hou ratin
id title
d ar se g
Soulles
1 NULL 2008 Golden Albatros 4.3
s girl
Weak
2 NULL 1980 Diarmud Inc. 3.8
Heart
Faith
White Cloud
3 4 Of 1995 4.3
Press
Light
Memor
4 NULL y Of 2000 Rutis Enterprises 2.7
Hope
Warrior
5 6 Of 2005 Maverick 4.6
Wind
… … … … … …
The adaptation table has the following columns:
book_id – The ID of the adapted book.
type – The type of adaptation (e.g. movie, game, play, musical).
title – The name of this adaptation.
release_year – The year when the adaptation was created.
rating – The average rating for the adaptation.
Here’s a snapshot of the data from this table:
book_id type title release_year rating
Gone With The Wolves:
1 movie 2008 3
The Beginning
Companions Of
3 movie 2001 4.2
Tomorrow
5 movie Homeless Warrior 2008 4
2 movie Blacksmith With Silver 2014 4.3
4 movie Patrons And Bearers 2004 3.2
… … … … …
The final table is book_review. It consists of the following columns:
book_id - The ID of a reviewed book.
review - The summary of the review.
author - The name of the review's author.
Here’s the data:
book_id review author
1 An incredible book Sylvia Jones
1 Great, although it has some flaws Jessica Parker
Dennis Stokes takes the reader for a ride full of
2 Thomas Green
emotions
3 Incredible craftsmanship of the author Martin Freeman
4 Not the best book by this author Jude Falth
5 Claudia Johnson at her best! Joe Marqiz
Alexander
6 I cannot recall more captivating plot
Durham
Exercise 1: List All Books and Their Authors
Exercise 2: List Authors and Books Published After 2005
Exercise 3: Show Books Adapted Within 4 Years and Rated
Lower Than the Adaptation
Exercise 4: Show All Books and Their Adaptations (If Any)
Exercise 5: Show All Books and Their Movie Adaptations
Exercise 6: Show All Books with Their Reviews (If Any)
Exercise 7: List All the Books and All the Authors
Exercise 8: Show Products Under 150 Calories and Their
Department
Exercise 9: List All Products with Their Producers, Departments,
and Carbs
Exercise 10: Show All the Products, Prices, Producers, and
Departments
Exercise 11: List All Workers and Their Direct Supervisors
Solution:
1)
SELECT
name,
title,
publish_year
FROM author
JOIN book
ON author.id = book.author_id;
Solution output:
Here’s the output snapshot. We got all this data by joining two tables:
name title publish_year
Marcella Cole Gone With The Wolves 2005
Lisa Mullins Companions And Officers 1930
Dennis Stokes Blacksmith With Silver 1984
Randolph Vasquez Faith Of Light 1995
Michael Rostkovsky Warrior Of Wind 2005
2)
SELECT
name,
title,
publish_year
FROM author
JOIN book
ON author.id = book.author_id
WHERE publish_year > 2005;
Solution output:
The output shows only one book published after 2005.
name title publish_year
Darlene Lyons Temptations In Nature 2007
3)
SELECT
book.title AS book_title,
adaptation.title AS adaptation_title,
book.publish_year,
adaptation.release_year
FROM book
JOIN adaptation
ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
AND book.rating < adaptation.rating;
Solution output:
The output shows three book–adaptation pairs that satisfy the
conditions.
book_title adaptation_title publish_year release_year
Patrons And
Memory Of Hope 2000 2004
Bearers
Music At The Lake Music At The Lake 2004 2007
Companion Of
Lighting Faith 1949 1952
Tomorrow
4)
SELECT
book.title,
adaptation.title,
adaptation.release_year
FROM book
LEFT JOIN adaptation
ON book.id = adaptation.book_id;
Solution output:
The output snapshot shows the required data, with some of the data
shown as NULL. These are the books without the adaptation.
title title-2 release_year
Gone With The Wolves: The
Soulless girl 2008
Beginning
Faith Of Light Companions Of Tomorrow 2001
Warrior Of Wind Homeless Warrior 2008
… … …
Guarding The
NULL NULL
Emperor
Blacksmith With
NULL NULL
Silver
… … …
5)
SELECT
book.title,
publishing_house,
adaptation.title,
adaptation.type
FROM book
LEFT JOIN adaptation
ON book.id = adaptation.book_id
WHERE type = 'movie'
OR type IS NULL;
Solution output:
Here’s the output snapshot. You can see that it shows only books
adapted as movies or not adapted at all.
title publishing_house title-2 type
Gone With The Wolves:
Soulless girl Golden Albatros movie
The Beginning
Companions Of
Faith Of Light White Cloud Press movie
Tomorrow
Warrior Of Wind Maverick Homeless Warrior movie
… … … …
Guarding The
Flying Pen Media NULL NULL
Emperor
Blacksmith With
Diarmud Inc. NULL NULL
Silver
6)
SELECT
book.title,
book_review.review,
book_review.author
FROM book_review
RIGHT JOIN book
ON book.id = book_review.book_id;
Solution output:
The query returns all the book titles, their reviews, and authors. Where
there’s no review or author information, a NULL is shown.
title review author
Soulless girl An incredible book Sylvia Jones
Great, although it has some
Soulless girl Jessica Parker
flaws
… … …
Guarding The Emperor NULL NULL
Companions And
NULL NULL
Officers
Blacksmith With Silver NULL NULL
7)
SELECT
title,
name
FROM book
FULL JOIN author
ON book.author_id = author.id;
Solution output:
The output shows all the books and all the authors, whether the authors
or books exist in both tables or not.
title name
Gone With The Wolves Marcella Cole
Companions And Officers Lisa Mullins
… …
NULL Daniel Branson
… …
Weep Of The West NULL
Joining 3 or More Tables
8)
Dataset 2
The first table in the dataset is department. Its columns are:
id – The unique ID of the department.
name – The department name, i.e. where a particular type of
product is sold.
Here’s the data from the table.
id name
1 fruits
2 vegetables
3 seafood
4 deli
5 bakery
6 meat
7 dairy
The second table is product, and it consists of the following columns:
id – The ID of a given product.
name – The product’s name.
department_id – The ID of the department where the product is
located.
shelf_id – The ID of the shelf of that department where the
product is located.
producer_id – The ID of the company that manufactures this
product.
price – The product’s price.
Here’s the data snapshot:
id name department_id shelf_id producer_id price
1 Apple 1 1 NULL 0.5
2 Avocado 1 1 7 1
3 Banana 1 1 7 0.5
4 Grapefruit NULL 1 1 0.5
5 Grapes 1 1 4 2
… … … … … …
The next table is nutrition_data. Its columns and data are given below:
product_id – The ID of a product.
calories – The calorific value of that product.
fat – The amount of fat in that product.
carbohydrate – The amount of carbohydrates in that product.
protein – The amount of protein in that product.
product_id calories fat carbohydrate protein
1 130 0 5 1
2 50 4.5 3 1
3 110 0 30 1
4 60 0 15 1
NULL 90 0 23 0
… … … … …
The fourth table is named producer. It has the following columns:
id – The ID of a given food producer.
name – The name of the producer.
Below is the data from this table:
id name
1 BeHealthy
2 HealthyFood Inc.
3 SupremeFoods
id name
4 Foodie
5 Gusto
6 Baker n Sons
7 GoodFoods
8 Tasty n Healthy
The last table in the dataset is sales_history. It has the following
columns:
date – The date of sale.
product_id – The ID of the product sold.
amount – The amount of that product sold on a particular day.
Here’s the data, too:
date product_id amount
2015-01-14 1 14
2015-01-14 1 13
2015-01-15 2 2
2015-01-16 2 6
date product_id amount
2015-01-17 3 8
… … …
8)
SELECT
p.name AS product,
d.name AS department
FROM department d
JOIN product p
ON d.id = p.department_id
JOIN nutrition_data nd
ON nd.product_id = p.id
WHERE nd.calories < 150;
Solution output:
The output shows a list of the products and the department they belong
to. It includes only those products with fewer than 150 calories.
product department
Apple fruits
Avocado fruits
Banana fruits
product department
Kiwi fruits
Lemon fruits
… …
9)
SELECT
prod.name AS producer_name,
d.name AS department_name,
p.name AS product_name,
nd.carbohydrate
FROM product p
LEFT JOIN producer prod
ON prod.id = p.producer_id
LEFT JOIN department d
ON d.id = p.department_id
LEFT JOIN nutrition_data nd
ON nd.product_id = p.id;
Solution output:
The output shows all the products with their producer and department
names and carbohydrate amounts:
producer_name department_name product_name carbohydrate
BeHealthy fruits Kiwi 20
BeHealthy vegetables Broccoli 8
BeHealthy meat Chicken NULL
producer_name department_name product_name carbohydrate
BeHealthy NULL Grapefruit 15
HealthyFood Inc. vegetables Celery 4
… … … …
10)
SELECT
p.name AS product_name,
p.price AS product_price,
prod.name AS producer_name,
d.name AS department_name
FROM product p
FULL JOIN producer prod
ON p.producer_id = prod.id
FULL JOIN department d
ON d.id = p.department_id;
Solution output:
The solution shows all the data from the selected tables and columns:
product_name product_price producer_name department_name
Chicken 5.5 BeHealthy meat
Broccoli 2.5 BeHealthy vegetables
Kiwi 0.3 BeHealthy fruits
product_name product_price producer_name department_name
Grapefruit 0.5 BeHealthy NULL
HealthyFood
Cucumber 0.7 vegetables
Inc.
… … … …
Self-Join
Dataset 3
The dataset for this example consists of only one
table: workshop_workers. It has the following columns.
id – The worker’s ID.
name – The worker’s first and last name.
specialization – The worker's specialization.
master_id – The ID of the worker's supervisor.
experience – The worker's years of experience.
project_id – The ID of the project to which the worker is currently
assigned.
Here’s the data:
i specializati master_i experienc project_i
name
d on d e d
Mathew
1 woodworking NULL 20 1
Conn
Kate
2 woodworking 1 4 1
Brown
3 John Doe incrusting 5 3 1
John
4 watchmaking 7 2 3
Kowalsky
Suzan
5 Gregowitc incrusting NULL 15 4
h
11)
SELECT
apprentice.name AS apprentice_name,
master.name AS master_name
FROM workshop_workers apprentice
JOIN workshop_workers master
ON apprentice.master_id = master.id;
Solution output:
The output shows all the apprentices and their direct supervisors.
apprentice_name master_name
Kate Brown Mathew Conn
apprentice_name master_name
John Doe Suzan Gregowitch
John Kowalsky Joe Darrington
Peter Parker Joe Darrington
Mary Smith Mathew Conn
Carlos Bell Suzan Gregowitch
Dennis Wright Joe Darrington
