转载

数据库面试常见24题型之-----查询

首先我们创建数据库基本元素

SELECT `EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO` 
FROM
  `scott`.`emp` 
LIMIT 0, 1000 ;

CREATE DATABASE IF NOT EXISTS students DEFAULT CHARACTER SET = 'utf8';
USE students;

#创建基本数据表
#班级表

CREATE TABLE batch(
       batchcode INT NOT NULL,
       batchname VARCHAR(30)
);

ALTER TABLE batch
ADD CONSTRAINT batch_bcode_pk PRIMARY KEY(batchcode);

INSERT INTO batch VALUES(96561,'工业工程96561班');
INSERT INTO batch VALUES(96571,'工业工程96571班');
INSERT INTO batch VALUES(96572,'工业工程96572班');
INSERT INTO batch VALUES(94381,'经济管理94381班');
INSERT INTO batch VALUES(96581,'质量管理96581班');
INSERT INTO batch VALUES(96171,'会计96171班');


#学生基本信息表

CREATE TABLE student(
       studno INT NOT NULL,
       studname VARCHAR(30),
       batchcode INT
);

ALTER TABLE student
ADD CONSTRAINT student_sno_pk PRIMARY KEY(studno);

ALTER TABLE student
ADD CONSTRAINT student_bcode_fk FOREIGN KEY(batchcode) 
REFERENCES batch(batchcode);

INSERT INTO  student VALUES(1057,'张三',96561);
INSERT INTO  student VALUES(1058,'李四',96561);
INSERT INTO  student VALUES(1059,'王五',96571);
INSERT INTO  student VALUES(1060,'马六',96571);
INSERT INTO  student VALUES(1061,'丁七',94381);
INSERT INTO  student VALUES(1062,'张涛',96171);

#课程信息表
CREATE TABLE course(
       courseid VARCHAR(10) NOT NULL,
       coursename VARCHAR(20)
);

ALTER TABLE course
ADD CONSTRAINTS course_cid_uk UNIQUE(courseid);

INSERT INTO course VALUES('A01','JAVA');
INSERT INTO course VALUES('A02','JSP');
INSERT INTO course VALUES('A03','Struts');
INSERT INTO course VALUES('A04','Oracle');
INSERT INTO course VALUES('A05','Spring');
INSERT INTO course VALUES('B01','经济管理');
INSERT INTO course VALUES('B02','国际贸易');
INSERT INTO course VALUES('B03','会计原理');
INSERT INTO course VALUES('B04','外贸函电');
INSERT INTO course VALUES('B05','马克思主义原理');

# 成绩表

CREATE TABLE score(
         studno INT NOT NULL,
         courseid VARCHAR(10) NOT NULL,
         grade INT
);

INSERT INTO score VALUES(1057,'A01',85);
INSERT INTO score VALUES(1057,'A02',77);
INSERT INTO score VALUES(1057,'A03',20);
INSERT INTO score VALUES(1057,'A04',59);
INSERT INTO score VALUES(1057,'A05',80);
INSERT INTO score VALUES(1058,'A01',79);
INSERT INTO score VALUES(1058,'A02',73);
INSERT INTO score VALUES(1058,'A03',62);
INSERT INTO score VALUES(1057,'B01',95);
INSERT INTO score VALUES(1058,'B01',88);
INSERT INTO score VALUES(1058,'B04',71);
INSERT INTO score VALUES(1060,'A01',69);
INSERT INTO score VALUES(1061,'B01',74);
INSERT INTO score VALUES(1061,'A01',55);


#教学计划表

CREATE TABLE courseplan(
       studno INT NOT NULL,
        courseid VARCHAR(10) NOT NULL,
         teachid INT NOT NULL,
         coursedt VARCHAR(20) 
);

INSERT INTO courseplan VALUES(1057,'A01',775,'周一');
INSERT INTO courseplan VALUES(1058,'B01',777,'周三');
INSERT INTO courseplan VALUES(1060,'B02',778,'周二');
INSERT INTO courseplan VALUES(1058,'A02',779,'周一');



#教师信息表

CREATE TABLE teacherinfo
(
       teachid INT NOT NULL,
       teachname VARCHAR(30)
);

ALTER TABLE teacherinfo
ADD CONSTRAINT teacherinfo_tid_pk PRIMARY KEY(teachid);

INSERT INTO teacherinfo VALUES(775,'张强');
INSERT INTO teacherinfo VALUES(776,'宋文龙');
INSERT INTO teacherinfo VALUES(777,'李可');
INSERT INTO teacherinfo VALUES(778,'刘平');
INSERT INTO teacherinfo VALUES(779,'王海明');

COMMIT;

SELECT * FROM courseplan;
INSERT INTO courseplan VALUES (1058,'A03',779,'周一');
SELECT * FROM course;
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM teacherinfo;
SELECT * FROM batch;
复制代码

1、完成查询如下表显示，显示选课表信息(courseplan)全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。

SELECT c.studno AS "学生编号",s.studname AS "学生姓名",o.coursename AS "课程名称",r.grade AS "成绩"
FROM courseplan c,course o,student s,score r
WHERE c.courseid = o.courseid AND c.studno = s.studno AND s.studno = r.studno AND o.courseid = r.courseid
ORDER BY c.studno ASC,o.coursename;
复制代码

2、查询显示单科最高成绩

SELECT s.studno AS "学生编号",s.studname AS "学生姓名",c.coursename AS "课程名称",r.grade AS "单科最高成绩"
FROM score r,student s,course c,(SELECT MAX(grade) AS "max_grade",courseid FROM score GROUP BY courseid) t
WHERE t.courseid = c.courseid AND r.grade = t.max_grade AND s.studno = r.studno;
复制代码

3、查询显示学生课程及格还是不及格

SELECT s.studno AS "学生编号",s.studname AS "学生姓名",c.coursename AS "课程名称",
CASE WHEN r.grade>=60 THEN '及格'
	ELSE '不及格'
 END '考试通过状态'
FROM score r,student s,course c
WHERE s.studno = r.studno AND r.courseid = c.courseid;
复制代码

4、统计学生选科的数量

SELECT DISTINCT s.studno AS "学生编号",s.studname AS "学生姓名",(SELECT COUNT(1) FROM courseplan WHERE c.`studno` = studno ) AS "选课数量"
FROM student s,courseplan c
WHERE s.`studno` = c.`studno`;
复制代码

5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩

SELECT DISTINCT s.studno AS "学生编号",s.studname AS "学生姓名",c.coursename AS "课程名称",r.grade AS "课程成绩"
FROM score r,student s,course c,(SELECT AVG(grade) AS "avg_grade",courseid FROM score GROUP BY courseid) t,(SELECT grade,courseid FROM score GROUP BY courseid) g
WHERE t.courseid = c.courseid AND r.grade = t.avg_grade AND s.studno = r.studno AND g.grade > t.avg_grade;
复制代码

6、查询显示需要补考的学生的学生编号，学生姓名和课程名称

SELECT s.studno AS "学生编号",s.studname AS "学生姓名",c.coursename AS "课程名称"
FROM student s,score r,course c
WHERE s.`studno` = r.`studno` AND c.`courseid` = r.`courseid` AND r.`grade`<60;
复制代码

7、统计各科成绩平均分，显示课程编号，课程名称，平均分。

SELECT c.`courseid` AS "课程编号",c.`coursename` AS "课程名称",r.grade AS "平均分"
FROM course c
INNER JOIN score r
ON c.`courseid` = r.`courseid`
INNER JOIN (SELECT AVG(grade) AS "avg_grade" FROM score GROUP BY courseid) t
ON t.avg_grade = r.`grade`;
复制代码

8、查询选修了java课程的学生信息

SELECT s.*
FROM student s
INNER JOIN courseplan c
ON s.`studno` = c.`studno`
INNER JOIN course r
ON r.`courseid` = c.`courseid` AND r.`coursename` != "JAVA";
复制代码

9、查询没有选修JAVA课程的学生信息

SELECT s.*
FROM student s
INNER JOIN courseplan c
ON s.`studno` = c.`studno`
INNER JOIN course r
ON r.`courseid` = c.`courseid` 
	AND NOT EXISTS(SELECT r.`coursename` FROM courseplan GROUP BY studno HAVING r.`coursename` = "JAVA");
复制代码

10、查询选修了教师李可课程的学生信息

SELECT s.*
FROM courseplan c,teacherinfo t,student s
WHERE c.`teachid` = t.`teachid`
	AND s.`studno` = c.`studno` 
	AND t.`teachname` = "李可";
复制代码

11、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期

SELECT s.`studno` AS "学生编号", s.`studname` AS "学生姓名",b.`batchcode` AS "班级编号",c.`courseid` AS "课程编号",t.`teachname` AS "授课教师",r.`coursedt` AS "上课日期"
FROM course c,student s,courseplan r,batch b,teacherinfo t
WHERE c.`courseid` = r.`courseid` 
	AND s.`studno` = r.`studno` 
	AND s.`batchcode` = b.`batchcode` 
	AND r.`teachid` = t.`teachid`
	AND r.`courseid` = 'A01' AND r.`courseid` = 'A02';
复制代码

12、查询96571班都有哪些课程，在什么时间有哪位教师授课

SELECT b.`batchcode` AS "班级编号",b.`batchname` AS "班级名称",c.`courseid` AS "课程编号",c.`coursename` AS "课程名称",r.coursedt AS "上课时间",t.`teachname` AS "授课教师"
FROM batch b,course c,courseplan r,teacherinfo t,student s
WHERE b.`batchcode` = s.`batchcode` 
	AND c.`courseid` = r.`courseid` 
	AND t.`teachid` = r.`teachid`
	AND s.`batchcode` = b.`batchcode`
	AND b.`batchcode` = '96571';
复制代码

13、查询周一不上课的班级

SELECT b.`batchcode` AS "周一不上课的班级编号",b.`batchname` AS "周一不上课的班级名称"
FROM batch b,courseplan c,student s
WHERE c.`studno` = s.`studno` AND s.`batchcode` = b.`batchcode` AND c.`coursedt`!='周一';
复制代码

14、查询周四上课的教师姓名

SELECT t.`teachname` AS "周四上课的教师姓名"
FROM courseplan c,teacherinfo t
WHERE c.`teachid` = t.`teachid` AND c.`teachid` = "周四";
复制代码

15、查询A02课程的授课教师和上课时间

SELECT t.`teachname` AS "A02课程的授课教师",c.`teachid` AS "上课时间"
FROM courseplan c,teacherinfo t
WHERE c.`teachid` = t.`teachid` AND c.`courseid` = 'A02';
复制代码

16、统计各个科目不及格人数占这个科目考生人数的百分比

SELECT DISTINCT CONCAT(ROUND((SELECT COUNT(1) FROM score WHERE grade<60 AND r.`courseid` = courseid)/(SELECT COUNT(1) FROM score WHERE r.`courseid` = courseid)*100,2),'%') AS "各个科目不及格人数占这个科目考生人数的百分比",c.`coursename` AS "课程名称"
FROM score r,course c
WHERE r.courseid = c.courseid;
复制代码

17、统计所有不及格人数占考生总数的百分比

SELECT CONCAT(ROUND((SELECT COUNT(1) FROM score WHERE grade<60)/(SELECT COUNT(1) FROM score)*100,2),'%') AS "所有不及格人数占考生总数的百分比";
复制代码

18、查询单科成绩在90分以上的学生是哪个班级的，授课教师是谁？

SELECT b.batchname AS "90分以上的班级名称",t.teachname AS "授课教师"
FROM score c,batch b,teacherinfo t,student s,courseplan p
WHERE s.`studno` = c.studno 
	AND s.batchcode = b.batchcode 
	AND p.teachid = t.teachid 
	AND s.`studno` = p. studno 
	AND c.grade IN (SELECT grade FROM score GROUP BY courseid HAVING grade>90);
复制代码

19、查询工业工程班的授课教师都是谁？

SELECT t.`teachname` AS "工业工程班授课教师"
FROM batch b,courseplan c,teacherinfo t,student s
WHERE b.`batchcode` = s.`batchcode`
	AND c.`teachid` = t.`teachid`
	AND c.`studno` = s.`studno`
	AND b.`batchcode` IN (SELECT batchcode FROM batch b HAVING b.`batchname` LIKE '工业工程%');
复制代码

20、查询1068号学生在什么时间都有课？

SELECT c.coursedt AS "1068号学生的上课时间"
FROM student s,courseplan c
WHERE s.`studno` = c.studno
	AND s.`studno` = '1068';
复制代码

21、查询哪些同学的考试成绩都在90分以上

SELECT s.*
FROM score r,student s
	WHERE s.`studno` = r.`studno`
	AND r.`grade`>90;
复制代码

22、查询同时代课超过两门课程的教师

SELECT DISTINCT t.`teachname` AS "同时代课超过两门课程的教师"
FROM courseplan c,teacherinfo t,batch b,student s
WHERE c.`teachid` = t.`teachid` AND b.`batchcode` = s.`batchcode` AND s.`studno` = c.`studno` AND t.`teachid` IN (SELECT teachid FROM courseplan WHERE c.`coursedt` = coursedt AND c.`teachid` = teachid AND c.`courseid` != courseid );
复制代码

23、汇总各个学生考试成绩的总分，并排名次。显示学生编号，学生姓名，班级编号，总分

SELECT DISTINCT s.studno AS "学生编号",s.studname AS "学生姓名",b.batchcode AS "班级编号",(SELECT SUM(grade) FROM score WHERE s.studno = studno) AS "总分"
FROM score r,student s,batch b
	WHERE s.studno = r.studno
	AND b.batchcode = s.batchcode
	ORDER BY (SELECT SUM(grade) FROM score WHERE s.studno = studno) DESC;
复制代码

24、按照班级分组，显示学生的编号，学生姓名和总分，在一个班级内按照总分排名

SELECT b.`batchcode` AS "班级编号",b.`batchname` AS "班级名称",s.`studno` AS "学生编号",s.`studname` AS "学生姓名",(SELECT SUM(grade) FROM score WHERE s.studno = studno) AS "总分"
FROM score r,student s,batch b
	WHERE s.`studno` = r.`studno`
	AND s.`batchcode` = b.`batchcode`
	GROUP BY b.`batchcode`
	ORDER BY (SELECT SUM(grade) FROM score,batch WHERE s.studno = studno AND batchcode = b.`batchcode`) DESC;
复制代码

做完这24道Mysql数据库查询题，我相信你的水平一定很厉害了，这里面的最后两道题有错误哦！！！看博客的大佬可以给与修改意见哦！！！

想要查看更多有关Mysql的题型，请点击收藏哦！-> juejin.im/post/5e6f7f…

原文  https://juejin.im/post/5e80b354f265da47df1cb612
正文到此结束
所属分类： Java 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：647 人
  • 运行天数：4,408天
  • 最后更新：2024年11月21日23点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG