/*
These are merely sample solutions to CSE2316/3316/CSC3161 DBMS
Assignment 1, 2001.

Please note that many questions can be done in more than one way,
such as with or without nested subqueries.
*/



1.
select stuid, stuname, major
from students;


2.
select students.stuid, students.stuname
from   students, enroll
where  ((    students.stuid  = enroll.stuid
         and enroll.courseno = 'hst205a')
         or   students.major = 'Maths');


3.
select students.stuid, students.major
from students
where not exists (
   select *
   from enroll
   where stuid = students.stuid
   and   courseno = "art103a");


4.
select staff.staffid, staff.staffname, count(distinct courses.room)
from staff, courses
where staff.staffid = courses.staffid
group by staff.staffid, staff.staffname
UNION
select staff.staffid, staff.staffname, 0
from staff
where not exists (
   select *
   from courses
   where staffid = staff.staffid );


5.
select enroll.courseno, enroll.stuid, students.stuname
from enroll, students
where enroll.stuid = students.stuid;


6.
select students.stuname, students.stuid
from staff, courses, enroll, students
where staff.staffname  = "Professor Smith"
  and staff.staffid    = courses.staffid
  and courses.courseno = enroll.courseno
  and enroll.stuid     = students.stuid
order by students.stuname asc;


7a.
select stuname, major, count (distinct courseno)
from students, enroll, courses
where students.stuid  = enroll.stuid
  and enroll.courseno = courses.courseno
  and courses.room    = 'h221'
group by stuname, major
having count (distinct courseno) < = 1;


7b.
select students1.stuname, students1.major
from   students students1
where  (((select count (*)
          from students, enroll, courses
          where students1.stuid = students.stuid
          and   students.stuid  = enroll.stuid
          and   enroll.courseno = courses.courseno
          and   courses.room    = 'h221') <= 1 )
          or
          not exists (select *
                      from enroll, courses
                      where students.stuid  = enroll.stuid
                      and   enroll.courseno = courses.courseno
                      and   courses.room    = 'h221' ));


8.
select enroll1.stuid, enroll1.courseno
from   enroll enroll1
where  enroll1.grade = null
   or  enroll1.grade < (select avg(grade)
                        from enroll
                        where enroll.courseno = enroll1.courseno
                        and   enroll.stuid    = enroll1.stuid);


9.
select count (*)
from students
where ((not exists (select *
                    from courses, enroll
                    where courses.courseno = enroll.courseno
                    and   enroll.stuid     = students.stuid
                    and   courses.sched like "%tu%"))
       or
       ((select count (courseno)
         from enroll
         where stuid = students.stuid) <= 1)
       or students.stuid not in (select stuid
                                 from enroll));


10.
select stuid, stuname
from students
where not exists (select *
                  from courses
                  where not exists (select * from enroll
                                    where courseno = courses.courseno
                                    and   stuid    = students.stuid));