SQL數(shù)據(jù)庫(kù)原理與設(shè)計(jì)-實(shí)驗(yàn)七“查詢語(yǔ)句”數(shù)據(jù)

1、70B0A14155718F1746C9E61DECDDFCCA9FE601F7FF5D30B6389406271102E38ED-drop database schoolcreate database school -創(chuàng)建數(shù)據(jù)庫(kù)go/*CREATE DATABASE schoolON ( NAME = school_dat, FILENAME = d:school1.mdf, SIZE = 2, MAXSIZE = 4, FILEGROWTH = 1 )LOG ON( NAME =school_log, FILENAME = d:school1.ldf, SIZE = 2MB, MAXSIZ

2、E = 3MB, FILEGROWTH = 1MB ) */gouse schoolgoEXEC sp_addtype ud_sno,char(6) go-創(chuàng)建表create table Student ( Sno ud_sno , Sname char(10) not null unique , Ssex char(2) check (ssex=男 or ssex=女) , Sage smallint check(sage16) , Sdept char(10) not null default JSJ , primary key (sno) ) create index ix_studen

3、t_sname ON student(sname)create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int check (Ccredit =0 and Ccredit=5), check( cnocpno) , -約束 primary key (cno) )create index ix_course_cpno ON course(cpno) create table SC( Sno ud_sno , Cno char(4) , Grade int check(grade=100) , cons

4、traint pk_sc primary key (sno,cno), foreign key (sno) references student(sno) , foreign key (cno) references course(cno) , )create index ix_sc_cno ON sc(cno) -添加數(shù)據(jù)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0003,陳小明,男,20,SX)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0004,楊秀紅

5、,女,21,JSJ)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0001,周志林,男,20,SX)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0009,錢明明,男,20,SX)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0002,李文慶,男,23,JSJ)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0081,劉亭,女, 22,

6、SX)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0091,賀秋雪,女,20,SX)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0078,王振,男, 21,JSJ)insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (0092,趙三,男, 22,SX)insert into student (sno,sname,ssex,sage,sdept) values (8001,張華,男,23,SX)inser

7、t into student (sno,sname,ssex,sage,sdept) values (8002,趙穎,女,21,SX)insert into student (sno,sname,ssex,sage,sdept) values (8003,錢凱,男,22,JSJ)insert into student (sno,sname,ssex,sage,sdept) values (8004,王華,男,21,SX)insert into student (sno,sname,ssex,sage) values (8005,張英,女,21)insert into student (sno,

8、sname,ssex,sage) values (8006,趙章,女,22)insert into student (sno,sname,ssex,sage) values (8007,錢利,男,23)insert into student (sno,sname,ssex,sage) values (8008,王鐵,男,21)insert into course(Cno,Cname,Cpno,Ccredit) values (1001,高等數(shù)學(xué),5)insert into course(Cno,Cname,Cpno,Ccredit) values (1002,離散數(shù)學(xué),1001,3)inser

9、t into course(Cno,Cname,Cpno,Ccredit) values (1003,程序設(shè)計(jì),5)insert into course(Cno,Cname,Cpno,Ccredit) values (1004,數(shù)據(jù)結(jié)構(gòu),1003,4)insert into course(Cno,Cname,Cpno,Ccredit) values (1005,數(shù)據(jù)庫(kù)原理,1004,4)insert into course(Cno,Cname,Cpno,Ccredit) values (1006,操作系統(tǒng),1004,5)insert into SC(Sno,Cno,Grade) values

10、(0002,1002,90)insert into SC(Sno,Cno,Grade) values (0002,1001,91)insert into SC(Sno,Cno,Grade) values (0002,1003,67)insert into SC(Sno,Cno,Grade) values (0002,1004,98)insert into SC(Sno,Cno,Grade) values (0001,1002,92)insert into SC(Sno,Cno,Grade) values (0001,1001,92)insert into SC(Sno,Cno,Grade) v

11、alues (0001,1003,76)insert into SC(Sno,Cno,Grade) values (0003,1001,77)insert into SC(Sno,Cno,Grade) values (0001,1004, 91)insert into SC(Sno,Cno,Grade) values (0004,1001, 97)insert into SC(Sno,Cno,Grade) values (0004,1002, 78)insert into SC(Sno,Cno,Grade) values (0004,1003, 65)insert into SC(Sno,Cn

12、o,Grade) values (0004,1004, 89)insert into SC(Sno,Cno,Grade) values (0091,1001, 93)insert into SC(Sno,Cno,Grade) values (0091,1002, 87)insert into SC(Sno,Cno,Grade) values (0091,1003, 99)insert into SC(Sno,Cno,Grade) values (0091,1004, 95)insert into SC(Sno,Cno,Grade) values (0009,1001, 93)insert in

13、to SC(Sno,Cno,Grade) values (0009,1002, 88)insert into SC(Sno,Cno,Grade) values (0009,1003, 60)insert into SC(Sno,Cno,Grade) values (0009,1004, 83)insert into SC(Sno,Cno,Grade) values (0092,1001, 98)insert into SC(Sno,Cno,Grade) values (0092,1002, 86)insert into SC(Sno,Cno) values (0092,1003)insert

14、into SC(Sno,Cno,Grade) values (0081,1001, 90)insert into SC(Sno,Cno) values (0081,1002)insert into SC(Sno,Cno) values (0081,1003)insert into SC(Sno,Cno,Grade) values (8001,1002,90)insert into SC(Sno,Cno,Grade) values (8001,1001,91)insert into SC(Sno,Cno,Grade) values (8001,1003,67)insert into SC(Sno

15、,Cno,Grade) values (8001,1005,98)insert into SC(Sno,Cno,Grade) values (8002,1002,87)insert into SC(Sno,Cno,Grade) values (8002,1001,96)insert into SC(Sno,Cno,Grade) values (8002,1003,68)insert into SC(Sno,Cno,Grade) values (8002,1005,92)insert into SC(Sno,Cno,Grade) values (8003,1001,91)insert into

16、SC(Sno,Cno,Grade) values (8003,1003,92)insert into SC(Sno,Cno,Grade) values (8003,1004,63)insert into SC(Sno,Cno,Grade) values (8003,1005,94)insert into SC(Sno,Cno,Grade) values (8004,1001,92)insert into SC(Sno,Cno,Grade) values (8004,1003,93)insert into SC(Sno,Cno,Grade) values (8004,1004,64)insert

17、 into SC(Sno,Cno) values (8004,1005)insert into SC(Sno,Cno) values (8006,1001)insert into SC(Sno,Cno,Grade) values (8006,1002,92)insert into SC(Sno,Cno,Grade) values (8006,1003,20)insert into SC(Sno,Cno) values (8006,1004)insert into SC(Sno,Cno,Grade) values (8007,1001,99)insert into SC(Sno,Cno,Grade) values (8007,1003,92)insert into SC(Sno,Cno,Grade) values (8007,1004,86)insert into SC(Sno,Cno,Grade) values (8007,1005,98)insert into SC(Sno,Cno,Grade)