In this project, we'll be practicing inserting and querying data using SQL. We'll make use of an online database emulator tool found Here. Careful, if you reload the page your changes will be lost.
On the left are the Tables with their fields. The right is where we will be writing our queries. The bottom is where we will see our results.
SELECT all the data FROM the artist table.
SQL Solution
SELECT * FROM artist;
SELECT the first_name, last_name, and country FROM the employee table.
SQL Solution
SELECT first_name, last_name, country
FROM employee;
SELECT the name, composer, and milliseconds FROM the track table WHERE the milliseconds are greater than 299000.
SQL Solution
SELECT name, composer, milliseconds
FROM track
WHERE milliseconds > 299000;
SELECT the count FROM the track table WHERE the milliseconds are greater than 299000.
SQL Solution
SELECT count(*)
FROM track
WHERE milliseconds > 299000;
Now that we have some basic query examples. Let's try doing some more complicated ones. Use www.sqlteaching.com or sqlbolt.com as resources for the missing keywords you'll need.
- Find the average length of all tracks in milliseconds
- Find the number of invoices in the USA
- Make a list of all the First Names of Customers that contain an 'a'
- Make a list of the 10 longest tracks
- Make a list of the 20 shortest tracks
- Find all the customers that live in California or Washington
- Find all the customers that live in California, Washington, Utah, Florida, or Arizona (Use IN keyword)
- Insert an artist to the database
- Insert yourself as a customer to the database
- Find a list of all Playlists that start with
Classical
- You can either continue exploring this dataset or look into setting up postgres on your local machine.
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.
testing