drop table class

create table class (

    classId nchar(6) primary key not null,

    cName nvarchar(20)

)

insert into class

values('','自动化1班')

insert into class

values('','计算机1班'),

('','计算机2班'),

('','自动化2班')

select * from class

delete class where classId=''

delete class where classId=''

delete class where classId=''

delete class where classId=''

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