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.
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.
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
Top comments (0)