SQL基础范例（学生课程系统）

drop?table?student,course,score,teacher

CREATE?TABLE?STUDENT??--学生表
?(SNO?VARCHAR(3)?NOT?NULL,???--学号
??SNAME?nVARCHAR(4)?NOT?NULL,???--姓名
??SSEX?nVARCHAR(2)?NOT?NULL,????--性别
??SBIRTHDAY?DATETIME,???--出生年月
??CLASS?VARCHAR(5))?????--班级编号
go
CREATE?TABLE?COURSE???????--课程表
?(CNO?VARCHAR(5)?NOT?NULL,????????--课程ID
??CNAME?nVARCHAR(10)?NOT?NULL,????--课程名称
??TNO?VARCHAR(10)?NOT?NULL)???--教师ID
go?
CREATE?TABLE?SCORE?????--成绩表
?(SNO?VARCHAR(3)?NOT?NULL,????--成绩ID
??CNO?VARCHAR(5)?NOT?NULL,???--课程编号
??DEGREE?NUMERIC(10,?1)?NOT?NULL)?????--分数
go?
CREATE?TABLE?TEACHER???--教师表
?(TNO?VARCHAR(3)?NOT?NULL,????--教师ID
??TNAME?nVARCHAR(4)?NOT?NULL,?--教师姓名
??TSEX?nVARCHAR(2)?NOT?NULL,???--性别
??TBIRTHDAY?DATETIME?NOT?NULL,?--出生年月??
??PROF?nVARCHAR(6),?--级称
??DEPART?nVARCHAR(10)?NOT?NULL)??--所属系部
go
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(108?,N'曾华'?,N'男'?,1977-09-01,'95033');
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(105?,N'匡明'?,N'男'?,1975-10-02,'95031');
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(107?,N'王丽'?,N'女'?,1976-01-23,'95033');
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(101?,N'李军'?,N'男'?,1976-02-20,'95033');
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(109?,N'王芳'?,N'女'?,1975-02-10,'95031');
INSERT?INTO?STUDENT?(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)?VALUES?(103?,N'陆君'?,N'女'?,1974-06-03,'95031');
GO
INSERT?INTO?COURSE(CNO,CNAME,TNO)VALUES?('3-105'?,N'计算机导论',825)
INSERT?INTO?COURSE(CNO,CNAME,TNO)VALUES?('3-245'?,N'操作系统'?,804);
INSERT?INTO?COURSE(CNO,CNAME,TNO)VALUES?('6-166'?,N'数据电路'?,856);
INSERT?INTO?COURSE(CNO,CNAME,TNO)VALUES?('9-888'?,N'高等数学'?,100);
GO
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(103,'3-245',86);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(105,'3-245',75);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(109,'3-245',68);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(103,'3-105',92);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(105,'3-105',88);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(109,'3-105',76);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(101,'3-105',64);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(107,'3-105',91);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(108,'3-105',78);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(101,'6-166',85);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(107,'6-166',79);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(108,'6-166',80);
INSERT?INTO?SCORE(SNO,CNO,DEGREE)VALUES?(105,'6-166',82);
GO
INSERT?INTO?TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?
????VALUES?(804,N'李诚',N'男','1958-12-02',N'副教授',N'计算机系');
INSERT?INTO?TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?
????VALUES?(856,N'张旭',N'男','1969-03-12',N'讲师',N'电子工程系');
INSERT?INTO?TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
????VALUES?(825,N'王萍',N'女','1972-05-05',N'助教',N'计算机系');
INSERT?INTO?TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?
????VALUES?(831,N'刘冰',N'女','1977-08-14',N'助教',N'电子工程系');


go?
--1、?查询Student表中的所有记录的Sname、Ssex和Class列。
select?sname,ssex,class?from?student
--2、?查询教师所有的单位即不重复的Depart列。
select?distinct?depart?from?teacher
--3、?查询Student表的所有记录。
select?*?from?student
--4、?查询Score表中成绩在60到80之间的所有记录。
select?*?from?score?where?degree?between?60?and?80
--5、?查询Score表中成绩为85，86或88的记录。
select?*?from?score?where?degree?in?(85,86,88)

select?*?from?score?where?degree?=85?or?degree?=?86?or?degree?=?88
--6、?查询Student表中“95031”班或性别为“女”的同学记录。
select?*?from?student?where?class?='95031'?or?ssex=?N'女'

--7、?以Class降序查询Student表的所有记录。
select?*?from?student?order?by?Class?DESC
--8、?以Cno升序、Degree降序查询Score表的所有记录。
select?*?from?score?order?by?Cno?ASC,Degree?DESC
--9、?查询“95031”班的学生人数。
select?count(*)?from?student?where?class?=?'95031'
--10、查询Score表中的最高分的学生学号和课程号。
select?distinct?s.Sno,c.Cno?from?student?as?s,course?as?c?,score?as?sc?
where?s.sno=(select?sno?from?score?where?degree?=?(select?max(degree)?from?score))
and?c.cno?=?(select?cno?from?score?where?degree?=?(select?max(degree)?from?score))

--11、查询‘3-105’号课程的平均分。
select?avg(degree)?from?score?where?cno?=?'3-105'
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select?avg(degree)?from?score?where?cno?like?'3%'?group?by?Cno?having?count(*)>=5
--13、查询最低分大于70，最高分小于90的Sno列。
select?sno?from?score?group?by?sno?having?min(degree)?>?70?and?max(degree)?<?90

select?distinct?sno?from?score?a?where(select?min(degree)?from?score?where?sno?=?a.sno)?>?70?and
(select?max(degree)?from?score?where?sno?=?a.sno)?<?90?

--=================================

go
--14、查询所有学生的Sname、Cno和Degree列。
select?s.sname,sc.cno,sc.degree?from?student?as?s,score?as?sc?where?s.sno?=?sc.sno
--15、查询所有学生的Sno、Cname和Degree列。
select?sc.sno,c.cname,sc.degree?from?course?as?c,score?as?sc?where?c.cno?=?sc.cno
--16、查询所有学生的Sname、Cname和Degree列。
select?s.sname,c.cname,sc.degree?from?student?as?s,course?as?c,score?as?sc?where?s.sno?=?sc.sno?and?c.cno?=?sc.cno

--17、查询“95033”班所选课程的平均分。
select?avg(degree)?from?score?where?sno?in?(select?sno?from?student?where?class?=?'95033')
--18、假设使用如下命令建立了一个grade表：
??create?table?grade(low?numeric(3,0),upp??numeric(3),rank??char(1))
??insert?into?grade?values(90,100,'A')
??insert?into?grade?values(80,89,'B')
??insert?into?grade?values(70,79,'C')
??insert?into?grade?values(60,69,'D')
??insert?into?grade?values(0,59,'E')
??commit;
--================go
select?*?from?student
select?*?from?course
select?*?from?score
select?*?from?teacher
select?*?from?grade
--??现查询所有同学的Sno、Cno和rank列。
select?sno,cno,
(case??when?degree>90?then?'A'
????when?degree>80?then?'B'
????when?degree>70?then?'C'
???????when?degree>60?then?'D'
????else?'E'?end)
from?score?
--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select?*?from?student?where?sno?in(select?sno?from?score?where??degree?>?
(select?degree?from?score?where?sno='109'?and?cno='3-105'))
--20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select?sno,degree?from?score?a?where?not?exists?
(select?*?from?score?where?sno?=?a.sno?and?degree?<?a.degree)?

select?sno,degree?from?score?

select?sno,degree?from?score?a?where?sno?in?(select?sno?