CREATE DATABASE 学生
ON
  PRIMARY (NAME =学生_data,
  FILENAME='C:\DB\学生_DATA.MDF',
SIZE=15MB,
  MAXSIZE=30MB,
  FILEGROWTH=20%)
log ON
  (NAME = 学生_LOG,
  FILENAME='C:\DB\学生_LOG.LDF',
  SIZE=3MB,
  MAXSIZE=10MB,
  FILEGROWTH=1MB)

USE 学生
GO

CREATE TABLE STUDENT(SNO CHAR(5) PRIMARY KEY,
          SNAME VARCHAR(8),
          SSEX  CHAR(2) CHECK (SSEX IN ('男','女')),
          SAGE INT,
          SDEPT VARCHAR(20) CHECK (SDEPT IN('数学系','计算机系','英语系')))
GO

CREATE TABLE COURSE(CNO CHAR(5) PRIMARY KEY,
          CNAME VARCHAR(20),
          CPNO CHAR(5),
          CCREDIT SMALLINT DEFAULT 3)
GO

CREATE TABLE SG(SNO CHAR(5),
        CNO CHAR(5),
        GRADE SMALLINT,
        CONSTRAINT C1 CHECK(GRADE BETWEEN 0 AND 100),
        CONSTRAINT C2 PRIMARY KEY (SNO,CNO), 
        CONSTRAINT C3 FOREIGN KEY(SNO) references STUDENT(SNO),
        constraint c4 foreign key(CNO) references COURSE(CNO))
GO

INSERT INTO STUDENT VALUES('1001','LS','男','18','计算机系')
GO

INSERT INTO STUDENT VALUES('1002','ZS','女','20','英语系')
GO

INSERT INTO COURSE VALUES('001','c语言','','')
GO

INSERT INTO COURSE VALUES('002','大学英语','001','4')
GO

INSERT INTO SG VALUES('1001','001','80')
GO

INSERT INTO SG VALUES('1002','002','90')
GO


SELECT STUDENT.SNO,SNAME,SSEX,GRADE
FROM STUDENT,SG
WHERE STUDENT.SNO=SG.SNO
ORDER BY SNAME ASC,GRADE DESC
GO

SELECT COURSE.CNO,MAX(GRADE),AVG(GRADE)
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO AND SG.CNO='001'
GROUP BY COURSE.CNO
GO

SELECT SDEPT
FROM STUDENT 
WHERE SNAME='ZS'
GO

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO=(SELECT SDEPT
      FROM STUDENT 
      WHERE SNAME='ZS')
GO

SELECT STUDENT.*
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO AND COURSE.CNAME='英语'
GO

UPDATE STUDENT
SET SAGE=SAGE+2
WHERE SDEPT='英语系'
GO


DELETE
FROM SG
WHERE SG.SNO=(SELECT SNO
FROM STUDENT
WHERE SDEPT='英语系')
GO

CREATE VIEW STUDENTCOURSE(SNO,SNAME,CNO,CNAME,GRADE)AS
SELECT STUDENT.SNO,SNAME,COURSE.CNO,CNAME,GRADE
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO 
GO