MySql的15个练习题

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的)

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

6. 查询"李"姓老师的数量

7. 查询学过"张三"老师授课的同学的信息

8. 查询没学过"张三"老师授课的同学的信息

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11. 查询没有学全所有课程的同学的信息

12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

14. 查询没学过"张三"老师讲授的任一门课程的学生姓名

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

一、建表导数据

CREATE TABLE student
(
    s_id                STRING COMMENT '学生编号'
    ,s_name             STRING COMMENT '学生姓名'
    ,s_birth            STRING COMMENT '学生出生日期'
    ,s_sex              STRING COMMENT '学生性别'
   
)
COMMENT '学生信息表'
LIFECYCLE 365
;

INSERT OVERWRITE TABLE student 
VALUES ("1","赵雷","1990-01-01","男")
,("2","钱电","1990-12-21","男")
,("3","孙风","1990-05-20","男")
,("4","李云","1990-08-06","男")
,("5","周梅","1991-12-01"," " )
,("6","吴兰","1992-03-01","女")
,("7","郑竹","1989-07-01","女")
,("8","王菊","1990-01-20","女");

SELECT * FROM student;

CREATE TABLE teacher
(
     t_id                STRING COMMENT '老师编号'
    ,t_name             STRING COMMENT '老师姓名'
   
)
COMMENT '教师信息表'
LIFECYCLE 365
;
INSERT OVERWRITE TABLE teacher
VALUES( "1","张三")
,("2","李四")
,("3","王五");

SELECT * FROM teacher;

CREATE TABLE course
(
     c_id               STRING COMMENT '课程编号'
    ,c_name             STRING COMMENT '课程名'
    ,t_id               STRING COMMENT '老师编号'
)
COMMENT '课程信息表'
LIFECYCLE 365
;

INSERT OVERWRITE TABLE course
VALUES(" 1","语文","2")
,("2","数学","1")
,("3","英语","4");

SELECT * FROM course;


CREATE TABLE score
(
    s_id                STRING COMMENT '学生编号'
    ,c_id               STRING COMMENT '课程编号'
    ,s_score            STRING COMMENT '学生成绩'
)
COMMENT '成绩表'
LIFECYCLE 270
;
INSERT OVERWRITE TABLE score
VALUES ("1","1","80")
,("1","2","90")
,("1","3","99")
,("2","1","70")
,("2","2","60")
,("2","3","180")
,("3","1","80")
,("3","2","80")
,("3","3","80")
,("4","1","50")
,("4","2","30")
,("4","3","-20")
,("5","1","76")
,("5","2","87")
,("6","1","31")
,("6","3","34")
,("7","2","89")
,("7","3","98");

SELECT *FROM  score;

二、查询

-- 1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select st.*,sc1.*,sc2.* from student st inner join score sc1 on (st.s_id=sc1.s_id) and sc1.c_id='1'
inner join score sc2 on (st.s_id=sc2.s_id) and sc2.c_id='2' 
where sc1.s_score>sc2.s_score;
-- 2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.* ,sc1.*,sc2.* from student st inner join score sc1 on (st.s_id=sc1.s_id) and sc1.c_id='1'
inner join score sc2 on (st.s_id=sc2.s_id) and sc2.c_id='2' 
where sc1.s_score<sc2.s_score;

-- 3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.s_id,s.s_name,AVG(c.s_score) 
from student s join score c
ON  s.s_id=c.s_id 
GROUP BY s.s_id,s.s_name
HAVING AVG(c.s_score)>=60;

-- 4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的)
SELECT s.s_id,s.s_name,AVG(c.s_score) 
from student s join score c
on  s.s_id=c.s_id 
GROUP BY s.s_id,s.s_name
HAVING AVG(c.s_score)<60 OR  AVG(c.s_score) is NULL ;


-- 5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id,s.s_name,SUM(c.s_score),COUNT(c.c_id)
from student s join score c
ON  s.s_id=c.s_id
GROUP BY s.s_id,s.s_name;

-- 6. 查询"李"姓老师的数量
SELECT COUNT(*) from teacher
WHERE t_name LIKE '李%';

-- 7. 查询学过"张三"老师授课的同学的信息
SELECT s.* 
from student s,teacher t,score sc,course c 
WHERE s.s_id = sc.s_id AND 
      sc.c_id = c.c_id AND 
      c.t_id = t.t_id AND 
      t.t_name = '张三';
-- 8. 查询没学过"张三"老师授课的同学的信息
SELECT s.*from student s where s.s_id not IN 
(SELECT st.s_id
from student st,teacher t,score sc,course c 
WHERE st.s_id = sc.s_id AND 
      sc.c_id = c.c_id AND 
      c.t_id = t.t_id AND 
      t.t_name = '张三');

-- 9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s.* from student s, score s1,score s2 
WHERE s.s_id = s1.s_id
AND s1.s_id = s2.s_id
AND s1.c_id='1'
AND s2.c_id='2';

-- 10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student st WHERE st.s_id in (select sc.s_id from score sc WHERE sc.c_id='1')
and st.s_id not in (SELECT sc.s_id from score sc WHERE sc.c_id='2');
-- 11. 查询没有学全所有课程的同学的信息
select s.* from student s
left join score sc1 on s.s_id =sc1.s_id AND sc1.c_id='1'
left join score sc2 on s.s_id =sc2.s_id and sc2.c_id='2'
left join score sc3 on s.s_id =sc3.s_id AND sc3.c_id='3'
where sc1.s_score is null or sc2.s_score is NULL or sc3.s_score is NULL ;

-- 12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT  * from student where s_id in (select s_id from score where c_id in(select c_id from score where s_id='1'));

-- 13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT * from student where s_id in (
select s_id FROM  score where s_id not in (select s_id from score WHERE c_id NOT in (select c_id from score where s_id='1'))
GROUP by s_id
HAVING COUNT(*)=(SELECT COUNT(*) from score WHERE s_id='1')AND s_id != '1');

-- 14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select st.s_name from student st where st.s_id  NOT  in (select sc.s_id from score sc where sc.c_id in 
(SELECT c_id from  course c join teacher t on t.t_id=c.t_id AND t.t_name='张三'));

-- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.s_id,st.s_name,AVG(sc.s_score) from student st inner join score sc on st.s_id=sc.s_id
WHERE s_score<60
GROUP by st.s_id,st.s_name
HAVING COUNT(*)>=2;