转载

数据库面试常见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教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：624 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日19点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG