sql经典题50例（学生表篇）（上）

最新推荐文章于 2024-05-14 09:44:04 发布

志yu

最新推荐文章于 2024-05-14 09:44:04 发布

阅读量1.1w

点赞数 7

分类专栏： SQL

本文链接：https://blog.csdn.net/ichglauben/article/details/82712210

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

这周开始徒手练习sql语句对着上篇的sql学习路径跟上一遍
现在上建表sql语句

SHOW CREATE TABLE COURSE
– 1.学生表
Student(SNO,Sname,Sage,Ssex) – SNO 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
– 2.课程表
Course(CNO,Cname,TNO) – C# –课程编号,Cname 课程名称,T# 教师编号
– 3.教师表
Teacher(TNO,Tname) – T# 教师编号,Tname 教师姓名
– 4.成绩表
SC(SNO,CNO,score) – S# 学生编号,C# 课程编号,score 分数
– 创建测试数据
CREATE TABLE Student (SNO VARCHAR(10) NOT NULL,Sname VARCHAR(10) NOT NULL,Sage DATETIME,Ssex ENUM(‘男’,’女’));
INSERT INTO Student VALUES(‘1001’ , ‘赵雷’ , ‘1996-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘1002’ , ‘钱电’ , ‘1997-12-21’ , ‘男’);
INSERT INTO Student VALUES(‘1003’ , ‘孙风’ , ‘1996-05-20’ , ‘男’);
INSERT INTO Student VALUES(‘1004’ , ‘李云’ , ‘1995-08-06’ , ‘男’);
INSERT INTO Student VALUES(‘1005’ , ‘周梅’ , ‘1996-12-01’ , ‘女’);
INSERT INTO Student VALUES(‘1006’ , ‘吴兰’ , ‘1995-03-01’ , ‘女’);
INSERT INTO Student VALUES(‘1007’ , ‘郑竹’ , ‘1994-07-01’ , ‘女’);
INSERT INTO Student VALUES(‘1008’ , ‘王菊’ , ‘1996-01-20’ , ‘女’);
COMMIT
CREATE TABLE Course(CNO VARCHAR(10),Cname NVARCHAR(10),TNO VARCHAR(10))
INSERT INTO Course VALUES(‘001’ , ‘企业管理’ , ‘02’);
INSERT INTO Course VALUES(‘002’ , ‘马克思’ , ‘01’);
INSERT INTO Course VALUES(‘003’ , ‘OO&UML’ , ‘04’);
INSERT INTO Course VALUES(‘004’ , ‘数据库’ , ‘03’);
INSERT INTO Course VALUES(‘005’ , ‘数据分析’ , ‘06’);
INSERT INTO Course VALUES(‘006’ , ‘英语’ , ‘05’);
INSERT INTO Course VALUES(‘007’ , ‘大物’ , ‘03’);
INSERT INTO Course VALUES(‘008’ , ‘嵌入式’ , ‘05’);
SELECT * FROM Course
CREATE TABLE Teacher(TNO VARCHAR(10),Tname VARCHAR(10))
INSERT INTO Teacher VALUES(‘01’ , ‘叶平’);
INSERT INTO Teacher VALUES(‘02’ , ‘李正’);
INSERT INTO Teacher VALUES(‘03’ , ‘马冬梅’);
INSERT INTO Teacher VALUES(‘04’ , ‘李想’);
INSERT INTO Teacher VALUES(‘05’ , ‘李玲’);
INSERT INTO Teacher VALUES(‘06’ , ‘尚加号’);
CREATE TABLE SC(SNO VARCHAR(10),CNO VARCHAR(10),score DECIMAL(5,1))
INSERT INTO SC VALUES(‘1001’ , ‘001’ , 80);
INSERT INTO SC VALUES(‘1001’ , ‘002’ , 90);
INSERT INTO SC VALUES(‘1001’ , ‘003’ , 79);
INSERT INTO SC VALUES(‘1001’ , ‘004’ , 69);
INSERT INTO SC VALUES(‘1001’ , ‘005’ , 89);
INSERT INTO SC VALUES(‘1001’ , ‘006’ , 91);
INSERT INTO SC VALUES(‘1002’ , ‘001’ , 73);
INSERT INTO SC VALUES(‘1002’ , ‘002’ , 68);
INSERT INTO SC VALUES(‘1002’ , ‘003’ , 88);
INSERT INTO SC VALUES(‘1002’ , ‘004’ , 69);
INSERT INTO SC VALUES(‘1003’ , ‘001’ , 85);
INSERT INTO SC VALUES(‘1003’ , ‘002’ , 84);
INSERT INTO SC VALUES(‘1003’ , ‘003’ , 95);
INSERT INTO SC VALUES(‘1003’ , ‘005’ , 65);
INSERT INTO SC VALUES(‘1004’ , ‘001’ , 59);
INSERT INTO SC VALUES(‘1004’ , ‘002’ , 35);
INSERT INTO SC VALUES(‘1004’ , ‘003’ , 24);
INSERT INTO SC VALUES(‘1004’ , ‘006’ , 59);
INSERT INTO SC VALUES(‘1005’ , ‘001’ , 76);
INSERT INTO SC VALUES(‘1005’ , ‘002’ , 87);
INSERT INTO SC VALUES(‘1005’ , ‘005’ , 69);
INSERT INTO SC VALUES(‘1006’ , ‘001’ , 31);
INSERT INTO SC VALUES(‘1006’ , ‘004’ , 72);
INSERT INTO SC VALUES(‘1006’ , ‘003’ , 34);
INSERT INTO SC VALUES(‘1007’ , ‘004’ , 93);
INSERT INTO SC VALUES(‘1007’ , ‘002’ , 89);
INSERT INTO SC VALUES(‘1007’ , ‘003’ , 98);
INSERT INTO SC VALUES(‘1007’ , ‘001’ , 82);
commit

1.查询‘001’课程比‘002’课程成绩高的所有学生的学号

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 number
SELECT SNO,COUNT(*) AS class_num,SUM(score) AS total_sum FROM SC GROUP BY SNO
-- grade 
SELECT DISTINCT
  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
    GROUP BY SNO) AS B
    ON B.SNO = Student.SNO

4.查询姓李老师的个数

SELECT * FROM Teacher
SELECT COUNT(*) FROM Teacher WHERE Tname LIKE '李%'

5.查询没学过叶平老师课的同学的学号、姓名

-- 要筛选目标 --学过老师的课的学号 姓名
-- 1001 02 03 04 05 07
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 = '叶平'
-- 逻辑不对  找对应的
SELECT  * FROM Student JOIN SC ON SC.SNO = Student.`SNO` WHERE SC.`CNO` != 002

SELECT DISTINCT
  Student.SNO,
  Sname
FROM
  Student
  JOIN SC
    ON SC.SNO = Student.`SNO`
WHERE SC.SNO NOT IN
  (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
1001    002 90.0
1002    002 68.0
1003    002 84.0
1004    002 35.0
1005    002 87.0
1007    002 89.0
SELECT * FROM SC WHERE CNO='001' 
SNO     CNO score
1001    001 80.0
1002    001 73.0
1003    001 85.0
1004    001 59.0
1005    001 76.0
1006    001 31.0
1007    001 82.0
SELECT
  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 GROUP BY ST.SNO HAVING COUNT(SC.`CNO`) <
(SELECT COUNT(*) FROM Course)

11.查询至少有一门与学号为‘1001’同学所学相同的同学的学号和姓名

SELECT DISTINCT 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”表中叶平老师教的课的成绩都更改为此课程的平均成绩

SELECT AVG(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' ) GROUP BY SNO HAVING COUNT(*) IN (SELECT COUNT(*) FROM SC WHERE SNO = '1002')

14.删除学习叶平老师可的SC表(做错了)

-- ERROR
SELECT * FROM SC WHERE CNO = '002'
DELETE  FROM 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='叶平'
UNKNOWN TABLE 'SC' IN MULTI DELETE

-- 先建表  后删表中数据
CREATE TABLE TMP AS SELECT DISTINCT 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
DELETE FROM 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
GROUP BY SC.CNO ORDER BY AVG(SCORE) DESC

16.向SC表插入一些记录符合以下要求:没有上过编号’003’课程的同学学号 2号课程的平均成绩

-- note: insert 和 select的嵌套使用
-- 上过003号课程的同学学号 -- 搞定没上过的同学学号
SELECT DISTINCT SNO FROM SC WHERE SNO NOT IN ( 
SELECT DISTINCT SNO FROM SC WHERE CNO='003')
INSERT SC
SELECT
  SNO,
  '002',
  (SELECT
    AVG(SCORE)
  FROM
    SC
  WHERE CNO = '002'
  GROUP BY CNO)
FROM
  Student
WHERE SNO NOT IN
  (SELECT DISTINCT
    SNO
  FROM
    SC
  WHERE CNO = '003')

17.按平均成绩从高到低显示所有学生的’数据库‘，”企业管理”,”英语”三门课程成绩按如下形式显示:
学生ID，数据库，英语，有效课程数，有效平均分

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
GROUP BY T.SNO
ORDER BY AVG(T.SCORE)
学生ID    数据库 企业管理    英语    有效课程数     平均成绩 
1006    72.0    31.0        \N      3       45.66667
1004    \N      59.0        59.0    3       47.33333
1005    \N      76.0        \N      2       72.50000
1002    69.0    73.0        \N      3       76.66667
1001    69.0    80.0        91.0    5       81.60000
1003    \N      85.0        \N      3       81.66667
1007    93.0    82.0        \N      3       91.00000

18.查询各科成绩最高分和最低分如下显示:课程ID,最高分,最低分

 -- 先查最高分和最低分表 再连接起来即可
SELECT CNO,MAX(SCORE) FROM SC GROUP BY CNO
SELECT CNO,MIN(SCORE) FROM SC GROUP BY CNO

SELECT
  SC.CNO AS   '课程ID', 
  MAX_SC AS '最高分',
  MIN_SC AS  '最低分'
FROM
  SC
  JOIN
    (SELECT
      CNO,
      MAX(SCORE) AS MAX_SC
    FROM
      SC
    GROUP BY CNO) AS L
    ON L.CNO = SC.CNO
  JOIN
    (SELECT
      CNO,
      MIN(SCORE) AS MIN_SC
    FROM
      SC
    GROUP BY CNO) AS R
    ON R.CNO = SC.`CNO`
GROUP BY 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搜索函数 
CASE WHEN sex = '1' THEN '男' 
         WHEN sex = '2' THEN '女' 
ELSE '其他' END 
-- -------------------------------
SELECT COUNT(*),CNO FROM SC GROUP BY CNO
SELECT COALESCE(AVG(SCORE),0) FROM SC GROUP BY CNO ORDER BY AVG(SCORE)
SELECT COUNT(*) FROM SC GROUP BY CNO
-- --------------------------------
SELECT
  CNO AS '科目编号',
  AVG(SCORE) AS '平均分',
  SUM(

    CASE

      WHEN IFNULL(SCORE, 0) >= 60
      THEN 1
      ELSE 0
    END
  ) / COUNT(*) AS '科目及格率'
FROM
  SC
GROUP BY CNO
ORDER BY SUM(

    CASE

      WHEN IFNULL(SCORE, 0) >= 60
      THEN 1
      ELSE 0
    END
  ) / COUNT(*) DESC-- 
科目编号    平均分 科目及格率
005   74.33333   1.0000
004   75.75000   1.0000
002   75.50000   0.8333
001   69.42857   0.7143
003   69.66667   0.6667
006   75.00000   0.5000
SELECT * FROM Course
CNO Cname           TNO
001 企业管理    02
002 马克思         01
003 OO&UML      04
004 数据库         03
005 数据分析    06
006 英语          05

20.查询如下课程平均成绩和及格率的百分数

SELECT 
SUM(CASE WHEN SC.CNO = '001' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='001' THEN 1 ELSE 0 END) AS '企业管理平均成绩',
SUM(CASE WHEN SC.CNO = '001' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='001' THEN 1 ELSE 0 END)||'%' AS '企业管理及格率',
SUM(CASE WHEN SC.CNO = '002' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='002' THEN 1 ELSE 0 END) AS '马克思平均成绩',
SUM(CASE WHEN SC.CNO = '002' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='002' THEN 1 ELSE 0 END)||'%' AS '马克思及格率',
SUM(CASE WHEN SC.CNO = '003' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='003' THEN 1 ELSE 0 END) AS 'OO&UML平均成绩',
SUM(CASE WHEN SC.CNO = '003' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='003' THEN 1 ELSE 0 END)||'%' AS 'OO&UML及格率',
SUM(CASE WHEN SC.CNO = '004' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='004' THEN 1 ELSE 0 END) AS '数据库平均成绩',
SUM(CASE WHEN SC.CNO = '004' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='004' THEN 1 ELSE 0 END)||'%' AS '数据库及格率',
SUM(CASE WHEN SC.CNO = '005' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='005' THEN 1 ELSE 0 END) AS '数据分析平均成绩',
SUM(CASE WHEN SC.CNO = '005' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='005' THEN 1 ELSE 0 END)||'%' AS '数据分析及格率',
SUM(CASE WHEN SC.CNO = '006' THEN SC.SCORE ELSE 0 END)/SUM(CASE WHEN SC.CNO='006' THEN 1 ELSE 0 END) AS '英语平均成绩',
SUM(CASE WHEN SC.CNO = '006' AND IFNULL(SC.SCORE,0)>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN SC.CNO='006' THEN 1 ELSE 0 END)||'%' AS '英语及格率'
FROM SC

企业管理平均成绩    企业管理及格率 马克思平均成绩 马克思及格率  OO&UML平均成绩  OO&UML及格率   数据库平均成绩 数据库及格率  数据分析平均成绩    数据分析及格率 英语平均成绩  英语及格率
69.42857        0.7143      75.50000    0.8750      69.66667      0.6667    75.75000    1.0000      74.33333        1.0000      75.00000    0.5000

21.查询不同老师所教的不同课程平均分从高到低显示

SELECT MAX(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
    GROUP BY SC.`CNO`
     ORDER BY AVG(SC.`score`) DESC

22.查询如下课程第3名至第6名的学生成绩单:企业管理(001) 马克思(002) OO&UML(003) 数据库(004) 数据分析(005) 英语(006)

-- 学生ID 学生姓名 企业管理 马克思 OO&UML 数据库 数据分析 英语 平均成绩
SELECT * FROM SC WHERE CNO = '001' GROUP BY SNO ORDER BY SCORE DESC 
SELECT * FROM SC WHERE CNO = '002' GROUP BY SNO ORDER BY SCORE DESC 
SELECT * FROM SC WHERE CNO = '003' GROUP BY SNO ORDER BY SCORE DESC 
SELECT * FROM SC WHERE CNO = '004' GROUP BY SNO ORDER BY SCORE DESC 
SELECT * FROM SC WHERE CNO = '005' GROUP BY SNO ORDER BY SCORE DESC 
SELECT * FROM SC WHERE CNO = '006' GROUP BY SNO ORDER BY SCORE DESC 

SELECT
  SC.SNO AS '学生ID',
  ST.`Sname` AS '学生姓名',
  A_001.SCORE AS '企业管理',
  A_002.SCORE AS '马克思',
  A_003.SCORE AS 'OO&UML',
  A_004.SCORE AS '数据库',
  A_005.SCORE AS '数据分析',
  A_006.SCORE AS '英语',
  AVG(SC.SCORE) AS '平均分'
FROM
  SC
  JOIN Student AS ST
    ON ST.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '001'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_001
    ON A_001.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '002'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_002
    ON A_002.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '003'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_003
    ON A_003.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '004'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_004
    ON A_004.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '005'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_005
    ON A_005.SNO = SC.SNO
  LEFT JOIN
    (SELECT
      *
    FROM
      SC
    WHERE CNO = '006'
    GROUP BY SNO
    ORDER BY SCORE DESC) AS A_006
    ON A_006.SNO = SC.SNO
GROUP BY SC.SNO

23.统计列印各科成绩，各分数段人数：课程ID,课程名称[100-85][85-70][70-60][<60]

SELECT CNO,COUNT(*) FROM SC WHERE SCORE >= 85 GROUP BY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 85  AND SCORE >= 70 GROUP BY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 70  AND SCORE >= 60 GROUP BY CNO
SELECT CNO,COUNT(*) FROM SC WHERE SCORE < 60 GROUP BY 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 LEFT JOIN (SELECT CNO,COUNT(*) AS NUM FROM SC WHERE SCORE >= 85 GROUP BY CNO
) AS SC85 ON SC.CNO = SC85.CNO LEFT JOIN (SELECT CNO,COUNT(*)AS NUM FROM SC WHERE SCORE < 85  AND SCORE >= 70 GROUP BY CNO
) AS SC70 ON SC.CNO = SC70.CNO LEFT JOIN (SELECT CNO,COUNT(*)AS NUM  FROM SC WHERE SCORE < 70  AND SCORE >= 60 GROUP BY CNO
) AS SC60 ON SC.CNO = SC60.CNO LEFT JOIN (SELECT CNO,COUNT(*)AS NUM FROM SC WHERE SCORE < 60 GROUP BY CNO
) AS SCL60 ON SC.CNO = SCL60.CNO  GROUP BY SC.CNO

CNO Cname   [100-85]    [85-70] [70-60] [<60]
001 企业管理    1   4   \N  2
002 马克思     3   1   1   1
003 OO&UML      3   1   \N  2
004 数据库     1   1   2   \N
005 数据分析    1   \N  2   \N
006 英语      1   \N  \N  1

24.查询学生平均成绩及其名次

-- 排名问题 设置函数得 
-- 解决问题:
SELECT (@rownum:=@rownum + 1) AS rank,A.AVG_SC FROM 
(
SELECT
  AVG(SCORE) AS AVG_SC
FROM
  SC
GROUP BY SNO
ORDER BY AVG_SC DESC
) AS A ,(SELECT @rownum:=0) AS r

25.查询各科成绩前三名的记录:(不考虑成绩并列情况)

--  注意格式 使用UNION
SELECT * FROM SC WHERE CNO = '001'
UNION 
SELECT * 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' ORDER BY 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' ORDER BY SC.SCORE DESC LIMIT 0,3)
UNION ALL
(SELECT CO.Cname,SCORE FROM SC JOIN Course AS CO ON CO.CNO = SC.CNO WHERE SC.CNO = '003' ORDER BY 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' ORDER BY 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' ORDER BY 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' ORDER BY SCORE DESC LIMIT 0,3)

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

SELECT COUNT(*) AS '选修人数' FROM SC GROUP BY 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 GROUP BY ST.SNO HAVING COUNT(*) = (SELECT COUNT(*) FROM SC GROUP BY 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 WHERE YEAR(SAGE) = 1996
SELECT * FROM Student

32.查询每门课的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT CNO AS '课程编号',AVG(SCORE) AS '平均成绩' FROM SC GROUP BY CNO ORDER BY AVG(SCORE),CNO DESC

志yu

关注

7
点赞
踩
83

收藏

觉得还不错? 一键收藏
1
评论
sql经典题50例（学生表篇）（上）

这周开始徒手练习sql语句对着上篇的sql学习路径跟上一遍现在上建表sql语句SHOW CREATE TABLE COURSE– 1.学生表Student(SNO,Sname,Sage,Ssex) – SNO 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别– 2.课程表 Course(CNO,Cname,TNO)...
复制链接

扫一扫