MYSQL--SELECT练习题1

最新推荐文章于 2024-05-15 00:41:01 发布
最新推荐文章于 2024-05-15 00:41:01 发布
阅读量185 收藏
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kidding10086/article/details/128639787
版权

文章目录

一.问题

有 3 个表 STUDENT(学生表)，LESSON（课程表），TIMETABLE（学生选课表）。其中

STUDENT（SNO，SNAME）代表（学号，姓名）

（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

TIMETABLE（ID,SNO，CNO，SCGRADE）代表（序号，学号，课号，成绩）

问题：

  1. 找出没选过“黎明”老师的所有学生姓名。

  2. 列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

  3. 既学过 1 号课程又学过 2 号课所有学生的姓名。

建表代码如下

START TRANSACTION;

DROP TABLE IF EXISTS TIMETABLE;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS LESSON;


CREATE TABLE STUDENT
(
 SNO VARCHAR(200) PRIMARY KEY,
 SNAME VARCHAR(200)
)DEFAULT CHARSET = 'utf8';

CREATE TABLE LESSON
(
 CNO VARCHAR(200) PRIMARY KEY,
 CNAME VARCHAR(200),
 CTEACHER VARCHAR(200)
) DEFAULT CHARSET = 'utf8';

CREATE TABLE TIMETABLE
(
 ID  INT PRIMARY KEY AUTO_INCREMENT,
 SNO VARCHAR(200),
 CNO VARCHAR(200),
 SCGRADE VARCHAR(200),
 FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
 FOREIGN KEY(CNO) REFERENCES LESSON(CNO)
)DEFAULT CHARSET = 'utf8';


INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 

INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '1', '学生 1'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '2', '学生 2'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '3', '学生 3'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '4', '学生 4'); 

INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 


commit;

二.解析

问题1

找出没选过“黎明”老师的所有学生姓名。

思路：首先找出所有选过”黎明“老师课程的学生，将其存储在视图LimingStudent中。然后用STUDENT表与LimingStudent进行比对，即可找出剩下没选过”黎明“老师课程的学生。

step1: 利用表连接，将timetable中的课程号换成上课老师的姓名

select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id;

在这里插入图片描述

step2: 找到所有选过“黎明”老师课程的学生编号

select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';

在这里插入图片描述

PS:上面语句中的表a 即为step1中的表

step3: 将step2中的查询结果存储在视图LimingStudent中

create view LimingStudent as select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';

step4: 将LimingStudent中的学生编号与Student中的学生编号进行比对，找到没选过“黎明”课的学生.

 select s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);

在这里插入图片描述

step5: 由于表连接是两个表的所有表项按照笛卡尔积一一比对，所以结果有重复。因此不要忘了要去重

 select distinct s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);

在这里插入图片描述

最后记得删除视图！

完整代码为

create view LimingStudent as select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';
 select distinct s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);
 drop view LimingStudent;

问题2

列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

思路: 找到两门以上不及格的学生编号，而后利用表连接找出学生姓名，利用分组函数avg找出平均成绩。

step1: 找到挂过科的学生的学生编号

select sno,cno,scgrade from timetable where scgrade < 60;

step2: 将step1中的表按照sno进行分组，找出每个学生不及格的课程数目

select t.sno,count(t.cno)count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno;

在这里插入图片描述

step3: 利用step2中的表，找到挂科数大于2门的学生编号

 select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;

在这里插入图片描述

step4: 将step3中的结果创建一个视图存储起来。

create view view1 as select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;

step5: 利用表连接查询对应的学生姓名

 select s.sname,v.sno from student s join view1 v on s.sno = v.sno;

在这里插入图片描述

step6: 利用分组函数avg计算出大于2门不及格的学生的平均成绩。注意sql语句的where部分，要用in来判断，而不能用=，如果view1中的数据大于1，用=会报错。

 select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno;

在这里插入图片描述

step7: 将step5的结果与step6的结果利用表连接合并

 select t1.sname ,t2.avg from ( select s.sname,v.sno from student s join view1 v on s.sno = v.sno) t1 join ( select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno) t2 on t1.sno = t2.sno;

在这里插入图片描述

完整代码

create view view1 as select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;
select t1.sname ,t2.avg from ( select s.sname,v.sno from student s join view1 v on s.sno = v.sno) t1 join ( select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno) t2 on t1.sno = t2.sno;
drop view view1;

问题3

既学过 1 号课程又学过 2 号课所有学生的姓名。

思路：首先找出学过1号课程的学生姓名，然后找出学过2号课程的学生姓名，最后取交集。

step1: 先在timetable表中找到选1号课程学生的学生编号

select sno,cno from timetable where cno = 1;

在这里插入图片描述

step2: 利用表连接，找到选1号课程对应的学生姓名

select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;

在这里插入图片描述

step3: 将step2的结果存储在一个视图中

create view view1 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;

step4: 按照上面的步骤选出选择2号课程的学生姓名，并存储在一个视图中

create view view2 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 2) t join student s on t.sno = s.sno;

step5: 将两个视图连接查询，找出同时存在于两个视图中的学生姓名

select v1.sname from view1 v1 join view2 v2 on v1.sname = v2.sname;

在这里插入图片描述

完整代码

create view view1 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;
create view view2 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 2) t join student s on t.sno = s.sno;
select v1.sname from view1 v1 join view2 v2 on v1.sname = v2.sname;
drop view view1;
drop view view2;
_kidding_
关注
MySQL 5.7 OCP（1Z0-888）题库测试及答案.rar
09-28
MySQL 5.7 OCP（1Z0-888）认证是Oracle公司为数据库管理员提供的专业认证，证明他们在MySQL 5.7版本中的技能和知识。这个认证涵盖了数据库安装与配置、性能优化、备份与恢复、安全性以及高级特性等多个方面。下面将...
MySQL练习题(面试题)
weixin_58362422的博客
06-09 572
CREATE TABLE SSC ( SNO VARCHAR(200), CNO VARCHAR(200), SCGRADE VARCHAR(200) ); CREATE TABLE S ( SNO VARCHAR(200 ), SNAME VARCHAR(200) ); CREATE TABLE C ( CNO VARCHAR(200), CNAME VARCHAR(200), CTEACHER VARCHAR(200) ); INSERT INTO C ( CNO, CNAME, CT.
MySQL练习题（单表多表查询）
阿瑾的博客
01-09 434
mysql> select e.depno “部门号”,d.depname “部门名称”,avg(s.inCome-s.outCome) “平均工资” from Employee e,salay s,department d where e.num=s.num and e.depno=d.depno group by d.depno having avg(s.inCome-s.outCome)>2500;求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；查找比所有财务部雇员收入都高的雇员的姓名；
MySQL练习题
桂林电子科技大学 -芳
04-14 400
有三个表，表S（学生表），C（课程表），SC（学生选课表）
MySQL数据库考测试试题(答案)
热门推荐
yipianfeng_ye的专栏
03-29 10万+
Mysql 测试题 （总分150分，时间2个小时） 一、单项选择题（1分1个,共100分） 1．以下聚合函数求数据总和的是( ) A．MAX B．SUM C．COUNT D．AVG 答案：B 2．可以用( )来声明游标 A．CREATE CURSOR B．ALTER CURSOR C．SET CURSOR D．DECLARE CURSOR ...
【数据库34道习题（一）】通过34道习题来真正掌握sql语句的运用
maikotom的博客
02-28 547
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+-------
mysql练习题-sql_practice.pdf
03-12
在进行具体的IT知识点阐述之前，需要先明确一下，本文件的内容实际上并不是直接的知识点，而是有关于“mysql练习题-sql_practice.pdf”文档的简介。此简介中包含了练习题相关的数据库表描述，以及对每个表进行操作的...
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
mysql练习题-create_table.pdf
03-12
从给定文件内容中可以看出，这些练习题都是基于MySQL数据库相关知识的习题。这些习题可以用来作为学习基础SQL语句、SQL*Plus命令和SQL函数等内容后的额外练习。下面将针对提到的部分练习题详细解释相关的知识点： 1...
MySQL50道练习题.zip
04-13
1. **基础操作**：这些练习题可能涵盖创建数据库、数据表，以及插入、更新和删除数据的基础知识。你需要熟悉`CREATE DATABASE`、`CREATE TABLE`、`INSERT INTO`、`UPDATE`和`DELETE`等语句。 2. **查询语法**：题目...
mysql中select查询强化50题（附解答）
OKkkbh的博客
11-27 2093
masql的查询操作练习题。题目多样，思路多变。嵌套查询，子查询，连接查询一篇博文一网打尽！真正强化了解数据库。
【MySQL】MySQL习题（15面试题）
你今天真好看呀
08-01 2127
问题15：面试题 有三个表，表S（学生表），C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO,CNAME,CTEACHER）代表（课号，课名，老师） SC（SNO,CNO,SCGRADE）代表（学号，课号，成绩） 问题： 1.找出没有选过’‘黎明’'老师的所有学生姓名 2.列出2门以上（含2门）不及格学生姓名及平均成绩 3.既学过1号课程又学过2号课程的所有...
一道数据库查询面试题
MarconiYe的博客
04-05 628
有 3 个表 S(学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩） 问题： 1，找出没选过“黎明”老师的所有学生姓名。 2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。 3，即学过 1 号课程又学过 2 号课所有学生的姓名。 题解: ---------------------------第一题-----------------.
MySQL-SQL编写练习：基本的SELECT语句
小孔靠得住的博客
04-03 669
DDL:数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE DML:数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT （重中之重） DCL:数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE
数据库学习之select语句练习
最新发布
banliyaoguai的博客
05-15 835
【代码】数据库学习之select语句练习。
数据库sql语句select数据查询练习题
qq_44905242的博客
12-03 3638
首先创建题目需求的表，分别为sc(学生选课表),student(学生表),stugrade(成绩表)1.查询学生选课表中的全部数据select * from sc;2.查询全体学生的姓名、学号和所在系。select sname,sno,sdept from student;3.查询全体学生的姓名及其出生年份。select sname,2022-sage as 出生年份 from student;4.查询计算机系全体学生的姓名。select sname from student where sdept='计算
Mysql数据库学习：select查询语句
m0_58462317的博客
07-13 815
select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员') or (job not in ('经理','销售员') and sai>=20000 );select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员');name in ('研发部','销售部')group by name;4、查询每个部门的员工数,并升序排序。
MySQL中的经典34道练习题（第8~13题）
m0_46160024的博客
05-22 233
本段代码展示了MySQL中的经典34道练习题（第8~13题），供读者参考。
第03章_基本的SELECT语句
Bleuuuu的博客
11-19 1495
第03章_基本的SELECT语句 【题目】 # 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY # 2.查询employees表中去除重复的job_id以后的数据 # 3.查询工资大于12000的员工姓名和工资 # 4.查询员工号为176的员工的姓名和部门号 # 5.显示表 departments 的结构，并查询其中的全部数据 1. 查询员工12个月的工资总和，并起别名为ANNUAL SALARY SELECT employee_id , last_name,salary * 12
mysql sql练习题_Mysql----sql语句练习题(一)
06-10
好的，我来为您解答MySQL的SQL语句练习题(一)。 1. 创建一个名为“test”的数据库。 ``` CREATE DATABASE test; ``` 2. 在“test”数据库中创建一个名为“student”的数据表，包含以下字段：学号（id，整型，主键），姓名（name，字符串类型，不超过20个字符），年龄（age，整型），性别（gender，字符串类型，不超过10个字符），成绩（score，整型）。 ``` USE test; CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(20), age INT, gender VARCHAR(10), score INT ); ``` 3. 插入以下数据到“student”数据表中： ``` INSERT INTO student VALUES (1, 'Tom', 18, 'male', 80), (2, 'Jerry', 19, 'male', 85), (3, 'Lily', 18, 'female', 90), (4, 'Lucy', 20, 'female', 95); ``` 4. 查询“student”数据表中所有学生的姓名和年龄。 ``` SELECT name, age FROM student; ``` 5. 查询“student”数据表中所有学生的学号、姓名和成绩，并按成绩从高到低排序。 ``` SELECT id, name, score FROM student ORDER BY score DESC; ``` 6. 查询“student”数据表中所有学生的平均年龄。 ``` SELECT AVG(age) FROM student; ``` 7. 查询“student”数据表中成绩大于等于90分的学生姓名和成绩。 ``` SELECT name, score FROM student WHERE score >= 90; ``` 8. 将“student”数据表中学号为3的学生的成绩更新为100分。 ``` UPDATE student SET score = 100 WHERE id = 3; ``` 9. 删除“student”数据表中年龄小于19岁的学生。 ``` DELETE FROM student WHERE age < 19; ``` 以上是MySQL的SQL语句练习题(一)的解答，希望能对您有所帮助。如果您有任何疑问或需要进一步的帮助，请随时向我提问。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值