webdevsimplified/learn-sql

Exercises for beginners to learn SQL

Learn SQL

If you haven't already make sure you watch this video which will teach you all the basics of SQL in 60 minutes.

After watching the video try to complete the exercises listed below using the data provided in this repository.

All of the solutions are available in the repository, and this video goes over all of the solutions.

Setup

First drop your existing database that was created in the tutorial. DROP DATABASE record_company;

Copy the code inside the schema.sql file, paste it into MySQL Workbench, and run it. (This file contains the code necessary to create and add the tables from the tutorial video)

Exercises

1. Create a Songs Table

Solution

This table should be called songs and have four properties with these exact names.

1. id: An integer that is the primary key, and auto increments.
2. name: A string that cannot be null.
3. length: A float that represents the length of the song in minutes that cannot be null.
4. album_id: An integer that is a foreign key referencing the albums table that cannot be null.

After successfully creating the table copy the code from data.sql into MySQL Workbench, and run it to populate all of the data for the rest of the exercises. If you do not encounter any errors, then your answer is most likely correct.

2. Select only the Names of all the Bands

Solution

Change the name of the column the data returns to Band Name

Band Name
Seventh Wonder
Metallica
The Ocean
Within Temptation
Death
Van Canto
Dream Theater

3. Select the Oldest Album

Solution

Make sure to only return one result from this query, and that you are not returning any albums that do not have a release year.

id	name	release_year	band_id
5	...And Justice for All	1988	2

4. Get all Bands that have Albums

Solution

There are multiple different ways to solve this problem, but they will all involve a join.

Return the band name as Band Name.

Band Name
Seventh Wonder
Metallica
The Ocean
Within Temptation
Death
Van Canto

5. Get all Bands that have No Albums

Solution

This is very similar to #4 but will require more than just a join.

Return the band name as Band Name.

Band Name
Dream Theater

6. Get the Longest Album

Solution

This problem sounds a lot like #3 but the solution is quite a bit different. I would recommend looking up the SUM aggregate function.

Return the album name as Name, the album release year as Release Year, and the album length as Duration.

Name	Release Year	Duration
Death Magnetic	2008	74.76666593551636

7. Update the Release Year of the Album with no Release Year

Solution

Set the release year to 1986.

You may run into an error if you try to update the release year by using release_year IS NULL in the WHERE statement of your UPDATE. This is because MySQL Workbench by default will not let you update a table that has a primary key without using the primary key in the UPDATE statement. This is a good thing since you almost never want to update rows without using the primary key, so to get around this error make sure to use the primary key of the row you want to update in the WHERE of the UPDATE statement.

8. Insert a record for your favorite Band and one of their Albums

Solution

If you performed this correctly you should be able to now see that band and album in your tables.

9. Delete the Band and Album you added in #8

Solution

The order of how you delete the records is important since album has a foreign key to band.

10. Get the Average Length of all Songs

Solution

Return the average length as Average Song Duration.

Average Song Duration
5.352472513259112

11. Select the longest Song off each Album

Solution

Return the album name as Album, the album release year as Release Year, and the longest song length as Duration.

Album	Release Year	Duration
Tiara	2018	9.5
The Great Escape	2010	30.2333
Mercy Falls	2008	9.48333
Master of Puppets	1986	8.58333
...And Justice for All	1988	9.81667
Death Magnetic	2008	9.96667
Heliocentric	2010	7.48333
Pelagial	2013	9.28333
Anthropocentric	2010	9.4
Resist	2018	5.85
The Unforgiving	2011	5.66667
Enter	1997	7.25
The Sound of Perseverance	1998	8.43333
Individual Thought Patterns	1993	4.81667
Human	1991	4.65
A Storm to Come	2006	5.21667
Break the Silence	2011	6.15
Tribe of Force	2010	8.38333

12. Get the number of Songs for each Band

Solution

This is one of the toughest question on the list. It will require you to chain together two joins instead of just one.

Return the band name as Band, the number of songs as Number of Songs.

Band	Number of Songs
Seventh Wonder	35
Metallica	27
The Ocean	31
Within Temptation	30
Death	27
Van Canto	32

learn-sql's Issues

alternative solution to exercise #5

SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums ON bands.id = albums.band_id
WHERE albums.id IS NULL;

The seem to be no need to add auto increment on 1st problem for songs table

/* For song Table, I dont think auto increment was necessary, because when values were inserted, code was not running
Below is what I think should be solution to the problem. */

CREATE TABLE songs (
id INT NOT NULL,
name VARCHAR(255) NOT NULL,
length FLOAT NOT NULL,
album_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (album_id) REFERENCES albums(id)
);

/*well for me, I'm on sql server hence I didn't add IDENTITY(1,1) on my code it it worked well, this identity is doing the same thing as auto increment on sql server */
If I'm wrong I'm open to correction.

SQL EXERCISES

Question #6

Solution query doesn't give any result for me. I changed between primary keys doesn't help. Help please

Songs Table missing Foreign Key

I'm 99% sure that in table Songs 'album_id' should be Set To Foreign Key same way 'band_id' is in table Albums.

Auto increment clashes with "id" when population from data.sql file

The data.sql file should not have the "id" column values as the "id" column has been set to be a primary key and auto_increment. Or am I missing something trivial here?

I get the error "Duplicate entry '1' for key 'bands.PRIMARY'"

MySQL

Issue in sol 5

Given:
SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums ON bands.id = albums.band_id
GROUP BY albums.band_id
HAVING COUNT(albums.id) = 0;

Instead it should be:
SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums ON bands.id = albums.band_id
GROUP BY bands.name
HAVING COUNT(albums.id) = 0;

Right?? Please correct me if I am wrong!!!

solution #5 throws an error

Solution of Exercise 5 throws an error:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'record_company.bands.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

found a solution to this issue:
https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc

issues in solution N2

since the LIMIT keyword do not exist in sql server
you can use this syntaxe to solve the problem
SELECT * FROM albums WHERE release_year = (SELECT MIN(release_year) FROM albums)

Issue in 1)

When I try to populate my tables with the data in data.sql, I am always greeted with an error when I try to run it which affects everything moving forward

Alternate solution to Exercise 7 (Not an Issue)

First off, thanks for creating this repo. It really has helped me a lot in getting started with SQL.
Now as posted in the solutions for exercise 7, the solution, I feel, is quite static. So, I searched about the error, Error Code: 1093. You can't specify target table 'albums' for update in FROM clause I got while trying to solve it in a single query. The following solution works dynamically:

UPDATE albums
SET release_year = 1986
WHERE albums.id = (
    SELECT id
    FROM (SELECT a.id
        FROM albums AS a
        WHERE release_year IS NULL) AS c
);

Upon reading this article I found that the above used to be a complex query in earlier versions of MYSQL which involved creating a temporary table. But starting from version 5.6 onward it has been optimised. So, I assume it is safe to use.
Also, coming from python background, this feels quite satisfying to do the task in one line (query in this case).

Issue in 5 solution

/SOLUTION 5/
SELECT bands.name AS "band name"
FROM bands
LEFT JOIN albums
ON bands.id=albums.band_id
GROUP BY bands.name
HAVING COUNT(albums.id)=0;
/including bands.name in group by is important as it is in result/

Question 5

MySQL Workbench is giving the following error when I run the solution given for 5.

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'record_company.bands.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by	0.0021 sec

Alternate solution to 12(Not an Issue)

select b.name as "Band Name",s."Number of songs" from bands as b inner join(select a.band_id as "id",count(s.length) as "Number of songs" from albums as a inner join songs as s on a.id=s.album_id group by a.band_id) as s on b.id=s.id;

Please try with the query given inside the inner join first and then do the rest for easy understanding from beginner point of view