sql学习（4张表引发的50个sql问题，提供表与数据的搭建）-- 连载一

从网上找到的这道题！发现自己的sql真的不是很好，锻炼一下，以下全部经过本人验证！和源文件有不同的地方。


-- 一个题目涉及到的50个Sql语句
-- 表的结构与数据的插入，请参照本文最下方

------------------------------------表结构--------------------------------------
-- 学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）
-- 课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）
-- 成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）
-- 教师表tblTeacher（教师编号TeaId、姓名TeaName）
---------------------------------------------------------------------------------

--问题
-- 1、查询“001”课程比“002”课程成绩高的所有学生的学号；
原文：
Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId Andt1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId Andt2.CourseId='002')

我的思路：查询出001，查询出002，发现两者中只有在stuId相等的情况下才可以进行比较
select * from
(select * from tblScore where CourseId = '001') t1,
(select * from tblScore where CourseId='002') t2
where t1.StuId = t2.StuId andt1.Score>t2.Score

-- 2、查询平均成绩大于60分的同学的学号和平均成绩；
我的思路：每个学号对应多个成绩，按照学号进行分组，并利用avg求出平均成绩
select StuId as'学号',avg(Score) as '平均成绩'
from tblScore group by StuId
having avg(Score)>60

--3、查询所有同学的学号、姓名、选课数、总成绩；
--原文：
SelectStuId,StuName,
SelCourses=(Select Count(CourseId) From tblScore t1 Wheret1.StuId=s1.StuId),
SumScore=(Select Sum(Score) From tblScore t2 Wheret2.StuId=s1.StuId) 
From tblStudent s1

我的思路：学号、选课数、总成绩在一张表，按照学号分组，选课数为CourseId的个数，sum求出总成绩，将这三个字段看作一张表，多表查询，当两张表学号相同时，满足题目要求
select t2.StuId '学号',t2.StuName '姓名',t1.classCount'选课数',t1.sumScore '总成绩' from
(select StuId ,count(CourseId) classCount ,sum(Score) sumScore fromtblscore group by StuId) t1 ,tblstudent t2
where t1.StuId = t2.StuId


-- 4、查询姓“李”的老师的个数；
我的思路：like模糊查询
select count(*) as'个数' from tblteacher
where TeaName like '李%'

-- 5、查询没学过“叶平”老师课的同学的学号、姓名；
-- 原文：
Select StuId,StuName FromtblStudent Where StuId Not In(
Select StuID From tblScore sc
Inner Join tblCourse cu ON sc.CourseId=cu.CourseId
Inner Join tblTeacher tc ONcu.TeaId=tc.TeaId    
Where tc.TeaName='叶平'
)

我的思路：先查询出叶平老师负责的课程，在通过CourseId查询出学生ID，判断那个学生不在这个ID范围内（子查询太多，效率不高，应该多用多表查询）
select t1.StuId,t1.StuName from tblstudent t1 where t1.StuIdnot in(
select StuId from tblscore where CourseId in(
select CourseId from tblcourse
where TeaId = (select TeaId from tblteacher where TeaName ='叶平')))

所以可以用这个（三张表多表），与原作者方法类似，只是减去了inner join ...on
select StuId,StuName
from tblstudent where StuId not in(
select StuId from
tblteacher te ,tblcourse co,tblscore sc
where te.TeaId = co.TeaId and co.CourseId = sc.CourseId andte.TeaName="叶平"
)

-- 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select  t.StuId,t.StuName from tblstudent as twhere StuId in(
select t1.StuId from
(Select * from tblscore where CourseId='001' ) t1,
(Select * from tblscore where CourseId='002') t2
where t1.StuId = t2.StuId)
关键思路：先查询出学过001的，在查询出学过002的，当两者stuid相同时为同一个人同时满足两个条件，从而获得StuId

-- 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
Select StuId,StuName From tblStudent st Where not exists(
Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaID
Where tc.TeaName='叶平' And CourseID not in
(Select CourseID From tblScore Where StuID=st.StuID)
)
我的思路：先查询出叶平老师负责的课程，在通过CourseId查询出学生ID，判断那个学生不在这个ID范围内
-- 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select StuId,StuName from tblstudent ts where
(select Score from tblscore t1 where t1.StuId=ts.StuId andCourseId='001')
>
(select Score from tblscore t2 where t2.StuId=ts.StuId andCourseId='002')

我的思路：上下面的我都用过，说下面的把，三张表多表查询，两个id相等为了保证三张表中的数据是同一个学生的数据，然后在比较条件（下面应该效率没有上面高）
selectst.StuId,st.StuName from tblstudent st ,
(select * from tblscore  where CourseId='002' )t1,
(select * from tblscore  where CourseId='001' )t2
where st.StuId = t1.StuId and t1.StuId =t2.StuId andt1.Score<t2.Score

-- 9、查询所有课程成绩小于60分的同学的学号、姓名；
-- 原文：
Select StuId,StuName From tblStudent st
Where StuId Not IN
(Select StuId From tblScore sc Where st.StuId=sc.StuId AndScore>60)

我的思路：先查询所有成绩大于60的（因为要求是所有课程，那么只要有一个课程大于60，就不符合规定，逆向解决），如果不在这个范围内，实现需求
select StuId,StuName from tblStudent st
where StuId not in(
select StuId from tblscore sc where st.StuId=sc.StuId andscore>=60)

-- 10、查询没有学全所有课的同学的学号、姓名；
selecttu.StuId,tu.StuName From tblStudent tu
where (select count(*) from tblScore ts wherets.StuId=tu.StuId)<
(select count(*) from tblcourse)
思路：获得每一个学生学习的课程数量（查询学生表时，每一个stuId对应一个学生，根据学生表查处tblScore表中的数量），与总共数量进行补交

-- 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
------运用连接查询（多表查询：多表必须保证外键与主键相同，所以一般都会有类似于 st.StuId=sc.StuId的语句，之后就是条件）
select distinct st.StuId,StuName fromtblStudent st,tblScore sc
where st.StuId=sc.StuId and sc.CourseId in(
select CourseId from tblScore where StuId='1001'
)
------嵌套子查询（从后向前推）
思路：从后向前->1.查询1001同学学习过的课程(courseId)，2.那些学生(courseId)学习的课程在1的范围内
select StuId,StuName from tblstudent where StuId in(
select distinct StuId  from tblscore whereCourseId in(
select CourseId from tblscore where StuId ='1001'))

--12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；
--原始语句（好像是错的...）
Select StuId,StuName From tblStudent
Where StuId In(
Select Distinct StuId From tblScore Where CourseId Not In
(Select CourseId From tblScore Where StuId='1001'))

--我的思路：题目本意查出(1001...的课程)属于(学生所学课程),第二行查询出1001所学的课程编号，（被包含的在前面，包含的在后面，前面为多最好用not in）；第四行与第一行相对应，保证为同一个学生；利用not exists 将（）中的没有学过1001课程的同学反过来，求出结果。
select StuId,StuName from tblstudent st where not exists(
select CourseId from tblscore where StuId='1001'
and CourseId not in(
select CourseId from tblscore sc where sc.StuId = st.StuId))

--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
--创建测试表
create table sc select * from tblScore

思路：先查询出叶平所教的CourseId，查询课程的平均值在所查CourseId中.
      更新：update 表 set 字段名称=新内容 [ where 条件]
Update sc Set Score=
(Select avg(Score) from tblScore t1 where t1.CourseId = sc.CourseId )
where CourseId in(
select co.CourseId from tblteacher te,tblcourse co
where te.TeaId=co.TeaId and te.TeaName='叶平')

--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
原文：好吧！这个我目前实在做不好！先拿原文吧！过几天练好了再来！
Select StuID,StuName From tblStudent st
Where StuId <> '1002'
And
Not Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId='1002')) 
And
Not Exists(Select * From tblScore Where StuId='1002' And CourseId Not In (Select CourseId From tblScore sc Where sc.StuId=st.StuId))

--15、删除学习“叶平”老师课的SC表记录；
--思路：很简单的删除语句 :delete from 表 [where 条件]
delete from sc where CourseId in(
select co.CourseId
from tblteacher te,tblcourse co
where te.TeaId = co.TeaId and te.TeaName='叶平')

数据表的创建
应该是可以的，我用的就是这几个表
create table tblStudent(
StuId varchar(32) primary key,
StuName varchar(32),
StuAge int,
StuSex varchar(2)
);
create table tblCourse(
CourseId varchar(10) primary key,
CourseName varchar(32),
TeaId varchar(10)
);
create table tblScore(
StuId varchar(32),
CourseId varchar(10) ,
Score double(4,1)
);
create table tblTeacher(
TeaId varchar(10) primary key,
TeaName varchar(32)
);
基础语句的插入(为避免出错，建议每个insert into到go之间分段执行，也就是需要执行4次，为4张表分别插入数据）
insert into tblStudent
select '1000','张无忌',18,'男' union
select '1001','周芷若',19,'女' union
select '1002','杨过',19,'男' union
select '1003','赵敏',18,'女' union
select '1004','小龙女',17,'女' union
select '1005','张三丰',18,'男' union
select '1006','令狐冲',19,'男' union
select '1007','任盈盈',20,'女' union
select '1008','岳灵珊',19,'女' union
select '1009','韦小宝',18,'男' union
select '1010','康敏',17,'女' union
select '1011','萧峰',19,'男' union
select '1012','黄蓉',18,'女' union
select '1013','郭靖',19,'男' union
select '1014','周伯通',19,'男' union
select '1015','瑛姑',20,'女' union
select '1016','李秋水',21,'女' union
select '1017','黄药师',18,'男' union
select '1018','李莫愁',18,'女' union
select '1019','冯默风',17,'男' union
select '1020','王重阳',17,'男' union
select '1021','郭襄',18,'女'
go

insert  into tblTeacher
select '001','姚明' union
select '002','叶平' union
select '003','叶开' union
select '004','孟星魂' union
select '005','独孤求败' union
select '006','裘千仞' union
select '007','裘千尺' union
select '008','赵志敬' union
select '009','阿紫' union
select '010','郭芙蓉' union
select '011','佟湘玉' union
select '012','白展堂' union
select '013','吕轻侯' union
select '014','李大嘴' union
select '015','花无缺' union
select '016','金不换' union
select '017','乔丹'
go

insert into tblCourse
select '001','企业管理','002' union
select '002','马克思','008' union
select '003','UML','006' union
select '004','数据库','007' union
select '005','逻辑电路','006' union
select '006','英语','003' union
select '007','电子电路','005' union
select '008','毛泽东思想概论','004' union
select '009','西方哲学史','012' union
select '010','线性代数','017' union
select '011','计算机基础','013' union
select '012','AUTO CAD制图','015' union
select '013','平面设计','011' union
select '014','Flash动漫','001' union
select '015','Java开发','009' union
select '016','C#基础','002' union
select '017','Oracl数据库原理','010'
go

insert into tblScore
select '1001','003',90 union
select '1001','002',87 union
select '1001','001',96 union
select '1001','010',85 union
select '1002','003',70 union
select '1002','002',87 union
select '1002','001',42 union
select '1002','010',65 union
select '1003','006',78 union
select '1003','003',70 union
select '1003','005',70 union
select '1003','001',32 union
select '1003','010',85 union
select '1003','011',21 union
select '1004','007',90 union
select '1004','002',87 union
select '1005','001',23 union
select '1006','015',85 union
select '1006','006',46 union
select '1006','003',59 union
select '1006','004',70 union
select '1006','001',99 union
select '1007','011',85 union
select '1007','006',84 union
select '1007','003',72 union
select '1007','002',87 union
select '1008','001',94 union
select '1008','012',85 union
select '1008','006',32 union
select '1009','003',90 union
select '1009','002',82 union
select '1009','001',96 union
select '1009','010',82 union
select '1009','008',92 union
select '1010','003',90 union
select '1010','002',87 union
select '1010','001',96 union

select '1011','009',24 union
select '1011','009',25 union

select '1012','003',30 union
select '1013','002',37 union
select '1013','001',16 union
select '1013','007',55 union
select '1013','006',42 union
select '1013','012',34 union
select '1000','004',16 union
select '1002','004',55 union
select '1004','004',42 union
select '1008','004',34 union
select '1013','016',86 union
select '1013','016',44 union
select '1000','014',75 union
select '1002','016',100 union
select '1004','001',83 union
select '1008','013',97
go