数据库实验习题2.2.sql

数据库第二章习题程序

/*USE SCHOOL
CREATE TABLE NEW_COURSE(
CNO CHAR(4) NOT NULL UNIQUE,
CNAME VARCHAR(50) NOT NULL,
CPOINTS INT,
CONSTRAINT PK_NEW_COURSE PRIMARY KEY(CNO))*/

/*INSERT NEW_COURSE VALUES ('0001','NETWORKS',2);
INSERT NEW_COURSE VALUES ('0002','DATABASE',3)*/

/*USE SCHOOL
CREATE TABLE SC(
SNO CHAR(5) REFERENCES STU_UNION(SNO) ON DELETE CASCADE,
CNO CHAR(4) REFERENCES NEW_COURSE(CNO) ON DELETE CASCADE,
GRADE INT,
CONSTRAINT PK_SC PRIMARY KEY (SNO,CNO))*/

/*INSERT INTO SC VALUES('95002','0001',2);
INSERT INTO SC VALUES('95002','0002',2);
INSERT INTO SC VALUES('10001','0001',2);
INSERT INTO SC VALUES('10001','0002',2)*/

/*(1)(2)部分内容
USE SCHOOL
ALTER TABLE SC
DROP CONSTRAINT FK__SC__CNO__2E1BDC42*/

/*USE SCHOOL
ALTER TABLE SC
ADD CONSTRAINT [FK_SC_SNO] FOREIGN KEY([SNO]) REFERENCES [STU_UNION]([SNO]) ON DELETE NO ACTION*/

/*(3)
USE SCHOOL
CREATE TABLE STU_HELP(
SID CHAR(8),
SNAME CHAR(20),
HELPED CHAR(10),
CONSTRAINT PK_STU_HELP PRIMARY KEY(SID),
CONSTRAINT FK_STU_HELP FOREIGN KEY(HELPED) REFERENCES STUDENTS(SID))*/

/*(4)
USE SCHOOL
CREATE TABLE DEPARTMENT(
SID CHAR(8),
SNAME CHAR(20),
LEADER CHAR(8),
CONSTRAINT PK_DEPARTMENT PRIMARY KEY(SID))*/

/*USE SCHOOL
CREATE TABLE LEADER(
SID CHAR(8),
SNAME CHAR(20),
MONITORED CHAR(8),
CONSTRAINT PK_LEADER PRIMARY KEY(SID))*/

/*USE SCHOOL
CREATE TABLE MONITOR(
SID CHAR(8),
SNAME CHAR(20),
LEADED CHAR(8),
CONSTRAINT PR_MONITOR PRIMARY KEY(SID),
CONSTRAINT FK_MONTIOR_LEADED FOREIGN KEY(LEADED) REFERENCES LEADER(SID))*/

/*USE SCHOOL
ALTER TABLE LEADER
ADD CONSTRAINT FK_LEADER_MONITORED FOREIGN KEY(MONITORED) REFERENCES MONITOR(SID)*/