Project Summary

In this project we will be practicing inserting and querying data using SQL. We'll make use of a handy online tool provided by DevMountain that will allow us to write SQL in your browser. Click Me

On the left are the Tables with their fields, the right is where we will be writing our queries, and the bottom is where we will see our results.

Any new tables or records that we add into the database will be removed after you refresh the page.

Table - person

Instructions

Create a table called person that records a person's id, name, age, height ( in cm ), city, favorite_color.
- id should be an auto-incrementing id/primary key - Use type: SERIAL
Add 5 different people into the person database.
- Remember to not include the person_id because it should auto-increment.
List all the people in the person table by height from tallest to shortest.
List all the people in the person table by height from shortest to tallest.
List all the people in the person table by age from oldest to youngest.
List all the people in the person table older than age 20.
List all the people in the person table that are exactly 18.
List all the people in the person table that are less than 20 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 their favorite color is not red.
List all the people in the person table where their favorite color is not red and is not blue.
List all the people in the person table where their favorite color is orange or green.
List all the people in the person table where their favorite color is orange, green or blue (use IN).
List all the people in the person table where their favorite color is yellow or purple (use IN).

My Answers

CREATE TABLE person ( id SERIAL PRIMARY KEY, name VARCHAR, favorite_color VARCHAR, height INT, age INT )

INSERT INTO person(name, favorite_color, height, age) VALUES('Alex', 'Blue', 168, 19) VALUES('Preston', 'Yellow', 195, 29) VALUES('Cameron', 'Green', 165, 27) VALUES('Zack', 'Red', 175, 25) VALUES('Blake', 'Orange', 195, 22)

SELECT height FROM person ORDER BY ABS(height) DESC

SELECT height FROM person ORDER BY ABS(height) ASC

SELECT * FROM person ORDER BY (age) DESC

SELECT * FROM person WHERE age > 20

SELECT * FROM person WHERE age = 18

SELECT * FROM person WHERE age < 20 OR age > 30

SELECT * FROM person WHERE NOT age = 27

SELECT * FROM person WHERE NOT favorite_color = 'Red'

SELECT * FROM person WHERE NOT favorite_color = 'Red' AND NOT favorite_color = 'Blue'

SELECT * FROM person WHERE favorite_color = 'Orange' OR favorite_color = 'Blue'

SELECT * FROM person WHERE favorite_color IN ('Orange','Green','Blue')

SELECT * FROM person WHERE favorite_color IN ('Yellow','Purple')

Solution

SQL Solutions

#1

CREATE TABLE person ( person_id SERIAL, name VARCHAR(200), age INTEGER, height INTEGER, city VARCHAR(200), favorite_color VARCHAR(200) );

#2

INSERT INTO person ( name, age, height, city, favorite_color ) 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 favorite_color != 'red';

#11

SELECT * FROM person WHERE favorite_color != 'red' AND favorite_color != 'blue';

#12

SELECT * FROM person WHERE favorite_color = 'orange' OR favorite_color = 'green';

#13

SELECT * FROM person WHERE favorite_color IN ( 'orange', 'green', 'blue' );

#14

SELECT * FROM person WHERE favorite_color IN ( 'yellow', 'purple' )

Table - orders

Instructions

Create a table called orders that records: person_id, product_name, product_price, quantity.
Add 5 orders to the orders table.
- Make orders for at least two different people.
- person_id should be different for different people.
Select all the records from the orders table.
Calculate the total number of products ordered.
Calculate the total order price.
Calculate the total order price by a single person_id.

My Answers

CREATE TABLE orders( person_id SERIAL PRIMARY KEY, product_name VARCHAR, product_price DECIMAL, quantity INT )

INSERT INTO orders(product_name, product_price, quantity) VALUES ('Shrimp', 10.82, 2) VALUES ('Chicken', 8.22, 5) VALUES ('Halibut', 14.22, 1) VALUES ('Lobsert', 18.96, 2) VALUES ('Steak', 12.56, 3)

SELECT * FROM orders

SELECT SUM(quantity) FROM orders

SELECT SUM(quantity*product_price) FROM orders

ELECT SUM(quantity*product_price) FROM orders Where person_id = 4

Solution

SQL Solutions

#1

CREATE TABLE orders ( person_id SERIAL, product_name VARCHAR(200), product_price NUMERIC, quantity INTEGER );

#2

INSERT INTO orders ( person_id, product_name, product_price, quantity ) VALUES ( 0, 'Product', 12.50, 2 );

#3

SELECT * FROM orders;

#4

SELECT SUM(quantity) FROM orders;

#5

SELECT SUM(product_price * quantity) FROM orders;

#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 orders WHERE person_id = 0;

Table - artist

My Answers

INSERT INTO artist(name) VALUES ('P!nk') VALUES ('Ed Sheeran') VALUES ('Charlie Puth')

SELECT * FROM artist ORDER BY name DESC LIMIT 10

SELECT * FROM artist ORDER BY name ASC LIMIT 5

SELECT * FROM artist WHERE name LIKE 'Black%'

SELECT * FROM artist WHERE name LIKE '%Black%'

Instructions

Add 3 new artists to the artist table. ( It's already created )
Select 10 artists in reverse alphabetical order.
Select 5 artists in alphabetical order.
Select all artists that start with the word 'Black'.
Select all artists that contain the word 'Black'.

Solution

SQL Solutions

#1

INSERT INTO artist ( name ) VALUES ( 'artist name' );

#2

SELECT * FROM artist ORDER BY name DESC LIMIT 10;

#3

SELECT * FROM artist ORDER BY name ASC LIMIT 5;

#4

SELECT * FROM artist WHERE name LIKE 'Black%';

#5

SELECT * FROM artist WHERE name LIKE '%Black%';

Table - employee

My Answers

SELECT first_name, last_name FROM employee WHERE city = 'Calgary'

SELECT * FROM employee ORDER BY(birth_date) DESC Limit 1

SELECT * FROM employee ORDER BY(birth_date) ASC Limit 1

SELECT * FROM employee WHERE reports_to = 1

SELECT COUNT(city) FROM employee WHERE city = 'Lethbridge'

Instructions

List all employee first and last names only that live in Calgary.
Find the birthdate for the youngest employee.
Find the birthdate for the oldest employee.
Find everyone that reports to Nancy Edwards (Use the ReportsTo column).
- You will need to query the employee table to find the Id for Nancy Edwards
Count how many people live in Lethbridge.

Solution

SQL Solutions

#1

SELECT first_name, last_name FROM employee WHERE city = 'Calgary';

#2

SELECT MAX(birth_date) from employee;

#3

SELECT MIN(birth_date) from employee;

#4

SELECT * FROM employee WHERE reports_to = 2;

#5

SELECT COUNT(*) FROM employee WHERE city = 'Lethbridge';

Table - invoice

My Answers

SELECT COUNT(billing_country) FROM invoice WHERE billing_country = 'USA'

SELECT * FROM invoice ORDER BY(total) DESC Limit 1

SELECT * FROM invoice ORDER BY(total) ASC Limit 1

SELECT * FROM invoice WHERE total > 5

SELECT COUNT(total) FROM invoice WHERE total < 5

SELECT COUNT(total) FROM invoice WHERE billing_state IN ('CA', 'TX', 'AZ')

SELECT AVG(total) FROM invoice

SELECT SUM(total) FROM invoice

Instructions

Count how many orders were made from the USA.
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 in CA, TX, or AZ (use IN).
Get the average total of the orders.
Get the total sum of the orders.

Solution

SQL Solutions

#1

SELECT COUNT(*) FROM invoice WHERE billing_country = 'USA';

#2

SELECT MAX(total) FROM invoice;

#3

SELECT MIN(total) FROM invoice;

#4

SELECT * FROM invoice WHERE total > 5;

#5

SELECT COUNT(*) FROM invoice WHERE total < 5;

#6

SELECT COUNT(*) FROM invoice WHERE billing_state in ('CA', 'TX', 'AZ');

#7

SELECT AVG(total) FROM invoice;

#8

SELECT SUM(total) FROM invoice;

Resources

SQL

Contributions

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.

Copyright

© 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.

blamb31/sql-1-afternoon

Project Summary

Table - person

Instructions

My Answers

Solution

Table - orders

Instructions

My Answers

Solution

Table - artist

My Answers

Instructions

Solution

Table - employee

My Answers

Instructions

Solution

Table - invoice

My Answers

Instructions

Solution

Resources

Contributions

Copyright