SQL 查询语句练习

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;