Name the Primary keys in both the tables and the Foreign key in Event table.

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

Table: EVENT

 

 
	 
Eventid
 
	 
Event
 
	 
NumPerformers
 
	 
CelebrityID
 
 
 
	 
101
 
	 
Birthday
 
	 
10
 
	 
C102
 
 
 
	 
102
 
	 
PromotionParty
 
	 
20
 
	 
C103
 
 
 
	 
103
 
	 
Engagement
 
	 
12
 
	 
C102
 
 
 
	 
104
 
	 
Wedding
 
	 
15
 
	 
C104
 
 

Table: CELEBRITY

 
	 
CelebritylD
 
	 
CelebrityName
 
	 
Phone
 
	 
FeeCharged
 
 
 
	 
C101
 
	 
FaizKhan
 
	 
9910195676
 
	 
200000
 
 
 
	 
C102
 
	 
Sanjaykumar
 
	 
8934664482
 
	 
250000
 
 
 
	 
C103
 
	 
NeeraKhanKapoor
 
	 
9811665686
 
	 
300000
 
 
 
	 
C104
 
	 
ReenaBhatia
 
	 
7087775650
 
	 
100000
 
 

(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:

(i) To display Eventld, Event name, Celebrityld for only those events that have more than 10 performers.

(ii) To display Celebrityld and names of celebrities who have "Khan" anywhere in their names.

(iii) To display names of celebrities and fee charged for those celebrities who charge more than 200000.

Answer :-

(a) Primary Key in Event table :- EventID
Primary Key in Celebrity table :- CelebrityID
Foreign key in Event Table :- CelebrityID

NumPerformer will never a primary key because it can have duplicate value but primary key always have unique value.

(b) 4 * 4 = 16

(c)
(i) Select Eventld, Event, Celebrityld from Event where NumPerformer > 10 ;
(ii) Select CelebrityID, CelebrityName from Celebrity where CelebrityName like “%khan%” ;
(iii) Select CelebrityName , FeeCharged from Celebrity where FeeCharged > 200000 ;