SQL: Assessment Test 1 - SQL Statement Fundamentals and GROUP BY Statements

Problem 1

Problem: Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.

-- Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.
SELECT staff_id ,customer_id , SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id, staff_id
HAVING SUM(amount) >= 110;
-- Solution TWO
SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_ID
HAVING SUM(amount) >= 110; 
##   staff_id customer_id    sum
## 1        2         187 110.81
## 2        2         148 110.78

Problem 2

Problem: How many films begin with the letter J?

-- Count the number of films that begin with the letter J
SELECT COUNT(title)
FROM film
WHERE title LIKE 'J%';
##   count
## 1    20

Problem 3

Problem: What customer has the highest customer ID number whose name starts with an ‘E’ and has an address ID lower than 500?

-- 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, customer_id 
FROM customer
WHERE first_name LIKE 'E%' AND address_id < 500
ORDER BY customer_id DESC
LIMIT 1;
##   first_name last_name customer_id
## 1      Eddie    Tomlin         434