数据库—操作

Create Table Student(
sno char(10) not null,
sname varchar(20),
sage smallint,
ssex char(1),
sdept char(2),
primary key (sno),
check(ssex in ('M','F')))


Create Table Course(
cno char(10) not null,
cname varchar(20),
cpno char(10) null,
credit smallint,
primary key (cno),
foreign key (cpno) references Course)

Create Table SC(
sno char(10) not null,
cno char(10) not null,
grade smallint,
primary key(sno,cno),
foreign key (sno) references Student,
foreign key (cno) references Course,
check(grade>=0 and grade<=100))

Insert into Student(sno,sname,sage,ssex,sdept)
values('95001','李勇',20,'M','CS');
Insert into Student(sno,sname,sage,ssex,sdept)
values('95002','刘晨',19,'F','IS');
Insert into Student(sno,sname,sage,ssex,sdept)
values('95003','王敏',18,'F','MA');
Insert into Student(sno,sname,sage,ssex,sdept)
values('95004','张立',19,'M','IS');

Insert into Course(cno,cname,cpno,credit)
Values('2','数学',null,2);
Insert into Course(cno,cname,cpno,credit)
Values('6','数据处理',null,2);
Insert into Course(cno,cname,cpno,credit)
Values('7','PASCAL语言','6',4);
Insert into Course(cno,cname,cpno,credit)
Values('4','操作系统','6',3);
Insert into Course(cno,cname,cpno,credit)
Values('5','数据结构','7',4);
Insert into Course(cno,cname,cpno,credit)
Values('1','数据库',null,2);
Insert into Course(cno,cname,cpno,credit)
Values('3','信息系统','1',4);


Insert into SC Values('95001','1',92);
Insert into SC Values('95001','2',85);
Insert into SC Values('95001','3',88); 
Insert into SC Values('95002','2',90);
Insert into SC Values('95002','3',80);
Insert into SC Values('95003','2',80);
Insert into SC Values('95003','1',90);



//查询学生总人数
//查询选修了课程的学生人数
//计算选修1号课程的学生的平均成绩
//查询选修1号课程的学生的最高分数
//查询学生201215012选修课程的总学分数
//求各个课程号及相对应的选课人数
//查询选修了三门课程的学生学号
//查询平均成绩大于等于90分的学生学号和平均成绩
//查询每一门课的间接先修课（即先修课的先修课）
//查询每个学生及其选课情况
//查询选修了2号课程且成绩在80分以上的所有学生的学号和姓名
//查询选修了“数据库”课程的同学数量


select count(*) from Student;    
select count(distinct Sno) from SC;
select avg(Grade) from SC where cno='1';
select max(Grade) from SC where cno='1';
select sum(credit) from SC,Course where sno='201215012' and SC.Cno=Course.Cno;
select Cno,Count(Sno) from SC Group by Cno;
select sno from SC group by sno having count(*)=3;
select Sno,avg(Grade) from SC Group by Sno having avg(Grade)>=90;
select First.Cno,Second.Cpno from Course First,Course Second where First.Cpno=Second.Cno;  
select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;
select Student.Sno,sname from Student,SC where Student.Sno=SC.Sno and Cno='2' and Grade>80;
select Count(*) from SC where SC.cno in (select SC.cno from SC where cno in (Select cno from Course where Cname='数据库')) Group by SC.cno;


/**查询选修了3门课，平均成绩在85分以上的有哪些同学**/
select Sname from Student,SC where Student.Sno=SC.Sno Group by Student.Sname having Count(*)=3 and avg(Grade)>85;

/**查询选修课“数据库”、“数学”的同学的数量**/
select count(*) from (select Sno from sc where sc.cno in(select cno from course where cname='数据库' or cname='数学') 
group by SC.Sno having count(*)=2) as S(Sno);

/**查询只选修了“数据库”“数学”这两门课的同学的数量。**/
select count(*) from (select SC.sno from SC where SC.sno in (select Sno from sc where sc.cno in(
select cno from course where cname='数据库' or cname='数学') 
group by SC.Sno having count(*)=2)group by Sc.sno having count(*)=2) as S(Sno);

/**查询选修了“数据库”课程，选课总学分超过6的学生的人数**/
 select count(*)from(select sum(credit) from (select CD.sno,CD.cno,Course.credit from Course,(select sno,cno from SC where sno in
(select sno from SC where cno in (Select cno from Course where Cname='数据库'))) as CD(sno,cno) where CD.cno=Course.cno) as DF(sno,cno,credit) group by sno)
 as FG(c) where c>6




/**查询选修了“数据库”课程的学生学号，姓名，选课数量**/
select sno,sname from(select SC.sno,SC.cno,Student.sname,Course.cname from Student,SC,Course 
where SC.cno=Course.cno and Student.sno=SC.sno) as xsc(sno,cno,sname,cname) where cname='数据库'

/**查询没有选“数据库”，选了“数学”课程的同学的学号和姓名
select sno from (select Sno from sc where sc.cno in(select cno from course where cname='数学')) as SDD(sno) where 


(select SC.sno,SC.cno,Student.sname,Course.cname from Student,SC,Course where SC.cno=Course.cno and Student.sno=SC.sno)