数据库操作指南：查询与管理

CREATE TABLE Student
(Sno char(9) not null unique,
 Sname char(20) unique,
 Ssex char(2),
 Sage smallint check(Sage>16),
 Sdept char(20)
);

CREATE TABLE Course
(Cno char(4) primary key,
 Cname char(40) unique,
 Cpno  char(4),
 Ccredit smallint,
);

CREATE TABLE Teacher
(
Tno char(9) primary key,
Tname char(20),
Department char(20),
Email char(25),
Salary smallint
);

CREATE TABLE  SC
(Sno char(9) not null,
 Cno char(4) not null,
 Grade int
); 

ALTER TABLE SC Alter column Grade float;
ALTER TABLE Student ADD Scome DATETIME;
ALTER TABLE Student drop column Scome;
ALTER TABLE Teacher drop column Email;
ALTER TABLE Student drop UQ__Student__7D78A4E7;

INSERT INTO Student VALUES ('200215121','李勇','男',20,'CS');
INSERT INTO Student VALUES ('200215122','刘晨','女',19,'CS');
INSERT INTO Student VALUES ('200215123','王敏','女',18,'MA');
INSERT INTO Student VALUES ('200215125','张立','女',19,'IS');

INSERT INTO Course VALUES (1,'数据库',5,4);
INSERT INTO Course(Cno,Cname,Ccredit) VALUES (2,'数学',2);
INSERT INTO Course VALUES (3,'信息系统',1,4);
INSERT INTO Course VALUES (4,'操作系统',6,3);
INSERT INTO Course VALUES (5,'数据结构',7,4);
INSERT INTO Course(Cno,Cname,Ccredit)VALUES (6,'数据处理',2);
INSERT INTO Course VALUES (7,'PASCAL语言',6,4);

INSERT INTO SC VALUES('200215121',1,92);
INSERT INTO SC VALUES('200215121',2,85);
INSERT INTO SC VALUES('200215121',3,88);
INSERT INTO SC VALUES('200215122',2,90);
INSERT INTO SC VALUES('200215122',3,80);

CREATE NONCLUSTERED INDEX Stusage ON student(Sage DESC);
CREATE CLUSTERED INDEX CLU_Stusno ON student(Sno);
CREATE CLUSTERED INDEX CLU_Stusname ON student(Sname);
--无法对 表 'student' 创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引 'CLU_Stusno'。--
CREATE UNIQUE INDEX ASC_CC ON Course (Ccredit);