April 28, 2024

SQL速成 Crash Course

Table of Contents

Tables to work with
Simple exercises (10)
Intermediate exercises (9)
Hard Exercises (10)

All you need is a good tutorial like this one here, which you can finish in 2 hrs.

And find yourself a good workbook to go through. Click here to download the SQL worksheet I’ve worked on. Many thanks to python4csip.com.

Tables to work with

student

Id	stu_name	stipend	sujet	average
1	KARAN	400	PHYSICS	68
2	DIVAKAR	450	COMP SC	68
3	DIVYA	400	CHEMISTRY	62
4	ARUN	350	PHYSICS	63
5	SABINA	500	MATHS	70
6	JOHN	550	COMP SC	55
7	ROBERT	300	CHEMISTRY	64
8	RUBINA	250	PHYSICS	NULL
9	VIKAS	400	HISTORY	62
10	MOHAN	450	MATHS	57
11	DICK	400	CHEMISTRY	89

guide

sujet	ADVISOR
PHYSICS	ALOK
COMP SC	RAJAN
CHEMISTRY	MANJU
MATHS	SMITA
HISTORY	KISHORE

Simple exercises (10)

Retrieve the names and stipends of all students.

select stu_name, stipend
from student;

Find the average stipend of all students.

select avg(stipend)
from student;

Get the names and subjects of students who have an average score of 65 or above.

select stu_name, sujet
from student
where average >= 65;

List the subjects and their corresponding advisors from the GUIDE table.

select sujet, advisor
from guide;

Display the names of students along with their advisor’s name for the students who have an advisor.

select stu_name, advisor
from student s
join advisor g on s.sujet = g.sujet
where g.sujet is not null;

Find the subjects for which there are no students.

select g.sujet
from guide g
left join student s on g.sujet = s.sujet
where s.sujet is null;

Get the names of students who have a NULL value for the ‘average’ column.

select stu_name
from student
where average is null;

Update the stipend of the student with Id 5 to 550.

update student
set stipend = 550
where id = 5;

Delete all records from the GUIDE table where the advisor is ‘MANJU’.

delete from guide
where advisor = 'MANJU'

Count the number of students in each subject.

select sujet, count(*)
from student
group by sujet;

Intermediate exercises (9)

Find the students who have the highest average score in each subject.

select *
from student
having average in (
	select max(average)
	from student
	group by sujet);

Calculate the percentage of students in each subject who have a stipend greater than the average stipend of that subject.

select s.sujet, (count(case when s.stipend > avg_suj_stipend then 1 end) /count(*) * 100) as percentage
(select count(*)
from student s
join (select sujet, avg(stipend) as avg_suj_stipend
from student
group by sujet) as subquery on subquery.sujet = s.sujet
group by sujet;

Retrieve the subjects where all students have a stipend greater than 300.

select sujet
from student
group by sujet
having min(stipend) > 300;

Calculate the difference between the highest and lowest stipend for each subject.

select sujet, (max(stipend)-min(stipend)) as difference
from student
group by sujet;

(6)Retrieve the names of students who have a stipend greater than their advisor’s average stipend.

SELECT s.stu_name
FROM student s
JOIN GUIDE g ON s.sujet = g.sujet
WHERE s.stipend > (
    SELECT AVG(stipend)
    FROM student
    WHERE sujet = g.sujet
);

Calculate the average stipend for each subject, excluding subjects where the average stipend is below 400.

select avg(stipend) as avg_stipend
from student
group by sujet
where avg_stipend >= 400;

Retrieve the names of students who have the same advisor as the student with Id 3, but a higher stipend.

select s.stu_name
from student s
join guide g on s.sujet = g.sujet
where s.id <> 3
and s.stipend > (select stipend from student where id=3)
and advisor = (select g.advisor from student s
join guide g on s.sujet = g.sujet where s.id=3);

##alternatively

SELECT s1.stu_name
FROM student s1
JOIN GUIDE g1 ON s1.sujet = g1.sujet
JOIN student s2 ON s2.sujet = g1.sujet
WHERE s2.Id = 3
AND s1.stipend > s2.stipend;

Find the subject(s) where the number of students is greater than the number of advisors.

SELECT sujet
FROM student
GROUP BY sujet
HAVING COUNT(*) > (SELECT COUNT(DISTINCT advisor) FROM GUIDE);

Calculate the average stipend for students in subjects where the average stipend is above the overall average stipend of all subjects.

select sujet, avg(stipend) as avg_stipend
from student
group by sujet
where avg_stipend > avg(stipend)

Hard Exercises (10)

Find the subject(s) with the highest average stipend among the students.

select sujet
from student
group by sujet
having avg(stipend) = (
	select max(avg_stipend)
    from (select avg(stipend) as avg_stipend
			from student
            group by sujet) as subquery
	);

Retrieve the names of students who have the same advisor as the student with Id 2.

select s.stu_name
from student s
join guide g on s.sujet = g.sujet
where s.id <> 2
and g.advisor = (
	select g.advisor
    from student s
	join guide g on s.sujet = g.sujet
    where s.id = 2);
    
##alternatively

SELECT s1.stu_name
FROM student s1
JOIN GUIDE g1 ON s1.sujet = g1.sujet
JOIN GUIDE g2 ON g1.advisor = g2.advisor
JOIN student s2 ON s2.sujet = g2.sujet
WHERE s2.Id = 2
AND s1.Id <> 2;

Calculate the average stipend for each subject, ordered in descending order of the average stipend.

select sujet, avg(stipend) as avg_stipend
from student
group by sujet
order by avg_stipend DESC;

Identify the student(s) who have a stipend greater than the average stipend of all students.

select *
from student
where stipend > (
	select avg(stipend)
    from student);

List the subjects and the count of students for each subject, including subjects with no students.

select g.sujet, count(s.id)
from guide g 
left join student s on g.sujet = s.sujet
group by sujet;

Find the advisor(s) who advise the maximum number of students.

select g.advisor
from guide g join student s on g.sujet=s.sujet
group by g.advisor
having count(s.id) = (
	select max(stu_count)
    from (
		select count(s.id) as stu_count
        from student s join guide g on s.sujet=g.sujet
        group by g.advisor) as subquery
	);

Retrieve the student(s) with the highest stipend in each subject.

select * 
from student s
join
(select sujet, max(stipend) as max_stipend
from student
group by sujet) as subquery
on s.sujet = subquery.sujet
where s.stipend = max_stipend;

Calculate the average stipend for each advisor, including advisors with no students.

select g.advisor, avg(stipend) as avg_stipend
from guide g 
left join student s on g.sujet = s.sujet
group by g.advisor;

Identify the subject(s) where the maximum stipend is greater than 500.

select sujet
from student
group by sujet
having max(stipend) > 500;

Find the student(s) who have the highest stipend in their subject, but their average score is below 70.

select s.*
from student s
join
(select sujet, max(stipend) as maximum_stipend
from student
group by sujet) as subquery on s.sujet = subquery.sujet
where s.stipend = maximum_stipend
and s.average < 70;