In this project, we'll continue to use Chinook to practice joins, nested queries, updating rows, group by, distinct, and foreign keys.
Any new tables or records that you add into the database will be removed after you refresh the page.
Use SQL Teaching or SQL Bolt as resources for referencing on keywords you'll need.
Syntax Hint
SELECT [Column names]
FROM [Table] [abbv]
JOIN [Table2] [abbv2] ON abbv.prop = abbv2.prop WHERE [Conditions];
SELECT a.Name, b.Name FROM SomeTable a JOIN AnotherTable b ON a.someid = b.someid;
SELECT a.Name, b.Name FROM SomeTable a JOIN AnotherTable b ON a.someid = b.someid WHERE b.email = 'e@mail.com';
- Get all invoices where the
UnitPrice
on theInvoiceLine
is greater than $0.99. - Get the
InvoiceDate
, customerFirstName
andLastName
, andTotal
from all invoices. - Get the customer
FirstName
andLastName
and the support rep'sFirstName
andLastName
from all customers.- Support reps are on the Employee table.
- Get the album
Title
and the artistName
from all albums. - Get all PlaylistTrack TrackIds where the playlist
Name
is Music. - Get all Track
Name
s forPlaylistId
5. - Get all Track
Name
s and the playlistName
that they're on ( 2 joins ). - Get all Track
Name
s and AlbumTitle
s that are the genre"Alternative"
( 2 joins ).
SQL Solutions
#1
SELECT *
FROM Invoice i
JOIN InvoiceLine il ON il.invoiceId = i.invoiceId
WHERE il.UnitPrice > 0.99;
#2
SELECT i.InvoiceDate, c.FirstName, c.LastName, i.Total
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId;
#3
SELECT c.FirstName, c.LastName, e.FirstName, e.LastName
FROM Customer c
JOIN Employee e ON c.SupportRepId = e.EmployeeId;
#4
SELECT al.Title, ar.Name
FROM Album al
JOIN Artist ar ON al.ArtistId = ar.ArtistId;
#5
SELECT pt.TrackId
FROM PlaylistTrack pt
JOIN Playlist p ON p.PlaylistId = pt.PlaylistId
WHERE p.Name = 'Music';
#6
SELECT t.Name
FROM Track t
JOIN PlaylistTrack pt ON pt.TrackId = t.TrackId
WHERE pt.PlaylistId = 5;
#7
SELECT t.name, p.Name
FROM Track t
JOIN PlaylistTrack pt ON t.TrackId = pt.TrackId
JOIN Playlist p ON pt.PlaylistId = p.PlaylistId;
#8
SELECT t.Name, a.title
FROM Track t
JOIN Album a ON t.AlbumId = a.AlbumId
JOIN Genre g ON g.GenreId = t.GenreId
WHERE g.Name = "Alternative";
- Get all tracks on the playlist(s) called Music and show their name, genre name, album name, and artist name.
- At least 5 joins.
Complete the instructions without using any joins. Only use nested queries to come up with the solution.
Syntax Hint
SELECT [Column names]
FROM [Table]
WHERE ColumnId IN ( SELECT ColumnId FROM [Table2] WHERE [Condition] );
SELECT Name, Email FROM Athlete WHERE AthleteId IN ( SELECT PersonId FROM PieEaters WHERE Flavor='Apple' );
- Get all invoices where the
UnitPrice
on theInvoiceLine
is greater than $0.99. - Get all Playlist Tracks where the playlist name is Music.
- Get all Track names for
PlaylistId
5. - Get all tracks where the
Genre
is Comedy. - Get all tracks where the
Album
is Fireball. - Get all tracks for the artist Queen ( 2 nested subqueries ).
SQL Solutions
#1
SELECT *
FROM Invoice
WHERE InvoiceId IN ( SELECT InvoiceId FROM InvoiceLine WHERE UnitPrice > 0.99 );
#2
SELECT *
FROM PlaylistTrack
WHERE PlaylistId = ( SELECT PlaylistId FROM Playlist WHERE Name = "Music" );
#3
SELECT Name
FROM Track
WHERE TrackId IN ( SELECT TrackId FROM PlaylistTrack WHERE PlaylistId = 5 );
#4
SELECT *
FROM Track
WHERE GenreId IN ( SELECT GenreId FROM Genre WHERE Name = "Comedy" );
#5
SELECT *
FROM Track
WHERE AlbumId IN ( SELECT AlbumId FROM Album WHERE Title = "Fireball" );
#6
SELECT *
FROM Track
WHERE AlbumId IN (
SELECT AlbumId FROM Album WHERE ArtistId IN (
SELECT ArtistId FROM Artist WHERE Name = "Queen"
)
);
Syntax Hint
UPDATE [Table]
SET [column1] = [value1], [column2] = [value2]
WHERE [Condition];
UPDATE Athletes SET sport = 'Picklball' WHERE sport = 'pockleball';
- Find all customers with fax numbers and set those numbers to
null
. - Find all customers with no company (null) and set their company to
"Self"
. - Find the customer
Julia Barnett
and change her last name toThompson
. - Find the customer with this email
luisrojas@yahoo.cl
and change his support rep to4
. - Find all tracks that are the genre
Metal
and have no composer. Set the composer to"The darkness around us"
. - Refresh your page to remove all database changes.
SQL Solutions
#1
UPDATE Customer
SET Fax = null
WHERE Fax IS NOT null;
#2
UPDATE Customer
SET Company = "Self"
WHERE Company IS null;
#3
UPDATE Customer
SET LastName = "Thompson"
WHERE FirstName = "Julia" AND LastName = "Barnett";
#4
UPDATE Customer
SET SupportRepId = 4
WHERE Email = "luisrojas@yahoo.cl";
#5
UPDATE Track
SET Composer = "The darkness around us"
WHERE GenreId = ( SELECT GenreId FROM Genre WHERE Name = "Metal" )
AND Composer IS null;
Syntax Hint
SELECT [Column1], [Column2]
FROM [Table] [abbr]
GROUP BY [Column];
- Find a count of how many tracks there are per genre. Display the genre name with the count.
- Find a count of how many tracks are the
"Pop"
genre and how many tracks are the"Rock"
genre. - Find a list of all artists and how many albums they have.
SQL Solutions
#1
SELECT Count(*), g.Name
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
GROUP BY g.Name;
#2
SELECT Count(*), g.Name
FROM Track t
JOIN Genre g ON g.GenreId = t.GenreId
WHERE g.Name = 'Pop' OR g.Name = 'Rock'
GROUP BY g.Name;
#3
SELECT ar.Name, Count(*)
FROM Artist ar
JOIN Album al ON ar.ArtistId = al.ArtistId
GROUP BY al.ArtistId;
Syntax Hint
SELECT DISTINCT [Column]
FROM [Table];
- From the
Track
table find a unique list of allComposer
s. - From the
Invoice
table find a unique list of allBillingPostalCode
s. - From the
Customer
table find a unique list of allCompany
s.
SQL Solutions
#1
SELECT DISTINCT Composer
FROM Track;
#2
SELECT DISTINCT BillingPostalCode
FROM Invoice;
#3
SELECT DISTINCT Company
FROM Customer;
Always do a select before a delete to make sure you get back exactly what you want and only what you want to delete! Since we cannot delete anything from the pre-defined tables ( foreign key restraints ), use the following SQL code to create a dummy table:
practice_delete TABLE
CREATE TABLE practice_delete ( Name string, Type string, Value integer );
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "bronze", 50);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "bronze", 50);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "bronze", 50);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "silver", 100);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "silver", 100);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "gold", 150);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "gold", 150);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "gold", 150);
INSERT INTO practice_delete ( Name, Type, Value ) VALUES ("delete", "gold", 150);
SELECT * FROM practice_delete;
Syntax Hint
DELETE FROM [Table] WHERE [Condition]
- Copy, paste, and run the SQL code from the summary.
- Delete all
"bronze"
entries from the table. - Delete all
"silver"
entries from the table. - Delete all entries whose value is equal to
150
.
SQL Solutions
#1
DELETE
FROM practice_delete
WHERE Type = "bronze";
#2
DELETE
FROM practice_delete
WHERE Type = "silver";
#3
DELETE
FROM practice_delete
WHERE Value = 150;
Let's simulate an e-commerce site. We're going to need users, products, and orders.
- Users need a name and an email.
- Products need a name and a price
- Orders need a ref to product.
- All 3 need primary keys.
- Create 3 tables following the criteria in the summary.
- Add some data to fill up each table.
- At least 3 users, 3 products, 3 orders.
- Run queries against your data.
- Get all products for the first order.
- Get all orders.
- Get the total cost of an order ( sum the price of all products on an order ).
- Add a foreign key reference from Orders to Users.
- Update the Orders table to link a user to each order.
- Run queries against your data.
- Get all orders for a user.
- Get how many orders each user has.
- Get the total amount on all orders for each user.
If you see a problem or a typo, please fork, make the necessary changes, and create a pull request so we can review your changes and merge them into the master repo and branch.
© DevMountain LLC, 2017. Unauthorized use and/or duplication of this material without express and written permission from DevMountain, LLC is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to DevMountain with appropriate and specific direction to the original content.