Question

Consider Sakila Database. Write a query in mySQL workbench to answer each of following questions.

Q1) By looking at city table.

Write a query that identify the in how many cities the company has branches. Rename the column name to TotalNumberOfCities.
You have to look at city table and write a query to count number of rows.

Q2) By looking at film table.

How many films we have that its description contains the word "Robot". Count number of films and rename the column to NumberofRobots

Q3) By looking at film table.
How many films we have that its description contains the word "Truman".

Count number of films and rename the column to NumberofTruman

Q4) By looking at film table.

How many films we have that its description contains the word “ Robot” or "Truman". Count number of films and rename the column to RobotORTruman

Q5) By looking at film table.

How many films we have that its description contains the word “ Robot” and "Truman". Count number of films and rename the column to RobotANDTruman

Q6) By looking at film table.

Group film by special_features and count how many films are in each special features. Rename the column to NumberofFilms

Q7) By looking at film table.

Order query 6 by NumberofFilms in descending format.

Answer 1

Question 1:

SQL query :

select count(city_id) as TotalNumberOfCities from city;

• This SQL query is using count() function to count the total cities.

*************************************

Question 2:

SQL query :

select count(film_id) as NumberofRobots from film where description like '%Robot%';

• This SQL query is using count() function to count the films that contains word Robot in the description along with like clause

*************************************

Question 3:

SQL query :

select count(film_id) as NumberofTruman from film where description like '%Truman%';

• This SQL query is using count() function to count the films that contains word Truman in the description along with like clause

*************************************

Question 4:

SQL query :

select count(film_id) as RobotORTruman from film where
description like '%Robot%' or
description like '%Truman%';

• This SQL query is using count() function to count the films that contains word Truman or Robort in the description along with like clause

*************************************

Question 5:

SQL query :

select count(film_id) as RobotANDTruman from film where
description like '%Robot%' AND
description like '%Truman%';

• This SQL query is using count() function to count the films that contains word Truman and Robort in the description along with like clause

*************************************

Question 6:

SQL query :

select special_features, count(film_id) as NumberofFilms from film
group by special_features;

• This SQL query will group the films by special features.

*************************************

Question 7:

SQL query :

select special_features, count(film_id) as NumberofFilms from film
group by special_features
order by NumberofFilms desc;'

• This SQL query will group the films by special features and order the records by NumberofFilms in descending orders

*************************************