数据库 SQL 经典例题 (上篇)

用客户端运行SQL 创建表 (如果出错可能是字段类型的错误)

我用的是psql 都一样.

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS "public"."course";
CREATE TABLE "public"."course" (
  "cid" varchar(10) COLLATE "pg_catalog"."default",
  "cname" varchar(10) COLLATE "pg_catalog"."default",
  "tid" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO "public"."course" VALUES ('01', '语文', '02');
INSERT INTO "public"."course" VALUES ('02', '数学', '01');
INSERT INTO "public"."course" VALUES ('03', '英语', '03');

DROP TABLE IF EXISTS "public"."sc";
CREATE TABLE "public"."sc" (
  "sid" varchar(10) COLLATE "pg_catalog"."default",
  "cid" varchar(10) COLLATE "pg_catalog"."default",
  "score" numeric(18,1)
)
;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO "public"."sc" VALUES ('01', '03', 99.0);
INSERT INTO "public"."sc" VALUES ('02', '01', 70.0);
INSERT INTO "public"."sc" VALUES ('02', '03', 80.0);
INSERT INTO "public"."sc" VALUES ('03', '01', 80.0);
INSERT INTO "public"."sc" VALUES ('03', '03', 80.0);
INSERT INTO "public"."sc" VALUES ('04', '01', 50.0);
INSERT INTO "public"."sc" VALUES ('05', '01', 76.0);
INSERT INTO "public"."sc" VALUES ('06', '01', 31.0);
INSERT INTO "public"."sc" VALUES ('06', '03', 34.0);
INSERT INTO "public"."sc" VALUES ('07', '03', 98.0);
INSERT INTO "public"."sc" VALUES ('01', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('02', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('03', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('04', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('05', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('07', '02', 68.6);
INSERT INTO "public"."sc" VALUES ('01', '01', 34.0);

DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
  "sid" varchar(10) COLLATE "pg_catalog"."default",
  "sname" varchar(10) COLLATE "pg_catalog"."default",
  "sage" timestamp(6),
  "ssex" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO "public"."student" VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO "public"."student" VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
INSERT INTO "public"."student" VALUES ('09', '高人', '2020-08-14 11:05:31', '男');


DROP TABLE IF EXISTS "public"."teacher";
CREATE TABLE "public"."teacher" (
  "tid" varchar(10) COLLATE "pg_catalog"."default",
  "tname" varchar(10) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO "public"."teacher" VALUES ('01', '张三');
INSERT INTO "public"."teacher" VALUES ('02', '李四');
INSERT INTO "public"."teacher" VALUES ('03', '王五');

问题来了-------前方高能!

1.查询“数学”课程比“语文”课程成绩高的所有学生的学号；
select a.sid 学号 from (select sid,score from sc where cid='01') a,(select sid,score from sc where cid='02') b where a.sid=b.sid and a.score<b.score
2.查询平均成绩大于60分的同学的学号和平均成绩；
select sid,AVG(score) from SC group by sid having avg(score) > 60;
3.查询所有同学的学号、姓名、选课数、总成绩
select  a.sid 学号, a.sname 姓名, count(b.cid) 选课数, sum(b.score) 总成绩 from student a left join sc b on a.sid=b.sid GROUP BY a.sid,a.sname ORDER BY a.sid
4.查询姓“李”的老师的个数；
select count(tname) from teacher where tname LIKE '李%' GROUP BY tname
5.查询没学过“张三”老师课的同学的学号、姓名;
select s.sid 学号,s.sname 姓名 from Student as s where sid not in(select DISTINCT(s.sid) from Course as c,SC as s,Teacher as t where c.cid=s.cid AND c.tid=t.tid AND t.tname='张三'); 
6.查询学过语文并且也学过数学课程的同学的学号、姓名；
select a.sid,a.sname  from 
(select s.sid,s.sname from Student as s,Course as c,SC as  sc where c.cname='数学' AND s.sid=sc.sid AND c.cid=sc.cid) a, 
(select s.sid,s.sname from Student as s,Course as c,SC as  sc where c.cname='语文' AND s.sid=sc.sid AND c.cid=sc.cid) b
 WHERE a.sid=b.sid;
7.查询学过“张三”老师所教的所有课的同学的学号、姓名；
select a.sid,a.sname from student a,sc b, course c,teacher d where d.tname='张三' and d.tid=c.tid and c.cid=b.cid and b.sid=a.sid ORDER BY a.sid
8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select a.sid,a.sname from (select s.sid,s.sname,score from sc,student s where sc.cid='01' and sc.sid=s.sid) a,(select sid,score from sc where cid='02') b where a.sid=b.sid and a.score<b.score
9.查询所有课程成绩小于60分的同学的学号、姓名；
select s.sid,s.sname,sc.score from Student as s,SC as sc where sc.score<60 AND sc.sid=s.sid;
10.查询没有学全所有课的同学的学号、姓名；
select a.sid,a.sname from student a ,sc b where a.sid=b.sid  GROUP BY a.sid,a.sname HAVING count(b.cid)<(select count(cid) from course)
11.查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
select DISTINCT(a.sid),a.sname from student a ,sc b where a.sid=b.sid and b.cid in (select cid from sc where sid='01') ORDER BY a.sid
12.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名 
select * from Student where sid in (
SELECT sid FROM SC WHERE 
cid in (SELECT cid FROM SC WHERE sid='01') AND sid!='01' GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE sid='01')
)
13.把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score = (select avg(score) from sc ) where cid in (select c.cid from course c left join teacher t on t.tid = c.tid where t.tname = '张三') ;
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
查询张三老师教的学生的学号:
select sc.sid from sc ,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and tname='张三' 
最终答案:
select sname from student where sid not in (
select sc.sid from sc ,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and tname='张三' 
)
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 (没查不考试的或者只考了一门的)
查询有不及格的课程的学号 : 
select * from sc  where score<60
查询不及格的课程>=2次的
 select sid,avg(score) from sc  where score<60 GROUP BY sid HAVING count(*)>=2
联合多表查询姓名 
最终答案:
select sc.sid,s.sname,avg(sc.score) from student s left join sc on s.sid=sc.sid where score<60 GROUP BY sc.sid,s.sname HAVING count(*)>=2
16、检索"01"课程分数小于60，按分数降序排列的学生信息
查询01课程分数小于60的学生学号:
select sid from sc where score < 60 and cid='01'
和学生表连接:
最终答案:
select * from student s left join sc on s.sid=sc.sid where sc.score<60 and sc.cid='01' ORDER BY sc.score
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(group by， order by 后面跟数字，指的是 select 后面选择的列（属性），1 代表第一个列（属性），依次类推)
SELECT a.*
       ,SUM(CASE WHEN b.cid='01' THEN b.score ELSE 0 END) AS 语文
       ,SUM(CASE WHEN b.cid='02' THEN b.score ELSE 0 END) AS 数学
       ,SUM(CASE WHEN b.cid='03' THEN b.score ELSE 0 END) AS 英语
       ,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) 平均分
FROM Student a
LEFT JOIN SC b
ON a.sid=b.sid
GROUP BY 1,2,3,4
ORDER BY 平均分 DESC;
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率(考虑不考虑没考的学生的分数?)
concat(a,'%')拼接  round(sql,2)取2位小数 DECIMAL(10,2)总共10位2位小数

SELECT co.cid 课程,co.cname 课程名
,max(sc.score) 最大值
,min(sc.score) 最小值
,AVG(sc.score) 平均分
,CONCAT(round((SUM(CASE WHEN sc.score>=60 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 及格率
,CONCAT(round((SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 中等率
,CONCAT(round((SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 优良率
,CONCAT(round((SUM(CASE WHEN sc.score>=90  THEN 1.0 ELSE 0 END)/COUNT(sc.sid)*100),2),'%') 优秀率
FROM course as co
LEFT JOIN
sc as sc ON co.cid=sc.cid
GROUP BY co.cid,co.cname

19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT cid,avg_score,CONCAT(de,'%') 及格率
 FROM(
SELECT sc0.cid,
    AVG(sc0.score) avg_score,
    CAST(
		( SELECT COUNT(1) FROM SC WHERE cid=sc0.cid AND score>=60)*1.0/(SELECT COUNT(1) FROM SC WHERE cid=sc0.cid)*100 AS DECIMAL(10,2)) de
FROM SC sc0
GROUP BY sc0.cid
ORDER BY avg_score,de DESC)a;
20、查询学生的总成绩并进行排名
rank 函数 自动排序
select b.sid,b.sname,b.grade, rank() over (ORDER BY b.grade DESC) as rank from 
 (SELECT a.sid,s.sname,SUM(a.score) AS grade FROM sc a left join student s on s.sid=a.sid GROUP BY a.sid,s.sname ORDER BY grade DESC)b 
 GROUP BY 1,2,3 ORDER BY rank ;
21、查询不同老师所教不同课程平均分从高到低显示 

SELECT t.tid,t.tname,c.cid,c.cname,sum(sc.score) 总分,count(sid) 数量, round((sum(sc.score)/count(sid)),2) 平均分 from teacher t 

left join course c on t.tid=c.tid

left join sc sc on c.cid=sc.cid

GROUP BY 1,2,3,4 ORDER BY 平均分 DESC

后面还有下回分解 !

数据库 SQL 经典例题 (上篇)

继续阅读

Testlink安装部署之XAMPP

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述