TUR06 CISC3000 Tutorial 06

❌ 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.

  Student (SID, SName, Bdate, Address, Sex)
  Instructor (IID, IName, Department)
  Courses (CID, CName)
  Qualified (IID, CID, DateQualified)
  Section (SNo, Semester, CID)
  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”.

select SID
from (Student join Enrolled using(SID))
      join (Section join Course using(CID))
      using (SNo, Semester)
where  CName = "Database" and "Networking"
-- Correct a.  >> 
select E.SID
from Enrolled E
where SNo in (
  select SNo from Section S, Courses C
  where S.CID = C.CID and (CName = 'Database' or CName = 'Networking')
)
group by SID
having count(*) >= 2

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

select CName
from (Courses join Section using (CID))
where (Semester = "I-2008" and
       Semester != "II-2008")
-- Correct b. >>
select C.CName
from Courses C, Section S
where C.CID = S.CID and S.Semester = 'I-2008'
and S.CID not in (
  select S1.CID 
  from Section S1
  where S1.Semester = 'II-2008'
)

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

select CName
from Courses
where CID like "%ISM%"
-- Correct c. >>
select CID, CName
from Courses
where CID like 'ISM%'

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

select name, semester
from student join takes using (ID)
where ID not in(
    select ID
  from takes
  where semester = "I-2008"
)
-- Correct d. >> 
select distinct S.SID, S.SName
from Student S
where not exists (
  select *
  from Enrolled E
  where E.SID = S.SID and E.Semester = 'I-2008'
)

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

select IName
from instructor join Qualified using (IID)
where CID = all (
  select CID
  from Qualified
)
-- Correct e >>
select I.IName
from Instructor I
where not exists (
    select *
  from Courses C
  where not exists (
      select *
    from Qualified Q
    where Q.CID = C.CID and Q.IID = I.IID
  )
)

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

with sec_enrollment as(
    select count(SID) as enrollment
    from Student join (Section natural join Enrolled) using (SID)
    group by SNo, Semester
         )
select max(enrollment)
from sec_enrollment
-- Correct f >>
select E.SNo
from Enrolled E
group by E.SNo
having count(distinct SID) >= all (
  select count(distinct E1.SID)
  from Enrolled E1
  group by E1.SNo
)