SQL 查询语句练习
班级表batch：




batchcode
班级编号




batchname
班级名


教师信息表teacherinfo：




teachid
教师编号




teachname
教师名


教学计划表courseplan：




studno
学生编号




courseid
课程编号


teachid
教师编号


coursedt
教学日期


信息表course：




courseid
课程编号




coursename
课程名


成绩表score：




studno
学生编号




courseid
课程编号


grade
成绩


学生表student：




studno
学生编号




studname
学生名


batchcode
班级编号


–1.显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示
SELECT st.studno as 学生编号,st.studname as 学生姓名,co.coursename as 课程名称,sc.grade as 成绩
FROM student st 
LEFT JOIN score sc 
ON (st.studno = sc.studno)
RIGHT JOIN course co
ON (co.courseid = sc.courseid)
order by st.studno,co.coursename;

–2.查询显示单科最高成绩
SELECT MAX(sc.grade)
FROM score sc
GROUP BY sc.courseid;

–3、查询显示学生课程及格还是不及格
SELECT st.studname,co.coursename,
CASE WHEN sc.grade>'60' THEN '及格' ELSE '不及格' END
FROM student st
LEFT JOIN score sc
ON (st.studno = sc.studno)
LEFT JOIN course co
ON (co.courseid = sc.courseid)
ORDER BY st.studno;

–4、统计学生选科的数量
SELECT c.coursename,COUNT(s.courseid)
FROM course c
LEFT JOIN score s
ON (c.courseid = s.courseid)
GROUP BY s.courseid,c.coursename;

–5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩
SELECT st.studno,st.studname,co.coursename,sc.grade
FROM student st
LEFT JOIN score sc
ON (st.studno = sc.studno)
LEFT JOIN course co
ON (sc.courseid = co.courseid)
WHERE sc.grade >
      (
      SELECT AVG(sc2.grade)
      FROM score sc2
      WHERE sc.courseid = sc2.courseid
      GROUP BY sc2.courseid
      );

–6、查询显示需要补考的学生的学生编号，学生姓名和课程名称
SELECT st.studno,st.studname,co.coursename
FROM student st
LEFT JOIN score sc
ON (st.studno = sc.studno)
LEFT JOIN course co
ON (sc.courseid = co.courseid)
WHERE sc.grade <= 60;

–7、统计各科成绩平均分，显示课程编号，课程名称，平均分
SELECT co.courseid,co.coursename,ROUND(AVG(sc.grade),2)
FROM course co
LEFT JOIN score sc
ON (co.courseid = sc.courseid)
GROUP BY co.courseid,co.coursename
ORDER BY co.courseid;

–8、查询选修了java课程的学生信息
SELECT *
FROM student st
LEFT JOIN score sc
ON(st.studno = sc.studno)
LEFT JOIN course co
ON (sc.courseid = co.courseid)
WHERE co.coursename = 'JAVA';

–9、查询没有选修JAVA课程的学生信息
SELECT *
FROM student st
LEFT JOIN score sc
ON(st.studno=sc.studno)
LEFT JOIN course co
ON (sc.courseid=co.courseid)
WHERE co.coursename != 'JAVA';

–10、查询选修了教师李可课程的学生信息
SELECT *
FROM student st
LEFT JOIN courseplan co
ON (st.studno = co.studno)
LEFT JOIN teacherinfo te
ON (co.teachid = te.teachid)
WHERE te.teachname = '李可';

–11、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期
SELECT st.studno, st.studname, st.batchcode, co.courseid, te.teachname, co.coursedt
FROM student st
LEFT JOIN courseplan co
ON (st.studno = co.studno)
RIGHT JOIN teacherinfo te
ON (co.teachid = te.teachid)
WHERE (co.courseid = 'A01') AND (co.courseid = 'A02');

–12、查询96571班都有哪些课程，在什么时间有哪位教师授课
SELECT DISTINCT(ce.coursename), cn.coursedt, te.teachname
FROM courseplan cn
RIGHT JOIN student st
ON (cn.studno = st.studno)
RIGHT JOIN teacherinfo te
ON (cn.teachid = te.teachid)
RIGHT JOIN course ce
ON (cn.courseid = ce.courseid)
WHERE cn.studno = (
      SELECT st1.studno
      FROM student st1
      WHERE st1.batchcode = '96571' AND cn.studno = st1.studno);

–13、查询周一不上课的班级
SELECT DISTINCT(bh.batchname)
FROM batch bh
LEFT JOIN student st
ON (bh.batchcode = st.batchcode)
LEFT JOIN courseplan cn
ON (st.studno = cn.studno)
WHERE cn.coursedt != '周一';

–14、查询周四上课的教师姓名
SELECT DISTINCT(te.teachname)
FROM teacherinfo te
LEFT JOIN courseplan cn
ON (te.teachid = cn.teachid)
WHERE cn.coursedt = '周四';

–15、查询A02课程的授课教师和上课时间
SELECT distinct(te.teachname), cn.coursedt
FROM teacherinfo te
LEFT JOIN courseplan cn
ON (te.teachid = cn.teachid)
WHERE cn.courseid = 'A02';

–16、统计各个科目不及格人数占这个科目考生人数的百分比
SELECT c1.coursename, ROUND(NVL((temp1.t1_num/temp2.t2_num),0),4)*100||'%'
FROM
(SELECT s1.courseid AS t1_id, COUNT(s1.studno) AS t1_num
FROM score s1
WHERE s1.grade<60
GROUP BY s1.courseid
ORDER BY s1.courseid) temp1 
LEFT JOIN
(SELECT s1.courseid AS t2_id, COUNT(s1.studno) AS t2_num
FROM score s1
GROUP BY s1.courseid
ORDER BY s1.courseid) temp2
ON (temp1.t1_id = temp2.t2_id)
RIGHT JOIN course c1
ON (temp1.t1_id = c1.courseid);

–17、统计所有不及格人数占考生总数的百分比
SELECT ROUND((temp1.t1_num/temp2.t2_num),4)*100||'%'
FROM
(SELECT COUNT(s1.grade) AS t1_num
FROM score s1
LEFT JOIN course c1
ON (s1.courseid = c1.courseid)
WHERE s1.grade < 60) temp1,
(SELECT COUNT(s2.grade) AS t2_num
FROM score s2
LEFT JOIN course c2
ON (s2.courseid = c2.courseid)) temp2;

–18、查询单科成绩在90分以上的学生是哪个班级的，授课教师是谁？
SELECT st.studname, ba.batchname, te.teachname
FROM score sc
LEFT JOIN student st
ON (sc.studno = st.studno)
LEFT JOIN batch ba
ON (st.batchcode = ba.batchcode)
LEFT JOIN courseplan cn
ON (cn.studno = st.studno)
LEFT JOIN teacherinfo te
ON (cn.teachid = te.teachid)
WHERE sc.grade > 90;

–19、查询工业工程班的授课教师都是谁？
SELECT DISTINCT(t1.teachname)
FROM teacherinfo t1
LEFT JOIN courseplan c1
ON (t1.teachid = c1.teachid)
LEFT JOIN student s1
ON (c1.studno = s1.studno)
LEFT JOIN batch b1
ON (s1.batchcode = b1.batchcode)
WHERE b1.batchname LIKE '%工业工程%';

–20、查询1068号学生在什么时间都有课？
SELECT c1.coursedt
FROM student s1
LEFT JOIN courseplan c1
ON (s1.studno = c1.studno)
WHERE s1.studno = 1068;

–21、查询哪些同学的考试成绩都在90分以上
SELECT s1.studname
FROM student s1
LEFT JOIN score s2
ON (s1.studno = s2.studno)
LEFT JOIN courseplan c1
ON (s2.studno = c1.studno)
WHERE s2.grade > 90;

–22、查询同时代课超过两门课程的教师
SELECT tt.teachname
FROM
     (SELECT COUNT(temp1.te1_id) AS te2_id
      FROM
            (SELECT (c1.teachid) AS te1_id
             FROM teacherinfo t1
             LEFT JOIN courseplan c1
             ON (t1.teachid = c1.teachid)
             GROUP BY c1.teachid
             ORDER BY c1.teachid) temp1
      GROUP BY temp1.te1_id) temp2
LEFT JOIN teacherinfo tt
ON (temp2.te2_id = tt.teachid)
WHERE temp2.te2_id > 1;

–23、汇总各个学生考试成绩的总分，并排名次。显示学生编号，学生姓名，班级编号，总分
SELECT st.studno, st.studname, st.batchcode, temp1.t_sum
FROM student st
LEFT JOIN 
(SELECT s1.studno AS t_no, SUM(s1.grade) AS t_sum
FROM score s1
GROUP BY s1.studno
) temp1
ON (st.studno = temp1.t_no)
ORDER BY temp1.t_sum DESC,temp1.t_no ASC;

基于数据表基于第一次分享的SQL语句联系




Snum
Sname
Ssex
Sage
Sphone
Dname




S001
王明
男
19
86824571
数学系


S002
李勇
男
23
89454321
物理系


S003
刘燕
女
21

计算机系


S004
王萍
女
23

电子系


S005
王佳
男
24
13098765892
计算机系


S006
赵婷
女
20

物理系


学生表S




Snum
Cnum
Score




S001
C1
83


S001
C2
89


S001
C3
NULL


S001
C4
85


S001
C5
69


S002
C3
78


S002
C4
75


S003
C1
85


S004
C2
NULL


S005
C1
92


S005
C3
76


选课表SC




Cnum
Cname
Cfreq




C1
数据库系统原理
4


C2
C程序设计
4


C3
计算机体系结构
3


C4
自动控制原理
2


C5
数据结构
4


课程表C
下面是题目的分享：
1. 简单查询操作。
1）求数学系学生的学号和姓名。
2）求选修了课程的学生学号。
3）求选修课程号为‘C1’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
4）求选修课程号为‘C1’且成绩在80~90之间的学生学号和成绩，成绩乘以0.8输出。
5）求数学系或计算机系姓王的学生的信息。
6）求缺少了成绩的学生的学号和课程号。
7）查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重复。
2. 连接查询操作。
1）查询每个学生的情况以及他所选修的课程。
2）求学生的学号、姓名、选修的课程及成绩。
3）求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩。
解题步骤如下，为个人代码，均成功测试过，仅供参考
--(1)
SELECT Snum,Sname FROM 学生 WHERE Dname = '数学系'

--(2)
SELECT Snum FROM 学生 WHERE Snum IN (SELECT Snum FROM 选课)

--(3)
SELECT Snum,Score FROM 选课 WHERE Cnum = 'C1' ORDER BY Score DESC, Snum;

--(4)
SELECT Snum,Score*0.8 as Score FROM 选课 WHERE Cnum = 'C1' AND Score Between 80 AND 90;

--(5)
SELECT Snum,Sname,Ssex,Sage,Sphone,Dname FROM 学生 
WHERE Sname Like '王%' AND Dname IN ('数学系','计算机系')

--(6)
SELECT Snum,Cnum FROM 选课 WHERE Score IS NULL

--(7)
SELECT DISTINCT Snum FROM 选课

--(1)
SELECT 学生.Snum,学生.Sname,学生.Ssex,学生.Sage,学生.Sphone,学生.Dname,课程.Cname 
FROM 学生 LEFT JOIN 选课 on 学生.Snum = 选课.Snum LEFT JOIN 课程 ON 课程.Cnum = 选课.Cnum

--(2)
SELECT 学生.Snum,学生.Sname,课程.Cname,选课.Score
FROM 学生 join 选课 on 学生.Snum = 选课.Snum join 课程 ON 课程.Cnum = 选课.Cnum

--(3)
SELECT 学生.Snum,学生.Sname,选课.Score
FROM 学生 join 选课 on 学生.Snum = 选课.Snum 
WHERE 选课.Cnum = 'C1' AND 选课.Score>= 90