请选择 进入手机版 | 继续访问电脑版

SQL【复习】经典MySQL练习50题之1-7

[复制链接]
滚雪球少年 发表于 2021-1-2 19:44:02 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
问题
试问:SELECT * FROM employees; 和 SELECT * FROM employees WHERE commission_pct LIKE “%%” AND last_name LIKE “%%”;效果是否一样?并说明原因
答:
效果不一样 ,因为表中大概存在NULL值,如果表中没有NULL值,则效果是一样的,若and改为or,则效果一样。
#--------------------------------
SELECT *
FROM employees
WHERE commission_pct LIKE '%%’ or last_name like “%%”;
  经典MySQL训练50题

表名和字段

学生表
Student(s_id、s_name、s_birth、s_sex):学生编号、学生姓名、出生年代、学生性别。
课程表
Course(c_id、c_name、t_id):课程编号、课程名称、西席编号。
西席表
Teacher(t_id、t_name):西席编号、西席姓名。
效果表
Score(s_id、c_id、s_score):学生编号、课程编号、分数。
第一题

1、查询"01"课程比"02"课程效果高的学生的信息及课程分数(重点)
  1. -- 解答一SELECT s.*FROM `student` AS s,`score` AS sc1,`score` AS sc2WHERE s.`s_id`=sc1.`s_id` AND s.`s_id`=sc2.`s_id`AND sc1.`c_id`='01' AND sc2.`c_id`='02'AND sc1.`s_score`>sc2.`s_score`;-- 解答二SELECT s.`s_id`,s.`s_name`,a.`c_id`FROM (SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS aINNER JOIN (SELECT * FROM `score` AS sc2 WHERE sc2.`c_id`='02') AS b ON a.`s_id`=b.`s_id`INNER JOIN `student` AS sON s.`s_id`=a.`s_id`WHERE a.`s_score`>b.`s_score`;
复制代码

第二题

2、查询均匀效果大于60分的学生的学号宁静均效果(重点)
  1. SELECT `s_id` AS "学号",AVG(`s_score`) AS "均匀效果"FROM `score` AS scGROUP BY sc.`s_id`HAVING AVG(sc.`s_score`)>60;
复制代码

第三题

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总效果
  1. SELECT s.`s_id` AS 'ID',s.`s_name` AS '姓名',COUNT(sc.`s_score`) AS '选课数',SUM(sc.`s_score`) AS '总分'FROM `student` AS sLEFT  JOIN `score` AS sc ON s.`s_id`=sc.`s_id`GROUP BY sc.`s_id`;
复制代码

第四题

4、查询"李"姓老师的数量
  1. SELECT COUNT(*)FROM `teacher` AS tWHERE t.`t_name`LIKE '李%';
复制代码

第五题

5、查询学过"张三"老师授课的同学的信息
  1. -- 解法一SELECT s.*FROM `student` AS s,`score` AS sc,`course` AS c,`teacher` AS tWHERE s.`s_id`=sc.`s_id` AND sc.`c_id`=c.`c_id` AND c.`t_id`=t.`t_id`AND t.`t_name`='张三';-- 解法二SELECT  s.*        FROM  Student AS s , Score AS  sc         WHERE s.s_id = sc.s_id         AND  sc.c_id IN  (SELECT  DISTINCT(c.c_id)                FROM  Course AS c,Teacher AS t                 WHERE c.t_id = t.t_id                 AND  t.t_name = "张三")
复制代码

第六题

6、查询没学过"张三"老师授课的同学的信息
  1. -- 解法一SELECT *FROM `student` AS sWHERE s.`s_id` NOT IN(        SELECT sc.`s_id`        FROM `student` AS s        LEFT JOIN `score` AS sc        ON s.`s_id`=sc.`s_id`        WHERE sc.`c_id` IN(                SELECT c.`c_id`                FROM `course` AS c                INNER JOIN `teacher` AS t                WHERE c.`t_id`=t.`t_id` AND t.`t_name`='张三'        ));
复制代码

第七题

7、查询学过编号为"01"而且也学过编号为"02"的课程的同学的信息
  1. -- 解法一SELECT s.*FROM (SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS aINNER JOIN (SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='02') AS bON a.`s_id`=b.`s_id`INNER JOIN `student` AS s ON s.`s_id`=a.`s_id`;-- 解法二SELECT  s.* FROM Student AS s, Score AS sc1 ,Score AS sc2WHERE s.s_id = sc1.s_id AND  s.s_id = sc2.s_id  AND  sc1.c_id = "01"AND  sc2.c_id = "02"
复制代码


来源:https://blog.csdn.net/qq_42374697/article/details/112007674
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发布主题

专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )