Page 306 - Information_Practice_Fliipbook_Class11
P. 306

ii.  Data of primary key only
                iii.  NULL data
                iv.  None of the mentioned
         Ans.  a. ii.  Foreign Key
              b. i.   All data from the table
          17.  Consider the following MOVIE database and answer the SQL queries 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              -
              a. Display all the information from the Movie table.
               b. List business done by the movies showing only MovieID, MovieName and BusinessCost.
               c. List the different categories of movies.
               d. Find the net profit of each movie showing its ID, Name and Net Profit.
                 (Hint: Net Profit = BusinessCost – ProductionCost)
                  Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name
                is coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show
                profit as zero?
               e. List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.
               f.  List all movies which fall in the category of Comedy or Action.
               g. List the movies which have not been released yet.
         Ans.  a. SELECT * FROM MOVIE;
              b. SELECT MovieID, MovieName, BusinessCost FROM MOVIE;
              c. SELECT DISTINCT(Category) FROM MOVIE;
              d. SELECT MovieId, Name, (BusinessCost – ProductionCost) as 'Net Profit' FROM MOVIE;
              e.  SELECT  MovieId,  Name, ProductionCost  FROM MOVIE  WHERE ProductionCost  > 80000  AND
                ProductionCost < 125000;
              f.  SELECT * FROM MOVIE WHERE category = 'Comedy' OR category = 'Action';
              g. SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;
          18.  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:
                 i.  It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
                 ii. 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 primary key.
               d.  Show the structure of the table TEAM using SQL command.
               e.  As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
                 Row 1: (1, Team Titan)
                 Row 2: (2, Team Rockers)
                 Row 3: (3, Team Magnet)
                 Row 4: (4, Team Hurricane)
               f.  Show the contents of the table TEAM.
               g.   Now create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for
                each attribute.




          292  Touchpad Informatics Practices-XI
   301   302   303   304   305   306   307   308   309   310   311