Practicing mySQL: Sakila Database

Introduction:
SQL is a powerful language used for managing, querying, and analyzing databases. It plays a crucial role in various industries, such as data science, web development, and business intelligence. In this blog post, we’ll explore a set of SQL questions and provide you with the correct answers and explanations to help you master this essential skill.

datasets

After 4 question we will explore SAKILA Database where we will answers few question associated with SQL database.

*Question 1:**
Match the Columns
Match the following with the appropriate SQL command.

• Options:*
• a-I, b-II, c-III, d-IV
• a-IV, b-III, c-II, d-I
• a-III, b-I, c-II, d-IV
• a-III, b-IV, c-I, d-II

**Answers:**
a-III, b-IV, c-I, d-II

**Explanation:**
- To find all the employees from the Employees table, you should use the SELECT command (Option III).
- To add information about a new employee, you should use the INSERT command (Option II).
- To change the structure of the Employees table, you should use the ALTER command (Option I).
- To remove the Employees table, you should use the DROP command (Option IV).

**Question 2:**
String Functions
What will be the output of the following query?

select reverse(substring('Sachin Tendulkar', -7, 3));

**Options:**
a. Te
b. eT
c. ndu
d. udn

**Answer:**
d. udn

**Explanation:**
The second parameter (-7) implies that you need to start from the seventh character as you traverse the string from the right. This is the character ‘n.’ Now, the third parameter (3) means that you need to select ’n’ and two characters after that. This results in the substring ‘ndu.’ Finally, applying the reverse function on this substring results in ‘udn.’

**Question 3:**
Benefits of a Data Warehouse
As an analyst working for a PAN India chain of hospitals, you have to convince the upper management to invest in building a data warehouse architecture. Which of the following is not a good reason in support of a data warehouse?

**Options:**
A. It will give a holistic view of various departments across hospital branches in different cities and, thus, help in better planning.
B. A data warehouse will make it easier to enter and update all patient-related information, as all the information would be stored in one place.
C. It will help preserve historic data, and hence, it will help in identifying trends.
D. It will assist in faster analysis and reporting, as relevant data can be retrieved easily using a warehouse.

**Answer:**
B. A data warehouse will make it easier to enter and update all patient-related information, as all the information would be stored in one place.

**Explanation:**
A data warehouse is not meant for creating or updating data. It is needed for data analysis. For entering and updating data, a transactional database is preferred.

**Question 4:**
Facts and Dimensions
Suppose you are an analyst at a bank, and you have a data file with the following six variables:
- Withdrawal amount
- Account balance after withdrawal
- Transaction charge amount
- Customer ID
- ATM ID
- Date of withdrawal

Based on a star schema, how many dimension variables would you need to determine the number of withdrawals made in Puducherry by a customer named Rohit Sharma?

**Options:**
A. At least 3
B. At least 1
C. At least 4
D. At least 2

**Answer:**
D. At least 2

• *Explanation:**
  You would require a customer name dimension, which connects with the customer ID, and an ATM location dimension, which connects to the ATM ID.

SAKILA DATABASE

The Sakila database is a nicely normalised schema modelling a DVD rental store, featuring things such as films, actors, film-actor relationships, and a central inventory table that connects movies, stores, and rentals. Download and study the Entity Relationship Diagram of the Sakila database available on the given link below:

Sakila Data : https://drive.google.com/drive/folders/1wQ7O5I0uJvbN3n6bMrueDKPvmVe_Z9pn?usp=sharing

Importing to MYSQL Workbench : https://youtu.be/bDKY_c4PRQA

(For importing data into your mysql workbenck kindly follow this video)

*Question 5:**
Busiest Actor
Write a query to find the full name of the actor who has acted in the maximum number of movies.

SELECT CONCAT(first_name, " ", last_name) AS Full_name
FROM ACTOR
LEFT JOIN FILM_ACTOR USING(ACTOR_ID)
GROUP BY Full_Name
ORDER BY COUNT(film_id) DESC
LIMIT 1;

**Explanation:**
This query uses a LEFT JOIN to combine the ACTOR and FILM_ACTOR tables, counting the number of movies each actor has acted in. It then orders the result in descending order and limits the output to the first result, which is the actor who has acted in the maximum number of movies.

**Question 6:**
Third most Busy Actor
Write a query to find the full name of the actor who has acted in the third most number of movies.

SELECT CONCAT(first_name, " ", last_name) AS Full_name
FROM ACTOR
LEFT JOIN FILM_ACTOR USING(ACTOR_ID)
GROUP BY Full_Name
ORDER BY COUNT(film_id) DESC
LIMIT 2, 1;

**Explanation:**
This query is similar to the previous one but uses the LIMIT clause with offset 2 and limit 1 to retrieve the third most busy actor.

**Question 7:**
Highest Grossing Film
Write a query to find the film which grossed the highest revenue for the video renting organization.

SELECT Title
FROM FILM
INNER JOIN inventory USING(FILM_ID)
INNER JOIN RENTAL USING(INVENTORY_ID)
INNER JOIN PAYMENT USING(RENTAL_ID)
GROUP BY Title
ORDER BY SUM(AMOUNT)
LIMIT 1;

**Explanation:**
This query joins the FILM, inventory, RENTAL, and PAYMENT tables, grouping the results by film title and calculating the total revenue (SUM(AMOUNT)). It then orders the results in ascending order and limits the output to the first result, which is the highest-grossing film.

*Question 8:**
Film-obsessed City
Write a query to find the city which generated the maximum revenue for the organization.

SELECT City
FROM CITY
INNER JOIN address USING(CITY_ID)
INNER JOIN CUSTOMER USING(ADDRESS_ID)
INNER JOIN PAYMENT USING(CUSTOMER_ID)
GROUP BY City
ORDER BY SUM(AMOUNT) DESC
LIMIT 1;

• *Explanation:**
  This query joins the CITY, address, CUSTOMER, and PAYMENT tables, grouping the results by city and calculating the total revenue (SUM(AMOUNT)). It then orders the results in descending order and limits the output to the first result, which is the city that generated the maximum revenue.

**Question 9:**
Analysis of Movie Categories
Write a query to find out how many times a particular movie category is rented. Arrange these categories in the decreasing order of the number of times they are rented.

SELECT Name, COUNT(Name) AS Rental_count
FROM category
INNER JOIN film_category USING(CATEGORY_ID)
INNER JOIN FILM USING(FILM_ID)
INNER JOIN inventory USING (film_id)
INNER JOIN rental USING (inventory_id)
GROUP BY Name
ORDER BY Rental_count DESC;

**Explanation:**
This query joins the category, film_category, FILM, inventory, and rental tables, counting the number of times each movie category is rented. It then orders the categories in descending order of rental count.

**Question 10:**
Science Fiction Enthusiasts
Write a query to find the full names of customers who have rented sci-fi movies more than 2 times. Arrange these names in alphabetical order.

SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS Customer_name
FROM category
INNER JOIN film_category USING(CATEGORY_ID)
INNER JOIN FILM USING(FILM_ID)
INNER JOIN inventory USING (film_id)
INNER JOIN RENTAL USING(INVENTORY_ID)
INNER JOIN CUSTOMER USING(CUSTOMER_ID)
WHERE Name = "sci-fi"
GROUP BY Customer_name
HAVING COUNT(RENTAL_ID) > 2
ORDER BY Customer_name;

Explanation: This query joins the category, film_category, FILM, inventory, RENTAL, and CUSTOMER tables, filtering for sci-fi movies and customers who have rented them more than 2 times. It then orders the customer names alphabetically.

**Question 11:**

Movie Fans from Arlington

Write a query to find the full names of those customers who have rented at least one movie and belong to the city Arlington.

SELECT CONCAT(FIRST_NAME, " ", LAST_NAME) AS Customer_name
FROM customer
INNER JOIN Rental USING(Customer_id)
INNER JOIN address USING(ADDRESS_ID)
INNER JOIN CITY USING(CITY_ID)
WHERE City = "Arlington"
GROUP BY Customer_name
HAVING COUNT(rental_id) >= 1;

Explanation: This query joins the customer, Rental, address, and CITY tables, filtering for customers from Arlington who have rented at least one movie. It then selects their full names and orders the result.

Question 11:

Country-wise Analysis of Movies

Write a query to find the number of movies rented across each country. Display only those countries where at least one movie was rented. Arrange these countries in alphabetical order.

SELECT country, COUNT(rental_id) AS Rental_count
FROM country
INNER JOIN city USING(COUNTRY_ID)
INNER JOIN ADDRESS USING(CITY_ID)
INNER JOIN CUSTOMER USING(ADDRESS_ID)
INNER JOIN RENTAL USING(CUSTOMER_ID)
GROUP BY country
ORDER BY country;

Explanation: This query joins the country, city, ADDRESS, CUSTOMER, and RENTAL tables, counting the number of movies rented in each country. It then filters out countries with no rentals and orders the result alphabetically.

Conclusion: SQL is a fundamental skill for anyone working with databases and data analysis. These SQL questions and explanations provide insights into various aspects of SQL, from basic commands to complex queries. Practice is essential to master SQL, so keep honing your skills by working on real-world database problems and challenges.

Thank me Later & Don’t forget to click on clap button!!!