SQL Basics

Setup Instructions

In this project you will be practicing inserting and querying data using SQL.

Set up the sample database

1. Download the exercise materials from Frodo, unzip them, and navigate to that directory

2. Create a new PostgreSQL database called sample-db in pgAdmin

   

3. Navigate to the Query Tool

   

4. Copy and paste the data.sql file into the Query Tool

Set up your Git repository

1. Run git init

2. Run git add and git commit (don’t forget the -m flag and a message)

3. Create a repo on GitHub and connect it to your local repo

You’ll query the sample-db database according to the prompts below. Use pgAdmin to test your queries to verify that you get the desired output. Then, log your solutions in the appropriate *.sql file. Make sure you commit your changes as you go!

SQL problems

Part 1: The person table

In the person.sql file, write out the code for the following problems:

1. Create a table called person with fields for person_id, name, age, height (in cm) , city, and favorite_color. The person_id should be an auto-incrementing id/primary key (use type: SERIAL).

2. Add 5 different people into the person database. Remember to not include the person_id, because it should auto-increment.

3. Select all the people in the person table by height from tallest to shortest.

4. Select all the people in the person table by height from shortest to tallest.

5. Select all the people in the person table by age from oldest to youngest.

6. Select all the people in the person table older than age 20.

7. Select all the people in the person table that are exactly 18.

8. Select all the people in the person table that are less than 20 and older than 30.

9. Select all the people in the person table that are not 27 (use not equals).

10. Select all the people in the person table where their favorite color is not red.

11. Select all the people in the person table where their favorite color is not red and is not blue.

12. Select all the people in the person table where their favorite color is orange or green.

13. Select all the people in the person table where their favorite color is orange, green or blue (use IN).

14. Select all the people in the person table where their favorite color is yellow or purple (use IN).

Part 2: The orders table

In the orders.sql file, write out the code for the following problems:

1. Create a table called orders that records: order_id, person_id, product_name, product_price, quantity.

2. Add 5 total orders from at least two different people to the orders table. (The person who made this order is indicated by its person_id — refer to the rows you made in Part 1.)

3. Select all the records from the orders table.

4. Calculate the total number of products ordered.

5. Calculate the total order price for all orders.

6. Calculate the total order price by a single person_id.

Part 3: The artist table

The next questions make use of the artist table, which is already present in the sample data — you don’t have to create it your self.

In the artist.sql file, write out the code for the following problems:

1. Add 3 new artists to the artist table (look at the schema and find which fields you need to specify!)

2. Select 10 artists in reverse alphabetical order.

3. Select 5 artists in alphabetical order.

4. Select all artists that start with the word 'Black'.

5. Select all artists that contain the word 'Black'.

Part 4: The employee table

The next questions make use of the employee table, which is also already present in the sample data.

In the employee.sql file, write out the code for the following problems:

1. List the first and last names of all employees that live in Calgary.

2. Find the birthdate for the youngest employee.

3. Find the birthdate for the oldest employee.

4. Find everyone that reports to Nancy Edwards (find Nancy Edwards’ employee_id, then look for employees who have that id in their reports_to field).

5. Count how many people live in Lethbridge.

Part 5: The invoice table

The next questions make use of the invoice table, which is also already present in the sample data.

In the invoice.sql file, write out the code for the following problems:

1. Count how many orders were made from the USA.

2. Find the largest order total amount.

3. Find the smallest order total amount.

4. Find all orders bigger than $5.

5. Count how many orders were smaller than $5.

6. Count how many orders were in CA, TX, or AZ (use IN).

7. Get the average total of the orders.

8. Get the total sum of the orders.

9. Update the invoice with an invoice_id of 5 to have a total order amount of 24.

10. Delete the invoice with an invoice_id of 1.

Finishing up

When you have completed the questions, add, commit, and push your work to GitHub.

If you have extra time, use it to poke around the other tables in the sample database — we’ll use them more in the future!