Mid Level SQL Practice Grounds

Last modified: December 09, 2019

You’ve covered the majority of the main use cases of SQL! You know the stuff, but now you’ve got some practicing to do to become really fluent and skilled at it. Here we’ve constructed a large list of challenges to give you that practice. If you forgot the rules of our practice playgrounds you can review them in the Basic SQL Practice page.

Good luck!

Q. Fetch all the tracks that are over 300000 milliseconds long.

references: where

Q. Fetch the id for the artist ‘Miles Davis’.

references: where

Q. Get all the tracks with genre_id of 20 from longest to shortest.

references: where order-by

Q. Get the artists who’s ids are between 55 and 98.

references: operators

Q. Get all the tracks except those with genre_ids of 15 or 18.

references: operators

Q. Get all the tracks that were composed by just Miles Davis

references: operators

Q. Get all the tracks that Miles Davis had a part in composing.

references: operators

Q. Fetch the the names of the tracks with the word ‘wild’ in it, regardless of case

references: operators

Q. How many tracks did Little Richard helped compose?

references: operators aggregate

Q. How many tracks with genre_id of 1 have composers listed?

references: aggregate

Q. How many unique composers are there in the tracks table with the genre_id of 1.

references:

Q. What is the average length for tracks with genre_ids of either 5, 7 or 10?

references: operators aggregate

Q. Get a list of genre_ids and the number of tracks in each.

references: group-by

Q. A list of the number of tracks grouped by genre_id and then album_id with the column order of genre_id, album_id and count.

references: group-by

Q. Take the above query, but order the album_id in descending order, keeping genre_id odered the same

references: order-by group-by

Q. Take the above query with the same ordering but group by album_id and then genre_id and change the order of the results to reflect that switch.

references: order-by group-by

Q. Get the first 5 tracks JOINed with their albums info.

references: joins limit

Q. Get the first 5 albums JOINed with their artists info.

references: joins limit

Q. Return each customer_id and the total of all of their invoices.

references: group-by aggregate

Q. There is a foreign key customer_id that relates invoices to the customers table. Fetch the company name in the company column of the customers table and the total amount that each has been invoiced by joining these tables.”

references: group-by aggregate joins

Q. Get the first_names and birth_dates of each of the employees in the format: January 01, 1976

references: dates

Q. Get the first_names and birth_dates of each of the employees in the format: Jan 1st, 1976

references: dates

Q. Get the first_names and birth_dates of each of the employees in the format: 09/23/1987

references: dates

Q. Get the year of the invoice_date in the format `Y2012` and total number of invoices per year.

references: dates

Q. Get the year and month of invoices and the total amount that was invoiced for that year and month.

references: dates aggregate

If you’ve completed all of these CONGRATULATIONS! You’re proficient and fluent enough in SQL now to complete a significant portion of analytic and transactional queries.

Written by: Dave Fowler
Reviewed by: Matt David

Q. Fetch all the tracks that are over 300000 milliseconds long. references: where

Q. Fetch the id for the artist ‘Miles Davis’. references: where

Q. Get all the tracks with genre_id of 20 from longest to shortest. references: where order-by

Q. Get the artists who’s ids are between 55 and 98. references: operators

Q. Get all the tracks except those with genre_ids of 15 or 18. references: operators

Q. Get all the tracks that were composed by just Miles Davis references: operators

Q. Get all the tracks that Miles Davis had a part in composing. references: operators

Q. Fetch the the names of the tracks with the word ‘wild’ in it, regardless of case references: operators

Q. How many tracks did Little Richard helped compose? references: operators aggregate

Q. How many tracks with genre_id of 1 have composers listed? references: aggregate

Q. How many unique composers are there in the tracks table with the genre_id of 1. references:

Q. What is the average length for tracks with genre_ids of either 5, 7 or 10? references: operators aggregate

Q. Get a list of genre_ids and the number of tracks in each. references: group-by

Q. A list of the number of tracks grouped by genre_id and then album_id with the column order of genre_id, album_id and count. references: group-by

Q. Take the above query, but order the album_id in descending order, keeping genre_id odered the same references: order-by group-by

Q. Take the above query with the same ordering but group by album_id and then genre_id and change the order of the results to reflect that switch. references: order-by group-by

Q. Get the first 5 tracks JOINed with their albums info. references: joins limit

Q. Get the first 5 albums JOINed with their artists info. references: joins limit

Q. Return each customer_id and the total of all of their invoices. references: group-by aggregate

Q. There is a foreign key customer_id that relates invoices to the customers table. Fetch the company name in the company column of the customers table and the total amount that each has been invoiced by joining these tables.” references: group-by aggregate joins

Q. Get the first_names and birth_dates of each of the employees in the format: January 01, 1976 references: dates

Q. Get the first_names and birth_dates of each of the employees in the format: Jan 1st, 1976 references: dates

Q. Get the first_names and birth_dates of each of the employees in the format: 09/23/1987 references: dates

Q. Get the year of the invoice_date in the format Y2012 and total number of invoices per year. references: dates

Q. Get the year and month of invoices and the total amount that was invoiced for that year and month. references: dates aggregate

Created by