PostgreSQL14学习笔记_2.1_练习

&＃64;[目录]
这是超经典SQL练习题&＃xff0c;做完这些你的&＃xff33;&＃xff31;&＃xff2c;就过关了的题目在PostgreSQL14上的解答。

创建数据库

CREATE DATABASE edudb_test;


创建表并插入数据

学生表

CREATE TABLE student(s_id VARCHAR(10),s_name VARCHAR(20),s_age DATE,s_sex CHAR(1)
);
INSERT INTO student(s_id, s_name, s_age, s_sex) VALUES(&＃39;01&＃39; , &＃39;赵雷&＃39; , &＃39;19900101&＃39; , &＃39;男&＃39;),(&＃39;02&＃39; , &＃39;钱电&＃39; , &＃39;19901221&＃39; , &＃39;男&＃39;),(&＃39;03&＃39; , &＃39;孙风&＃39; , &＃39;19900520&＃39; , &＃39;男&＃39;),(&＃39;04&＃39; , &＃39;李云&＃39; , &＃39;19900806&＃39; , &＃39;男&＃39;),(&＃39;05&＃39; , &＃39;周梅&＃39; , &＃39;19911201&＃39; , &＃39;女&＃39;),(&＃39;06&＃39; , &＃39;吴兰&＃39; , &＃39;19920301&＃39; , &＃39;女&＃39;),(&＃39;07&＃39; , &＃39;郑竹&＃39; , &＃39;19890701&＃39; , &＃39;女&＃39;),(&＃39;08&＃39; , &＃39;王菊&＃39; , &＃39;19900120&＃39; , &＃39;女&＃39;)
;


科目表

CREATE TABLE course(c_id VARCHAR(10),c_name VARCHAR(20),t_id VARCHAR(10)
);
INSERT INTO course VALUES(&＃39;01&＃39; , &＃39;语文&＃39; , &＃39;02&＃39;),(&＃39;02&＃39; , &＃39;数学&＃39; , &＃39;01&＃39;),(&＃39;03&＃39; , &＃39;英语&＃39; , &＃39;03&＃39;)
;


教师表

CREATE TABLE teacher(t_id VARCHAR(10),t_name VARCHAR(20)
);
INSERT INTO teacher VALUES(&＃39;01&＃39; , &＃39;张三&＃39;),(&＃39;02&＃39; , &＃39;李四&＃39;),(&＃39;03&＃39; , &＃39;王五&＃39;)
;


成绩表

CREATE TABLE score(s_id VARCHAR(10),c_id VARCHAR(10),sc_score DECIMAL(3,1)
);
INSERT INTO score VALUES(&＃39;01&＃39; , &＃39;01&＃39; , 80),(&＃39;01&＃39; , &＃39;02&＃39; , 90),(&＃39;01&＃39; , &＃39;03&＃39; , 99),(&＃39;02&＃39; , &＃39;01&＃39; , 70),(&＃39;02&＃39; , &＃39;02&＃39; , 60),(&＃39;02&＃39; , &＃39;03&＃39; , 80),(&＃39;03&＃39; , &＃39;01&＃39; , 80),(&＃39;03&＃39; , &＃39;02&＃39; , 80),(&＃39;03&＃39; , &＃39;03&＃39; , 80),(&＃39;04&＃39; , &＃39;01&＃39; , 50),(&＃39;04&＃39; , &＃39;02&＃39; , 30),(&＃39;04&＃39; , &＃39;03&＃39; , 20),(&＃39;05&＃39; , &＃39;01&＃39; , 76),(&＃39;05&＃39; , &＃39;02&＃39; , 87),(&＃39;06&＃39; , &＃39;01&＃39; , 31),(&＃39;06&＃39; , &＃39;03&＃39; , 34),(&＃39;07&＃39; , &＃39;02&＃39; , 89),(&＃39;07&＃39; , &＃39;03&＃39; , 98)
;


&＃xff15;&＃xff10;道练习题

1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT st.*, sc1.sc_score FROM student AS st INNER JOIN score AS sc1 ON st.s_id &＃61; sc1.s_idWHERE c_id &＃61; &＃39;02&＃39; AND sc1.sc_score > (SELECT sc2.sc_score FROM score AS sc2 WHERE c_id&＃61;&＃39;01&＃39; AND sc1.s_id &＃61; sc2.s_id);
/*原博主的方法*/
SELECT sc1.*, sc2.c_id, sc2.sc_score
FROM (SELECT * FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc1
LEFT JOIN
(SELECT * FROM score WHERE c_id&＃61;&＃39;02&＃39;) AS sc2
ON sc1.s_id &＃61; sc2.s_id
WHERE sc1.sc_score > sc2.sc_score;


1.1 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT sc1.*, sc2.c_id, sc2.sc_score
FROM (SELECT * FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc1
INNER JOIN
(SELECT * FROM score WHERE c_id&＃61;&＃39;02&＃39;) AS sc2
ON sc1.s_id &＃61; sc2.s_id;


1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT sc1.*, sc2.c_id, sc2.sc_score
FROM (SELECT * FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc1
LEFT JOIN
(SELECT * FROM score WHERE c_id&＃61;&＃39;02&＃39;) AS sc2
ON sc1.s_id &＃61; sc2.s_id;


1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT sc2.*
FROM (SELECT * FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc1
RIGHT JOIN
(SELECT * FROM score WHERE c_id&＃61;&＃39;02&＃39;) AS sc2
ON sc1.s_id &＃61; sc2.s_id
WHERE sc1.sc_score is null;
/*原博主的方法*/
SELECT sc2.*
FROM score AS sc2
WHERE sc2.c_id &＃61; &＃39;02&＃39; and sc2.s_id not in (SELECT s_id FROM score WHERE c_id &＃61; &＃39;01&＃39;);


2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT st.s_id, st.s_name, sc.avg_score FROM student AS st INNER JOIN (SELECT s_id, AVG(sc_score) AS avg_score FROM score GROUP BY s_id) AS sc ON st.s_id &＃61; sc.s_idWHERE sc.avg_score >&＃61; 60;


3. 查询在 SC 表存在成绩的学生信息

SELECT st.*FROM student AS st INNER JOIN (SELECT DISTINCT s_id FROM score) AS sc ON st.s_id &＃61; sc.s_id;
/*原博主的方法
因为没有连接查询&＃xff0c;原博主的效率更高。*/
SELECT * FROM student WHERE s_id in (SELECT DISTINCT s_id FROM score);


4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT st.s_id, st.s_name, sc.course_count, sc.sum_scoreFROM student AS st LEFT JOIN (SELECT s_id, count(c_id) AS course_count, sum(sc_score) as sum_score FROM score GROUP BY s_id) AS sc ON st.s_id &＃61; sc.s_id;


4.1 查有成绩的学生信息

SELECT st.*FROM student AS st INNER JOIN (SELECT DISTINCT s_id FROM score) AS sc ON st.s_id &＃61; sc.s_id;


5. 查询「李」姓老师的数量

SELECT count(t_id) FROM teacher WHERE t_name like &＃39;李%&＃39;;


6. 查询学过「张三」老师授课的同学的信息

SELECT st.* FROM student AS st LEFT JOIN score AS sc ON st.s_id &＃61; sc.s_id WHERE sc.c_id &＃61; (SELECT c_id FROM course WHERE t_id &＃61; (SELECT t_id FROM teacher WHERE t_name &＃61; &＃39;张三&＃39;))
;
/*原博主的方法*/
SELECT * FROM student WHERE s_id in (SELECT DISTINCT s_id FROM scoreWHERE c_id &＃61; (SELECT c_id FROM Course WHERE t_id &＃61; (SELECT t_id FROM teacherWHERE t_name &＃61; &＃39;张三&＃39;))
);


7. 查询没有学全所有课程的同学的信息

/*这里添加了IS NULL的条件。如果删除此条件&＃xff0c;则不会显示一门课没考的同学信息*/
SELECT st.*, course_count FROM student AS st LEFT JOIN (SELECT s_id, count(c_id) AS course_count FROM score GROUP BY s_id) AS sc ON st.s_id &＃61; sc.s_id WHERE sc.course_count<3 OR sc.course_count IS NULL
;
/*原博主的方法*/
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM scoreGROUP BY s_id HAVING count(c_id)<3
);
SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM scoreGROUP BY s_id HAVING count(c_id)&＃61;3
);


8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT st.* FROM student AS st INNER JOIN (SELECT DISTINCT s_id FROM score WHERE c_id IN (SELECT c_id FROM score WHERE s_id&＃61;&＃39;01&＃39;) AND s_id!&＃61;&＃39;01&＃39;) AS sc ON st.s_id &＃61; sc.s_id
;
/*原博主的方法*/
SELECT * FROM student WHERE s_id IN (SELECT s_id FROM scoreWHERE c_id in (SELECT DISTINCT c_id FROM score WHERE s_id&＃61;&＃39;01&＃39;)AND s_id!&＃61;&＃39;01&＃39;
);


9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT st.* FROM student AS st INNER JOIN (SELECT s_id, count(c_id) AS count_c_id FROM score GROUP BY s_id) AS scON st.s_id &＃61; sc.s_idWHERE sc.count_c_id &＃61; (SELECT count(c_id) FROM score WHERE s_id&＃61;&＃39;01&＃39;)
;
/*原博主的方法*/
SELECT * FROM studentWHERE s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING count(c_id)&＃61;(SELECT count(c_id) FROM score WHERE s_id&＃61;&＃39;01&＃39;));


10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT st.* FROM student AS stWHERE st.s_id NOT IN (SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM course WHERE t_id &＃61; (SELECT t_id FROM teacher WHERE t_name &＃61; &＃39;张三&＃39;)));
;


11. 查询两门及其以上不及格课程的同学的学号&＃xff0c;姓名及其平均成绩

SELECT st.s_id, st.s_name, sc.avg_sc FROM student AS st INNER JOIN (SELECT DISTINCT s_id, count(sc_score) AS count_sc, avg(sc_score) AS avg_sc FROM score GROUP BY s_id) AS sc ON st.s_id &＃61; sc.s_id WHERE sc.count_sc >&＃61;2 AND sc.avg_sc <60
;


12. 检索" 01 "课程分数小于 60&＃xff0c;按分数降序排列的学生信息

SELECT st.s_id, st.s_name, sc.sc_score
FROM student AS st
RIGHT JOIN score AS sc
ON st.s_id&＃61;sc.s_id
WHERE sc.c_id&＃61;&＃39;01&＃39; AND sc.sc_score <60
ORDER BY sc.sc_score DESC;


13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT st.* FROM student AS st LEFT JOIN (SELECT s_id, sc_score FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc ON st.s_id &＃61; sc.s_id WHERE sc.sc_score<60 ORDER BY sc.sc_score DESC
;


14. 查询各科成绩最高分、最低分和平均分&＃xff1a;

以如下形式显示&＃xff1a;课程 ID&＃xff0c;课程 name&＃xff0c;最高分&＃xff0c;最低分&＃xff0c;平均分&＃xff0c;及格率&＃xff0c;中等率&＃xff0c;优良率&＃xff0c;优秀率
及格为>&＃61;60&＃xff0c;中等为&＃xff1a;70-80&＃xff0c;优良为&＃xff1a;80-90&＃xff0c;优秀为&＃xff1a;>&＃61;90
要求输出课程号和选修人数&＃xff0c;查询结果按人数降序排列&＃xff0c;若人数相同&＃xff0c;按课程号升序排列


WITH RECURSIVE sc1(c_id, score, jige, zhongdeng, youliang,youxiu) AS (
SELECT c_id, sc_score, CASE WHEN sc_score>&＃61;60 THEN 1 ELSE 0 END AS jige, CASE WHEN sc_score>&＃61;70 THEN 1 ELSE 0 END AS zhongdeng,CASE WHEN sc_score>&＃61;80 THEN 1 ELSE 0 END AS youliang,CASE WHEN sc_score>&＃61;90 THEN 1 ELSE 0 END AS youxiuFROM score
)
SELECT c_id, count(score), max(score) AS max_sc, min(score) AS min_sc, round(avg(score),2) AS avg_sc, round(avg(jige)*100,2) AS per_jige,round(avg(zhongdeng)*100,2) AS per_zhongdeng,round(avg(youliang)*100,2) AS per_youliang,round(avg(youxiu)*100,2) AS per_youxiugFROM sc1 GROUP BY c_idORDER BY count(score) DESC, c_id
;


15. 按各科成绩进行排序&＃xff0c;并显示排名&＃xff0c; Score 重复时保留名次空缺

SELECT st.s_id, st.s_name, RANK() OVER(ORDER BY sc_score DESC) AS rank1, sc.sc_score
FROM (SELECT s_id, sc_score FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc INNER JOIN student AS st
ON sc.s_id&＃61;st.s_id
;


15.1 按各科成绩进行排序&＃xff0c;并显示排名&＃xff0c; Score 重复时合并名次

SELECT st.s_id, st.s_name, DENSE_RANK() OVER(ORDER BY sc_score DESC) AS rank1, sc.sc_score
FROM (SELECT s_id, sc_score FROM score WHERE c_id&＃61;&＃39;01&＃39;) AS sc INNER JOIN student AS st
ON sc.s_id&＃61;st.s_id
;


16. 查询学生的总成绩&＃xff0c;并进行排名&＃xff0c;总分重复时保留名次空缺

SELECT st.s_id, st.s_name, RANK() OVER(ORDER BY sum_score DESC) AS rank1, sc.sum_score
FROM (SELECT s_id, sum(sc_score) AS sum_scoreFROM score GROUP BY s_id) AS sc INNER JOIN student AS st
ON sc.s_id&＃61;st.s_id;


16.1 查询学生的总成绩&＃xff0c;并进行排名&＃xff0c;总分重复时不保留名次空缺

SELECT st.s_id, st.s_name, DENSE_RANK() OVER(ORDER BY sum_score DESC) AS rank1, sc.sum_score
FROM (SELECT s_id, sum(sc_score) AS sum_scoreFROM score GROUP BY s_id) AS sc INNER JOIN student AS st
ON sc.s_id&＃61;st.s_id;


17. 统计各科成绩各分数段人数&＃xff1a;课程编号&＃xff0c;课程名称&＃xff0c;[100-85]&＃xff0c;[85-70]&＃xff0c;[70-60]&＃xff0c;[60-0] 及所占百分比

注&＃xff1a;题干中分数段[100-85]用的是闭区间&＃xff0c;会导致85、70和60被重复计算。
所以改为半开区间&＃xff08;100-85]&＃xff0c;&＃xff08;85-70]&＃xff0c;&＃xff08;70-60]&＃xff0c;&＃xff08;60-0]。

WITH RECURSIVE sc1(c_id, score) AS (
SELECT c_id, sc_score, CASE WHEN sc_score<60 THEN 1 ELSE 0 END AS scale1, CASE WHEN sc_score>&＃61;60 AND sc_score<70 THEN 1 ELSE 0 END AS scale2,CASE WHEN sc_score>&＃61;70 AND sc_score<85 THEN 1 ELSE 0 END AS scale3,CASE WHEN sc_score>&＃61;85 THEN 1 ELSE 0 END AS scale4FROM score
)
SELECT sc1.c_id, course.c_name,sum(sc1.scale1) AS scale1, sum(sc1.scale2) AS scale2, sum(sc1.scale3) AS scale3, sum(sc1.scale4) AS scale4, round(avg(scale1)*100,2) AS per_scale1,round(avg(scale2)*100,2) AS per_scale2,round(avg(scale3)*100,2) AS per_scale3,round(avg(scale4)*100,2) AS per_scale4FROM sc1 INNER JOIN course ON sc1.c_id&＃61;course.c_idGROUP BY sc1.c_id, course.c_name
;


18. 查询各科成绩前三名的记录

SELECT * FROM (SELECT *, RANK() OVER(PARTITION BY c_id order by sc_score DESC) AS rank1FROM score) AS sc
WHERE rank1<&＃61;3
;


19. 查询每门课程被选修的学生数

SELECT cr.c_name, count(sc.s_id)
FROM score AS sc RIGHT JOIN course AS cr
ON sc.c_id &＃61; cr.c_id
GROUP BY cr.c_name;


20. 查询出只选修两门课程的学生学号和姓名

SELECT st.s_id, st.s_name
FROM student AS st
INNER JOIN (SELECT s_id, count(c_id) AS count_c_idFROM scoreGROUP BY s_id) AS sc
ON st.s_id &＃61; sc.s_id
WHERE sc.count_c_id &＃61; 2
;
/*原博主的方法*/
SELECT s_id, s_name FROM student
WHERE s_id IN (SELECT s_id FROM (SELECT s_id, count(c_id) AS count_c_id FROM score GROUP BY s_id) AS scWHERE count_c_id &＃61; 2
);


21. 查询男生、女生人数

SELECT s_sex, count(s_id) FROM student GROUP BY s_sex;


22. 查询名字中含有「风」字的学生信息

/*使用了正则表达式*/
SELECT student.* FROM student WHERE s_name~&＃39;.*风.*&＃39;;SELECT student.* FROM student WHERE s_name LIKE &＃39;%风%&＃39;;


23. 查询同名同性学生名单&＃xff0c;并统计同名人数

WITH RECURSIVE st1 AS (SELECT s_name, count(s_name) AS count_name FROM student GROUP BY s_name, s_sex
)
SELECT student.s_name FROM student LEFT JOIN st1 ON student.s_name &＃61; st1.s_name WHERE st1.count_name>1;


24. 查询 1990 年出生的学生名单

SELECT * FROM student WHERE EXTRACT(YEAR FROM s_age)&＃61;1990;


25. 查询每门课程的平均成绩&＃xff0c;结果按平均成绩降序排列&＃xff0c;平均成绩相同时&＃xff0c;按课程编号升序排列

SELECT c_id, avg(sc_score) AS avgsc
FROM score
GROUP BY c_id
ORDER BY avgsc DESC, c_id
;


26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT st.s_id, st.s_name, sc.avg_score
FROM student AS st INNER JOIN (SELECT s_id, avg(sc_score) AS avg_score FROM score GROUP BY s_id) AS sc
ON st.s_id &＃61; sc.s_id
WHERE sc.avg_score>&＃61;85;


27. 查询课程名称为「数学」&＃xff0c;且分数低于 60 的学生姓名和分数

SELECT s_id, sc_score
FROM score LEFT JOIN course
ON score.c_id &＃61; course.c_id
WHERE course.c_name&＃61;&＃39;数学&＃39; AND score.sc_score<60;


28. 查询所有学生的课程及分数情况&＃xff08;存在学生没成绩&＃xff0c;没选课的情况&＃xff09;

SELECT st.s_name, sc2.c_name, sc2.sc_score FROM (SELECT sc.s_id, course.c_name, sc.sc_score FROM score AS sc LEFT JOIN course ON sc.c_id &＃61; course.c_id)
AS sc2 RIGHT JOIN student AS st
ON sc2.s_id &＃61; st.s_id;


29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT st.s_name, sc2.c_name, sc2.sc_score FROM (SELECT sc.s_id, course.c_name, sc.sc_score FROM score AS sc LEFT JOIN course ON sc.c_id &＃61; course.c_id)
AS sc2 RIGHT JOIN student AS st
ON sc2.s_id &＃61; st.s_id
WHERE sc2.sc_score>&＃61;70;


30. 查询不及格的课程

SELECT st.s_name, sc2.c_name, sc2.sc_score FROM (SELECT sc.s_id, course.c_name, sc.sc_score FROM score AS sc LEFT JOIN course ON sc.c_id &＃61; course.c_id)
AS sc2 RIGHT JOIN student AS st
ON sc2.s_id &＃61; st.s_id
WHERE sc2.sc_score<60;


31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT st.s_id, st.s_name
FROM student AS st RIGHT JOIN score AS sc
ON st.s_id&＃61;sc.s_id
WHERE sc.c_id &＃61; &＃39;01&＃39; AND sc.sc_score>&＃61;80;


32. 求每门课程的学生人数

SELECT c_id, count(s_id) FROM score GROUP BY c_id;


33. 成绩不重复&＃xff0c;查询选修「张三」老师所授课程的学生中&＃xff0c;成绩最高的学生信息及其成绩

SELECT st.*, sc.sc_score
FROM student AS st RIGHT JOIN score AS sc
ON st.s_id &＃61; sc.s_id
WHERE sc.c_id &＃61; (SELECT c_id FROM course INNER JOIN teacher ON course.t_id &＃61; teacher.t_id WHERE teacher.t_name &＃61; &＃39;张三&＃39;)
ORDER BY sc_score DESC
LIMIT 1;


34. 成绩有重复的情况下&＃xff0c;查询选修「张三」老师所授课程的学生中&＃xff0c;成绩最高的学生信息及其成绩

成绩有重复&＃xff0c;意味着成绩最高的可能是多个

SELECT st.*, sc.sc_score
FROM student AS st RIGHT JOIN score AS sc
ON st.s_id &＃61; sc.s_id
WHERE sc.sc_score &＃61; (SELECT max(sc2.sc_score) FROM score AS sc2 WHERE c_id &＃61; (SELECT c_id FROM course INNER JOIN teacher ON course.t_id &＃61; teacher.t_id WHERE teacher.t_name &＃61; &＃39;张三&＃39;))
;


35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

WITH RECURSIVE sc2 AS (SELECT c_id, count(s_id) as count_s_id, sc_score FROM score GROUP BY c_id, sc_score)
SELECT sc.s_id, sc.c_id, sc.sc_score
FROM score AS sc INNER JOIN sc2
ON sc.sc_score &＃61; sc2.sc_score AND sc.c_id &＃61; sc2.c_id
WHERE sc2.count_s_id >1;


36. 查询每门功课成绩最好的前两名

SELECT st.s_id, st.s_name, sc.c_id, sc.rank1
FROM student AS st
RIGHT JOIN (SELECT s_id, c_id, ROW_NUMBER() over(PARTITION BY c_id order by sc_score DESC) AS rank1 FROM score) AS sc
ON st.s_id &＃61; sc.s_id
WHERE sc.rank1<3
ORDER BY sc.c_id, sc.rank1
;


37. 统计每门课程的学生选修人数&＃xff08;超过 5 人的课程才统计&＃xff09;

SELECT c_id, count_s_id
FROM (SELECT c_id, count(s_id) AS count_s_id FROM score GROUP BY c_id) AS sc
WHERE count_s_id>5;


38. 检索至少选修两门课程的学生学号

SELECT s_id
FROM (SELECT s_id, count(c_id) AS count_c_id FROM score GROUP BY s_id) AS sc
WHERE count_c_id>1;


39. 查询选修了全部课程的学生信息

SELECT s_id
FROM (SELECT s_id, count(c_id) AS count_c_id FROM score GROUP BY s_id) AS sc
WHERE count_c_id&＃61;3;


40. 查询各学生的年龄&＃xff0c;只按年份来算

SELECT s_id, EXTRACT(YEAR FROM NOW())-EXTRACT(YEAR FROM s_age) AS age
FROM student;


41. 按照出生日期来算&＃xff0c;当前月日 <出生年月的月日则&＃xff0c;年龄减一

SELECT s_id, date_part(&＃39;year&＃39;,age(s_age)) AS age
FROM student;


42. 查询本周过生日的学生

SELECT s_id,s_name,s_age FROM (SELECT s_id,s_name,s_age, EXTRACT(week FROM s_age) AS num_week FROM student) AS st
WHERE num_week &＃61; EXTRACT(week FROM now());


43. 查询下周过生日的学生

SELECT s_id,s_name,s_age FROM (SELECT s_id,s_name,s_age, EXTRACT(week FROM (s_age - interval &＃39;1 week&＃39;)) AS num_week FROM student) AS st
WHERE num_week &＃61; EXTRACT(week FROM now());


44. 查询本月过生日的学生

SELECT s_id,s_name,s_age FROM (SELECT s_id,s_name,s_age, date_part(&＃39;month&＃39;, s_age) AS birth_monthFROM student) AS st
WHERE birth_month &＃61; EXTRACT(month FROM now());


45. 查询下月过生日的学生

SELECT s_id,s_name,s_age FROM (SELECT s_id,s_name,s_age, EXTRACT(month FROM (s_age - interval &＃39;1 month&＃39;)) AS num_month FROM student) AS st
WHERE num_month &＃61; EXTRACT(month FROM now());&＃96;