comp3311 proj1

proj1.sql

-- COMP9311 15s1 Project 1
--
-- MyMyUNSW Solution Template

-- Q1: ...
create or replace view Q1(unswid, name)
as
select people.unswid as unswid, people.name as name
from people, course_enrolments
where people.id = course_enrolments.student
group by people.id
having count(*) > 65
;

-- Q2: ...
create or replace view Q2_nstudents(n)
as
select count(*) as n
from students
left join staff on students.id = staff.id
where staff.id is null
;

create or replace view Q2_nstaff(n)
as
select count(*) as n
from staff
left join students on students.id = staff.id
where students.id is null
;

create or replace view Q2_nboth(n)
as
select count(*) as n
from students
inner join staff on students.id = staff.id
;

create or replace view Q2(nstudents, nstaff, nboth)
as
select Q2_nstudents.n as nstudents, Q2_nstaff.n as nstaff, Q2_nboth.n as nboth
from Q2_nstudents, Q2_nstaff, Q2_nboth
;

-- Q3: ...
  -- questions:
    -- how to retrieve just the first record?
create or replace view Q3(name, ncourses)
as
select people.name as name, count(*) as ncourses
from people, course_staff, staff_roles
where people.id = course_staff.staff
  and course_staff.role = staff_roles.id
  and staff_roles.name = 'Course Convenor'
group by people.id
order by ncourses desc
;

-- Q4: ...
create or replace view Q4a(id)
as
select people.unswid as id
from program_enrolments, programs, people, semesters
where program_enrolments.student = people.id
  and program_enrolments.program = programs.id
  and program_enrolments.semester = semesters.id
  and programs.code = '3978'
  and semesters.year = 2005
  and semesters.term = 'S2'
group by people.id
;

create or replace view Q4b(id)
as
select people.unswid as id
from stream_enrolments, program_enrolments, streams, people, semesters
where stream_enrolments.stream = streams.id
  and stream_enrolments.partof = program_enrolments.id
  and program_enrolments.student = people.id
  and program_enrolments.semester = semesters.id
  and streams.code = 'SENGA1'
  and semesters.year = 2005
  and semesters.term = 'S2'
group by people.id
;

create or replace view Q4c(id)
as
select people.unswid as id
from program_enrolments, programs, orgunits, people, semesters
where program_enrolments.student = people.id
  and program_enrolments.semester = semesters.id
  and program_enrolments.program = programs.id
  and programs.offeredby = orgunits.id
  and orgunits.unswid in ('COMPSC', 'ZCOM')
  and semesters.year = 2005
  and semesters.term = 'S2'
group by people.id
;


-- Q5: ...
create or replace view Q5(name)
as
select orgunits.name from orgunits
inner join (
  select facultyOf(orgunit_groups.owner) as faculty
    from orgunit_groups
    inner join orgunits on orgunit_groups.member = orgunits.id
    where orgunits.utype = (
      select id from orgunit_types where name = 'Committee'
    )
) as committees on committees.faculty = orgunits.id
group by orgunits.id
order by count(*) desc
;


-- Q6: ...
create or replace function Q6(text)
	returns table (course text, year integer, term text, convenor text)
as $$
  select $1, semesters.year as year, semesters.term::text as term, people.name as convenor
  from courses, subjects, semesters, course_staff, staff_roles, people
  where subjects.code = $1
    and courses.subject = subjects.id
    and courses.semester = semesters.id
    and courses.id = course_staff.course
    and course_staff.role = staff_roles.id
    and course_staff.staff = people.id
    and staff_roles.name = 'Course Convenor'
$$ language sql
;


select check_all();