Exercise 1
Q1
ONLINE CONTESTS system organises contests on various topics. Each contest on a
particular topic has a set of 5 questions to be answered, and is sponsored by a company.
The following tables are maintained within database by the system :
Table : CONTEST Table : COMPANY
Column Description Column Description
contest_cd
contest_des
clos_dt
comp_cd
Contest code.
Name and description.
Closing date.
Sponsoring company code.
comp_cd
comp_nm
address
tel_no
cont_per
Company code. (PK)
Company name.
Address.
Telephone number.
Contact person’s name.

Table : QUES_ANS Table : CON_ANS
Column Description Column Description

Contest code.
Answer 1.
Answer 2.
Answer 3.
Answer 4.
Answer 5.

Table : CONTESTANT
Column Description
con_cd
con_nm
e_mail
dt_of_entry
Contestant code.
Contestant name.
E-mail address.
Date of entry. a) Create tables CONTEST, COMPANY, QUES_ANS, CON_ANS, and CONTESTANT
b) In COMPANY table, column comp_cd, system generated sequential values like
1000,1002,1004,1006, … will be added at the time of record addition.
c) Add few required records to those tables.
Notice: PK: Primary key

Q2
Use the tables in Question 1 to state T-SQL command / query for each of the following:

a) Set the foreign key constraint for the company code in the CONTEST table.


corresponding records from QUES_ANS and CON_ANS tables.
CONTEST_HIST table has the structure as follows :

Table : CONTEST_HIST

Column Description contest_cd
contest_des
comp_cd
dt_of_withdraw
Contest code.
Name and description.
Company code
Date of deletion