SELECT * FROM (SELECT SCORE,SC.SNO AS SC_SNO FROM SC JOIN Student AS ST ON ST.SNO = SC.`SNO` WHERE CNO = ‘001’) AS A,
(SELECT SCORE,SC.SNO AS SC_SNO FROM SC JOIN Student AS ST ON ST.SNO = SC.`SNO` WHERE CNO = ‘002’) AS B WHERE A.SC_SNO=B.SC_SNO AND A.SCORE > B.SCORE
2.查询平均成绩大于60分同学的学号和平均成绩
SELECT SNO,AVG(SCORE) AS AVG_SC FROM SC GROUP BY SNO HAVING AVG_SC>60
3.查询所有同学的学号 姓名 选课数 总成绩
SELECT * FROM SC
-- class numberSELECT SNO,COUNT(*) AS class_num,SUM(score) AS total_sum FROM SC GROUPBY SNO
-- grade
SELECTDISTINCT
SC.SNO,
Sname,
B.class_num,
B.total_sum
FROM
Student
JOIN SC
ON SC.SNO = Student.SNO
JOIN
(SELECT
SNO,
COUNT(*) AS class_num,
SUM(score) AS total_sum
FROM
SC
GROUPBY SNO) AS B
ON B.SNO = Student.SNO
4.查询姓 李 老师的个数
SELECT * FROM Teacher
SELECTCOUNT(*) FROM Teacher WHERE Tname LIKE'李%'
5.查询没学过 叶平老师课的同学的学号、姓名
-- 要筛选目标 --学过老师的课的学号 姓名-- 1001 02 03 04 05 07SELECT SC.SNO FROM Course AS CO JOIN Teacher AS TA ON CO.TNO = TA.TNO JOIN SC ON SC.CNO = CO.CNO WHERE Tname = '叶平'
-- 逻辑不对 找对应的
SELECT * FROM Student JOIN SC ON SC.SNO = Student.`SNO`WHERE SC.`CNO` != 002SELECTDISTINCT
Student.SNO,
Sname
FROM
Student
JOIN SC
ON SC.SNO = Student.`SNO`WHERE SC.SNO NOTIN
(SELECT
SC.SNO
FROM
Course AS CO
JOIN Teacher AS TA
ON CO.TNO = TA.TNO
JOIN SC
ON SC.CNO = CO.CNO
WHERE Tname = '叶平')
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECT
*
FROM
Student
JOIN SC
ON SC.SNO = Student.SNO
WHERE CNO = '001'AND SC.SNO IN
(SELECT
SC.SNO
FROM
SC
WHERE CNO = '002')
7.查询学过叶平老师所教的所有课的同学的学号和姓名
SELECT TNO FROM Teacher WHERE Tname = '叶平'SELECT
SC.`SNO`,
ST.Sname
FROM
SC
JOIN Course AS CO
ON CO.CNO = SC.CNO
JOIN Teacher AS TA
ON TA.TNO = CO.TNO
JOIN Student AS ST
ON ST.SNO = SC.SNO
WHERE TA.TNO =
(SELECT
TNO
FROM
Teacher
WHERE Tname = '叶平')
8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的姓名、学号
SELECT * FROM SC WHERE CNO='002'
SNO CNO score
100100290.0100200268.0100300284.0100400235.0100500287.0100700289.0SELECT * FROM SC WHERE CNO='001'
SNO CNO score
100100180.0100200173.0100300185.0100400159.0100500176.0100600131.0100700182.0SELECT
ST.Sname,
SC.SNO
FROM
SC
JOIN Student AS ST
ON ST.SNO = SC.SNO
WHERE CNO = '002'AND SCORE < ANY
(SELECT
SCORE
FROM
SC
WHERE CNO = '001')
9.查询所有课程成绩小于60分的同学的学号、姓名
SELECT DISTINCT ST.SNO,ST.SNAME FROM SC JOIN Student AS ST ON ST.SNO = SC.SNO WHERE SCORE < 60
10.查询没有学全所有课的同学的学号、姓名
SELECT ST.SNO,ST.SNAME FROM SC JOIN Student AS ST ON ST.SNO = SC.SNO GROUPBY ST.SNO HAVINGCOUNT(SC.`CNO`) <
(SELECTCOUNT(*) FROM Course)
11.查询至少有一门与学号为‘1001’同学所学相同的同学的学号和姓名
SELECTDISTINCT ST.SNO,ST.SNAME FROM Student AS ST JOIN SC ON SC.SNO = ST.SNO WHERE CNO IN (
SELECT CNO FROM SC WHERE SC.`SNO` = '1001' )
12.把“SCORE”表中叶平老师教的课的成绩都更改为此课程的平均成绩
SELECTAVG(SCORE) FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO JOIN Teacher AS TA ON TA.TNO = CO.TNO
WHERE TA.TNAME = '叶平'
13.查询和“1002”同学学习的课程完全相同的其他同学学号和姓名
-- 分解成 数目和 CNO都相同
SELECT ST.SNAME,ST.SNO FROM SC JOIN Student AS ST ON SC.SNO = ST.SNO WHERE SC.CNO IN (
SELECT CNO FROM SC WHERE SNO = '1002' ) GROUPBY SNO HAVING COUNT(*) IN (SELECT COUNT(*)FROM SC WHERE SNO = '1002')
14.删除学习叶平老师可的SC表(做错了)
-- ERRORSELECT * FROM SC WHERE CNO = '002'DELETEFROM SC JOIN Course AS CO ON CO.CNO = SC.CNO JOIN Teacher AS TA ON TA.TNO = CO.TNO
WHERE TNAME = '叶平'SELECT * FROM SC,Course,Teacher WHERE Course.`CNO`=SC.`CNO`AND Course.`TNO` = Teacher.`TNO`AND Tname='叶平'DELETE SC FROM Course,Teacher WHERE Course.`CNO`=SC.`CNO`AND Course.`TNO` = Teacher.`TNO`AND Tname='叶平'UNKNOWNTABLE'SC'IN MULTI DELETE
-- 先建表 后删表中数据CREATETABLE TMP ASSELECTDISTINCT SC.CNO FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO JOIN Teacher AS TA ON TA.TNO = CO.TNO
WHERE TNAME = '叶平'
-- SELECT * FROM TMP
DELETEFROM SC WHERE CNO = (SELECT CNO FROM TMP)
15.查询不同老师所教不同课程平均分从高到低显示
SELECT SC.`CNO`AS'课程班级号',CO.CNAME AS'课程名称',AVG(SC.SCORE)AS'均分',TA.TNAME AS'教师姓名'FROM SC JOIN Course AS CO ON CO.CNO = SC.`CNO`JOIN Teacher AS TA ON TA.TNO = CO.TNO
GROUPBY SC.CNO ORDERBYAVG(SCORE) DESC
16.向SC表插入一些记录 符合以下要求:没有上过编号’003’课程的同学学号 2号课程的平均成绩
-- note: insert 和 select的嵌套使用-- 上过003号课程的同学学号 -- 搞定没上过的同学学号SELECTDISTINCT SNO FROM SC WHERE SNO NOTIN (
SELECTDISTINCT SNO FROM SC WHERE CNO='003')
INSERT SC
SELECT
SNO,
'002',
(SELECTAVG(SCORE)
FROM
SC
WHERE CNO = '002'GROUPBY CNO)
FROM
Student
WHERE SNO NOTIN
(SELECTDISTINCT
SNO
FROM
SC
WHERE CNO = '003')
SELECT SNO,CNO,SCORE FROM SC WHERE CNO = '004'SELECT SNO,CNO,SCORE FROM SC WHERE CNO = '001'SELECT SNO,CNO,SCORE FROM SC WHERE CNO = '006'
-- 有难度题 总结在 查单个的时候 记得要和外面的表进行关联 否则无法出来结果
SELECT
SNO AS'学生ID',
(SELECT
SCORE
FROM
SC
WHERE CNO = '004'AND SC.SNO = T.SNO) AS'数据库',
(SELECT
SCORE
FROM
SC
WHERE CNO = '001'AND SC.SNO = T.SNO) AS' 企业管理 ',
(SELECT
SCORE
FROM
SC
WHERE CNO = '006'AND SC.SNO = T.SNO) AS'英语',
COUNT(*) AS' 有效课程数 ',
AVG(T.SCORE) AS' 平均成绩 'FROM
SC AS T
GROUPBY T.SNO
ORDERBYAVG(T.SCORE)
学生ID 数据库 企业管理 英语 有效课程数 平均成绩
100672.031.0 \N 345.666671004 \N 59.059.0347.333331005 \N 76.0 \N 272.50000100269.073.0 \N 376.66667100169.080.091.0581.600001003 \N 85.0 \N 381.66667100793.082.0 \N 391.00000
18.查询各科成绩最高分和最低分 如下显示:课程ID,最高分,最低分
-- 先查最高分和最低分表 再连接起来即可SELECT CNO,MAX(SCORE) FROM SC GROUPBY CNO
SELECT CNO,MIN(SCORE) FROM SC GROUPBY CNO
SELECT
SC.CNO AS'课程ID',
MAX_SC AS'最高分',
MIN_SC AS'最低分'FROM
SC
JOIN
(SELECT
CNO,
MAX(SCORE) AS MAX_SC
FROM
SC
GROUPBY CNO) AS L
ON L.CNO = SC.CNO
JOIN
(SELECT
CNO,
MIN(SCORE) AS MIN_SC
FROM
SC
GROUPBY CNO) AS R
ON R.CNO = SC.`CNO`GROUPBY SC.CNO
19.按各科平均成绩从低到高和及格率的百分数从高到低排序
-- NVL(), IFNULL() 和 COALESCE() 函数也可以达到相同的结果。将NULL显示为0-- case when then else end用法举例-- 简单Case函数 when-- 及格率查询 常用函数 case when
有难度题目
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- Case搜索函数
CASEWHEN sex = '1'THEN'男'WHEN sex = '2'THEN'女'ELSE'其他'END
-- -------------------------------
SELECTCOUNT(*),CNO FROM SC GROUPBY CNO
SELECT COALESCE(AVG(SCORE),0) FROM SC GROUPBY CNO ORDERBYAVG(SCORE)
SELECTCOUNT(*) FROM SC GROUPBY CNO
-- --------------------------------
SELECT
CNO AS'科目编号',
AVG(SCORE) AS'平均分',
SUM(
CASEWHEN IFNULL(SCORE, 0) >= 60THEN1ELSE0END
) / COUNT(*) AS'科目及格率'FROM
SC
GROUPBY CNO
ORDERBYSUM(
CASEWHEN IFNULL(SCORE, 0) >= 60THEN1ELSE0END
) / COUNT(*) DESC--
科目编号 平均分 科目及格率
00574.333331.000000475.750001.000000275.500000.833300169.428570.714300369.666670.666700675.000000.5000SELECT * FROM Course
CNO Cname TNO
001 企业管理 02002 马克思 01003 OO&UML 04004 数据库 03005 数据分析 06006 英语 05
SELECTMAX(TA.TNO) AS'教师ID',MAX(TA.TNAME) AS'教师姓名',SC.CNO AS'课程ID',MAX(CO.CNAME) AS'课程名称',IFnullAVG(SC.`score`) AS'平均成绩'FROM
SC
JOIN Course AS CO
ON CO.CNO = SC.CNO
JOIN Teacher AS TA
ON TA.TNO = CO.TNO
GROUPBY SC.`CNO`ORDERBYAVG(SC.`score`) DESC
SELECT CNO,COUNT(*) FROM SC WHERE SCORE >= 85GROUPBY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 85AND SCORE >= 70GROUPBY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 70AND SCORE >= 60GROUPBY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 60GROUPBY CNO
SELECT SC.`CNO`,CO.`Cname`,SC85.NUM AS'[100-85]',SC70.NUM AS'[85-70]',SC60.NUM AS'[70-60]',SCL60.NUM AS'[<60]'FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO LEFTJOIN (SELECT CNO,COUNT(*) AS NUM FROM SC WHERE SCORE >= 85GROUPBY CNO
) AS SC85 ON SC.CNO = SC85.CNO LEFTJOIN (SELECT CNO,COUNT(*)AS NUM FROM SC WHERE SCORE < 85AND SCORE >= 70GROUPBY CNO
) AS SC70 ON SC.CNO = SC70.CNO LEFTJOIN (SELECT CNO,COUNT(*)AS NUM FROM SC WHERE SCORE < 70AND SCORE >= 60GROUPBY CNO
) AS SC60 ON SC.CNO = SC60.CNO LEFTJOIN (SELECT CNO,COUNT(*)AS NUM FROM SC WHERE SCORE < 60GROUPBY CNO
) AS SCL60 ON SC.CNO = SCL60.CNO GROUPBY SC.CNO
CNO Cname [100-85] [85-70] [70-60] [<60]
001 企业管理 14 \N 2002 马克思 3111003 OO&UML 31 \N 2004 数据库 112 \N
005 数据分析 1 \N 2 \N
006 英语 1 \N \N 1
24.查询学生平均成绩及其名次
-- 排名问题 设置函数得 -- 解决问题:SELECT (@rownum:=@rownum + 1) AS rank,A.AVG_SC FROM
(
SELECTAVG(SCORE) AS AVG_SC
FROM
SC
GROUPBY SNO
ORDERBY AVG_SC DESC
) AS A ,(SELECT @rownum:=0) AS r
25.查询各科成绩前三名的记录:(不考虑成绩并列情况)
-- 注意格式 使用UNIONSELECT * FROM SC WHERE CNO = '001'UNIONSELECT * FROM SC WHERE CNO = '002'
(SELECT CO.Cname,SC.SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '001'ORDERBY SC.SCORE DESC LIMIT 0,3)
UNION
(SELECT CO.Cname,SC.SCORE FROM SC AS SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '002'ORDERBY SC.SCORE DESC LIMIT 0,3)
UNIONALL
(SELECT CO.Cname,SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '003'ORDERBY SCORE DESC LIMIT 0,3)
UNION
(SELECT CO.Cname,SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '004'ORDERBY SCORE DESC LIMIT 0,3)
UNION
(SELECT CO.Cname,SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '005'ORDERBY SCORE DESC LIMIT 0,3)
UNION
(SELECT CO.Cname,SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '006'ORDERBY SCORE DESC LIMIT 0,3)
26.查询每门课程被选修的学生数
SELECTCOUNT(*) AS'选修人数'FROM SC GROUPBY CNO
27.查询出只选修一门课程的全部学生的学号和姓名
SELECT SNO,COUNT(*) FROM SC GROUP BY SNO HAVING COUNT(*) = 1
--
SELECT ST.SNO,ST.SNAME FROM SC JOIN Student AS ST ON ST.SNO = SC.SNO GROUPBY ST.SNO HAVING COUNT(*) = (SELECT COUNT(*)FROM SC GROUPBY SNO HAVING COUNT(*) = 1
)
28.查询男生、女生人数
SELECT COUNT(*) FROM Student AS ST WHERE SSEX = '男'
SELECT COUNT(*)FROM Student AS ST WHERE SSEX = '女'
29.查询姓李的学生名单
SELECT * FROM Student AS ST WHERE Sname LIKE'李%'
30.查询同名同姓学生名单,并统计同名人数
SELECT Sname,COUNT(*) FROM Student GROUP BY SNO HAVING COUNT(*) > 1
31.1996年出生的学生名单(注:Student表中Sage列的类型是datetime)
SELECT Sname,YEAR(SAGE) AS'出生名单'FROM Student WHEREYEAR(SAGE) = 1996SELECT * FROM Student