实验一 参考答案
- 格式：doc
- 大小：47.00 KB
- 文档页数：5
实验一：参考答案1.1）CREATE DATABASE STUDBON(NAME=StuData,FILENAME='d:\db\StuDat.mdf',SIZE=2MB,MAXSIZE=UNLIMITED,FILEGROWTH=2MB)LOG ON(NAME=StuLog,FILENAME='d:\db\StuLog.ldf',SIZE=2MB,MAXSIZE=10MB,FILEGROWTH=10%)2.设置StuDB为当前数据库USE STUDB;(1)CREATE TABLE S(CLASS CHAR(5),SNO CHAR(2) NOT NULL,NAME CHAR(10) UNIQUE,SEX CHAR(2) DEFAULT '男',AGE INT CHECK (AGE BETWEEN 15 AND 30),PRIMARY KEY (CLASS,SNO));(2)INSERT INTO S V ALUES('95031','25','李明','男',21);INSERT INTO S V ALUES('95101','10','王丽','女',20);(3)INSERT INTO S(CLASS,SNO,NAME) V ALUES('95031','30','郑和');(4)CREATE INDEX inxage ON S(AGE DESC);(5)ALTER TABLE S ADD COMEDA TE DATETIME;(6)ALTER TABLE S ADD CONSTRAINT DK_S_COMEDATE DEFAULT GETDATE() FOR COMEDATE;(7)DROP INDEX S.inxage;(8)ALTER TABLE S DROP CONSTRAINT CK__S__AGE__79A81403--注：在AGE列上有CHECK约束存在，要修改其类型要先删除约束。
--CK__S__AGE__79A81403是约束名，请注意自己数据库中的名称。
ALTER TABLE S ALTER COLUMN AGE SMALLINT; (9)DROP TABLE S;(10)CREATE DATABASE STUDBGOUSE STUDBGOCREATE TABLE STUDENT(SNO CHAR(9) PRIMARY KEY,SNAME CHAR(20) UNIQUE,SSEX CHAR(2),SAGE SMALLINT,SDEPT CHAR(20));GOCREATE TABLE COURSE( CNO CHAR(4) PRIMARY KEY,CNAME CHAR(40),CPNO CHAR(4),CCREDIT SMALLINT,FOREIGN KEY (CPNO) REFERENCES COURSE(CNO) )GOCREATE TABLE SC(SNO CHAR(9),CNO CHAR(4),GRADE SMALLINT,PRIMARY KEY (SNO,CNO),FOREIGN KEY (SNO) REFERENCES STUDENT(SNO), FOREIGN KEY (CNO) REFERENCES COURSE(CNO))GOINSERT INTO STUDENTV ALUES('200215121','李勇','男',20,'CS')INSERT INTO STUDENTV ALUES('200215122','刘晨','女',19,'CS')INSERT INTO STUDENTV ALUES('200215123','王敏','女',18,'MA')INSERT INTO STUDENTV ALUES('200215125','张立','男',19,'IS')INSERT INTO STUDENTV ALUES('200215126','张三','男',19,'IS')INSERT INTO STUDENTV ALUES('200215127','李四','男',19,'MA')INSERT INTO STUDENTV ALUES('200215128','王五','男',19,'MA')INSERT INTO STUDENTV ALUES('200215130','李小林','男',19,'CS')INSERT INTO STUDENTV ALUES('200215131','林冲','男',23,'EC')GOINSERT INTO COURSEV ALUES ('6','数据处理',NULL,2)INSERT INTO COURSEV ALUES ('7','PASCAL语言','6',4)INSERT INTO COURSEV ALUES ('5','数据结构','7',4)INSERT INTO COURSEV ALUES ('1','数据库','5',4)INSERT INTO COURSEV ALUES ('2','数学',NULL,2)INSERT INTO COURSEV ALUES ('3','信息系统','1',4)INSERT INTO COURSEV ALUES ('4','操作系统','6',3)GOINSERT INTO SC V ALUES ('200215121','1',92) INSERT INTO SC V ALUES ('200215121','2',85) INSERT INTO SC V ALUES ('200215121','3',88) INSERT INTO SC V ALUES ('200215122','1',92) INSERT INTO SC V ALUES ('200215122','2',80) INSERT INTO SC (SNO,CNO) V ALUES ('200215125','1') INSERT INTO SC(SNO,CNO) V ALUES ('200215121','4') INSERT INTO SC(SNO,CNO) V ALUES ('200215121','6') INSERT INTO SC(SNO,CNO) V ALUES ('200215121','7') INSERT INTO SC(SNO,CNO) V ALUES ('200215122','5') INSERT INTO SC V ALUES ('200215126','6',55)GO*3CREATE DATABASE SPJDB;GOUSE SPJDBGOCREATE TABLE S(SNO CHAR(3) P RIMARY KEY,SNAME V ARCHAR(10) NOT NULL,STA TUS CHAR(2) NOT NULL,CITY V ARCHAR(6) );GOCREATE TABLE P(PNO CHAR(3) P RIMARY KEY,PNAME V ARCHAR(10) NOT NULL,COLOR V ARCHAR(6) N OT NULL,WEIGHT N UMERIC(5,3) );GOCREATE TABLE J(JNO CHAR(3) P RIMARY KEY,JNAME V ARCHAR(10) NOT NULL,CITY V ARCHAR(6) );GOCREATE TABLE SPJ(SNO CHAR(3) R EFERENCES S,PNO CHAR(3) R EFERENCES P,JNO CHAR(3) R EFERENCES J,QTY NUMERIC(6),PRIMARY KEY (SNO,PNO,JNO));GOINSERT INTO S V ALUES('S1','精益',20,'天津'); INSERT INTO S V ALUES('S2','盛锡',10,'北京'); INSERT INTO S V ALUES('S3','东方红',30,'北京'); INSERT INTO S V ALUES('S4','丰泰盛',20,'天津'); INSERT INTO S V ALUES('S5','为民',30,'上海'); INSERT INTO S V ALUES('S6','吉利',10,'武汉'); GOINSERT INTO P V ALUES('P1','螺母','红',12); INSERT INTO P V ALUES('P2','螺栓','绿',17); INSERT INTO P V ALUES('P3','螺丝刀','蓝',14); INSERT INTO P V ALUES('P4','螺丝刀','红',14); INSERT INTO P V ALUES('P5','凸轮','蓝',40); INSERT INTO P V ALUES('P6','齿轮','红',30); INSERT INTO P V ALUES('P7','把手','红',30);GOINSERT INTO J V ALUES('J1','三建','北京'); INSERT INTO J V ALUES('J2','一汽','长春'); INSERT INTO J V ALUES('J3','弹簧厂','天津'); INSERT INTO J V ALUES('J4','造船厂','天津'); INSERT INTO J V ALUES('J5','机车厂','唐山'); INSERT INTO J V ALUES('J6','无线电厂','常州'); INSERT INTO J V ALUES('J7','半导体','南京'); INSERT INTO J V ALUES('J8','二建','南京');GOINSERT INTO SPJ V ALUES('S1','P1','J1',200); INSERT INTO SPJ V ALUES('S1','P1','J3',100); INSERT INTO SPJ V ALUES('S1','P1','J4',700); INSERT INTO SPJ V ALUES('S1','P2','J2',100); INSERT INTO SPJ V ALUES('S2','P3','J1',400); INSERT INTO SPJ V ALUES('S2','P3','J4',500); INSERT INTO SPJ V ALUES('S2','P3','J5',400); INSERT INTO SPJ V ALUES('S2','P5','J1',400); INSERT INTO SPJ V ALUES('S2','P5','J2',100); INSERT INTO SPJ V ALUES('S3','P1','J1',200); INSERT INTO SPJ V ALUES('S3','P3','J1',200); INSERT INTO SPJ V ALUES('S4','P5','J1',100); INSERT INTO SPJ V ALUES('S4','P6','J3',300); INSERT INTO SPJ V ALUES('S5','P2','J4',100); INSERT INTO SPJ V ALUES('S5','P3','J1',200); INSERT INTO SPJ V ALUES('S5','P6','J2',200); INSERT INTO SPJ V ALUES('S5','P6','J4',500); GO。
