SQL Commands Flashcards
(110 cards)
1
Q
SELECT ALL
A
SELECT * FROM abc_table
2
Q
CREATE A TABLE FROM SCRATCH
A
CREATE TABLE favoritebooks(id INTEGER PRIMARY KEY, name TEXT, rating INTEGER); INSERT INTO favoritebooks VALUES (1, "Brown Bear", 10), (2, "If You Give A Mouse A Cookie" , 7), (3, "The Hungry Caterpillar" , 9) ;
3
Q
INSERT INTO favoritebooks _____? WHAT IS MISSING
A
INSERT INTO favoritebooks VALUES
4
Q
Find Movie of 2000 or higher and sort by ASC release_year CREATE TABLE movies (id INTEGER PRIMARY KEY, name TEXT, release_year INTEGER);
INSERT INTO movies VALUES (1, “Avatar”, 2009);
INSERT INTO movies VALUES (2, “Titanic”, 1997);
INSERT INTO movies VALUES (3, “Star Wars: Episode IV - A New Hope”, 1977);
INSERT INTO movies VALUES (4, “Shrek 2”, 2004);
INSERT INTO movies VALUES (5, “The Lion King”, 1994);
INSERT INTO movies VALUES (6, “Disney’s Up”, 2009);
SELECT * FROM movies;
A
SELECT * FROM movies
WHERE release_year >= 2000
ORDER BY release_year ASC;
5
Q
Insert data into table lazy way
A
INSERT INTO active_wear VALUES
(1, “leggings”, “black”, “medium”, “Adidas”, 15, 59.99),
(2, “jacket”, “pink”, “small”, “Adidas”, 25, 59.99),
(3, “SOCKS”, “BLACK AND WHITE”, “unisex”, “Nike”, 50, 12.99),
(4, “Women T-shirt”, “White”, “large”, “Adidas”, 250, 35.99),
6
Q
HOW MANY FROM A BRAND?
A
SELECt COUNT(brand) FROM active_store WHERE brand = "Nike";
7
Q
Max product quantity
A
SELECT MAX(quantity), item FROM active_store;
8
Q
Min product
A
SELECT MIN(price), item FROM active_store;
9
Q
There is not BY after where
A
There is not BY after where
10
Q
Order by price DESC - from high to lowest
A
SELECT * FROM store
ORDER BY price desc;
11
Q
Order price by ASC price
A
SELECT * FROM store
ORDER BY price desc;
12
Q
Select all columns and rows from the customers table where the value in the age column is greater than 21 and thevalue in the state column is ‘PA’
hint there is not BY after Where and , after AND (served on next line)
A
SELECT *FROM customers
WHERE age > 21
AND state = ‘PA’
13
Q
Select all columns and rows from the customers table where the value in the plan column is “free” or “basic”
A
SELECT *FROM customers
WHERE plan IN (“free”, “basic”)
14
Q
How to use WHERE and INCLUDING in conditional statement
A
SELECT *FROM customers
WHERE plan IN (“free”, “basic”)
15
Q
Select all columns and rows from the customers table where the value in the age column is greater than 21, and order the results by age starting with the highest value and DESC down
A
SELECT *FROM customers
WHERE age > 21
ORDER BY age DESC
16
Q
ORDER IS FOLLOWED BY BY
A
ORDER BY
17
Q
Select the gender column and the number of rows in the students table, and group by the value of the gender column
A
SELECT gender, COUNT(*)FROM students
GROUP BY gender
18
Q
Create a column called “type” which assigns whether someone is an “adult” or “minor” based on their age
A
SELECT name, CASE WHEN age > 18 THEN “adult”
ELSE “minor” END “type”
FROM customers
19
Q
Create a column called “flag” which assigns a 1 if someone’s tenure is greater than 5 years
A
SELECT name, CASE WHEN sum(tenure) > 5
THEN 1ELSE 0 END “flag”
FROM customers
20
Q
Select only the max age from the customers table
A
SELECT MAX(age)FROM customers
21
Q
Join the customers table and orders table based on customer ID to select all instances of “name” from the customers table and show then associated “item” from the orders table.
A
SELECT customers.name, orders.item
FROM customers
LEFT JOIN ordersON customers.id = orders.customer_id
22
Q
This will show you the names of all the columns in a table
A
HELP TABLE database.table
23
Q
This will give you a sample of 20 rows from every column in the table
A
SELECT * FROM database.table LIMIT 20or
or
SELECT TOP 20 * FROM database.table
24
Q
SET ID TO autoincrement
A
CREATE TABLE exercise_logs
(id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT,
minutes INTEGER,
calories INTEGER,
heart_rate INTEGER);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“biking”, 30, 100, 110);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“biking”, 10, 30, 105);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“dancing”, 15, 200, 120);
25
Select all in tables which activities you have done that burns more that 50 calories and order by calories burned
SELECT * FROM exercise_logs WHERE calories > 50 ORDER BY calories;
26
AND operator
FIND ACTIVITIES WHERE CALORIE BURN IS MORE THAT 50 AND MINUTES ARE LESS THAN 30 MINS
SELECT * FROM exercise_logs WHERE calories > 50 AND minutes < 30;
27
/ OR / OPERATOR
SELECT * FROM exercise_logs WHERE calories > 50 OR heart_rate > 100;
28
USING WHERE AND OR OPERATOR TOGETHER
SELECT title FROM songs ;
SELECT title FROM songs WHERE mood = "epic"
OR releaseD > 1990 ;
29
Several Where and AND
SELECT title FROM songs ;
SELECT title FROM songs WHERE mood = "epic"
OR released > 1990 ;
SELECT title FROM songs WHERE mood = "epic"
AND released > 1990 AND duration <= 240;
30
SHOW JUST TYPING LOGS
SELECT * FROM exercise_logs WHERE type = "biking";
31
FIND TYPE OF ACTIVITIES THAT ARE OUTSIDE ACTIVITIES USING WHERE AND OR OPERATORS
Second example show IN operator
SELECT * FROM exercise_logs WHERE type = "biking" OR type = "hiking" OR type = "tree climbing" OR type = "rowing";
second example
SELECT * FROM exercise_logs WHERE type IN ("biking", "hiking", "tree climbing", "rowing");
or not
SELECT * FROM exercise_logs WHERE type IN ("biking", "hiking", "tree climbing", "rowing");
32
creation of new table
INSERT INTO drs_favorites(type, reason) VALUES ("biking", "Improves endurance and flexibility.");
INSERT INTO drs_favorites(type, reason) VALUES ("hiking", "Increases cardiovascular health.");
33
FIND DOCTOR RECOMMENDED ACTIVITY
SELECT type FROM drs_favorites;
SELECT * FROM exercise_logs WHERE type IN (
SELECT type FROM drs_favorites);
34
SELECT type FROM drs_favorites WHERE reason = "Increases cardiovascular health.");
SELECT type FROM drs_favorites WHERE reason = "Increases cardiovascular health.");
35
like use
SELECT type FROM drs_favorites WHERE reason = "Increases cardiovascular health.");
36
find sum of calories by type of activities and create new column in table
SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;
37
HAVING IS USED FOR TOTAL GROUP AFTER GROUP BY AND AND INDIVIDUAL CALCULATIONS
SELECT * FROM exercise_logs;
SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;
SELECT type, SUM(calories) AS total_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 150
38
Another having problem
SELECT * FROM exercise_logs;
SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 50
39
SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*)
SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*)
40
PERFECT HAVING EXAMPLE remember no comma
SELECT author, sum(words) as total_words from books
GROUP BY author
HAVING total_words>1000000;
41
We've created a database of a few popular authors and their books, with word counts for each book. In this first step, select all the authors who have written more than 1 million words, using GROUP BY and HAVING. Your results table should include the 'author' and their total word count as a 'total_words' column.
SELECT author, sum(words) as total_words from books
GROUP BY author
HAVING total_words>1000000;
42
Now select all the authors that write more than an average of 150,000 words per book. Your results table should include the 'author' and average words as an 'avg_words' column.
SELECT author, avg(words) as avg_words
FROM books
GROUP BY author
HAVING avg_words>150000;
43
What does an organization ask you to do?
Purchase habits, how many customer use A service, How many use A and B, what is the age age range customer selects what service, how often users login, what is the average lengh of time user login any time
44
Heart rate count example
SELECT COUNT(*) FROM exercise_logs WHERE heart_rate > 220 - 30;
45
target rate heart btwn 50% n 80%
/* 50-90% of max*/
SELECT COUNT(*) FROM exercise_logs WHERE
heart_rate >= ROUND(0.50 * (220-30))
AND heart_rate <= ROUND(0.90 * (220-30));
46
Use of CASE PART 1 & PART 2
PART 1
```
/* CASE */
SELECT type, heart_rate FROM exercise_logs;
```
PART 2
/* CASE */
SELECT type, heart_rate,
CASE
WHEN heart_rate > 220-30 THEN "above max"
WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
ELSE "below target"
END as "hr_zone"
FROM exercise_logs;
47
CASE PART 3
/* CASE */
SELECT type, heart_rate,
CASE
WHEN heart_rate > 220-30 THEN "above max"
WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
ELSE "below target"
END as "hr_zone"
FROM exercise_logs;
48
In this first step, select all of the rows, and display the name, number_grade, and percent_completed, which you can compute by multiplying and rounding the fraction_completed column.
SELECT name, number_grade, ROUND (fraction_completed*100) AS percent_completed FROM student_grades;
49
The goal is a table that shows how many students have earned which letter_grade. You can output the letter_grade by using CASE with the number_grade column, outputting 'A' for grades > 90, 'B' for grades > 80, 'C' for grades > 70, and 'F' otherwise. Then you can use COUNT with GROUP BY to show the number of students with each of those grades.
SELECT COUNT (*),
CASE
WHEN number_grade > 90 THEN "A"
WHEN number_grade > 80 THEN "B"
WHEN number_grade > 70 THEN "C"
ELSE "F"
END as "letter_grade" FROM student_grades
GROUP BY "letter_grade";
50
Identity and create column for particular time decade
SELECT COUNT (*),
CASE
WHEN Year >= 2000 THEN "2000's Movies"
WHEN Year <= 1999 THEN "90's Movies"
ELSE "Unknown"
END as "Movie_Time_Decade" FROM topmovies
GROUP BY "Movie_Time_Decade";
51
AVERAGE EXER burns more than 50 cals
SELECT * FROM exercise_logs;
SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
GROUP BY type
HAVING total_calories > 50
52
Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.
SELECT customer_id,SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) > 110;
53
How many films begin with the letter J?
SELECT COUNT(*) FROM film
WHERE title LIKE 'J%';
54
What customer has the highest customer ID number whose name starts with an 'E' and has an address ID lower than 500?
SELECT first_name,last_name FROM customer
WHERE first_name LIKE 'E%'
AND address_id <500
ORDER BY customer_id DESC
LIMIT 1;
55
AS CLAUSE
SELECT column_name AS new_name
| FROM TABLE
56
SELECT amount AS rental_price FROM payment
SELECT amount AS rental_price FROM paymen
57
SELECT SUM(amount) AS net_revenue FROM payment;
SELECT SUM(amount) AS net_revenue FROM payment;
58
How much each customer has spent
SELECT customer_id, Sum(amount)
From payment
GROUP BY customer_id;
or
SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id;
59
SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;
SELECT customer_id, SUM(amount) AS total_spend
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;
60
SELECT INNER JOIN CODE
SELECT * FROM table_a
INNER JOIN table_b
ON table_A.colum_match=table_B.columnmatch
SELECT*FROM Registrations
INNER JOIN Logins
ON Registrations.name=Logins.name
SELECT reg_id, Logins.name, log_id
SELECT*FROM Registrations
INNER JOIN Logins
ON Registrations.name=Logins.name
61
Innner join coding sample
SELECT*FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id
SELECT*FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id
SELECT payment_id, payment.customer_id, first_name FROM customer
INNER JOIN payment
On customer.customer_id=payment.customer_id
62
OUTER JOIN CODE
SELECT * FROM TABLE_B
FULL OUTER JOIN TABLEA
ON TABL_B.COL_MATCH-TABLEA_MATCHTABLE
EXAMPLE
SELECT*FROM Registration
FULL OUT JOIN Logins
ON Registration.name=Logins.name
EXAMPLE
```
SELECT*FROM Registration
FULL OUT JOIN Logins
ON Registration.name=Logins.name
WHERE Registration.id IS null OR
Logins.id IS null
```
63
FULL OUTER JOIN CODE / NULL OF BOTH TABLE UNIQUENESS
```
SELECT*FROM payment
FULL OUTER JOIN customer
ON customer.customer_id = payment.customer_id
WHERE payment.customer_id IS null OR
customer.customer_id IS null
```
64
LEFT OUTER JOIN CODE
SELECT*FROM payment
left OUTER JOIN customer
ON customer.customer_id = payment.customer_id
65
LEFT JOIN ONLY UNIQUE TO TABLE B AND NOT FOUND IN TABLE A (payment) and only found in table b
SELECT*FROM payment
left OUTER JOIN customer
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null
66
SELECT film.film_id, title, inventory_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id
SELECT film.film_id, title, inventory_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id
67
```
SELECT film.film_id, title, inventory_id,store_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS null
```
```
SELECT film.film_id, title, inventory_id,store_id
FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS null
```
68
SELECT film.film_id, title, inventory_id
FROM film
right OUTER JOIN inventory
ON inventory.film_id = film.film_id
SELECT film.film_id, title, inventory_id
FROM film
right OUTER JOIN inventory
ON inventory.film_id = film.film_id
69
UNION
SELECT*FROM Sales2021_Q1
UNION
SELECT*FROM Sales2021_Q2
```
OR ADD ORDER BY
SELECT*FROM Sales2021_Q1
UNION
SELECT*FROM Sales2021_Q2
Order by name
```
70
What are the customer email of customer who live in california?
SELECT district, email FROM address
INNER JOIN customer
ON address.address_id=customer.address_id
WHERE district = "california"
71
JOIN CODE /* INNER JOIN */
SELECT persons.name, hobbies.name FROM persons
JOIN hobbies
ON persons.id = hobbies.person_id
WHERE persons.name = "Bobby McBobbyFace"
72
SELECT students.first_name, students.last_name, student_projects.title
FROM students
JOIN student_projects
ON students.id = student_projects.student_id;
SELECT students.first_name, students.last_name, student_projects.title
FROM students
JOIN student_projects
ON students.id = student_projects.student_id;
73
SELECT customers.name, customers.email, orders.item, orders.price
FROM customers
LEFT OUTER JOIN orders
ON customers.id=orders.customer_id;
```
SELECT customers.name, customers.email, orders.item, orders.price AS spent FROM customers
LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY spent desc;
```
74
SELECT customers.name,customers.email,sum(orders.price) as spent
FROM customers
LEFT OUTER JOIN orders
ON customers.id=orders.customer_id
GROUP BY customers.name
ORDER BY spent desc;
SELECT c.name,c.email,sum(o.price) as spent
FROM customers c
LEFT OUTER JOIN orders o
ON c.id=o.customer_id
GROUP BY c.name
ORDER BY spent desc;
75
SELF JOIN
SELECT students.first_name, students.last_name, buddies.email as buddy_email
FROM students
JOIN students buddies
ON students.buddy_id = buddies.id;
SELF JOIN
SELECT students.first_name, students.last_name, buddies.email as buddy_email
FROM students
JOIN students buddies
ON students.buddy_id = buddies.id;
76
```
SELECT a.fullname, b.fullname
FROM friends
JOIN persons a
ON a.id = friends.person1_id
JOIN persons b
ON b.id = friends.person2_id;
```
```
SELECT a.fullname, b.fullname
FROM friends
JOIN persons a
ON a.id = friends.person1_id
JOIN persons b
ON b.id = friends.person2_id;
```
77
USE NEXT TIME
SELECT people.show_id, people.director, titles.title, titles.type
FROM "CharlotteChaze/BreakIntoTech"."netflix_people" people
LEFT JOIN "CharlotteChaze/BreakIntoTech"."netflix_titles_info" titles
ON people.show_id = titles.show_id ;
78
```
SELECT TIMEOFDAY ( )
SELECT NOW ( )
SELECT CURRENT_TIME
```
```
SELECT TIMEOFDAY ( )
SELECT NOW ( )
SELECT CURRENT_TIME
```
79
EXTRACT ( )
AGE ()
TO_CHAR( )
EXTRACT ( )
AGE ()
TO_CHAR( )
80
EXTRACT (YEAR FROM date_col)
EXTRACT (YEAR FROM date_col)
81
```
SELECT EXTRACT(YEAR FROM payment_date)
FROM payment
```
```
SELECT EXTRACT(QUARTER FROM payment_date)
FROM payment
```
```
SELECT EXTRACT(YEAR FROM payment_date)
FROM payment
```
```
SELECT EXTRACT(QUARTER FROM payment_date)
FROM payment
```
82
```
SELECT TO_CHAR(payment_date, 'MONTH-YYYY')
FROM payment
```
```
SELECT TO_CHAR(payment_date, 'MONTH-YYYY')
FROM payment
```
83
```
SELECT TO_CHAR(payment_date, 'MM-DD-YYYY')
FROM payment
```
```
SELECT TO_CHAR(payment_date, 'MM-DD-YYYY')
FROM payment
```
84
```
SELECT DISTINCT (TO_CHAR(payment_date, 'MONTH'))
FROM payment
```
```
SELECT DISTINCT (TO_CHAR(payment_date, 'MONTH'))
FROM payment
```
85
UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE user_id=1 AND date = "2015-04-01";
UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE user_id=1 AND date = "2015-04-01";
86
UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE id = 1;
UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE id = 1;
87
DELETE FROM diary_logs WHERE id = 1;
SELECT * FROM diary_logs;
DELETE FROM diary_logs WHERE id = 1;
SELECT * FROM diary_logs;
88
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-01",
"I had a horrible fight with OhNoesGuy and I buried my woes in 3 pounds of dark chocolate.");
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-01",
"I had a horrible fight with OhNoesGuy and I buried my woes in 3 pounds of dark chocolate.");
89
ALTER TABLE diary_logs ADD emotion TEXT;
ALTER TABLE diary_logs ADD emotion TEXT;
90
ALTER TABLE diary_logs ADD emotion TEXT;
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-02",
"OhNoesGuy and I made up and now we're bes, emotiont friends forever and w3 celebrated with a tub of ice cream.");
We went to Disneyland!, "happy"
ALTER TABLE diary_logs ADD emotion TEXT;
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-02",
"OhNoesGuy and I made up and now we're bes, emotiont friends forever and w3 celebrated with a tub of ice cream.");
We went to Disneyland!, "happy"
91
delete all table
ALTER TABLE diary_logs ADD emotion TEXT;
INSERT INTO diary_logs (user_id, date, content) VALUES (1, "2015-04-02",
"OhNoesGuy and I made up and now we're bes, emotiont friends forever and w3 celebrated with a tub of ice cream.");
We went to Disneyland!, "happy"
92
SELECT * FROM clothes;
| INSERT INTO clothes (type, design, price) VALUES ("pants", "rainbow", 40.00);
SELECT * FROM clothes;
| INSERT INTO clothes (type, design, price) VALUES ("dress", "pink polka dots", 10.00);
93
ALTER TABLE clothes ADD price INTEGER;
SELECT * FROM clothes;
UPDATE clothes SET price = 10 WHERE id = 1;
UPDATE clothes SET price = 20 WHERE id = 2;
UPDATE clothes SET price = 30 WHERE id = 3;
SELECT * FROM clothes ;
ALTER TABLE clothes ADD price INTEGER;
SELECT * FROM clothes;
UPDATE clothes SET price = 10 WHERE id = 1;
UPDATE clothes SET price = 20 WHERE id = 2;
UPDATE clothes SET price = 30 WHERE id = 3;
SELECT * FROM clothes ;
94
SELECT * FROM clothes ;
INSERT INTO clothes (type, design, price) VALUES ("pants", "rainbow", 40.00);
SELECT*FROM clothes;
SELECT * FROM clothes ;
INSERT INTO clothes (type, design, price) VALUES ("pants", "rainbow", 40.00);
SELECT*FROM clothes;
95
During which months did payment occurred?
```
SELECT DISTINCT(TO_CHAR(payment_date, 'Month')
FROM payment
```
96
HOW MANY PAYMENTS OCCURED ON A MONDAY?
dow = monday 0=sunday monday=1 etc, SQL standard
```
Select COUNT (*)
FROM payment
Where EXTRACT (dow FROM payment_date) = 1
```
97
WHAT IS THE RENTAL RATE OF THE REPLACEMENT COST.
SELECT rental_rate/replacement_cost FROM film OR
```
SELECT ROUND(rental_rate/replacement_cost*100) FROM film
OR
SELECT ROUND(rental_rate/replacement_cost*100, 2) FROM film
```
98
HOW TO FIND LENGTH OF STRING - lastname
SELECT LENGTH(last_name) FROM customer
99
contatenation - togehter
SELECT first_name || last_name FROM customer
Add Space string
SELECT first_name || ' ' || last_name FROM customer
100
UPPER CASE STRINGS
SELECT UPPER(first_name) || ' ' || UPPER(last_name) FROM customer
101
CREATE CUSTOMER EMAILS FOR EMPLYEES USING FIRST LETTER OF NAME, FULL LAST NAME AND THEN @WHATEVER
SELECT LEFT(first_name, 1) || ' ' || last_name || '@gmail.com' FROM customer
CORRECTION
SELECT LEFT(first_name, 1) || last_name || '@gmail.com' FROM customer
102
CREATE EMAIL FOR EMPLOY
SELECT LOWER (LEFT(first_name, 1)) || LOWER(last_name) || '@gmail.com' FROM customer
SELECT LOWER (LEFT(first_name, 1)) || LOWER(last_name) || '@gmail.com' AS custom_email FROM customer
103
FIND STUDENT WHO SCORE HIGHER THAN THE AVERAGE, THUS THIS INVOLVES SUBQUERIES
SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)
104
FIND STUDENTS IN THE HONOR ROLL
SELECT student, grade
FROM test_scores
WHERE student In (SELECT student FROM honor_roll_table)
105
SELECT FILMS WITH RENTAL RATES HIGHER THAN THE AVERAGE RATE
SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film)
106
SELECT FILM TITLE THAT HAVE BEEN RETURNED BETWEEN A CERTAIN SET OF DATES
SELECT * FROM rental
| WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30'
107
SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30'
SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30'
108
```
SELECT film_id, title
FROM FILM
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30')
```
```
SELECT film_id, title
FROM FILM
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id=rental.inventory_id
WHERE return_date BETWEEN '2005-05-29'AND '2005-05-30')
```
109
SELECT first_name, last_name
FROM customer AS C
WHERE EXISTS(SELECT * FROM payment aS P WHERE p.customer_id=c.customer_id AND amount > 11)
SELECT first_name, last_name
FROM customer AS C
WHERE EXISTS(SELECT * FROM payment aS P WHERE p.customer_id=c.customer_id AND amount > 11)
110
SELECT emp.name, report.name
From employees AS emp
Join employees AS report
ON emp.employ_id=report.report_id
SELECT emp.name, report.name AS rep
From employees AS emp
Join employees AS report
ON emp.employ_id=report.report_id
