Part B sql queries
1. Consider the following MOVIE table and write the SQL queries based on it.
Movie_ID MovieName Type ReleaseDate ProductionCost BusinessCost
M001 The Kashmir Files Action 2022/01/26 1245000 1300000
M002 Attack Action 2022/01/28 1120000 1250000
M003 Looop Lapeta Thriller 2022/02/01 250000 300000
M004 Badhai Do Drama 2022/02/04 720000 68000
M005 Shabaash Mithu Biography 2022/02/04 1000000 800000
M006 Gehraiyaan Romance 2022/02/11 150000 120000
a) Display all information from movie.
b) Display the type of movies.
c) Display movieid, moviename, total_eraning by showing the business done by the
movies. Claculate the business done by movie using the sum of productioncost and
businesscost.
d) Display movieid, moviename and productioncost for all movies with
productioncost greater thatn 150000 and less than 1000000.
e) Display the movie of type action and romance.
f) Display the list of movies which are going to release in February, 2022.
Answers:
a) select * from movie;
b) select distinct from a movie;
c) select movieid, moviename, productioncost + businesscost
"total earning" from movie;
d) select movie_id,moviename, productioncost from movie
where producst is >150000 and <1000000;
e) select moviename from movie where type ='action' or
type='romance';
f) select moviename from moview where month(releasedate)=2;
2. Write following queries:
a) Write a query to display cube of 5.
b) Write a query to display the number 563.854741 rounding off to the next hnudred.
c) Write a query to display "put" from the word "Computer".
d) Write a query to display today's date into [Link] format.
e) Write a query to display 'DIA' from the word "MEDIA".
f) Write a query to display moviename - type from the table movie.
g) Write a query to display first four digits of productioncost.
h) Write a query to display last four digits of businesscost.
i) Write a query to display weekday of release dates.
j) Write a query to display dayname on which movies are going to be released.
Answers:
a) select pow(5,3);
b) select round(563.854741,-2);
c) select mid("Computer",4,3);
d) select concat(day(now()), concat('.',month(now()),
concat('.',year(now())))) "Date";
e) select right("Media",3);
f) select concat(moviename,concat(' - ',type)) from movie;
g) select left(productioncost,4) from movie;
h) select right(businesscost,4) from movie;
i) select weekday(releasedate) from movie;
j) select dayname(releasedate) from movie;
3. Suppose your school management has decided to conduct cricket matches between
students of Class XI and Class XII. Students of each class are asked to join any one
of the four teams – Team Titan, Team Rockers, Team Magnet and Team
Hurricane. During summer vacations, various matches will be conducted between
these teams. Help your sports teacher to do the following:
a) Create a database “Sports”.
b) Create a table “TEAM” with following considerations:
a. It should have a column TeamID for storing an integer value between 1 to 9,
which refers to unique identification of a team.
b. Each TeamID should have its associated name (TeamName), which should be a
string of length not less than 10 characters.
c. Using table level constraint, make TeamID as the primary key.