Advanced SQL Queries

file: dvdrental2_for_output2

Task 1

Manager asked me to provide the syntactically correct SQL statement for creating a new column titled "title_description".

Query

SELECT CONCAT(title,'-',description) AS title_description FROM film

---

Task 2

A fellow data analyst couldn't understand how the following SQL queries were not working in the database. I was asked to provide a brief explanation and the corrected syntax.

select title,rental_rate,
case 
when rental_rate > 2 then ‘Expensive
when rental_rate = 2 then ‘Average’
when rental_rate < 2 then ‘Cheap’
from film

Comments & Syntax

It won’t work because there was a lack of the end-statement to end the case (ex. end as length_analysis).

select title,rental_rate,
case 
	when rental_rate > 2 then 'Expensive'
	when rental_rate = 2 then 'Average'
	when rental_rate < 2 then 'Cheap'
end as length_analysis
from film

---

Task 3

The store supervisor wants to check the performance of the two staff that they have by checking all the payments that the staff processed. The supervisor established a quota of 25,000 for all the payments processed by the staff and would like to see a report showing the total payment amount processed by each staff and a column saying whether they met or did not meet the quota. Manager debriefed me the store supervisor's request and tasked me to provide a query and output.

select staff_id, sum(amount),
case
	when sum(amount) = 25000 then 'Quota Met'
	when sum(amount) > 25000 then 'Quota Met'
	when sum(amount) < 25000 then 'Quota Unmet'
end as quota_rating
from payment
group by staff_id
order by staff_id asc

Output