Scenario 1 answers lab 4

sen1answers.sql

-- 1
select ssid
from students
order by marks desc
limit 1 offset 1;

-- 2
select branch, count(branch)
from students
group by branch
having count(branch) >= 3;

-- 3
select branch, avg(marks)
from students
group by branch
having avg(marks) > (
	select avg(marks)
    from students
    where gender = 'FEMALE'
);

-- 4

select branch, avg(marks)
from students
where gender = "MALE"
group by branch
having avg(marks) > (
	select avg(marks)
    from students
    where gender = 'FEMALE'
);

-- 5
with males(branch, avg_marks) as (
	select branch, avg(marks)
	from (select * from students where gender = 'MALE') m
	group by branch
), 
females (branch, avg_marks) as (
	select branch, avg(marks)
	from (select * from students where gender = 'FEMALE') f
	group by branch
)
select males.branch, males.avg_marks
from males, females
where males.branch = females.branch and males.avg_marks > females.avg_marks;

-- 6
select max(m.marks) - max(f.marks)
from (select marks from students where gender = 'MALE') m, (select marks from students where gender = 'FEMALE') f;

-- 7
select count(*)
from students
where 90 > marks and marks > 70 and branch = 'CS';

-- 8
update students
set gender = lower(gender)
where ssid <> '';
select gender from students;