Computer Science 2 Marks Each

Differentiate between Candidate Key and Primary Key in context of RDBMS.

What is the importance of a Primary Key in a table? Explain with a suitable example.

Differentiate between the terms primary key and alternate key.

Write the full form of DDL and DML?

What is an Alternate Key?

Write SQL commands for the statements (a) to (h) on the table HOSPITAL
(a) To insert a new row in the HOSPITAL table with the following data: 11,’ Kasif’, 37,’ENT’,’2018-02-25’, 300, ’M’.
(b) To set charges to NULL for all the patients in the Surgery department.
(c) To display patient details who are giving charges in the range 300 and 400 (both inclusive).
(d) To display the details of that patient whose name second character contains ‘a’.
(e) To display total charges of ENT Department.
(f) To display details of the patients who admitted in the year 2019.
(g) To display the structure of the table hospital.
(h) Write the command to create the above table.

Consider the following table HOSPITAL.

Table: HOSPITAL

PNo	Name	Age	Department	DateofAdm	Charges	Sex
1	Mayank	65	Surgery	23/02/2018	600	M
2	Babita	24	ENT	01/01/2019	400	F
3	Kashish	45	Orthopaedic	19/12/2018	400	M
4	Tarun	12	Surgery	01/10/2018	600	M
5	Manisha	36	ENT	12/01/2018	400	F
6	Imran	16	ENT	24/02/2018	400	M
7	Ankita	NULL	Cardiology	20/08/2018	800	F
8	Zoya	45	Gynecology	22/02/2018	500	F
9	Kush	19	Cardiology	13/01/2019	800	M
10	Shalini	31	Medicine	19/02/2018	300	F

Note: PNo is the primary key in the above table.

Write SQL commands for the statements (a) to (g) on the table HOSPITAL.
(a) To display the details of all the patients whose name starts with the alphabet ‘Z’.
(b) To change the age of the patient Kush to 20.
(c) To increase the charges of all the patients by 5%.
(d) To remove the record of the patient whose Name is Tarun.
(e) To add another column DocName(Doctor Name) of the type varchar in the above table.
(f) To display patient detail whose age is missing(null).
(g) To decrease the charges by 5% of all the patients admitted to the ENT department.

Write SQL commands for the following:

Table: TEACHER

TID	NAME	AGE	DEPT	DATEOFJOIN	SAL	SEX
T118	Navin	40	Computer	2010-01-10	12000	M
T107	Chetna	37	History	2008-03-24	20000	F
T105	Sandeep	46	Maths	2006-12-12	30000	M
T110	Sangeeta	35	History	2010-07-01	25000	F
T101	Rudransh	42	Maths	2004-09-05	40000	M
T121	Neeraj	38	Physics	2011-04-01	28000	M

(i) To show information about the teachers of the history department.
(ii) To list the names of teachers earning a salary between 20000 and 30000.
(iii) To count the number of male teachers.
(iv) Display gender wise total number of teachers.
(v) To list the name and age of teachers of female teachers in descending order of date of join.
(vi) Increase the salary by 10% for Maths departments.
( vii) To delete the record of teacher Neeraj.

Consider the table: CLUB as given below:

Table: CLUB

MEMBER_ID	MEMBER_NAME	ADDRESS	AGE	FEE
M002	NISHA	GURGAON	19	3500
M003	NIHARIKA	NEW DELHI	21	2100
M004	SACHIN	FARIDABAD	18	3500

(a) What is the cardinality and degree of the above given table?

(b) If a new column contact_no has been added and three more members have joined the club then how these changes will affect the degree and cardinality of the above given table.

Observe the following table and answer the parts (i) and (ii) accordingly

Table: Product

Pno	Name	Qty	PurchaseDate
101	Pen	102	12-12-2011
102	Pencil	201	21-02-2013
103	Eraser	90	09-08-2010
109	Sharpener	90	31-08-2012
113	Clips	900	12-12-2011

(a) Write the names of most appropriate columns, that can be considered as candidate keys.

(b) What is the degree and cardinality of the above table?

Observe the table ‘Club’ given below:

Table: Club

Member_id	Member_Name	Address	Age	Fee
M001	Sumit	New Delhi	20	2000
M002	Nisha	Gurgaon	19	3500
M003	Niharika	New Delhi	21	2100
M004	Sachin	Faridabad	18	3500

(i) What is the cardinality and degree of the above given table?

(ii) If a new column contact_no has been added and three more members have joined the club then how these changes will affect the degree and cardinality of the above given table.

Observe the following table CANDIDATE carefully and write the name of the RDBMS operation out of

(i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT,

which has been used to produce the output as shown in RESULT ? Also, find the Degree and Cardinality of the RESULT.

Observe the following STUDENTS and EVENTS tables carefully and write the name of the RDBMS operation which will be used to produce the output as shown in LIST ? Also, find the Degree and Cardinality of the LIST.

Observe the following tables VIDEO and MEMBER carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output FINAL RESULT as shown below, Also, find the Degree and Cardinality of the final result.

Write the difference between WHERE and HAVING clause.