Consider the following MOVIE table and write the SQL query based on it. MovieID MovieName Category ReleaseDate ProductionCost BusinessCost 001 Hindi_Movie Musical 2018-04 - Computer Science (Python)

Question

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

Display all the information from the Movie table.
List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.
List the different categories of movies.
Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
List details of all movies which fall in the category of comedy or action.
List details of all movies which have not been released yet.

Short/Brief Note

Solution

SELECT * FROM MOVIE;
SELECT MovieID, MovieName, ProductionCost + BusinessCost as “Total_Earning” FROM MOVIE;
SELECT DISTINCT Category FROM MOVIE;
OR
SELECT DISTINCT(Category) FROM MOVIE;
SELECT MovieID, MovieName, BusinessCost – ProductionCost as “Net Profict” FROM MOVIE;
SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE ProductionCost > 10000 and ProductionCost < 100000;
SELECT * FROM MOVIE WHERE Category IN (‘Comedy’, ‘Action’);
SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;

shaalaa.com

SQL for Data Query

Is there an error in this question or solution?

Q 3.Q 2. e)Q 4.

Chapter 9: Structured Query Language (SQL) - Exercise [Page 177]

APPEARS IN

NCERT Computer Science [English] Class 12

Chapter 9 Structured Query Language (SQL)
Exercise | Q 3. | Page 177

RELATED QUESTIONS

Which function is used with the ORDER BY clause to custom sort order?

Which of the following is not a constraint?

Which constraint ensures that a column cannot have NULL values where NULL means missing/unknown/not applicable value?

Which constraint ensures that all the values in a column are distinct/unique?

Activity table is related to student table and every student has been asked to enroll for minimum 2 activities. While checking the records select student id from activity; lots of duplicate entries were found as students are enrolling for more than one activity. It is becoming difficult to understand who all have still not enrolled for any activity. We can use ______ clause with student_id to resolve the issue.

______ operator defines the range of values in which the common value must fall into to make condition true.

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

List business is done by the movies showing only MovieID, MovieName, and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

List the different categories of movies.

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

Find the net profit of each movie showing its MovieID, MovieName, and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

List MovieID, MovieName, and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.

Consider the following MOVIE table and write the SQL query based on it.

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	-	100000	-
006	Punjabi_Movie	Comedy	-	30500	-

List details of all movies which fall in the category of comedy or action.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID of all those matches where both teams have scored more than 70.
Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
Display the MatchID and date of matches played by Team 1 and won by it.
Display the MatchID of matches played by Team 2 and not won by it.
Change the name of the relation TEAM to T_DATA. Also, change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID and date of matches played by Team 1 and won by it.

Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the Query for the following:

Display the MatchID of matches played by Team 2 and not won by it.

The SELECT statement when combined with the ______ clause returns records without repetition.

Consider the following tables – Bank_Account and Branch:

Table: Bank_Account

ACode	Name	Type
A01	Amrita	Savings
A02	Parthodas	Current
A03	Miraben	Current

Table: Branch

ACode	City
A01	Delhi
A02	Mumbai
A01	Nagpur

What will be the output of the following statement?

SELECT * FROM Bank_Account NATURAL JOIN Branch;

______ clause is used with a SELECT statement to display data in a sorted form with respect to a specified column.

The code given below reads the following records from the table employee and displays only those records that have employees coming from the city 'Delhi':

E_code - String
E_name - String
Sal - Integer
City - String

Note the following to establish connectivity between Python and MySQL:

Username is root
Password is root
The table exists in a MySQL database named emp.
The details (E_code,E_name,Sal,City) are the attributes of the table.

Write the following statements to complete the code:

Statement 1 - to import the desired library.

Statement 2 - to execute the query that fetches records of the employees/coming from the city ‘Delhi’.

Statement 3 - to read the complete data of the query (rows whose city is Delhi) into the object named details, from the table employee in the database.

import ____________ as mysql  #Statement 1
def display():
   mydb=mysql.connect(host="localhost",user="root"
   passwd="root", database=" emp")
   mycursor=mydb.cursor()
   ____________ #Statement 2
   details = ____________ # Statement 3
   for i in details:
       print (i)

Consider the table CLUB given below and write the output of the SQL queries that follow.

CID	CNAME	AGE	GENDER	SPORTS	PAY	DOAPP
5246	AMRITA	35	FEMALE	CHESS	900	2006- 03-27
4687	SHYAM	37	MALE	CRICKET	1300	2004- 04-15
1245	MEENA	23	FEMALE	VOLLEYBALL	1000	2007- 06-18
1622	AMRIT	28	MALE	KARATE	1000	2007- 09-05
1256	AMINA	36	FEMALE	CHESS	1100	2003- 08-15
1720	MANJU	33	FEMALE	KARATE	1250	2004- 04-10
2321	VIRAT	35	MALE	CRICKET	1050	2005- 04-30

SELECT COUNT(DISTINCT SPORTS) FROM CLUB;
SELECT CNAME, SPORTS FROM CLUB
WHERE DOAPP<"2006-04-30" AND CNAME LIKE "%NA";
SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = "MALE" AND PAY BETWEEN 1000 AND 1200;

Consider the following MOVIE table and write the SQL query based on it. MovieID MovieName Category ReleaseDate ProductionCost BusinessCost 001 Hindi_Movie Musical 2018-04 - Computer Science (Python)

Advertisements

Advertisements

Question

Advertisements

Solution

APPEARS IN

RELATED QUESTIONS

Select a course

Consider the following MOVIE table and write the SQL query based on it. MovieID MovieName Category ReleaseDate ProductionCost BusinessCost 001 Hindi_Movie Musical 2018-04 - Computer Science (Python)

Advertisements

Advertisements

Question

Advertisements

SolutionShow Solution

APPEARS IN

RELATED QUESTIONS

Select a course

Solution