ADVANCED SQL COMMANDS

Question 1

SELECT DISTINCT(to_char(payment_date, ‘MONTH’)) FROM payment

Answer 1

Extracts the distinct payments months that were tracked in payment table. to_char function extracts the month from payment_date

Question 2

SELECT COUNT((to_char(payment_date, 'DAY'))), (to_char(payment_date, 'DAY')) AS payment_day
FROM payment
GROUP BY (to_char(payment_date, 'DAY'))

Answer 2

my alternate way of finding number of payments grouped day of week

Question 3

SELECT COUNT(*)
FROM payment
WHERE EXTRACT (DOW FROM payment_date) = 1

Answer 3

another way to get the totals for monday. DOW is day of week and Sunday is where index starts. Monday = 1.

Question 4

SELECT length(first_name) FROM customer

Answer 4

provides # of characters for each first name

Question 5

SELECT first_name |’ ‘| last_name FROM customer

Answer 5

concatenates first name and last name

Question 6

SELECT SUBSTRING(first_name from 1 for 1) || ‘.’ || last_name || ‘@nextit.com’ AS full_name FROM customer

Answer 6

my way to make a first letter of name and last name email

Question 7

SELECT LOWER(LEFT(first_name, 1)) || ‘.’ || LOWER(last_name) || ‘@nextit.com’ AS full_name FROM customer

Answer 7

different way to get a first name, last email

Question 8

SELECT title, rental_rate FROM film
WHERE rental_rate >
(SELECT AVG(rental_rate) FROM film)

Answer 8

returns list of films that have a rental rate greater than the average rental rate for all films (subquery)

Question 9

SELECT student, grade
FROM test_scores
WHERE student IN 
(SELECT student 
FROM honor_roll_table)

Answer 9

a subquery can operate on a seperate table. In this case subquery is operating on honor_roll_table. IN functions is used when multiple results are returned. (ie multiple students vs. one average score)

Question 10

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')

Answer 10

use the rental table to get a condition on return date. Then utilize inventory table to connect film_id because rental table and inventory table both have an inventory id. Then pull titles from film table.

Think of ‘IN’ function here as an opportunity to pull everything that matches a subquery. all the film_id is film table that match film_id in another query

Question 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)

Answer 11

Returns first name and last name from customer table where a row exists for the subquery within it. So any row for amount greater than 11. Highlighting the EXISTS function.

Question 12

SELECT f1.title, f2.title, f1.length
FROM film AS f1
INNER JOIN film as f2
ON f1.film_id != f2.film_id
AND f1.length = f2.length

Answer 12

this is a self join of the film table. Trying to match films with different titles but the same lengths. That does not equal portion of this is crucial otherwise you just match films together.