本公众号使用SQL SERVER2014测试。SQL语句可能因数据库系统不同而存在少许差异。
数据表
--1.学生表
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CId,Cname,TId)
--CId --课程编号,Cname 课程名称,TId 教师编号
--3.教师表
Teacher(TId,Tname)
--TId 教师编号,Tname教师姓名
--4.成绩表
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数
创建测试数据
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' ,'男');insert into Student values('02' , '钱电' , '1990-12-21' ,'男');insert into Student values('03' , '孙风' , '1990-05-20' ,'男');insert into Student values('04' , '李云' , '1990-08-06' ,'男');insert into Student values('05' , '周梅' , '1991-12-01' ,'女');insert into Student values('06' , '吴兰' , '1992-03-01' ,'女');insert into Student values('07' , '郑竹' , '1989-07-01' ,'女');insert into Student values('09' , '张三' , '2017-12-20' ,'女');insert into Student values('10' , '李四' , '2017-12-25' ,'女');insert into Student values('11' , '李四' , '2017-12-30' ,'女');insert into Student values('12' , '赵六' , '2017-01-01' ,'女');insert into Student values('13' , '孙七' , '2018-01-01' ,'女');
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))insert into Course values('01' , '语文' , '02')insert into Course values('02' , '数学' , '01')insert into Course values('03' , '英语' , '03')
教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10))insert into Teacher values('01' , '张三')insert into Teacher values('02' , '李四')insert into Teacher values('03' , '王五')
成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80)insert into SC values('01' , '02' , 90)insert into SC values('01' , '03' , 99)insert into SC values('02' , '01' , 70)insert into SC values('02' , '02' , 60)insert into SC values('02' , '03' , 80)insert into SC values('03' , '01' , 80)insert into SC values('03' , '02' , 80)insert into SC values('03' , '03' , 80)insert into SC values('04' , '01' , 50)insert into SC values('04' , '02' , 30)insert into SC values('04' , '03' , 20)insert into SC values('05' , '01' , 76)insert into SC values('05' , '02' , 87)insert into SC values('06' , '01' , 31)insert into SC values('06' , '03' , 34)insert into SC values('07' , '02' , 89)insert into SC values('07' , '03' , 98)各表查询结果
练习题目
1.1 查询同时存在" 01 "课程和"02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 "课程但存在"02 "课程的情况
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3. 查询在 SC 表存在成绩的学生信息
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1 查有成绩的学生信息
5. 查询「李」姓老师的数量
6. 查询学过「张三」老师授课的同学的信息
7. 查询没有学全所有课程的同学的信息
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
参考答案
--------------------------------------------------------
--1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select a.*,b.CId, b.score ,c.CId,c.score from Student a ,SC b,SC c where a.SId = b.SId and b.CId=01 and a.SId=c.SId and c.CId=02 and b.score>c.score--1.1 查询同时存在" 01 "课程和"02 "课程的情况
select a.*,b.CId,b.score from SC a ,SC b where a.SId=b.SId and a.CId=01 and b.CId=02--1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select t1.*,t2.CId,t2.score from (select * from SC a where a.CId=01) t1 left join (select * from SC b where b.CId=02) t2 on t1.sid=t2.SId;--1.3 查询不存在" 01 "课程但存在"02 "课程的情况
select * from sc a where a.SId not in (select b.SId score from SC b where b.CId=01) and a.CId=02--2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.SId as 学生编号,a.Sname as 学生姓名,avg(b.score) as '平均成绩' from Student a,SC b where a.SId=b.SId group by a.SId,a.Sname having avg(b.score)>=60--3. 查询在 SC 表存在成绩的学生信息
select DISTINCT student.*from student,scwhere student.SId=sc.SId--4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.SId,a.Sname,count(b.CId) as 选课总数,sum(b.score) as 总成绩 from Student a left join SC b on a.SId = b.SIdgroup by a.SId ,a.Sname order by a.sid--4.1 查有成绩的学生信息
select * from Student a where a.SId in (select sid from sc)--5. 查询「李」姓老师的数量
select count(*) from Teacher a where a.Tname like'李%%'
--6. 查询学过「张三」老师授课的同学的信息
select * from Student a where a.SId in (select sid from sc where CId in (select CId from Course where TId in (select TId from Teacher where Tname='张三')))--7. 查询没有学全所有课程的同学的信息
select * from Student a where a.sid in (select sid from sc group by sid having count(*) < (select count(*) from Course))--8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct a.* from Student a,sc b where a.SId=b.SId and b.CId in (select CId from sc where SId=01)--9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from Student where sid in (select tt.sid from (select t1.SId,t2.CId from Student t1 ,(select sc.CId from sc where sc.SId=01) t2) tt left join SC on tt.CId=sc.CId and tt.SId=sc.SIdwhere sc.SId is nullgroup by tt.SId)--10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select tt.Sname from Student tt where tt.Sname not in ( select t1.Sname from Student t1,sc t2 where t1.SId=t2.SId and t2.CId in (select b.CId from Teacher a,Course b where a.TId=b.TId and a.Tname='张三'))来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31537575/viewspace-3170908/ ，如需转载，请注明出处，否则将追究法律责任。
