T-SQL复习之旅

table Student; drop table Course; drop table Score; drop table Teacher create table Student (ID integer not null identity, Name nvarchar(20), Age int, Sex nvarchar(8)); create table Course(ID integer not null primary key identity, Name nvarchar(20), TeacherID int); create table Score(StudentID int, CourseID int,Score float); create table Teacher(ID integer not null primary key identity, Name nvarchar(20)); 创建表

插入数据

insert into Student select N‘刘一‘, 18, N‘男‘ union all select N‘钱二‘, 19, N‘女‘ union all
select N‘张三‘, 17, N‘男‘ union all select N‘李四‘, 18, N‘女‘ union all
select N‘王五‘, 17, N‘男‘ union all select N‘赵六‘, 19, N‘女‘

insert into Teacher select N‘叶平‘ union all select N‘贺高‘ union all select N‘杨艳‘ union all select ‘周磊‘ 
union all select ‘周全‘ union all select ‘杨帆‘

insert into Course select ‘语文‘,1 union all select ‘数学‘,2 union all select ‘英语‘,3 union all select ‘物理‘,4
union all select ‘数据库‘,5 union all select ‘企业管理‘,6
select * from Course

insert into Score 
select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all 
select 2,1,79 union all select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all 
select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all
select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all
select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all
select 6,1,35 union all select 6,2,68 union all select 6,4,71

insert into Score
select 5,5,46 union all select 5,6,78 union all
select 6,5,35 union all select 6,6,68
select * from Score order by StudentID

插入数据

简单查询

--1.查询不及格的课程,并按课程号从大到小排序    （简单）
select distinct CourseID from Score where Score<60 order by CourseID desc

--2.检索“4”课程分数小于60，按分数降序排序的同学学号    (简单)
select StudentID from Score where CourseID=4 and Score<60 order by StudentID desc

--3.查询姓“李”的老师的个数    （简单）
select count(ID) as "个数" from Teacher where Name like ‘李%‘;

--4.查询姓"张"的学生名单 （简单）
select ID as ‘学号‘, Name as ‘姓名‘, Age as ‘年龄‘, Sex as ‘性别‘ from Student where Name like ‘张%‘

--5.查询每门课程被选修的学生数    （简单）
select CourseID as ‘课程ID‘, count(StudentID) as ‘学生数‘ from Score group by CourseID

--6.检索至少选修两门课程的学生学号    (简单)
select StudentID as ‘学号‘ from Score group by StudentID having count(CourseID)>=2

--7.删除"2"同学的"1"课程的成绩（简单）
delete from Score where StudentID=2 and CourseID=1

--8.删除学习"叶平"老师课的Score记录（简单）
delete from Score where CourseID=(select cc.ID from Course cc, Teacher t where cc.TeacherID=t.ID and t.Name=‘叶平‘)

--9.查询所有学生的选课情况    （简单）
select sc.StudentID as ‘学号‘,st.Name as ‘姓名‘,sc.CourseID as ‘课程号‘,cc.Name as ‘课程名‘ 
from Score sc, Student st, Course cc where sc.StudentID=st.ID and sc.CourseID=cc.ID

--10.查询任何一门课程成绩在70分以上的姓名，课程名和分数        （简单）
select st.Name as ‘姓名‘, cc.Name as ‘课程名‘, sc.Score as ‘分数‘ from Student st, Course cc, Score sc
where sc.CourseID=cc.ID and sc.StudentID=st.ID and sc.Score>70

--11.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名    （简单）
select st.ID as ‘学号‘, st.Name as ‘姓名‘ from Student st, Score sc
where st.ID=sc.StudentID and sc.Score>80 and sc.CourseID=3

--12.求没选课的学生人数    （简单）
select count(ID) as ‘学生人数‘ from Student where ID not in (select StudentID from Score)

--13.统计每门课程的学生选修人数和课程号。要求按照人数降序排序    (简单)
select CourseID as ‘课程号‘, count(StudentID) as ‘选修人数‘ from Score group by CourseID order by COUNT(StudentID) desc, CourseID

--14.查询所有同学的学号，姓名，选课数，总成绩    （简单）
select st.ID as ‘学号‘, st.Name as ‘姓名‘, COUNT(sc.CourseID) as "选课数",SUM(sc.Score) as "总分数" from 
Student st left join Score sc on st.ID=sc.StudentID 
group by st.ID, st.Name order by ‘学号‘;

--15.查询所有课程成绩小于60分的同学的学号，姓名（简单）
select st.ID as ‘学号‘, st.Name as ‘姓名‘ from Student st where st.ID in 
(select StudentID from Score where Score<60)

--16.查询课程名称为“数学”且分数低于60分的学生姓名和分数(简单)
select st.Name as ‘姓名‘, sc.Score as ‘分数‘ from Student st, Score sc where st.ID=sc.StudentID and sc.Score<60 and 
sc.CourseID=(select ID from Course where Name=‘数学‘)

--17.查询不同老师所教不同课程平均分从高到低显示（简单）
select t.Name as ‘教师名‘, avg(sc.Score) as ‘平均分‘ from Score sc, Course cc, Teacher t 
where sc.CourseID =cc.ID and cc.TeacherID=t.ID group by t.Name order by ‘平均分‘ desc

简单查询

复杂查询

--解析：同表数据比较。
--1.查询“1”课程比“2”课程成高的所有学生的学号,姓名,课程分数        （重要）
select s1.StudentID as ‘学号‘,st.Name as ‘姓名‘, s1.Score as ‘语文成绩‘, s2.Score as ‘数学成绩‘ from Score s1, Score s2, Student st
where s1.Score>s2.Score and s1.CourseID=1 and s2.CourseID=2 and s1.StudentID=s2.StudentID and st.ID=s1.StudentID

select st.ID as ‘学号‘, st.Name as ‘姓名‘,s1.Score as ‘语文分数‘, s2.Score as ‘数学分数‘ from 
(select StudentID, Score from Score where CourseID=1) s1,
(select StudentID, Score from Score where CourseID=2) s2 inner join
Student st on st.ID=s2.StudentID where s1.Score>s2.Score and s1.StudentID=s2.StudentID

--2.查询同姓名学生名单,并统计同名人数        （重要）
select Name as ‘姓名‘, COUNT(Name) as ‘人数‘ from Student group by Name having COUNT(Name)>1

select A.Name as ‘姓名‘,COUNT(A.ID) as ‘人数‘ from Student A, Student B where A.Name=B.Name and A.ID!=B.ID group by A.Name

--3.查询不同课程成绩相同的学生的学号，课程号，学生成绩
select distinct A.StudentID as ‘学号‘, A.CourseID as ‘课程号‘, B.Score as ‘学生成绩‘
from Score A, Score B where A.Score=B.Score and A.CourseID!=B.CourseID order by B.Score

--4.查询学过“1”并也学过编号“2”课程的同学的学号，姓名
select ID as ‘学号‘, Name as ‘姓名‘ from Student where ID in
(select s1.StudentID from Score s1 inner join Score s2 on s1.StudentID=s2.StudentID where s1.CourseID=1 and s2.CourseID=2)

select st.ID as ‘学号‘, st.Name as ‘姓名‘ from Student st, Score sc where st.ID=sc.StudentID and sc.CourseID=1 and 
exists(select * from Score as sc2 where sc2.StudentID=sc.StudentID and sc2.CourseID=2)


--解析：where中不能有聚合函数，就用having代替
--5.查询平均成绩大于60的同学的学号，姓名和平均成绩        （重要）
--select StudentID, avg(Score) as "平均成绩" from Score group by StudentID having avg(Score)>60;
select st.ID as ‘学号‘, st.Name as ‘姓名‘, avg(Score) as "平均成绩" from Student st join Score sc on st.ID=sc.StudentID 
group by st.ID, st.Name having avg(sc.Score)>60;

--6.查询全部学生都选修的课程的课程号和课程名    (重要)
select ID as ‘课程号‘, Name as ‘课程名‘ from Course where ID in (select CourseID from Score group by CourseID 
having count(StudentID)=(select count(ID) from Student))

--7.查询两门以上不及格课程的学生的学号及平均成绩    (重要)
select StudentID as ‘学号‘, avg(Score) as ‘平均成绩‘ from Score where StudentID in
(select StudentID from Score where Score<60 group by StudentID having COUNT(CourseID)>=2) group by StudentID

--8.查询和2号同学学习的课程完全相同的其他同学学号和姓名（重要）
select ID as ‘学号‘, Name as ‘姓名‘ from Student where ID
in (select StudentID from Score where CourseID in (select CourseID from Score where StudentID=2) and StudentID !=2
group by StudentID having count(CourseID)=(select count(CourseID) from Score where StudentID=2))

--9.查询平均成绩大于85的所有学生的学号，姓名和平均成绩
select st.ID as ‘学号‘, st.Name as ‘姓名‘, avg(sc.Score) as ‘平均成绩‘ from Student st, Score sc
where st.ID=sc.StudentID group by st.ID, st.Name having avg(sc.Score)>85

--10.查询没有学全所有课程的同学的学号，姓名
select st.ID as ‘学号‘, st.Name as ‘姓名‘ from Student st where st.ID in
(select StudentID from Score group by StudentID having count(CourseID)<(select count(ID) from Course))

--11.查询出只选修了一门课程的全部学生的学号和姓名
select sc.StudentID as ‘学号‘, st.Name as ‘姓名‘ from Score sc, Student st where sc.StudentID=st.ID
group by sc.StudentID,st.Name having COUNT(CourseID)=1

--12.查询没有选修课程的学生的学号和姓名
select st.ID as ‘学号‘, st.Name as ‘姓名‘ from Score sc right join Student st on sc.StudentID=st.ID
group by st.ID,st.Name having COUNT(CourseID)=0



--13.把Score表中“叶平”老师教的课的成绩都更改为此课程的平均成绩（重要）
update Score set Score=s1.Score2 from (select avg(Score) as Score2, sc.CourseID as CID from 
Score sc,Course cc, Teacher t where sc.CourseID=cc.ID and cc.TeacherID=t.ID and t.Name=‘叶平‘ group by CourseID) s1
where Score.CourseID=s1.CID
--update的使用


--14.向Score表中插入一些记录，这些记录要求符合以下条件：没有上过编号3课程的同学学号,3,2号课的平均成绩（重要）
insert into Score values((select ID from Student where ID not in(select StudentID from Score where CourseID=3)),3,
(select avg(Score) from Score where CourseID=2))
--insert的使用

--15.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门课程成绩，按如下形式显示：学生ID,语文，数学，英语，有效课程数，有效平均分 
select sc.StudentID as ‘学生ID‘, 
(ISNULL((select Score from Score where CourseID =(select ID from Course where Name=‘语文‘) and StudentID=sc.StudentID),0)) as ‘语文‘, 
(ISNULL((select Score from Score where CourseID =(select ID from Course where Name=‘数学‘) and StudentID=sc.StudentID),0)) as ‘数学‘, 
(ISNULL((select Score from Score where CourseID =(select ID from Course where Name=‘英语‘) and StudentID=sc.StudentID),0)) as ‘英语‘, 
count(CourseID) as ‘有效课程数‘, 
avg(sc.Score) as ‘有效平均分‘
from Score sc group by sc.StudentID order by ‘有效平均分‘ desc

--16.查询各科成绩最高和最低的分。以如下形式显示：课程ID，最高分，最低分
select CourseID as ‘课程ID‘, MAX(Score) as ‘最高分‘, MIN(Score) as ‘最低分‘ from Score group by CourseID

--17.查询每门课程的平均成绩，结果按照平均成绩升序排序，平均成绩相同时，按照课程号降序排序
select CourseID as ‘课程号‘, avg(Score) as ‘平均分‘ from Score group by CourseID order by avg(Score), CourseID desc


--计数可以用sum(case when ... then ... else ... end) 进行
--18.查询如下课程平均成绩和及格率的百分数：语文，数学（重要）
select sum(case when CourseID=1 then Score else 0 end)/sum(case CourseID when 1 then 1 else 0 end) as ‘语文平均分‘,
100*sum(case when CourseID=1 and Score>=60 then 1 else 0 end)/sum(case when CourseID=1 then 1 else 0 end) as ‘语文及格百分数(%)‘,


                  

	 	
                    

                        

                            

                                
< 上一篇
MySql 新建用户与数据库的实际操作步骤
                            
                            

                                 
下一篇 >
大数据学习总结记录—SSDB