Essential PSQL 50 Selected Practice Questions with Answers

Environment Setup（PGsql）

t_course Table

CREATE TABLE public.t_course (
    cid varchar(10) NOT NULL,
    cname varchar(10) NOT NULL,
    tid varchar(10) NOT NULL,
    CONSTRAINT t_course_pkey PRIMARY KEY (cid)
);

INSERT INTO public.t_course (cid, cname, tid) VALUES
('01', 'Chinese', '02'),
('02', 'Math', '01'),
('03', 'English', '03');

t_score Table

CREATE TABLE public.t_score (
    sid varchar(10) NOT NULL,
    cid varchar(10) NOT NULL,
    score numeric(18, 1) NOT NULL,
    CONSTRAINT t_score_pkey PRIMARY KEY (sid, cid)
);

INSERT INTO public.t_score (sid, cid, score) VALUES
('01', '02', 90.0),
('01', '01', 80.0),
('01', '03', 99.0),
('02', '02', 60.0),
('02', '01', 70.0),
('02', '03', 80.0),
('03', '01', 80.0),
('03', '02', 80.0),
('03', '03', 80.0),
('04', '01', 50.0),
('04', '02', 30.0),
('04', '03', 20.0),
('05', '01', 76.0),
('05', '02', 87.0),
('06', '01', 31.0),
('06', '03', 34.0),
('07', '02', 90.0),
('07', '03', 98.0);

t_student Table

CREATE TABLE public.t_student (
    sid varchar(6) NOT NULL,
    sname varchar(10) NOT NULL,
    sage timestamp NULL,
    ssex varchar(10) NOT NULL,
    CONSTRAINT t_student_pkey PRIMARY KEY (sid),
    CONSTRAINT t_student_ssex_check CHECK (((ssex)::text = 'Male'::text) OR ((ssex)::text = 'Female'::text))
);

INSERT INTO public.t_student (sid, sname, sage, ssex) VALUES
('01', 'Zhao Lei', '1990-05-18 00:00:00', 'Male'),
('02', 'Qian Dian', '1990-05-24 00:00:00', 'Male'),
('03', 'Sun Feng', '1990-05-20 00:00:00', 'Male'),
('04', 'Li Yun', '1990-05-25 00:00:00', 'Male'),
('05', 'Zhou Mei', '1991-12-01 00:00:00', 'Female'),
('06', 'Wu Lan', '1992-01-01 00:00:00', 'Female'),
('07', 'Zheng Zhu', '1989-10-31 00:00:00', 'Female'),
('08', 'Zhang San', '2017-12-20 00:00:00', 'Female'),
('09', 'Li Si', '2017-12-25 00:00:00', 'Female'),
('10', 'Wang Wu', '2021-09-14 00:00:00', 'Female'),
('11', 'Zhao Liu', '2013-09-13 00:00:00', 'Female'),
('12', 'Sun Qi', '2014-10-01 00:00:00', 'Female');

t_teacher Table

CREATE TABLE public.t_teacher (
    tid varchar(10) NOT NULL,
    tname varchar(10) NOT NULL,
    CONSTRAINT t_teacher_pkey PRIMARY KEY (tid)
);

INSERT INTO public.t_teacher (tid, tname) VALUES
('01', 'Zhang San'),
('02', 'Li Si'),
('03', 'Wang Wu');

be careful

MySQL can use single quotes (') or double quotes (") to represent values, but PG can only use single quotes (') to represent values.

PG's double quotes (") are used to represent system identifiers, such as table names or field names. MySQL can use backticks (`) to represent system identifiers, such as table names and field names, which are not supported in PG.

MySQL can use single quotes (') or double quotes (") to represent values, but PG can only use single quotes (') to represent values.

Practice Starts

### Environment Setup

#### t_course Table

sql
CREATE TABLE public.t_course (
cid varchar(10) NOT NULL,
cname varchar(10) NOT NULL,
tid varchar(10) NOT NULL,
CONSTRAINT t_course_pkey PRIMARY KEY (cid)
);

INSERT INTO public.t_course (cid, cname, tid) VALUES
('01', 'Chinese', '02'),
('02', 'Math', '01'),
('03', 'English', '03');


#### t_score Table

sql
CREATE TABLE public.t_score (
sid varchar(10) NOT NULL,
cid varchar(10) NOT NULL,
score numeric(18, 1) NOT NULL,
CONSTRAINT t_score_pkey PRIMARY KEY (sid, cid)
);

INSERT INTO public.t_score (sid, cid, score) VALUES
('01', '02', 90.0),
('01', '01', 80.0),
('01', '03', 99.0),
('02', '02', 60.0),
('02', '01', 70.0),
('02', '03', 80.0),
('03', '01', 80.0),
('03', '02', 80.0),
('03', '03', 80.0),
('04', '01', 50.0),
('04', '02', 30.0),
('04', '03', 20.0),
('05', '01', 76.0),
('05', '02', 87.0),
('06', '01', 31.0),
('06', '03', 34.0),
('07', '02', 90.0),
('07', '03', 98.0);


#### t_student Table

sql
CREATE TABLE public.t_student (
sid varchar(6) NOT NULL,
sname varchar(10) NOT NULL,
sage timestamp NULL,
ssex varchar(10) NOT NULL,
CONSTRAINT t_student_pkey PRIMARY KEY (sid),
CONSTRAINT t_student_ssex_check CHECK (((ssex)::text = 'Male'::text) OR ((ssex)::text = 'Female'::text))
);

INSERT INTO public.t_student (sid, sname, sage, ssex) VALUES
('01', 'Zhao Lei', '1990-05-18 00:00:00', 'Male'),
('02', 'Qian Dian', '1990-05-24 00:00:00', 'Male'),
('03', 'Sun Feng', '1990-05-20 00:00:00', 'Male'),
('04', 'Li Yun', '1990-05-25 00:00:00', 'Male'),
('05', 'Zhou Mei', '1991-12-01 00:00:00', 'Female'),
('06', 'Wu Lan', '1992-01-01 00:00:00', 'Female'),
('07', 'Zheng Zhu', '1989-10-31 00:00:00', 'Female'),
('08', 'Zhang San', '2017-12-20 00:00:00', 'Female'),
('09', 'Li Si', '2017-12-25 00:00:00', 'Female'),
('10', 'Wang Wu', '2021-09-14 00:00:00', 'Female'),
('11', 'Zhao Liu', '2013-09-13 00:00:00', 'Female'),
('12', 'Sun Qi', '2014-10-01 00:00:00', 'Female');


#### t_teacher Table

sql
CREATE TABLE public.t_teacher (
tid varchar(10) NOT NULL,
tname varchar(10) NOT NULL,
CONSTRAINT t_teacher_pkey PRIMARY KEY (tid)
);

INSERT INTO public.t_teacher (tid, tname) VALUES
('01', 'Zhang San'),
('02', 'Li Si'),
('03', 'Wang Wu');


### Practice Starts

1.  Query the information of students whose score of the course "01" is higher than that of the course "02" and the course scores

sql
with data_01 as (select sid, score from t_score ts1 where cid = '01'),

data_02 as (select sid, score from t_score ts2 where cid = '02')

select ts.*, d1.score from t_student ts, data_01 d1, data_02 d2

where

d1.sid = d2.sid

and d1.score > d2.score

and ts.sid = d1.sid;


2.  Query the situation where both the course "01" and the course "02" exist

sql
with data_01 as (select sid, score from t_score ts1 where cid = '01'),

data_02 as (select sid, score from t_score ts2 where cid = '02')

select ts.* from t_student ts, data_01 d1, data_02 d2

where

d1.sid = d2.sid

and ts.sid = d1.sid;


3.  Query the situation where the course "01" exists, but the course "02" may not exist (display as null if it does not exist)

sql
with data_01 as (select sid, score from t_score ts1 where cid = '01'),

data_02 as (select sid, score from t_score ts2 where cid = '02')

select

d1.sid as sid,

d1.score as "01",

d2.score as "02"

from

data_01 as d1 left join data_02 as d2 on d1.sid = d2.sid;


4.  Query the situation where the course "01" does not exist but the course "02" exists

sql
select ts.sid, ts.cid, ts.score from t_score ts

where

cid = '02'

and sid not in (select sid from t_score ts1 where cid = '01')


5.  Query the student ID, student name and average score of students whose average score is greater than or equal to 60 points

sql
select st.*, sid_avg.avger as "avg" from

t_student st,

(select ts.sid, round(sum(score)/3,1) as avger from t_score ts group by ts.sid) sid_avg

where

st.sid = sid_avg.sid and sid_avg.avger >= 60

order by "avg" asc;


6.  Query the student information where there are grades in the SC table.

sql
select * from t_student ts

where sid in (select distinct sid from t_score ts)


7.  Query the student ID, student name, total number of selected courses and the sum of grades of all courses of all students

sql
select

ts2.*, lo.num, lo.avger

from

t_student ts2

left join

(select ts.sid, count(1) as num, round(sum(score)/3,1) as avger

from t_score ts

group by ts.sid

) lo

on

ts2.sid = lo.sid;


8.  Display students who have not selected courses (display as NULL)

sql
select * from t_student ts where ts.sid not in (select sid from t_score ts group by sid order by sid)


9.  Query the student information with grades

sql
select *

from t_student

where

sid not in(select distinct sid from t_score)

` Refer to the table (small table in large table exists)` 

10.  Query the number of teachers whose surname is "Li"

sql
select count(*) from (select tid from t_teacher where tname like 'Li%') as tech;


11.  Query the information of students who have taken the courses taught by the teacher "Zhang San"  
– Get the teacher tid  
– Get the relevant cid through tid  
– Traverse t_score to get sid through cid  
– Get all information through sid

sql
with cid_col as (select cid from t_course tc where tc.tid in

(select tid from t_teacher where tname = 'Zhang San'))

select * from t_student ts

inner join (select sid from t_score where t_score.cid in (select cid from cid_col)) as sid_col

on sid_col.sid = ts.sid


12. Query the information of students who have not taken all courses  
– Get the number of courses  
– Get the course selection table and select the list of those who have not selected all  
– Query student information through the list

sql
select ts2.* from t_student ts2

inner join

( select sid from t_score ts group by sid

having count(cid) < (select count(*) from t_course)

) res_col

on res_col.sid = ts2.sid ;

13. Query the information of students who have at least one course in common with the student whose student ID is "01" ()  
– First check the course set cid_col_01 of student 01  
– Check for other students to see if they are in this set

sql
select distinct t_student.sid, t_student.sname, t_student.sage, t_student.ssex from t_student inner join

(

select t_score.sid from t_score

where t_score.cid in (select cid from t_score where sid = '01')

) sid_col

on sid_col.sid = t_student.sid;


15.  Query the student names of students who have not taken any courses taught by the teacher "Zhang San"  
    – First check all courses of Teacher Zhang San  
    – First check the teacher tid  
    – Check the cid through tid  
    – Find all those who have taken courses through the grade table  
    – Exclude those who have taken courses

sql
with cid_col as

(select cid from t_course where tid in (select tid from t_teacher where tname = 'Zhang San'))

select * from t_student

where sid not in(

select sid from t_score where cid in (select cid from cid_col)

);


16. Query the student ID, student name and average score of students who have two or more failed courses

sql
select t_student.sid, t_student.sname, avg(score) from t_student

inner join t_score

on t_student.sid = t_score.sid

group by t_student.sid having count(score < 60 or score = null) >= 2

sql
select t_student.sid, t_student.sname, avg(score) from t_student

inner join t_score

on t_student.sid = t_score.sid where t_score.score < 60

group by t_student.sid having count(*) >= 2


17. Retrieve the student information whose score of the course "01" is less than 60 and sorted in descending order of the score  
– Find the cid of the course "01" in t_course  
– Find the sid through the cid in t_score

sql
select ts.*, cid_score_lo.score as score from t_student ts

inner join

(select sid, score from t_score ts2 where cid

in (select cid from t_course where cid = '01')

)cid_score_lo

on

cid_score_lo.sid = ts.sid

and

cid_score_lo.score < 60

order by score desc


18. Display all students' grades of all courses and average grades in descending order of average grades

sql
SELECT sid,

MAX(CASE WHEN cid='01' THEN score ELSE 0 END) "Chinese",

MAX(CASE WHEN cid='02' THEN score ELSE 0 END) "Mathematics",

MAX(CASE WHEN cid='03' THEN score ELSE 0 END) "English",

round(sum(score)/3,1) "Average Grade"

FROM t_score

GROUP BY sid

ORDER BY "Average Grade" DESC


19. Query the highest score, lowest score, average score of each course and the pass rate, medium rate, good rate, excellent rate of each course.  
The pass is >= 60, the medium is: 70 - 80, the good is: 80 - 90, the excellent is: >= 90.  
The output is required to be the course ID and the number of students taking the course. The query results are sorted in descending order of the number of students. If the number of students is the same, they are sorted in ascending order of the course ID.

sql
select

ts.cid,

max(tc.cname) "Name",

max(ts.score) "Highest Score",

min(ts.score) "Lowest Score",

round(sum(ts.score) / count(ts.sid),1) "Average Score",

sum(case when ts.score >= 60 then 1 else 0 end) "Pass Rate",

sum(case when ts.score >= 70 and ts.score <80 then 1 else 0 end) "Medium Rate",
sum(case when ts.score >= 80 and ts.score <90 then 1 else 0 end) "Good Rate",
sum(case when ts.score >= 90 then 1 else 0 end) "Excellent Rate",

count(sid) "Number of Students"

from t_score ts

left join t_course tc

on ts.cid = tc.cid

group by ts.cid

order by "Number of Students", cid;


20. Sort by each course grade and display the ranking. When the Score is repeated, keep the ranking blank

sql
select cid, sid, score, (select count(*) from t_score where cid = ts.cid and score > ts.score) + 1 rank

from t_score ts order by cid, score desc;


21. Query the total score of students and rank them. When the total score is repeated, do not keep the ranking blank

sql
select sid, sum(score), dense_rank() over(order by sum(score) desc) from

t_score group by sid;


22. Count the number of people in each score range of each course: course ID, course name, [100 - 85], (85 - 70], (70 - 60], (60 - 0] and the percentage

sql
select
ts.cid,
max(tc.cname),
sum(case when score >= 85 then 1 else 0 end) "[100-85]",
sum(case when score >= 70 and score < 85 then 1 else 0 end) "(85-70]",
sum(case when score >= 60 and score < 70 then 1 else 0 end) "(70-60]",
sum(case when score < 60 then 1 else 0 end) "(60-0]"
from t_score ts
left join t_course tc
on ts.cid = tc.cid
group by ts.cid


23. Query for Students Who Have Not Taken All Courses

sql
with course_num as(select count(*) from t_course)
,sid_col as (select distinct sid from t_score)
,extra_col as (select distinct sid from t_score group by sid having count(sid) = (select * from course_num) )
select * from t_student ts where sid not in (select * from sid_col)
or sid not in (select * from extra_col)
order by sid


24. Query the number of students selected for each course

sql
select ts.cid, max(tc.cname), count(ts.sid) from t_score ts left join t_course tc on ts.cid = tc.cid group by ts.cid ;


25. Query the student ID and name of students who have selected only two courses

sql
select

ts2.sid,

ts2.sname,

ri.num

from t_student ts2

right join

(select ts.sid, count() as num from t_score ts group by ts.sid having count() = 2) ri

on ri.sid = ts2.sid


26. Query the number of male and female students

sql
select ssex, count(_) as num from t_student group by ssex ;


27. Query the student information whose name contains the character "Feng"

sql
select * from t_student where sname like '%Feng%';


28. Query the list of students with the same name and count the number of people with the same name

sql
select sname, count() from t_student group by sname having count() > 1;


29. Query the list of students born in 1990

sql
select * from t_student where date_part('year', sage) = '1990'

select * from t_student where extract(year from sage) = '1990'


30. Query the average score of each course. The results are sorted in descending order of the average score. When the average score is the same, they are sorted in ascending order of the course ID.

sql
select cid, avg(score) av_s from t_score ts group by cid order by av_s desc,cid asc;


31. Query the student ID, student name and average score of all students whose average score is greater than or equal to 85

sql
select

ts.sid,

ts.sname,

ri.avs

from t_student ts

right join (select sid, avg(score) avs from t_score ts group by sid having avg(score) >=85 ) ri

on ri.sid = ts.sid


32. Query the student name and score of students whose course name is "Mathematics" and whose score is lower than 60

sql
select

ts.sid, ts2.sname, ts.score

from t_score ts

inner join t_student ts2 on ts2.sid = ts.sid

and ts.score < 60 and ts.cid in (select cid from t_course where cname ='Mathematics')


33. Query the courses and scores of all students (there are situations where students have no grades or have not selected courses

sql
select

ts.*,

ri."Chinese",

ri."Mathematics",

ri."English"

from t_student ts

left join

(select ts2.sid,

max(case when ts2.cid = '01' then score else null end) "Chinese",

max(case when ts2.cid = '02' then score else null end) "Mathematics",

max(case when ts2.cid = '03' then score else null end) "English"

from t_score ts2 group by ts2.sid) ri

on ts.sid = ri.sid


34. Query the name, course name and score of students whose score of any course is above 70

sql
select

ts.sid,

ts.sname,

ri."Chinese",

ri."Mathematics",

ri."English"

from t_student ts

inner join

(select

ts2.sid,

max(case when ts2.cid = '01' then score else null end) "Chinese",

max(case when ts2.cid = '02' then score else null end) "Mathematics",

max(case when ts2.cid = '03' then score else null end) "English"

from t_score ts2 group by ts2.sid) ri

on ri.sid = ts.sid and ri."Chinese" >=70 and ri."Mathematics" >=70 and ri."English" >=70


35. Query courses with failed grades

sql
select cid from t_score ts group by cid having min(score) < 60


36. Query the student ID and name of students whose course ID is 01 and whose course score is 80 or above

sql
select

ts2.sid, ts2.sname

from

t_student ts2

right join

(select sid from t_score ts where ts.cid = '01' and ts.score >=80) ri

on ri.sid = ts2.sid


37. Query the number of students for each course

sql
select cid, count(_) from t_score group by cid;


38. When the grades are not repeated, query the student information and grade of the student with the highest grade among the students who have taken the courses taught by the teacher "Zhang San"

sql
with cid_col as (select cid from t_course where tid in

(select tid from t_teacher where tname = 'Zhang San'))

select ts._, ri.score from t_student ts

inner join (select sid, score from t_score where cid in (select * from cid_col))ri

on ri.sid = ts.sid

order by ri.score desc limit 1

sql
select

t_student._,

t_score.score

from t_course,t_score,t_student,t_teacher

where

t_teacher.tname = 'Zhang San'

and t_teacher.tid = t_course.tid

and t_course.cid = t_score.cid

and t_score.sid = t_student.sid

order by t_score.score desc limit 1


39. When the grades are repeated, query the student information and grade of the student with the highest grade among the students who have taken the courses taught by the teacher "Zhang San"

sql
select lef.sid, lef.sname, lef.score from

(

select

t_student._,

t_score.score,

t_score.cid

from t_course,t_score,t_student,t_teacher

where

t_teacher.tname = 'Zhang San'

and t_teacher.tid = t_course.tid

and t_course.cid = t_score.cid

and t_score.sid = t_student.sid

) lef

inner join

(select cid,max(score) max_sc from t_score group by cid) rig

on

lef.cid = rig.cid

and

lef.score = rig.max_sc


40. Query the student ID, course ID and student score of students with the same score in different courses

sql
select ts1.sid, ts1.cid, ts1.score from t_score ts1

inner join t_score ts2

on ts2.cid = ts1.cid and ts1.sid<> ts2.sid and ts1.score = ts2.score

group by ts1.cid, ts1.sid

order by cid


41. Query the top two students with the best grades in each course

sql
(select * from t_score where cid = '01' order by score desc limit 2)

union

(select * from t_score where cid = '02' order by score desc limit 2)

union

(select * from t_score where cid = '03' order by score desc limit 2)

order by cid

sql
select ts1.sid, ts1.cid, ts1.score from t_score ts1

left join t_score ts2

on ts2.cid = ts1.cid and ts1.score < ts2.score

group by ts1.sid, ts1.cid

having count(ts1.sid) < 2

order by cid


42. Count the number of students selected for each course (only count courses with more than 5 students)

sql
select cid, count() as num from t_score group by cid having count() > 5 order by cid


43. Retrieve the student ID of students who have selected at least two courses

sql
select sid, count() as num from t_score group by sid having count() >= 2 order by sid


44. Query the information of students who have selected all courses

sql
with nu as (select count(_) from t_course tc)

select sid, count() as num from t_score group by sid having count() >= (select * from nu) order by sid


45. Query the age of each student, calculated only by year

sql
select sname, to_number( to_char(current_date,'yyyy'), '9999') - to_number(to_char(sage,'yyyy'), '9999') as "Year" from t_student order by "Year"

46. Calculate the age based on the date of birth. If the current month and day are less than the month and day of the birth date, subtract one from the age

sql
select sname, extract ("year" from age(sage)) as "Age" from t_student order by "Age"

select sname, date_part ('year', age(sage)) as "Age" from t_student order by "Age"


47. Query students whose birthday is this month

sql
select sname, to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = 0


48. Query students whose birthday is next month

sql
select sname, to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = -1


49. Query students whose date of birth is from May 18th to 25th, 1990  
General do not use between and statement

sql

select * from t_student ts where

ts.sage >= cast(concat('1990-05-18', '0:00:00') as timestamp)

and

ts.sage <= cast(concat('1990-05-25', '3:59:59') as timestamp)




If it is xml, modification is required.

OK,If you find this article helpful, feel free to share it with more people.

If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. [https://www.sqlynx.com/download/](https://www.sqlynx.com/download/) Free download

Blog