CREATE TABLE Student
( Sno CHAR(9) PRIMARY KEY, --列级完整性约束条件
Sname CHAR(20) UNIQUE, --Sname取唯一值
Ssex CHAR(2) ,
Sage SMALLINT,
Sdept CHAR(20)
)
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
)
CREATE TABLE SC
( Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), --主码由两个属性构成，必须作为表级完整性进行定义
FOREIGN KEY(Sno) REFERENCES Student(Sno), --表级完整性约束条件，Sno是外码，被参照表是Student

FOREIGN KEY(Cno) REFERENCES Course(Cno) --表级完整性约束条件， Cno是外码，被参照表是Course
)

insert into Student values(‘200215121‘,‘李勇‘,‘男‘,‘20‘,‘CS‘)
insert into Student values(‘200215122‘,‘刘晨‘,‘女‘,‘19‘,‘CS‘)
insert into Student values(‘200215123‘,‘王敏‘,‘女‘,‘18‘,‘MA‘)
insert into Student values(‘200215125‘,‘张立‘,‘男‘,‘19‘,‘IS‘)

insert into Course values(‘1‘,‘数据库‘, null,4)
insert into Course values(‘2‘,‘数学‘,null,2)
insert into Course values(‘3‘,‘信息系统‘, ‘1‘,4)
insert into Course values(‘4‘,‘操作系统‘, null,3)
insert into Course values(‘6‘,‘数据处理‘,null,2)
insert into Course values(‘7‘,‘PASCAL语言‘, ‘6‘,4)
insert into Course values(‘5‘,‘数据结构‘, ‘7‘,4)
update Course
set Cpno=‘5‘ where Cno=‘1‘
update Course
set Cpno=‘6‘ where Cno=‘4‘

insert into SC values(‘200215121‘,‘1‘,92)
insert into SC values(‘200215121‘,‘2‘,85)
insert into SC values(‘200215121‘,‘3‘,88)
insert into SC values(‘200215122‘,‘2‘,90)
insert into SC values(‘200215122‘,‘3‘,80)
insert into SC values(‘200215123‘,‘4‘,87)
insert into SC values(‘200215123‘,‘5‘,92)

select * from Student
select * from Course
select * from SC

CREATE TABLE Gpa
(Cno CHAR(4),
Average SMALLINT,
)

insert into Gpa(Cno ,Average)
select Student.Sdept,ROUND(AVG(SC.Grade),2)AS Average
from SC INNER join Student
on Student.Sno=SC.Sno
group by Sdept

select * from Gpa

select COUNT(Cno) as Scno, round(AVG(sc.Grade),2) AS ‘average‘
from Student inner join SC
on Student.Sno=SC.Sno
group by Cno

select Sno,Sname,Sage,Sdept
from Student
where Sage in (select MAX(Sage) from Student group by Student.Sdept
)

select Sdept, COUNT(Sdept) as ‘count‘
from Student
group by Sdept
order by count

select Sdept, AVG(Sage) as ‘avgage‘
from Student
group by Sdept
order by avgage desc

select SUM(Ccredit) AS ‘无选修总学分‘
from Course
where Cpno is null

select Sno,COUNT(SC.Sno)as ‘门数‘,AVG(Grade)as ‘平均成绩‘,SUM(Ccredit)as ‘总学分‘
from SC inner join Course
on SC.Cno=Course.Cno
group by Sno

select Student.Sno ,Sname,Course.Cno,Cname,Grade
from Student inner join SC
on Student.Sno =SC.Sno
inner join Course
on SC.Cno=Course.Cno
where SC.Cno is not null

/*select Sdept,AVG(Grade) as Gpa
from SC inner join Student
on Student.Sno=SC.Sno
where Gpa>85
group by SC.Sno
order by Gpa*/

select SC.Sno, Sname ,Cno from
Student inner join SC
on Student.Sno= SC.Sno
where Cno=1 or Cno=2

select SC.Sno, Sname from
Student inner join SC
on Student.Sno= SC.Sno
where Cno=‘1‘ and SC.Sno in (select Sno from SC where Cno=‘2‘);

select Student.Sno,Sname ,Grade from
Student inner join SC
on Student.Sno = SC.Sno
inner join Course
on SC.Cno=Course.Cno
where Cname = ‘数据库‘ and Grade <60

select Student.Sno ,Ssex,Sage,Sdept,Sname,Course.Cno,Cname,Grade
from Student full join SC
on Student.Sno =SC.Sno
full join Course
on SC.Cno=Course.Cno
where Course.Cno is null

select Sno
from SC
group by Sno
HAVING COUNT(Sno)>=3

select Sno
from SC
where Grade>=80

数据库实验