sql的练习 -二期 -2019/04/22 --每日练习

面试的时候手写sql很正常，我记得京东的面试官问过我一个组装数据的问题，因为面试官是美女，故意做错 希望能给她留下深印象（刚毕业左右join没记牢做错了）---图片来源

 

 

看到网上练习题博客上的练习题，情不自禁做了起来：

第一题：查询01课程成绩比02课程成绩好的学生

SELECT c.* ,a.s_score AS '01Score' ,b.s_score  AS '02Score'   FROM
student c,
(SELECT * FROM score WHERE c_id = '01') AS a 
LEFT JOIN (SELECT * FROM score WHERE c_id = '02') AS b 
ON a.s_id=b.s_id   WHERE a.s_score>b.s_score AND c.s_id = a.s_id;

第三题：课程平均分大于60的学生 信息
SELECT b.*, a.avgScore
FROM
(SELECT s_id, avg(s_score) as avgScore FROM score GROUP BY s_id) as a 
left JOIN student AS b ON a.s_id= b.s_id WHERE a.avgScore > 60;


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
SELECT b.*, a.avgScore
FROM
(SELECT s_id, avg(s_score) as avgScore FROM score GROUP BY s_id) as a 
left JOIN student AS b ON a.s_id= b.s_id WHERE a.avgScore < 60

UNION
SELECT a.* , 0 AS aveScore
FROM student as a WHERE a.s_id not in (SELECT s_id FROM score GROUP BY s_id);


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT  a.s_id, a.s_name, b.cNum, b.scoreSum
FROM
student as a,
(SELECT s_id , count(s_id) AS cNum ,sum(s_score) AS scoreSum FROM score GROUP BY s_id) as b 
WHERE a.s_id = b.s_id; 
-------错了，应该以学生表为主表进行left join操作
SELECT a.s_id, a.s_name, COUNT(b.s_id), SUM(b.s_score)
FROM
student as a
LEFT JOIN score as b ON a.s_id = b.s_id
GROUP BY a.s_id;


//李姓老师的数量
SELECT count(t_id) FROM teacher WHERE teacher.t_name LIKE '李%';


-- 7、查询学过"张三"老师授课的同学的信息
SELECT *
FROM
course as a
JOIN teacher as b ON a.t_id = b.t_id 
LEFT JOIN score as c ON c.c_id = a.c_id
JOIN student as d ON c.s_id = d.s_id
WHERE b.t_name = '张三';


-- 7、查询没学过"张三"老师授课的同学的信息
SELECT * FROM student WHERE s_id not in
(SELECT d.s_id
FROM
course as a
JOIN teacher as b ON a.t_id = b.t_id 
LEFT JOIN score as c ON c.c_id = a.c_id
JOIN student as d ON c.s_id = d.s_id
WHERE b.t_name = '张三');

 

 

2019-08-31/ 10:08

CREATE TABLE `s_user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `math` bigint(13) DEFAULT NULL,
  `english` bigint(13) DEFAULT NULL,
  `chinese` bigint(13) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;




insert
INTO s_user(name,math,english,chinese)
values ('张三',88 , 91, 85),('李四', 72, 86, 93),('王五', 94, 66,75);


select s_user.*, scodeavg.scode as '自己分数总和', allavg.scode as '班级分数总和平均分'
from s_user,
(select id ,math+english+chinese as scode from s_user) as scodeavg,
(select avg(math+english+chinese) as scode from s_user) as allavg
where s_user.id = scodeavg.id  and scodeavg.scode > allavg.scode;