Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 64 of 79

Database Query using SQL — Question 22

Back to all questions
22
Question

Question 22

Consider the tables given below and answer the questions that follow:

Table: EVENT

EventIdEventNumPerformersCelebrityID
101Birthday10C102
102Promotion Party20C103
103Engagement12C102
104Wedding15C104

Table: CELEBRITY

CelebrityIDCelebrityNamePhoneFeeCharged
C101Faiz Khan99101XXXXX200000
C102Sanjay Kumar89346XXXXX250000
C103Neera Khan Kapoor98116XXXXX300000
C104Reena Bhatia70877XXXXX100000

(a) Name the Primary keys in both the tables and the Foreign key in 'Event' table. Can NumPerformers (Number of performers) be set as the Primary key? Give reason.

(b) How many rows will be present in the Cartesian join of the above-mentioned two tables?

(c) Write the commands in SQL:

  1. To display EventId, Event name, CelebrityId for only those events that have more than 10 performers.
  2. To display CelebrityId and names of celebrities who have "Khan" anywhere in their names.
  3. To display names of celebrities and fee charged for those celebrities who charge more than 200000.
Answer

(a) In the EVENT table, the primary key is EventId, and in the CELEBRITY table, the primary key is CelebrityID. The foreign key in the EVENT table is CelebrityID, which references the CelebrityID in the CELEBRITY table.

NumPerformers cannot be set as the primary key because primary keys must uniquely identify each row in the table. NumPerformers can have duplicate values for different events, as multiple events can have the same number of performers.

(b) The EVENT table has 4 rows and the CELEBRITY table has 4 rows. So, the Cartesian join will have: 4 × 4 = 16 rows.

(c)

1.

SELECT EVENTID, EVENT, CELEBRITYID 
FROM EVENT 
WHERE NUMPERFORMERS > 10;
Output
+---------+-----------------+-------------+
| EVENTID | EVENT           | CELEBRITYID |
+---------+-----------------+-------------+
|     102 | PROMOTION PARTY | C103        |
|     103 | ENGAGEMENT      | C102        |
|     104 | WEDDING         | C104        |
+---------+-----------------+-------------+

2.

SELECT CELEBRITYID, CELEBRITYNAME  
FROM CELEBRITY  
WHERE CELEBRITYNAME LIKE '%Khan%';
Output
+-------------+-------------------+
| CELEBRITYID | CELEBRITYNAME     |
+-------------+-------------------+
| C101        | FAIZ KHAN         |
| C103        | NEERA KHAN KAPOOR |
+-------------+-------------------+

3.

SELECT CELEBRITYNAME, FEECHARGED 
FROM CELEBRITY 
WHERE FEECHARGED > 200000;
Output
+-------------------+------------+
| CELEBRITYNAME     | FEECHARGED |
+-------------------+------------+
| SANJAY KUMAR      |     250000 |
| NEERA KHAN KAPOOR |     300000 |
+-------------------+------------+