数据库完整版.docVIP

PAGE PAGE 1 上机一 1.创建表S CREATE TABLE S (Sno CHAR(9)PRIMARY KEY, Sname VARCHAR(20)UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); 2.创建表C CREATE TABLE C (Cno CHAR(4)PRIMARY KEY, Cname CHAR(40)NOT NULL, Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY(Cpno)REFERENCES C(Cno) ); 3.创建表SC CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno)REFERENCES S(Sno) FOREIGN KEY(Cno)REFERENCES C(Cno) ); 4.创建表E(eno,ename,birthday,sex,mphone,marry_s),主码为eno CREATE TABLE E (Eno CHAR(9)PRIMARY KEY, Ename VARCHAR(20)UNIQUE, Ebirthday DATE, Esex CHAR(2), Emphone CHAR(11), Emarry_S CHAR(4) ); 5.在E(mphone)上创建唯一性索引 CREATE UNIQUE INDEX Emphone ON E(Emphone); 上机二 从用户system中的表S,C,SC中查询 1.查询计算机系CS的学生信息 select * from system.s where sdept=CS; 2.查询年龄小于20的姓陈的女学生学号，姓名，年龄 select sname,sno,sage from system.s where sname like陈% and ssex=女and sage20; 3.查询有先修课的课程号，课程名，学分，结果按学分降序排序 select cno,cname,ccredit from system.c where cpno is not null order by ccredit desc; 4.查询男学生的人数和最大年龄 select count(*) 总个数,max(sage) 最大值 from system.s where ssex=男; 上机三 从用户system中的表S,C,SC中查询 1.查询有挂科的学生学号，姓名 select sno,sname from system.s where sno in( select sno from system.sc where grade60); 2.查询有男生选修的课程号，课程名 select distinct c.cno,cname from system.c,system.sc,system.s where s.sno=sc.sno and sc.cno=c.cno and ssex=男; 3.查询至少选三门课的女学生学号和平均成绩，并按平均成绩降序排列 select system.sc.sno,avg(grade) from system.sc,system.s where ssex=女 and system.s.sno=system.sc.sno group by system.sc.sno having count(*)=3 order by avg(grade) desc; 4.查询没有选修课程‘数据库’的学生学号、姓名 select sno,sname from system.s where not exists (select* from system.sc where sno=system.s.sno and cno in (select cno from system.c where cname=数据库)); 上机四 1.在表S中插入你的信息 insert into s (sno,sname,ssex,sdept,sage) values伍彩妮,女,cs,20); 2.将system.S中‘CS’系的且年龄小于24的学