Basic SQL Queries

The following tasks were given by Refocus. As a data analyst in a DVD rental store, I was given a database that can be opened and used in PgAdmin to extract, clean and process data. Instructions were sent to me from sales and operations to identify patterns such as popular genres, peak rental times, and late returns. The following tasks require basic SQL skills, problem-solving, and communication skills.

file: dvdrental.tar

Task 1

Manager asked for the titles and the length of films that start with the letters “S” and “T” and have a replacement rate of 15.99 to 20.99. They only needed the first 20 rows starting from the films with the longest running time.

Query

SELECT title,replacement_cost FROM film
WHERE replacement_cost BETWEEN 15.99 and 20.99
AND title LIKE 'S%'
OR title LIKE 'T%'

Output

---

Task 2

Manager asked for the IDs and the titles of films with a running time of less than 100 minutes and replacement costs greater than 15.99. They only needes the films with ratings G, PG and PG-13. The output should be ordered by title in ascending order

Query

SELECT film_id,title FROM film
WHERE length < 100
AND replacement_cost BETWEEN 15.99 and 20.99
AND rating IN ('G','PG','PG-13')

Output

---

Task 3

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.

-----Query 1
SELECT * FROM film
WHERE rating = G

-----Query 2
SELECT customer_id FROM film

Comments & Syntax

When sorting a table with a certain value/info, we need to add apostrophes surrounding the value to ensure that the query can be read. The correct syntax is the following:

-----Corrected Query 1
SELECT * FROM film
WHERE rating = 'G'

There is an error due to the incorrect name of column. Customer_id is not found from the film table, however, what can be used is the film_id column since it’s found in that table.

-----Corrected Query 2
SELECT film_id FROM film

---

Task 4

There was a discrepancy with a financial report wherein the payment transactions for a customer with customer id # 514 was missing. I was instructed to provide the customer's payment transactions.

Query

SELECT amount FROM payment
WHERE customer_id=514

---

Task 5

The regulations officer asked from me to provide the number of R Rated films the DVD rental have from the regular media audit.

Query

SELECT COUNT(rating) FROM film
WHERE rating='R'

Output