數(shù)據(jù)庫實驗47603

1、（一）數(shù)據(jù)定義1. 建立基本表1 創(chuàng)建學(xué)生表Student，由以下屬性組成：學(xué)號SNO（INT型，主鍵），姓名SNAME（CHAR型，長度為8，非空），性別SEX（CHAR型，長度為2），所在系DEPTNO（INT型）。CREATE TABLE Student(SNO int PRIMARY KEY,SNAME char(8) not null,SEX CHAR(2) ,DEPTNO INT);2 創(chuàng)建課程表Course，由以下屬性組成：課程號CNO（INT型），課程名CNAME（CHAR型，長度為20，非空），授課教師編號TNO（INT型），學(xué)分CREDIT（INT型）。其中（CNO，TNO

2、）為主鍵。CREATE TABLE Course(CNO int ,CNAME char(20) not null,TNO INT ,CREDIT INT,PRIMARY KEY(CNO,TNO);3 創(chuàng)建學(xué)生選課表SC，由以下屬性組成：學(xué)號SNO，課程號CNO，成績GRADE。所有屬性均為INT型，其中（SNO，CNO）為主鍵。CREATE TABLE SC(SNO INT,CNO INT,GRADE INT,PRIMARY KEY(SNO,CNO);4 創(chuàng)建教師表Teacher，由以下屬性組成：教師編號TNO（INT型，主鍵），教師姓名TNAME（CHAR型，長度為8，非空），所在系DEP

3、TNO（INT型）。CREATE TABLE Teacher(TNO INT PRIMARY KEY,TNAME CHAR(8) NOT NULL,DEPTNO INT);5 創(chuàng)建系表Dept，由以下屬性組成：系號DEPTNO（INT型，主鍵），系名DNAME（CHAR型，長度為20，非空）。CREATE TABLE Dept(DEPTNO INT PRIMARY KEY,DNAME CHAR(20) NOT NULL);2. 修改基本表1 在Student表中加入屬性AGE（INT型）。ALTER TABLE Student ADD AGE INT;2 將Student表中的屬性AGE類型改

4、為SMALLINT型。ALTER TABLE Student ALTER COLUMN AGE SMALLINT;3. 刪除基本表1 在所有操作結(jié)束后刪除Student表。DROP TABLE Student;2 在所有操作結(jié)束后刪除Course表。DROP TABLE Course;3 在所有操作結(jié)束后刪除SC表。DROP TABLE SC;4 在所有操作結(jié)束后刪除Teacher表。DROP TABLE Teacher;5 在所有操作結(jié)束后刪除Dept表。DROP TABLE Dept;（二）數(shù)據(jù)操作1. 插入數(shù)據(jù)1 向Student表插入下列數(shù)據(jù)：（1001，張?zhí)?，m，10，20）、（10

5、02，李蘭，f，10，21）、（1003，陳銘，m，10，21）、（1004，劉茜，f，20，21）、（1005，馬陽，m，20，22）。INSERTINTO StudentVALUES (1001,張?zhí)?m,10,20);INSERTINTO StudentVALUES (1002,李蘭,f,10,21);INSERTINTO StudentVALUES (1003,陳銘,m,10,21);INSERTINTO StudentVALUES (1004,劉茜,f,20,21);INSERTINTO StudentVALUES (1005,馬陽,m,20,22);2 向Course表插入下列數(shù)據(jù)

6、：（1，數(shù)據(jù)結(jié)構(gòu)，101，4）、（2，數(shù)據(jù)庫，102，4）、（3，離散數(shù)學(xué)，103，4）、（4，C語言程序設(shè)計，101，2）。INSERTINTO CourseVALUES (1,數(shù)據(jù)結(jié)構(gòu),101,4);INSERTINTO CourseVALUES (2,數(shù)據(jù)庫,102,4);INSERTINTO CourseVALUES (3,離散數(shù)學(xué),103,4);INSERTINTO CourseVALUES (4,C語言程序設(shè)計,101,2);3 向SC表插入下列數(shù)據(jù)：（1001，1，80）、（1001，2，85）、（1001，3，78）、（1002，1，78）、（1002，2，82）、（1002，

7、3，86）、（1003，1，92）、（1003，3，90）、（1004，1，87）、（1004，4，90）、（1005，1，85）、（1005，4，92）。INSERTINTO SCVALUES (1001,1,80);INSERTINTO SCVALUES (1001,2,85);INSERTINTO SCVALUES (1001,3,78);INSERTINTO SCVALUES (1002,1,78);INSERTINTO SCVALUES (1002,2,82);INSERTINTO SCVALUES (1002,3,86);INSERTINTO SCVALUES (1003,1,92

8、);INSERTINTO SCVALUES (1003,3,90);INSERTINTO SCVALUES (1004,1,87);INSERTINTO SCVALUES (1004,4,90);INSERTINTO SCVALUES (1005,1,85);INSERTINTO SCVALUES (1005,4,92);4 向Teacher表插入下列數(shù)據(jù)：（101，張星，10）、（102，李珊，10）、（103，趙應(yīng)，10）、（104，劉田，20）。INSERTINTO TeacherVALUES (101,張星,10);INSERTINTO TeacherVALUES (102,李珊,10

9、);INSERTINTO TeacherVALUES (103,趙應(yīng),10);INSERTINTO TeacherVALUES (104,劉田,20);5 向Dept表插入下列數(shù)據(jù)：（10，計算機）、（20，信息）。INSERTINTO DeptVALUES (10,計算機);INSERTINTO DeptVALUES (20,信息);2. 單表查詢1 查詢所有學(xué)生的信息。SELECT *FROM Student;2 查詢所有女生的姓名。SELECT SNAMEFROM StudentWHERE SEX=f;3 查詢成績在80到89之間的所有學(xué)生的選課記錄，查詢結(jié)果按成績的降序排列。SELEC

10、T sc.sno,cno,gradeFROM scWHERE grade80 and grade90ORDER BY grade DESC;4 查詢各個系的學(xué)生人數(shù)。select deptno ,count(distinct sname)from student Sgroup by deptno;或select dname ,count(distinct sname)from student S,dept Dwhere s.deptno=D.deptnogroup by dname;3. 連接查詢查詢信息系年齡在21歲以下（含21歲）的女生姓名及其年齡。select S.sname,S.age

11、from student S,dept Dwhere S.deptno=D.deptno and s.age=21 and s.sex=f and D.dname=信息;4. 嵌套查詢1 查詢修課總學(xué)分在10分以下的學(xué)生姓名。select snamefrom studentwhere sno in(select S from (select a.Sno as S,b.Credit as Cr from SC a left join Course b on a.Cno=b.Cno) c group by S having sum(Cr)10);2 查詢各門課程取得最高成績的學(xué)生姓名及其成績。SE

12、LECT SNAME,GRADEFROM Student,SCWHERE Student.SNO=SC.SNO AND GRADE IN (SELECT MAX(GRADE) FROM SC GROUP BY CNO)3 查詢選修了1001學(xué)生選課的全部課程的學(xué)生學(xué)號。SELECT DISTINCT SNOFROM SC SCXWHERE SNO != 1001 AND NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO = 1001 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.SNO = SCX.SN

13、O AND SCZ.CNO = SCY.CNO);4 查詢選修了張星老師開設(shè)的全部課程的學(xué)生姓名。SELECT SNAMEFROM StudentWHERE SNO NOT IN(SELECT DISTINCT SNOFROM SC SCXWHERE CNO IN (SELECT C.CNO FROM Teacher T, COURSE C WHERE T.TNO=C.TNO AND T.TNAME!=張星 ) )；5. 修改數(shù)據(jù)將張星老師數(shù)據(jù)結(jié)構(gòu)課的學(xué)生成績?nèi)考?分。UPDATE SCSET GRADE= GRADE + 2WHERE CNO IN (SELECT CNO FROM Course WHERE CNAME=數(shù)據(jù)結(jié)構(gòu)and TNO in (SELECT TNO FROM Teacher WHERE TNAME=張星)；6. 刪除數(shù)據(jù)刪除馬陽同學(xué)的所有選課記錄。DELETEFROM SCWHERE SNO IN(SELECT SC.SNO FROM Student S,SC WHERE S.SNO=SC.SNO AND S.SNAME=馬陽);（三