当前位置: 开发笔记 > 编程语言 > 正文

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

作者：zhangmy0815522 | 来源：互联网 | 2023-09-15 10:51

这周开始徒手练习sql语句对着上篇的sql学习路径跟上一遍现在上建表sql语句SHOWCREATETABLECOURSE–1.学生表Student(SNO,Sname,Sage,S

这周开始徒手练习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

推荐阅读

java
VScode格式化文档换行或不换行的设置方法

本文介绍了在VScode中设置格式化文档换行或不换行的方法，包括使用插件和修改settings.json文件的内容。详细步骤为：找到settings.json文件，将其中的代码替换为指定的代码。 ... [详细]

蜡笔小新 2023-12-14 17:15:38
go
游标的使用笔记

本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]

蜡笔小新 2023-12-13 15:24:30
sum
SpringJdbcTemplate的使用详解

本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]

蜡笔小新 2023-12-13 14:27:11
copy
Java中vector的使用详解

本文详细介绍了Java中vector的使用方法和相关知识，包括vector类的功能、构造方法和使用注意事项。通过使用vector类，可以方便地实现动态数组的功能，并且可以随意插入不同类型的对象，进行查找、插入和删除操作。这篇文章对于需要频繁进行查找、插入和删除操作的情况下，使用vector类是一个很好的选择。 ... [详细]

蜡笔小新 2023-12-13 14:14:39
sum
Kotlin中扩展函数的惯用用法及其合理性

本文讨论了Kotlin中扩展函数的一些惯用用法以及其合理性。作者认为在某些情况下，定义扩展函数没有意义，但官方的编码约定支持这种方式。文章还介绍了在类之外定义扩展函数的具体用法，并讨论了避免使用扩展函数的边缘情况。作者提出了对于扩展函数的合理性的质疑，并给出了自己的反驳。最后，文章强调了在编写Kotlin代码时可以自由地使用扩展函数的重要性。 ... [详细]

蜡笔小新 2023-12-12 19:17:21
io
MyBatis多表查询与动态SQL使用

本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]

蜡笔小新 2023-12-12 17:12:51
sum
Which is more efficient: char str[] or char *str?

This article discusses the efficiency of using char str[] and char *str and whether there is any reason to prefer one over the other. It explains the difference between the two and provides an example to illustrate their usage. ... [详细]

蜡笔小新 2023-12-12 10:13:35
copy
文件压缩解压的哈夫曼树实现

本文介绍了使用哈夫曼树实现文件压缩和解压的方法。首先对数据结构课程设计中的代码进行了分析，包括使用时间调用、常量定义和统计文件中各个字符时相关的结构体。然后讨论了哈夫曼树的实现原理和算法。最后介绍了文件压缩和解压的具体步骤，包括字符统计、构建哈夫曼树、生成编码表、编码和解码过程。通过实例演示了文件压缩和解压的效果。本文的内容对于理解哈夫曼树的实现原理和应用具有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-11 14:13:46
sum
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30
copy
Oracle 11g物理Active Data Guard实时查询（Realtime query）特性

在Oracle11g以前版本中的的DataGuard物理备用数据库，可以以只读的方式打开数据库，但此时MediaRecovery利用日志进行数据同步的过 ... [详细]

蜡笔小新 2023-12-11 15:49:10
command
使用集算器将日志文件结构化，轻松使用SQL查询

本文介绍了一种轻巧方便的工具——集算器，通过使用集算器可以将文本日志变成结构化数据，然后可以使用SQL式查询。集算器利用集算语言的优点，将日志内容结构化为数据表结构，SPL支持直接对结构化的文件进行SQL查询，不再需要安装配置第三方数据库软件。本文还详细介绍了具体的实施过程。 ... [详细]

蜡笔小新 2023-12-11 13:27:46
sum
c#语言-多线程中的锁系统(一)

介绍平常在多线程开发中，总避免不了线程同步。本篇就对net多线程中的锁系统做个简单描述。目录一：lock、Monitor1：基础 ... [详细]

蜡笔小新 2023-10-16 17:56:14
php
MySQL修改表结构操作命令总结【MySQL】

数据库|mysql教程MySQL,修改表结构命令数据库-mysql教程表的结构如下:错误页面源码,ubuntu电脑自动休眠,爬虫造景视频,rapapiphp,廊坊seo开发lzwm ... [详细]

蜡笔小新 2023-10-16 17:22:44
sum
Abp+MongoDb改造默认的审计日志存储位置

一、背景在实际项目的开发当中，使用AbpZero自带的审计日志功能写入效率比较低。其次审计日志数据量中后期十分庞大，不适合与业务数据存放在一起。所以我们可以重新实现A ... [详细]

蜡笔小新 2023-10-16 15:09:22
io
Azure SQL Database (25) Azure SQL Database创建只读用户

　　《WindowsAzurePlatform系列文章目录》　　本文将介绍如何在AzureSQLDatabase创建只读用户。　　请先按照笔者之前的文章：AzureSQLDatabas ... [详细]

蜡笔小新 2023-10-16 14:25:01

zhangmy0815522

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章