SQL数据库原理与设计-实验七“查询语句”数据

1、70B0A14155718F1746C9E61DECDDFCCA9FE601F7FF5D30B6389406271102E38ED-drop database schoolcreate database school -创建数据库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-创建表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) -添加数据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,高等数学,5)insert into course(Cno,Cname,Cpno,Ccredit) values (1002,离散数学,1001,3)inser

9、t into course(Cno,Cname,Cpno,Ccredit) values (1003,程序设计,5)insert into course(Cno,Cname,Cpno,Ccredit) values (1004,数据结构,1003,4)insert into course(Cno,Cname,Cpno,Ccredit) values (1005,数据库原理,1004,4)insert into course(Cno,Cname,Cpno,Ccredit) values (1006,操作系统,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)