Question

Navdeep creates a table RESULT with a set of records to maintain the marks secured by students in Sem1, Sem2, Sem3, and their divisions. After the creation of the table, he entered data of 7 students in the table.

ROLL_NO	SNAME	SEM1	SEM2	SEM3	DIVISION
101	KARAN	366	410	402	I
102	NAMAN	300	350	325	I
103	ISHA	400	410	415	I
104	RENU	350	357	415	I
105	ARPIT	100	75	178	IV
106	SABINA	100	205	217	II
107	NEELAM	470	450	471	I

Based on the data given above answer the following questions:

1. Identify the most appropriate column, which can be considered as the Primary key.
2. If two columns are added and 2 rows are deleted from the table result, what will be the new degree and cardinality of the above table?
3. Write the statements to:
   a. Insert the following record into the table
   Roll No - 108, Name - Aadit, Sem1- 470, Sem2 - 444, Sem3 - 475, Div - I.
   b. Increase the SEM2 marks of the students 3% whose name begins with ‘N’.
   OR
   Write the statements to:
   a. Delete the record of students securing IV division.
   b. Add a column REMARKS in the table with datatype as varchar with 50 characters.

Answer 1

1. ROLL_NO
2. New Degree: 8
   New Cardinality: 5
3. a. INSERT INTO RESULT VALUES (108, ‘Aadit’, 470, 444, 475, ‘I’);
   b. UPDATE RESULT SET SEM2=SEM2+ (SEM2*0.03) WHERE SNAME LIKE “N%”;
   OR
   a. DELETE FROM RESULT WHERE DIV=’IV’;
   b. ALTER TABLE RESULT ADD (REMARKS VARCHAR(50));