❌ Question 01

Grant and Revoke statement

CH05
User Margret is the owner of the SmallClub schema that contains tables:
**_Sailors (sid, sname, rating, age)<br />Reserves (sid, bid, day)_**
Write the SQL statements to grant or revoke privileges accordingly for a) to d).
Use views where appropriate.

a) Margret authorizes Roger and Rita to retrieve and insert Sailors, and to retrieve Reserves as well. In which these privileges can be passed on to other users. :::danger a) Margret authorizes Roger and Rita to retrieve and insert Sailors, and to retrieve Reserves as well. In which these privileges can be passed on to other users.
Grant select, insert on Sailors to Roger, Rita with grant option; Grant select on Reserves to Roger, Rita with grant option;:::

b) Rita authorizes Cherry the same privileges as described in a) but does not allow such privileges to be further passed on to other users. :::danger b) Rita authorizes Cherry the same privileges as described in a) but does not allow such privileges to be further passed on to other users.
Grant select, insert on Sailors to Cherry; Grant select on Reserves to Cherry;:::

c) Roger authorizes Cherry to retrieve Sailors and Reserves, and insert only “sname” attribute of Sailors. :::danger c) Roger authorizes Cherry to retrieve Sailors and Reserves, and insert only “sname” attribute of Sailors.
Grant select, insert (sname) on Sailors to Cherry; Grant select on Reserves to Cherry;:::

d) Margret drops all the privileges that granted to Rita, and withdraw only the grant option for retrieve and insert privileges on Sailors from Roger. :::danger d) Margret drops all the privileges that granted to Rita, and withdraw only the grant option for retrieve and insert privileges on Sailors from Roger.
Revoke select, insert on Sailors from Rita cascade; Revoke select on Reserves from Rita cascade;
Revoke grant option for select, insert on Sailors from Roger cascade;:::

Question 02

Consider the following UNIVERSITY relational database schema. The database describes the student enrolment status, instructors’ qualification in teaching courses and the class scheduling in a university.

  1. Student (SID, SName, Bdate, Address, Sex)
  2. Instructor (IID, IName, Department)
  3. Courses (CID, CName)
  4. Qualified (IID, CID, DateQualified)
  5. Section (SNo, Semester, CID)
  6. Enrolled (SID, SNo, Semester)

Write each of the following queries in SQL.
a. Display the ID of students who are enrolled in the courses “Database” and “Networking”.

  1. select SID
  2. from (Student join Enrolled using(SID))
  3. join (Section join Course using(CID))
  4. using (SNo, Semester)
  5. where CName = "Database" and "Networking"
  6. -- Correct a. >>
  7. select E.SID
  8. from Enrolled E
  9. where SNo in (
  10. select SNo from Section S, Courses C
  11. where S.CID = C.CID and (CName = 'Database' or CName = 'Networking')
  12. )
  13. group by SID
  14. having count(*) >= 2

b. Find the names of courses that are taught in the semester “I-2008” but not the semester “II-2008”.

  1. select CName
  2. from (Courses join Section using (CID))
  3. where (Semester = "I-2008" and
  4. Semester != "II-2008")
  5. -- Correct b. >>
  6. select C.CName
  7. from Courses C, Section S
  8. where C.CID = S.CID and S.Semester = 'I-2008'
  9. and S.CID not in (
  10. select S1.CID
  11. from Section S1
  12. where S1.Semester = 'II-2008'
  13. )

c. Display the course name for all courses whose course ID is with an “ISM” prefix.

  1. select CName
  2. from Courses
  3. where CID like "%ISM%"
  4. -- Correct c. >>
  5. select CID, CName
  6. from Courses
  7. where CID like 'ISM%'

d. Give the name of the students who were not enrolled in any courses during the semester “I-2008”.

  1. select name, semester
  2. from student join takes using (ID)
  3. where ID not in(
  4. select ID
  5. from takes
  6. where semester = "I-2008"
  7. )
  8. -- Correct d. >>
  9. select distinct S.SID, S.SName
  10. from Student S
  11. where not exists (
  12. select *
  13. from Enrolled E
  14. where E.SID = S.SID and E.Semester = 'I-2008'
  15. )

e. Give the name of instructors who are qualified to teach every course.

  1. select IName
  2. from instructor join Qualified using (IID)
  3. where CID = all (
  4. select CID
  5. from Qualified
  6. )
  7. -- Correct e >>
  8. select I.IName
  9. from Instructor I
  10. where not exists (
  11. select *
  12. from Courses C
  13. where not exists (
  14. select *
  15. from Qualified Q
  16. where Q.CID = C.CID and Q.IID = I.IID
  17. )
  18. )

f. Give the number of section that has the most students.

  1. with sec_enrollment as(
  2. select count(SID) as enrollment
  3. from Student join (Section natural join Enrolled) using (SID)
  4. group by SNo, Semester
  5. )
  6. select max(enrollment)
  7. from sec_enrollment
  8. -- Correct f >>
  9. select E.SNo
  10. from Enrolled E
  11. group by E.SNo
  12. having count(distinct SID) >= all (
  13. select count(distinct E1.SID)
  14. from Enrolled E1
  15. group by E1.SNo
  16. )