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: