Page 234 - Informatics_Practices_Fliipbook_Class12
P. 234
d. UPDATE Student
SET StCode = (SELECT StCode FROM Stream WHERE StreamName = 'Humanities')
WHERE Name = 'Jay';
e. SELECT Name
FROM Student
WHERE Name LIKE '%a%'
ORDER BY Name;
f. SELECT Name
FROM Student
WHERE StCode IN (SELECT StCode FROM Stream WHERE StreamName IN ('Science',
'Humanities'))
ORDER BY Name, AdmNo ASC;
g. SELECT StreamName, COUNT(*) AS NumberOfStudents
FROM Student ST, Stream SR
WHERE ST.StCode = SR.StCode
GROUP BY StreamName
HAVING COUNT(*) > 1;
h. SELECT Name, StreamName
FROM Student ST, Stream SR
WHERE ST.StCode = SR.StCode
ORDER BY AdmNo DESC;
i. SELECT * FROM Student, Stream;
Degree: Sum of degrees of both tables
Cardinality: Number of rows in Student * Number of rows in Stream
The cartesian product on the student and stream table has degree of 5 and cardinality of 18.
j. ALTER TABLE Stream ADD COLUMN TeacherIncharge VARCHAR(50);
UPDATE TABLE Stream SET TeacherIncharge = "Jatin" where Stcode = "S01";
UPDATE TABLE Stream SET TeacherIncharge = "Rahul" where Stcode = "S02";
UPDATE TABLE Stream SET TeacherIncharge = "Kapil" where Stcode = "S03";
k. SELECT TeacherIncharge, Name
FROM Student ST, Stream SR
WHERE ST.StCode = SR.StCode;
l. The degree will be 6 and the cardinality will be 18.
Answers
Multiple Choice Questions
1. (d) 2. (a) 3. (d) 4. (a) 5. (c) 6. (c) 7. (a) 8. (c)
9. (d) 10. (d)
True or False
1. (F) 2. (F) 3. (T) 4. (T) 5. (F)
Fill in the blanks
1. Foreign 2. x * y 3. referential integrity 4. FROM 5. NATURAL JOIN
220 Touchpad Informatics Practices-XII

