数据分析sql习题笔记

参考：数据分析-SQL练习 - JaconHunt - 博客园.html

create database  school charset='utf8';
use school;

#学生表
create table `Student`
(
  `Sno` varchar(20) not null COMMENT '人名',
  `Sname` varchar(20) not null COMMENT '姓名',
  `Ssex` varchar(20) not null COMMENT '性别',
  `Sbirthday` datetime COMMENT '出生日期',
  `Class` varchar(20) COMMENT '班级'
);
#课程表
create table `Course`(
  `Cno` varchar(20) not null COMMENT '课程号',
  `Cname` varchar(20) not null COMMENT '课程名称',
  `Tno` varchar(20) not null COMMENT '教工编号'
);
#成绩表
Create table `Score` (
  `Sno` varchar(20) not null COMMENT '学号',
  `Cno` varchar(20) not NULL comment '课程号',
  `Degree` DECIMAL(4,1) null COMMENT '成绩'
);
#教师表
create table `Teacher` (
  `Tno` varchar(20) not null COMMENT '教工编号',
  `Tname` varchar(20) not null COMMENT '教工姓名',
  `Tsex` varchar(20) not null COMMENT '教工性别',
  `Tbirthday` datetime null COMMENT '教工出生日期',
  `Prof` varchar(20) null COMMENT '职称',
  `Depart` varchar(20) null COMMENT '教工所在部门'
);

insert into `Student` value
(108,'曾华','男','1977-09-01',95033),
(105,'匡明','男','1975-10-02',95031),
(107,'王丽','女','1976-01-23',95033),
(101,'李军','男','1976-02-20',95033),
(109,'王芳','女','1975-02-10',95031),
(103,'陆军','男','1974-06-03',95031);

insert into `Course` value
('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',831);

insert into `Score` value
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'2-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81);

insert into `Teacher` value
(804,'李城','男','1958-12-02','副教授','计算机系'),
(856,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');

对其中的一些题做了答案修改或者笔记备注。

21 查询score中选学多门课程的同学中分数为非最高分成绩的记录

select * from Score a where Degree<(select MAX(Degree)from  Score b
where a.Cno = b.Cno) and Sno in(select Sno from Score group by  Sno having  count(*) > 1);

• group by Sno having count(*) 数Sno 被归组后每组的数量。

• 如果要查询多个表的多个字段，就要先拼接表后查询。但如果只需要查其中一个表但需要借助其它表做过滤，就可以拼接后过滤或者就子查询。

28 查询出“计算机系“教师所教课程的成绩表

select Score.Degree,Teacher.* from Score inner join Course on Score.Cno=Course.Cno inner join Teacher on Course.Tno in (select Tno from Teacher where Depart='计算机系');

• 并没有真的关联Teacher，它就自动关联了， 且用 inner join ，Teacher表有左边的就随左边的表拓展，没有的左边表就随之拓展，所以很多无关的记录都加进来了，

正确做法（全部关联后再过滤）：

select Score.Degree,Teacher.* from Score inner join Course on Score.Cno=Course.Cno inner join Teacher on Course.Tno=Teacher.Tno and  Teacher.Depart='计算机系';

34 查询成绩比该课程平均成绩低的同学的成绩表

 SELECT * FROM Score INNER JOIN (SELECT CNO,AVG(DEGREE) as 平均分 FROM Score GROUP BY CNO) as A on Score.Cno= A.Cno WHERE DEGREE < A.平均分;

35 查询正在任课教师的Tname和Depart

SELECT distinct tname, depart from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno;

36 查询所有未讲课的教师的Tname和Depart

 select Tname,Depart from Teacher where Tno not in (select Tno from Course,Score where Course.Cno=Score.Cno);