SQL LAB


SECTION 2

1: Select statement syntax

2: Select everything from a table

3: Select 2 columns

4: We want to send out a promotional email to our existing customers

5: Find the number of unique release_years in the film table

6: We want to know the rating in our film table

7: How many unique names are there in the table

8:Find the number of distinct payment amounts in the payment table (How many distinct amounts are there)

9: Get only the people named David… col1(name) col2 (choice) table=table

10: add in the choice of red 

11: Get all the customers with first_name jared 

12: find all rental_rate greater than 4 from film

also replacement_cost greater than or equal to 19.99

13: Get the count of movies that have pg-13 or R in the film table

14: make the rating not equal to pg-13/r

15: A customer forgot their wallet at the store, We need to track down their email to inform them. What is the email for Nancy Thomas

16: A customer wants to know what the movie ‘outlaw panky’ is about, can you give them a description

17: A customer is late on their payment, their address is 259 ipoh drive, can you find their phone number

18: what were the 5 most recent payments based off the payment date?

19: We want to reward our first 10 paying customers. What are the customer ids of the first 10 customers who created a payment 

20: What are the title of the 5 shortest in length movies? 

21: if the previous customer can watch any movie 50 minutes of length in runtime or less

how many options does she have? 

22: Find the number of payments done between 8 and 9 dollars

23: Find all the payments that happened on the first half of February 2007 and the 15th

24: Find the distinct payment amounts from payment table

25: Find all payments that are .99c,1.98,1.99

26: Find all customers named john, jake, and julie

27: Find all customers who name starts with J

28: Find all customers with er somewhere in their name






1A: SELECT column FROM table;

2A: SELECT * FROM table;

3A: SELECT column1,colum2 FROM table;

4A: SELECT first_name, last_name, email FROM customer;

5A: SELECT DISTINCT release_years FROM film;

6A: SELECT DISTINCT rating FROM film;

7A: SELECT COUNT(DISTINCT name) FROM film;

8A: SELECT COUNT(DISTINCT amount FROM payment;

9A: SELECT name,choice FROM table

….  WHERE name = ‘David’;

10a: SELECT name,choice FROM table

…    WHERE name = ‘David’ AND choice = ‘red’;

11a: SELECT * FROM customers

..     WHERE first_name = ‘Jared’;

12a: SELECT * FROM film

.        WHERE rental_rate > 4 AND replacement_cost >= 19.99;

13a: SELECT COUNT(*) FROM film

. WHERE rating = ‘pg-13’ OR rating = ‘r’;

14a: SELECT COUNT(*) FROM film

. WHERE rating != ‘pg-13’ OR rating != ‘r’;

15a: SELECT email FROM customer

. WHERE first_name = ‘Nancy’ AND last_name = ‘Thomas’;

16a: SELECT description FROM film

. WHERE title = ‘Outlaw Panky’;

17a: SELECT phone FROM address

. WHERE address = ‘259' IPOH Drive’;

18a: SELECT * FROM payment

. ORDER BY payment_date DESC

. LIMIT 5;

19: SELECT customer_id FROM payment

.     ORDER BY payment_date ASC

.      LIMIT 10;

20: SELECT title,length FROM film

.     ORDER BY length ASC

.     LIMIT 5

21: SELECT COUNT(title) FROM film

.      WHERE length <= 50;

22: SELECT * FROM payment

.  WHERE amount BETWEEN 8 AND 9;

23: SELECT * FROM payment

.     WHERE payment_date BETWEEN ‘2007-02-01’ AND ‘2007-02-15’;

24: SELECT DISTINCT(amount) FROM payments;

25: SELECT * FROM payment

. WHERE amount IN (0.99,1.98,1.99)

26: SELECT * FROM customer

. WHERE first_name IN (‘john’,’jake’,’julie’);

27: SELECT * FROM customer

. WHERE first_name LIKE ‘J%’;

28: SELECT * FROM customer

. WHERE first_name LIKE ‘er%’;










SECTION 3


1: Find the lowest replacement cost of a movie in film table

2: Find the most you’d have to pay to replace film

3: Find the Average replacement cost

4: only get 2 decimal places back on avg

5: Total cost of replacement_cost column from film table

6: Which customer is spending the most money in total?

7: Find how many transactions each customer is having

8: Find how much each customer spent with each staff member

9: Find out how much is being spent per day

10: Find the staff member who handled the most amount of payments

11: What is the average replacement cost per film rating?

12: What are the top 5 customer ids by total spend?

13:  Filter by Sum (amount) > 100

14: We are launching a platinum service for our most loyal customers that have had 40 or more transaction payments. What customer id’s are eligible for platinum status?

15: What are the customer id’s of customers who have spent more than $100 in payment transactions with our staff_id member 2



1A: SELECT MIN (replacement_cost) FROM film;

2A: SELECT MAX (replacement_cost) FROM film;

3A: SELECT AVG (replacement_cost) FROM film;

4A: SELECT ROUND(AVG(replacment_cost) 2, FROM film;

5A: SELECT SUM(replacement_cost) FROM film;

6A: SELECT customer_id,SUM(amount) FROM payment

. GROUP BY customer_id

. ORDER BY SUM(amount) DESC | ASC

7A: SELECT customer_id,COUNT(amount) FROM payment

. GROUP BY customer_id

. ORDER BY COUNT(amount) DESC | ASC

8A: SELECT customer_id,staff_id,SUM(amount) FROM payment

. GROUP BY staff_id, customer_id

. ORDER BY customer_id

9a: SELECT DATE(payment_date), SUM(amount) FROM payment

. GROUP BY DATE (payment_date)

10: SELECT staff_id, COUNT(amount)

.     FROM payment

.     GROUP BY staff_id

11: SELECT rating, AVG(replacement_cost)

.     FROM film

.     GROUP BY rating

12: SELECT customer_id, SUM (amount)

.     FROM payment 

.     GROUP BY customer_id

.     ORDER BY SUM(amount) DESC

.      LIMIT 5

13: SELECT customer_id,SUM(amount) FROM payment

. GROUP BY customer_id

. HAVING SUM(amount) > 100;

14: SELECT customer_id, COUNT(*) FROM payment

. GROUP BY customer_id

. HAVING COUNT(*) >= 40;

15: SELECT customer_id, SUM(amount) FROM payment

. WHERE staff_id = 2

. GROUP BY customer_id

. HAVING SUM(amount) > 100;








Section 5


1: change Display/name of “amount” column to “rental price” (payment table)

2: Have Sum(amount) displayed/name as net_revenue

3: Do a group by…. column: customer id… Agg SUM(amount) to total_spent Table: payment

Also filter the Aggregate where it’s > 100

4:


1: SELECT amount AS rental_price FROM payment;

2: SELECT sum(amount) AS net_revenue FROM payment;

3: SELECT customer_id,SUM(amount) AS total_spent

. FROM payment

. GROUP BY customer_id

. HAVING SUM(amount) > 100;

4: