- drop table class
- create table class (
- classId nchar(6) primary key not null,
- cName nvarchar(20)
- )
- insert into class
- values('09033','自动化 1 班')
- insert into class
- values('09031','计算机 1 班'),
- ('09032','计算机 2 班'),
- ('09034','自动化 2 班')
- select * from class
- delete class where classId='09033'
- delete class where classId='09031'
- delete class where classId='09032'
- delete class where classId='09034'
- update class set cName='自动化 3 班'
- where classId=09034
- alter table student
- alter column classId nchar(6)not null
alter table student -- 给 student 的 classId 属性增加一个外键
add constraint classId foreign key(classId) references class(classId)
alter table student -- 删掉该外键约束
drop constraint classId
-- 然后才能删掉 class
- drop table class
- select * from course
- select * from score
- select * from student
- select * from teacher
-- 检查学号为 107 的学生的课程名, 任课老师
- select ci.cName,ti.teaName from student as si
- inner join score as sco on sco.stuId=si.stuId
- inner join course as ci on ci.cId=sco.cId
- inner join teacher as ti on ti.teaId=ci.teaId
- where si.stuId=107
-- 检索王同学不学习且不助教的任课老师和课程名
- select ti.teaName,ci.cName from course as ci
- inner join teacher as ti on ti.teaId=ci.teaId
- except (
-- 王同学学习的课程及任课老师
- select ti.teaName,ci.cName from student as si
- inner join score as sco on sco.stuId=si.stuId
- inner join course as ci on ci.cId=sco.cId
- inner join teacher as ti on ci.teaId=ti.teaId
- where si.stuName like '王 %'
- union
-- 王同学助教的任课老师的课
- select ti.teaName,ci.cName from course as ci
- inner join teacher as ti on ci.teaId=ti.teaId
- where ti.teaWork='助教' and ti.teaName like '王 %'
- )
-- 检索至少选修两门课程的学生学号
- select sco.stuId,COUNT(*) from score as sco
- group by sco.stuId having COUNT(*)>1
-- 子查询
-- 按学号列出每个学生所选修课程中最高分的课程名称及其分数
- select si.stuId,ci.cName,sco.score
- from student as si
- inner join score as sco on sco.stuId=si.stuId
- inner join course as ci on ci.cId=sco.cId
- inner join teacher as ti on ci.teaId=ti.teaId
- where sco.score>=
- (
- select MAX(ssco.score) from student as ssi
- inner join score as ssco on ssco.stuId=ssi.stuId
- inner join course as cci on cci.cId=ssco.cId
- inner join teacher as tti on cci.teaId=tti.teaId
- where ssi.stuName=si.stuName
- )
- order by si.stuId asc
来源: http://www.bubuko.com/infodetail-3280597.html