DigiGirls's SQL Practice Assignment.

This is the assignment given at DigiGirls Data Analysis Training. DVD rental database was used for this assignment. It was done using PostgreSQL.

QUESTION 1.

Display the customer names that share the same address. (E.g husband and wife).

This was done by joining the customer table to itself and giving a condition for the customer_id that represents different customers not to be equal and the address_id that represents the same address of the customers to be equal.

The result of the query showed an empty table because there was no customers that shares the same address.

QUESTION 2.

What is the name of the customer who made the highest total payments?

QUESTION 3.

What is the movie(s) that were rented the most?

The number of times each film was rented was found using the first query, which was 34, and the second query returned the title of the film with film_id 103, which is the film_id that had a count of 34 from the previous query.

QUESTION 4.

Which movies have been rented so far.

QUESTION 5.

Which movies have not been rented so far?

The same code was used to answer question 4 and 5. while question 4 made used of the IN operator, question 5 made use of NOT IN operator.

QUESTION 6.

Which customers have not rented any movies so far?

All customers have rented movies so far according to the output of the query.

QUESTION 7.

Display each movie and the number of times it got rented.

QUESTION 8.

Show the first name, the last name and the number of movies each actor has acted in.

QUESTION 9.

Display the names of the actors who have acted in more than 20 movies.

I will use two appraoches to solve this problem. Firstly, I will create a temporary table that will house my first query and then write a query from the temporary table.