pgsql COMP9311 23T2

代写sql

 

-- Q1:
create or replace view Q1(subject_code)
as
SELECT code AS subject_code
FROM Subjects JOIN OrgUnits ON Subjects.offeredBy = OrgUnits.id
JOIN OrgUnit_types ON OrgUnit_types.id= OrgUnits.utype
WHERE OrgUnit_types.name = 'Centre';

-- Q2:
create or replace view Q2(course_id)
as
SELECT Courses.id AS course_id
FROM Courses JOIN Classes ON Courses.id = Classes.course
JOIN Class_types ON Classes.ctype= Class_types.id
join ( select course
from Classes join Class_types on Classes.ctype= Class_types.id
where name = 'Seminar' ) t on t.course=Courses.id
GROUP BY Courses.id
HAVING COUNT(DISTINCT Class_types.id ) >= 4 ;

-- Q3:
create or replace view Q3(unsw_id)
as
SELECT people.unswid AS unsw_id
FROM people
JOIN course_enrolments ON people.id = course_enrolments.student
JOIN courses ON course_enrolments.course = courses.id
JOIN semesters ON courses.semester = semesters.id
JOIN subjects ON courses.subject = subjects.id
WHERE (subjects._equivalent LIKE '%LAWS%' or subjects._equivalent LIKE 'JURD%' )
and semesters.year = 2010
GROUP BY people.unswid
HAVING COUNT(*)>=2;

-- Q4:
create or replace view Q4(course_id, avg_mark)
as
SELECT courses.id AS course_id, ROUND(AVG(course_enrolments.mark)::numeric, 4) AS avg_mark
FROM course_enrolments JOIN courses ON course_enrolments.course = courses.id
JOIN subjects ON courses.subject = subjects.id
JOIN semesters ON courses.semester = semesters.id
WHERE semesters.year = 2010 and subjects.code LIKE 'COMP%' AND course_enrolments.mark IS NOT NULL
GROUP BY courses.id
HAVING AVG(course_enrolments.mark) =
( select max(avg_mark)
from (
SELECT courses.id AS course_id, ROUND(AVG(course_enrolments.mark)::numeric, 4) AS avg_mark
FROM course_enrolments JOIN courses ON course_enrolments.course = courses.id
JOIN subjects ON courses.subject = subjects.id
JOIN semesters ON courses.semester = semesters.id
WHERE semesters.year = 2010 and subjects.code LIKE 'COMP%' AND course_enrolments.mark IS NOT NULL
GROUP BY courses.id) as temp );

-- Q5:
create or replace view Q5(faculty_id, room_id)
as
select faculty_id, room_id
from (
select faculty_id, room_id,RANK() OVER (PARTITION BY faculty_id ORDER BY ordernum DESC) as ordernum
from (
SELECT orgunits.id AS faculty_id, rooms.id AS room_id,count(*) as ordernum
FROM orgunits JOIN subjects ON orgunits.id = subjects.offeredby
JOIN courses ON courses.subject = subjects.id
JOIN classes ON classes.course = courses.id
JOIN class_types ON class_types.id =classes.ctype
JOIN rooms ON rooms.id =classes.room
JOIN semesters ON semesters.id= courses.semester
join orgunit_types on orgunit_types.id= orgunits.utype
WHERE semesters.year = 2005 AND class_types.name = 'Tutorial'
and orgunit_types.name = 'Faculty'
GROUP BY orgunits.id, rooms.id ) as temp1 ) as temp2
where ordernum<=1 ;

-- Q6:
create or replace view Q6(program_id, stream_id)
as
select program_id,stream_id
from (
select program_id,stream_id,RANK() OVER (PARTITION BY program_id ORDER BY ordernum DESC) as ordernum
from (
SELECT a.id as program_id, s.id as stream_id,count(*) as ordernum
FROM programs a JOIN program_enrolments b ON a.id = b.program
JOIN stream_enrolments c ON b.id = c.partOf
JOIN streams s ON c.stream = s.id
JOIN semesters sem ON sem.id = b.semester
JOIN orgunits org ON org.id = a.offeredby
WHERE org.name = 'Faculty of Arts and Social Sciences'
and sem.year = '2005' and sem.term='S1'
GROUP BY a.id, s.id
) as temp1 ) as temp2
where ordernum<=1 ;

-- Q7:
create view Q7 as
with a1 as (SELECT s.id,count(c.id) as coure_count
FROM Subjects AS s
JOIN OrgUnits AS o ON s.offeredBy = o.id
JOIN courses AS c ON s.id = c.subject
JOIN semesters AS sem ON c.semester = sem.id
WHERE sem.year = 2008 and UPPER(o.name) like '%LAW%'
group by s.id
having count(c.id) >=2 )

select tempb.subject_id,tempb.staff_name from
(select tempa.id AS subject_id, p.name AS staff_name,count(distinct c1.id) as stnum
from a1 tempa
JOIN courses AS c1 ON tempa.id = c1.subject
join course_staff AS stf on c1.id = stf.course
JOIN semesters AS sm1 ON c1.semester = sm1.id
join people AS p on stf.staff = p.id
where sm1.year = 2008
group by tempa.id,p.name
having count(distinct c1.id) =
( select coure_count from a1 where id= tempa.id ) ) as tempb;

-- Q8:
create or replace view Q8
as
SELECT DISTINCT p.unswid AS unsw_id, p.name
FROM people AS p
JOIN (
SELECT pe.student
FROM (
SELECT c.subject, ce.student, RANK() OVER (PARTITION BY c.id ORDER BY ce.mark DESC) as rank
FROM courses AS c
JOIN course_enrolments AS ce ON ce.course = c.id
JOIN subjects AS s ON s.id = c.subject
WHERE UPPER(s.code) LIKE 'MATH%'
and c.id in ( SELECT course FROM course_enrolments group by course having COUNT(*) >100 )
) AS course_ranks
JOIN program_enrolments AS pe ON pe.student = course_ranks.student
JOIN programs AS prog ON prog.id = pe.program
JOIN orgunits AS o ON o.id = prog.offeredby
WHERE course_ranks.rank <= 10 AND lower(o.name) = 'faculty of science'
GROUP BY pe.student
) AS top_students
ON p.id = top_students.student;

-- Q9:
create or replace view Q9
as
SELECT
p.unswid AS prof_id,
ROUND(CAST(COUNT(CASE WHEN ce.mark < 50 THEN 1 END) AS NUMERIC) / COUNT(ce.mark), 4) AS fail_rate
FROM
people AS p
JOIN
staff_roles AS sr ON sr.id = p.id
JOIN
affiliations AS a ON a.staff = p.id
JOIN
course_staff AS cs ON cs.staff = p.id
JOIN
courses AS c ON c.id = cs.course
JOIN
subjects AS s ON s.id = c.subject
JOIN
course_enrolments AS ce ON ce.course = c.id
WHERE
sr.name ILIKE '%Professor%' AND
sr.name = 'Course Convenor' AND
s.career = 'UG' AND
ce.mark IS NOT NULL AND
p.unswid IN (
SELECT orgunit
FROM orgunits
WHERE name = 'School of Mechanical and Manufacturing Engineering'
)
GROUP BY p.unswid
HAVING COUNT(ce.course) > 0;


-- Q10:
create or replace view Q10
as
SELECT
pe.student AS student_id,
pe.program AS program_id,
p.uoc - COALESCE(SUM(passed_courses.earned_uoc), 0) AS remain_uoc
FROM
program_enrolments AS pe
JOIN
programs AS p ON pe.program = p.id
JOIN
program_degrees AS pd ON pd.program = p.id
JOIN
semesters AS sem ON sem.id = pe.semester
LEFT JOIN
(
SELECT ce.student, ce.course, sem.id AS semester, SUM(s.uoc) AS earned_uoc
FROM course_enrolments AS ce
JOIN courses AS c ON c.id = ce.course
JOIN subjects AS s ON s.id = c.subject
JOIN semesters AS sem ON sem.id = c.semester
WHERE ce.mark >= 50
GROUP BY ce.student, ce.course, sem.id
) AS passed_courses ON passed_courses.student = pe.student AND passed_courses.semester = pe.semester
WHERE
pd.abbrev = 'MA' AND
(sem.ending - sem.starting) > 2000
GROUP BY
pe.student, pe.program, p.uoc
HAVING p.uoc / 2 > COALESCE(SUM(passed_courses.earned_uoc), 0);

-- Q11
create or replace function Q11(year2 CourseYearType,term2 char(2),orgunit_id integer)
RETURNS table(grade char(2), rate numeric(4)) AS $$
DECLARE
result_str varchar;

declare counterHD integer;
declare counterDN integer;
declare counterCR integer;
declare counterPS integer;
declare counterFL integer;
declare sumcounter integer;
declare tempcounter integer;
declare svg_mark_temp numeric;

declare my_cursor CURSOR for select m.avg_mark from (
select j.ids ,j.course_id,j.mark,k.avg_mark from (
select h.course as course_id ,h.mark,h.ids from orgunits g,(
select e.*,f.course ,f.mark ,f.ids as ids from subjects e, (
select c.*,d.course ,d.mark,d.ids as ids from courses c, (
select a.id as ids,b.course,b.mark from students a,course_enrolments b where a.id = b.student) d
where c.id = d.course) f
where e.id = f.subject) h
where g.id = h.offeredby
and g.id = orgunit_id) j,(
SELECT courses.id AS course_id, ROUND(AVG(course_enrolments.mark)::numeric, 4) AS avg_mark
FROM course_enrolments JOIN courses ON course_enrolments.course = courses.id
JOIN subjects ON courses.subject = subjects.id
JOIN semesters ON courses.semester = semesters.id
WHERE semesters.year = year2 and semesters.term=term2 AND course_enrolments.mark IS NOT NULL
GROUP BY courses.id) k
where j.course_id = k.course_id) m;

begin
counterHD := 0;
counterDN := 0;
counterCR := 0;
counterPS := 0;
counterFL :=0;
sumcounter := 0;
 
open my_cursor;
 
loop
fetch my_cursor into svg_mark_temp;
if found then
tempcounter := svg_mark_temp ;
if (tempcounter >= 85) THEN
grade :='HD';
counterHD := counterHD+1;
sumcounter := sumcounter+1;
raise notice 'hd%', counterCR;
raise notice 'sum%', sumcounter;
elseif (tempcounter <85 and tempcounter >= 75 )then
grade := 'DN';
counterDN := counterDN+1;
sumcounter := sumcounter+1;
raise notice 'dn%', counterCR;
raise notice 'sum%', sumcounter;
elseif (tempcounter <75 and tempcounter >= 65) THEN
grade :='CR';
counterCR := counterCR+1;
sumcounter := sumcounter+1;
 
elseif ( tempcounter<65 and tempcounter >= 50 ) THEN
grade :='PS';
counterPS := counterPS+1;
sumcounter := sumcounter+1;
raise notice 'ps%', counterCR;
raise notice 'sum%', sumcounter;
elseif (tempcounter <50) THEN
grade :='FL';
counterFL := counterFL+1;
 
raise notice 'fl%', counterCR;
 
 
if tempcounter is not null then
sumcounter := sumcounter+1;
raise notice 'sum%', sumcounter;
end if;
end if;
else
exit;
end if;
 
end loop;
close my_cursor;

begin
grade := 'HD';
rate := ROUND(cast(counterHD as numeric) /cast(sumcounter as numeric) , 4) ;
 
return next;

grade := 'DN';
rate := ROUND(cast(counterDN as numeric) /cast(sumcounter as numeric) , 4) ;
return next;

grade := 'CR';
raise notice 'cr444%', counterCR;
raise notice 'sum444%', sumcounter;
rate := ROUND(cast(counterCR as numeric) /cast(sumcounter as numeric) , 4) ;
return next;
 
grade := 'PS';
rate := ROUND(cast(counterPS as numeric) /cast(sumcounter as numeric) , 4) ;
return next;
 
grade := 'FL';
rate := ROUND(cast(counterFL as numeric) /cast(sumcounter as numeric) , 4) ;
return next;
exception
when division_by_zero then
raise notice '除数不能为0';
end;
 
END; $$
LANGUAGE plpgsql;

select Q11(2009, 'S1', 1350)

-- Q12
create or replace function Q12(subject_prefix character(4))
RETURNS setof text AS $$

BEGIN
RETURN query select f.ids ||' ' ||f.orgunits from subjects e,(
select c.id as ids, c.subject,d.orgunits from courses c,(
select a.* ,b.orgunits from course_staff a,
(SELECT
staff, array_to_string(array_agg(distinct orgunit order by orgunit asc),'/') AS orgunits
FROM
affiliations
GROUP BY staff
having count(distinct orgunit) >=4 ) b
where a.staff = b.staff ) d
where c.id = d.course) f
where e.id =f.subject
and SUBSTRING (e.code , 1, 4) = subject_prefix;
 
END;
$$ LANGUAGE plpgsql ;

select Q12('PHYS');