Experiment two SQL definition language

Create Table Stud
(Sno char (8) Primary key,
Sname char (4) NOT null unique,--student name
Ssex char (2) Default ' male ' Check (ssex= ' male ' or ssex= ' female '),
Sage tinyint,
Sdept char (20))

CREATE TABLE Cour
(CNO char (2) PRimary key,
CNAME varchar (50),
Cpno char (2),
Ccredit tinyint)

CREATE TABLE SCC
(Sno char (8),
CNO char (2),
Grade tinyint,
Constraint Pk_gra primary KEY (SNO,CNO),
Constraint Fk_stu foreign KEY (SNO) references Stud (SNO),
Constraint Fk_cour foreign KEY (CNO) references cour (CNO),
Constraint Ck_grad Check (grade>=0 and grade<=100))

Go
INSERT INTO stud (Sno,sname, ssex,sage,sdept) VALUES (' 95001 ', ' Li Yong ', ' Male ', ' CS ')
INSERT INTO stud (Sno,sname, ssex,sage,sdept) VALUES (' 95002 ', ' Liu Chen ', ' female ', ' is ')
INSERT INTO stud (Sno,sname, ssex,sage,sdept) VALUES (' 95003 ', ' Wang Min ', ' female ', ' MA ')
INSERT INTO stud (Sno,sname, ssex,sage,sdept) VALUES (' 95004 ', ' Zhang Li ', ' Male ', ' is ')
INSERT INTO stud (Sno,sname, ssex,sage,sdept) VALUES (' 95005 ', ' Liu Yun ', ' female ', ' CS ')
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 1 ', ' database ', 4, ' 5 ')
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 2 ', ' Math ', 6, NULL)
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 3 ', ' Information system ', 3, ' 1 ')
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 4 ', ' OS ', 4, ' 6 ')
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 5 ', ' Data structure ', 4, ' 7 ')
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 6 ', ' Data processing ', 3, NULL)
Insert into Cour (CNO, cname,ccredit,cpno) VALUES (' 7 ', ' Pascal language ', 4, ' 6 ')
INSERT into SCC (Sno,cno,grade) VALUES (' 95001 ', ' 1 ', 92)
INSERT into SCC (Sno,cno,grade) VALUES (' 95001 ', ' 2 ', 85)
INSERT into SCC (Sno,cno,grade) VALUES (' 95001 ', ' 3 ', 88)
INSERT into SCC (Sno,cno,grade) VALUES (' 95002 ', ' 2 ', 90)
INSERT into SCC (Sno,cno,grade) VALUES (' 95002 ', ' 3 ', 80)
INSERT into SCC (Sno,cno,grade) VALUES (' 95003 ', ' 2 ', 85)
INSERT into SCC (Sno,cno,grade) VALUES (' 95004 ', ' 1 ', 58)
INSERT into SCC (Sno,cno,grade) VALUES (' 95004 ', ' 2 ', 85)

ALTER TABLE stud add Scome date//1) Add a field to the Student table for admission time Scome,
ALTER TABLE stud drop column SDEPT//2) deletes the Sdept field from the student table;

exec sp_helpconstraint ' SC '--Find the foreign key name of SC table
ALTER TABLE SC drop constraint fk_cou_cno//3) deletes the foreign key constraint between the CNO field and the Course Table CNO field in the Created SC table;

ALTER TABLE SC add constraint Fk_cou_cno
Foreign KEY (CNO) References course (CNO)//Add foreign key

Create Table Studd
(Sno char (8) Primary key,
Sname char (4) is not null unique,
Ssex char (2) Default ' male ' Check (ssex= ' male ' or ssex= ' female '),
Sage tinyint,
Sdept char (20))
drop table studd//redefine a simple table, and then delete the table structure with the SQL language drop statement;

Create unique index Sstud on stud sname desc//The SQL language CREATE INDEX statement to define the descending unique indexes of student fields for table sname;

Drop index Sstud on stud//Delete the indexes with the SQL language drop statement;

Experiment two SQL definition language