In this project we will be practicing inserting and querying data using SQL. We'll make use of an existing database called DVD rental that can be downloaded here Click me
- Once you have downloaded the
.zip
extract the file from it, and it should be a.tar
file. - Right click your local database and create a new database called dvd_rental.
- Then right click your new
dvd_rental
database and select restore. - Make sure the format is
Custom or tar
then select the button on the right of Filename with the three dots...
inside it. - This will open a file explorer, you will need to navigate to where you extracted the
dvd_rental.tar
file.- Make sure you change the Format: to all files in the bottom right.
- Once you have found your file press select
- Then press restore.
- you should now have all the tables you need for todays afternoon project.
Use [www.sqlteaching.com](http://www.sqlteaching.com/) or [sqlbolt.com](http://sqlbolt.com/) as resources for the missing keywords you'll need.
- Create a table called
person
that records aperson
'sid
,name
,age
,height
( in cm ),city
,favorite_color
.id
should be a SERIAL PRIMARY KEY
- Add 5 different people into the
person
table.- Remember to not include the
id
because it should auto-increment.
- Remember to not include the
- List all the people in the
person
table byheight
from tallest to shortest. - List all the people in the
person
table byheight
from shortest to tallest. - List all the people in the
person
table byage
from oldest to youngest. - List all the people in the
person
table older thanage
20. - List all the people in the
person
table that are exactly 18 years old. - List all the people in the
person
table that are less than 20 years old and older than 30. - List all the people in the
person
table that are not 27 (Use not equals). - List all the people in the
person
table where theirfavorite_color
is not red. - List all the people in the
person
table where theirfavorite_color
is not red and is not blue. - List all the people in the
person
table where theirfavorite_color
is orange or green. - List all the people in the
person
table where theirfavorite_color
is orange, green or blue (use IN). - List all the people in the
person
table where theirfavorite_color
is yellow or purple (use IN).
SQL Solutions
#1
CREATE TABLE person ( ID SERIAL PRIMARY KEY, name string, age integer, height integer, city string, FavoriteColor string );
#2
INSERT INTO person ( name, age, height, city, FavoriteColor ) VALUES ( "First Last", 21, 182, "city", "Color" );
#3
SELECT * FROM person ORDER BY height DESC;
#4
SELECT * FROM person ORDER BY height ASC;
#5
SELECT * FROM person ORDER BY age DESC;
#6
SELECT * FROM person WHERE age > 20;
#7
SELECT * FROM person WHERE age = 18;
#8
SELECT * FROM person WHERE age < 20 OR age > 30;
#9
SELECT * FROM person WHERE age != 27;
#10
SELECT * FROM person WHERE FavoriteColor != "red";
#11
SELECT * FROM person WHERE FavoriteColor != "red" AND FavoriteColor != "blue";
#12
SELECT * FROM person WHERE FavoriteColor = "orange" OR FavoriteColor = "green";
#13
SELECT * FROM person WHERE FavoriteColor IN ( "orange", "green", "blue" );
#14
SELECT * FROM person WHERE FavoriteColor IN ( "yellow", "purple" )
- Create a table called
order
that records:person_id
,product_name
,product_price
,quantity
. - Add 5 Orders to the
order
table.- Make orders for at least two different people.
person_id
should be different for different people.
- Select all the records from the
order
table. - Calculate the total number of products ordered.
- Calculate the total
order
price. - Calculate the total
order
price by a singleperson_id
.
SQL Solutions
#1
CREATE TABLE order ( person_id integer, product_price string, product_price float, quantity integer );
#2
INSERT INTO order ( person_id, product_price, product_price, quantity ) VALUES ( 0, "Product", 12.50, 2 );
#3
SELECT * FROM order;
#4
SELECT SUM(quantity) FROM order;
#5
SELECT SUM(product_price * quantity) FROM order;
#6
/* The value of person_id depends on what IDs you used. Use a valid ID from your table */
SELECT SUM(product_price * quantity) FROM order WHERE person_id = 0;
- Add 3 new Actors to the
actor
table. ( It's already created )- You can ignore the last_update column and not put anything in it.
- Select 10 actors in reverse alphabetical order.
- Select 5 actors in alphabetical order.
- Select all actors whose
first name
start with "Al". - Select all artists whose
first name
contain the letters "th".
SQL Solutions
#1
INSERT INTO actor ( first_name, last_name ) VALUES ( 'Bob', 'Ross' );
#2
SELECT * FROM actor ORDER BY first_name Desc LIMIT 10;
#3
SELECT * FROM actor ORDER BY first_name ASC LIMIT 5;
#4
SELECT * FROM actor WHERE first_name LIKE 'Al%';
#5
SELECT * FROM actor WHERE first_name LIKE '%th%';
- List all films
title
,description
, andrating
that are rated pg-13. - Find the
length
of the shortest film. - Find the
length
of the longest film. - Find every
film_id
from thefilm_category
table where the category iscomedy
- You will need to query the
category
table to find the id for Vomedy Movies
- You will need to query the
- Count how many films are of type
comedy
.
SQL Solutions
#1
SELECT title, description, rating
FROM film
WHERE rating = 'PG-13'
#2
SELECT MAX(length) FROM FILM;
#3
SELECT MIN(length) FROM FILM;
#4
SELECT *
FROM film_category
WHERE category_id = 5
#5
SELECT Count(*)
FROM film_category
WHERE category_id = 5
- Count how many rentals were made by
customer_id
25. - Find the largest order total amount.
- Find the smallest order total amount.
- Find all orders bigger than $5.
- Count how many orders were smaller than $5.
- Count how many orders were made by customers 255, 341, and 124 (use IN).
- Get the average total of the orders.
- Get the total sum of the orders.
SQL Solutions
#1
SELECT COUNT(*) FROM payment
WHERE customer_id = 25;
#2
SELECT MAX(amount) FROM payment;
#3
SELECT MIN(amount) FROM payment;
#4
SELECT *
FROM payment
WHERE amount > 5;
#5
SELECT COUNT(*)
FROM payment
WHERE amount < 5;
#6
SELECT COUNT(*)
FROM payment
WHERE customer_id in (255, 341, 124);
#7
SELECT AVG(amount) FROM payment;
#8
SELECT SUM(amount) FROM payment;
- Find the average length of the films.
- use a sub query to find all the films that are longer than the average.
- Using the
film
table display a count of how many movies there are of each rating. - Using the
payment
table display the average amount when checkout out by the employee with astaff_id
of 2 - Using the
payment
table, show the average total for each customer in ascending order. - Using the
rental
table, show how many times eachinventory_id
has been rented, order it by most rended to least rented.
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.