Page 299 - Information_Practice_Fliipbook_Class11
P. 299
3. Abhishek has been assigned a project by his teacher on Railway Management. He has created the following two tables:
Table: Trains
TrainID TrainName Source Destination
3457 Rajdhani Delhi Kolkata
1274 Jammu Express Jammu Delhi
3891 Shatabdi Lucknow Mumbai
6192 Jhelum Express Amritsar Mumbai
4072 Vande Bharat Delhi Trivandrum
Table: Passengers
RefNo TrainID PassengerName DOJ
R112 3457 Surya 2021-03-12
R981 1274 Manoj 2020-12-09
R402 4072 NULL 2021-07-07
R505 3457 Morjina 2020-01-23
a. From the tables given above, identify the following:
(i) Primary key from both tables
(ii) Foreign key in Trains table
(iii) Degree and Cardinality of both tables
b. Can the attribute TrainID in the table Passengers have the value 9999? Why/ why not?
4. Tia is in class 12 and is learning SQL. As the school is about to begin with the Annual Theatre Festival, her teacher has
asked her to create the following table in MySQL that will store the schedule of all plays that will be enacted during the
festival:
Table: THEATRE
TCODE TITLE AUDI LANGUAGE SHOWDATE TICKET PRICE
T001 Akbar The Great Nahi Rahe 2 Hinglish 2023-05-12 150
T002 Mughle-E-Azam 1 Hindi 2023-05-08 200
T003 Romeo and Juliet 2 English 2023-05-09 125
T004 The Lost Love 2 English 2023-05-10 100
T005 Salaam Noni Appa 1 Hindi 2023-05-12 250
(i) Write the command to create the given table with TCODE as primary key. None of the fields should be left blank.
(ii) Write the following SQL queries:
a. To display the names of plays that will be staged in Audi 1.
b. To display the records of those plays that have language as Hindi.
c. To display the names of plays that have ticket price of more than 200.
d. To display the count of plays that will be screened Hindi and English languages.
e. To display the names of plays in alphabetical order.
f. To add an attribute TROUPE to the table. The attribute is of type string OF SIZE 40 and cannot be left blank.
g. To delete the records of those plays that are staged in Audi 1.
NCERT Exercise Solutions
1. Give the terms for each of the following:
a. Collection of logically related records.
b. DBMS creates a file that contains description about the data stored in the database.
c. Attribute that can uniquely identify the tuples in a relation.
d. Special value that is stored when actual data value is unknown for an attribute.
Database Concepts and the Structured Query Language 285

