Question

Write code in SQL.

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

Please find the query as below:

Q1) SELECT COUNT ( DISTINCT branch ) AS 'TotalNumberOfCities' FROM city;

Q2) SELECT COUNT (*) AS 'NumberOfRobots' FROM film WHERE description LIKE '%Robot%' ;

Q3) SELECT COUNT (*) AS 'NumberOfTruman' FROM film WHERE description LIKE '%Truman%' ;

Q4) SELECT COUNT (*) AS 'RobotORTruman' FROM film WHERE description LIKE '%Robot%' or description LIKE '%Truman%';

Q5) SELECT COUNT (*) AS 'RobotANDTruman' FROM film WHERE description LIKE '%Robot%'  AND description LIKE '%Truman%';

Q6) SELECT COUNT(special_feature) AS 'NumberOfFilms'
FROM film
GROUP BY special_feature;

Q7) SELECT COUNT(special_feature) AS 'NumberOfFilms'
FROM film
GROUP BY special_feature ORDER BY DESC;

Note:Please replace the Table and Column Names with your respective Table and Column names.