源代码：
CREATE DATABASE JWGL
/*建立教务管理系统*/ ON
(NAME = JWGL_Data,
FILENAME = \'e:\\sql_data\\Jwgl_Data.mdf\', SIZE = 20, MAXSIZE = 500, FILEGROWTH = 25%) LOG ON
(NAME = JWGL_LOG,
FILENAME = \'e:\\sql_data\\Jwgl_Log.ldf\', SIZE = 10, MAXSIZE = 100, FILEGROWTH = 1%);
CREATE TABLE Student
/*建立学生表*/ (Sno CHAR(8) PRIMARY KEY NOT NULL, Sname VARCHAR(10) NOT NULL,
Sex CHAR(2) CHECK (Sex IN (\'男\',\'女\'))NOT NULL,
Age TINYINT CHECK(Age BETWEEN 15 AND 30)NOT NULL, Phonenumber CHAR(12) UNIQUE, Sdept VARCHAR(20)NOT NULL, );
CREATE TABLE Course
/*建立课程表*/ (Cno CHAR(10) PRIMARY KEY NOT NULL, Cname VARCHAR(20) UNIQUE NOT NULL,
Total_perior TINYINT CHECK(Total_perior BETWEEN 32 AND 108),/*总学时*/ Week_perior TINYINT CHECK(Week_perior BETWEEN 2 AND 7),/*周学时*/ Credit TINYINT CHECK(Credit BETWEEN 1 AND 7)NOT NULL,/*学分*/ Pcno CHAR(10)/*先修课*/ );
CREATE TABLE SC
/*建立学生选课表*/ (Sno CHAR(8)NOT NULL, Cno CHAR(10)NOT NULL,
Grade TINYINT CHECK(Grade BETWEEN 1 AND 100), PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) );
DROP TABLE Student
/*删除学生表*/ DROP TABLE Course
/*删除课程表*/ DROP TABLE SC
/*删除学生选课表*/
INSERT
/*向学生表中插入数据*/ INTO Student
VALUES(\'04111020\',\'张倩\',\'女\',19,\'18789430000\',\'计算机\'); INSERT
INTO Student
VALUES(\'04111021\',\'张花\',\'女\',20,\'15236982111\',\'信息\'); INSERT
INTO Student
VALUES(\'04111022\',\'李强\',\'男\',20,\'13625984666\',\'英语\'); INSERT
INTO Student
VALUES(\'04111023\',\'王若\',\'女\',19,\'18789437777\',\'计算机\'); INSERT
INTO Student
VALUES(\'04111024\',\'小淼\',\'男\',20,\'15555982111\',\'信息\'); INSERT
INTO Student
VALUES(\'04111025\',\'李新\',\'男\',20,\'13621111666\',\'英语\'); INSERT INTO Student
VALUES(\'04111026\',\'刘浮\',\'男\',19,\'15278982181\',\'信息\'); INSERT
INTO Student
VALUES(\'04111027\',\'二毛\',\'男\',20,\'13666684666\',\'英语\'); INSERT
INTO Student
VALUES(\'04111028\',\'朱琪\',\'女\',19,\'18789111777\',\'计算机\'); INSERT
INTO Student
VALUES(\'04111029\',\'小马\',\'男\',20,\'155533332111\',\'信息\'); INSERT
INTO Student
VALUES(\'04111030\',\'吴欣\',\'女\',20,\'13621199666\',\'英语\')
INSERT
/*向课程表中插入数据*/ INTO Course
SELECT \'0011\',\'数据结构\',\'64\',\'2\',\'3\',\'c语言\'
UNION SELECT \'0012\',\'数据库\',
\'64\',\'2\',\'3\',\'离散数学\' UNION SELECT \'0013\',\'信息管理\',\'64\',\'2\',\'3\',\'计算机基础\' UNION SELECT \'0014\',\'专业英语\',\'64\',\'2\',\'4\',\'基础英语\' UNION SELECT \'0015\',\'电气学\',
\'64\',\'2\',\'4\',\'电子科技\'
INSERT
/*向学生选课表中插入数据*/ INTO SC
SELECT \'04111020\',\'0011\',\'90\' UNION SELECT \'04111020\',\'0012\',\'87\' UNION SELECT \'04111022\',\'0012\',\'85\' UNION SELECT \'04111022\',\'0015\',\'76\' UNION SELECT \'04111023\',\'0014\',\'69\' UNION SELECT \'04111024\',\'0013\',\'90\' UNION SELECT \'04111025\',\'0011\',\'92\' UNION SELECT \'04111026\',\'0011\',\'83\' UNION SELECT \'04111027\',\'0012\',\'87\' UNION SELECT \'04111027\',\'0013\',\'79\' UNION SELECT \'04111028\',\'0011\',\'88\' UNION SELECT \'04111028\',\'0014\',\'85\' UNION SELECT \'04111029\',\'0012\',\'97\' UNION SELECT \'04111030\',\'0013\',\'69\' UNION SELECT \'04111028\',\'0015\',\'88\'
CREATE TABLE Student_temp
/*建立 Student_temp空表*/ (Sno CHAR(8) PRIMARY KEY NOT NULL, Sname VARCHAR(10) NOT NULL,
Sex CHAR(2) CHECK (Sex IN (\'男\',\'女\'))NOT NULL,
Age TINYINT CHECK(Age BETWEEN 15 AND 30)NOT NULL, Phonenumber CHAR(12) UNIQUE, Sdept VARCHAR(20)NOT NULL, );
INSERT INTO
Student_temp
/*在Student_temp表中保存计算机系所有学生的情况*/ SELECT * FROM Student
WHERE Sdept = \'计算机\';
ALTER TABLE Student ADD Addre CHAR(20);
/*向Student表中插入地址列*/
DELETE FROM SC
/*删除计算机系学生的选课记录*/ WHERE Sno IN(SELECT Sno FROM Student
WHERE Sdept =\'计算机\');
UPDATE Student
/*将每个学生的年龄增加1岁*/ SET Age=Age+1; UPDATE
SC SET Grade=0 WHERE Sno IN(SELECT Sno FROM Student
WHERE Sdept = \'计算机\') ;
SELECT Sno,Sname,Sdept
/*查询学生表中所有学生的学号、姓名、所在系*/ FROM Student;
SELECT *
/*查询学生表中的所有信息*/ FROM Student;
SELECT Sname,2013-Age AS Birthday
/*查询学生表中所有学生的出生年份，用2013-Age的值作为Birthday的列值*/ FROM Student;
SELECT *
/*查询课程表中的所有信息*/ FROM Course;
SELECT *
/*查询学生选课表中的所有信息*/ FROM SC;
SELECT DISTINCT Sno
/*查询所有选了课程的学生的学号*/ FROM SC;
SELECT Sno,Sname
/*查询计算机系所有学生的学号和姓名*/ FROM Student
WHERE Sdept =\'计算机\';
SELECT Sno
/*查询考试成绩在80分以上的学生学号*/ FROM SC
WHERE Grade >=80;
SELECT Sname,Sdept,Age /*查询年龄在20-23岁的学生姓名、所在系和年龄*/ FROM Student
WHERE Age >=20 AND Age
SELECT Sname,Sdept,Age,Sex /*查询计算机和英语系的学生姓名、年龄和性别*/ FROM Student
WHERE Sdept IN(\'计算机\',\'英语\');
SELECT Sname,Sdept,Age,Sex /*查询不是计算机和英语系的学生姓名、年龄和性别*/ FROM Student
WHERE Sdept NOT IN(\'计算机\',\'英语\');
SELECT *
/*查询信息系所有学生的情况*/ FROM Student
WHERE Sdept LIKE \'信息\';
SELECT *
/*查询姓张的学生所有信息*/ FROM
Student
WHERE Sname LIKE \'张%\';
SELECT
Cno,Credit
/* 查询\'数据结构\'课程的课程号和学分*/ FROM Course
WHERE Cname LIKE \'数据结构\';
SELECT Sno,Cno
/*查询没有考试成绩的课程后和学分*/ FROM SC
WHERE Grade IS NULL;
SELECT *
/*查询全体学生的信息,查询结果按所在系的升序排列,同一系中的学生按年龄降序排列*/ FROM Student
ORDER BY Sdept ,Age DESC;
SELECT AVG(Grade) AS \'平均成绩\'
/*查询选修\'0014\'号课程的学生的平均分*/ FROM SC
WHERE Cno =\'0014\';
SELECT MIN(DISTINCT Grade) AS \'最低分\' /*查询成绩表中的最低分*/ FROM SC;
SELECT COUNT(*)AS\'总人数\'
/*统计学生总人数*/ FROM Student;
SELECT COUNT (DISTINCT Sno)/*统计选修了课程的学生人数*/ FROM SC;
SELECT Cno ,COUNT (Sno )/*查询每门课程的课程号及选课人数*/ FROM SC GROUP BY Cno;
SELECT Student .*,SC .*
/*查询每个学生及其选修课的情况*/ FROM Student ,SC
WHERE Student.Sno=SC.Sno;
SELECT Student.Sno,Sname,Grade
/*查询选修了\'数据库\'课程并且成绩在85分以上的学生的学号和姓名*/
FROM Student ,Course,SC
WHERE Student.Sno = SC.Sno AND SC.Cno
= Course .Cno AND Cname = \'数据库\'AND Grade>85;
SELECT Sno,Sname
/*查询和王若在同一个系学习的学生的学号和姓名*/ FROM Student
WHERE Sdept =
(SELECT Sdept
FROM Student
SELECT Sname ,Age
/*查询其他系中比英语系某一学生年龄小的学生是姓名和年龄*/ FROM Student
WHERE Sdept \'英语\'
AND AGE
FROM Student
WHERE Sdept =\'英语\');
SELECT Sname /*查询选修了0014号课程的学生的姓名*/ FROM Student
WHERE EXISTS
(SELECT * FROM SC
WHERE Sno =Student.Sno AND Cno=\'0014\');
SELECT Cno FROM Course
UNION ALL
/*ALL表示合并两个查询输出的全部记录，没有ALL表示结果中不包含重复行*/ SELECT Cno FROM SC ORDER BY Cno;
CREATE PROCEDURE Student_Course
/*在学生-课程数据库中创建存储过程，查看和\'小马\'有关的基本信息及选修课程*/ AS
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Sno AND Sname=\'王若\' IF EXISTS(SELECT name FROM sysobjects
WHERE name=\'Student_Course\' AND type=\'P\')
DROP PROCEDURE Student_Course; WHERE Sname = \'王若\' ); EXECUTE Student_Course;/*查看存储的信息*/
SELECT Sno
/*查询选修\'0012\'或\'0015\'号课程的所以学生的学号*/ FROM SC
WHERE Cno=\'0012\' UNION SELECT Sno FROM SC
WHERE Cno=\'0015\';
CREATE UNIQUE INDEX Stusno ON Student(sno);/*建立索引*/ CREATE UNIQUE INDEX Coucon ON Course (Cno);
CREATE UNIQUE INDEX SCno ON SC (Sno ASC,Grade DESC);
drop index Stusno ON Student/*删除索引*/ drop index Coucon ON Course drop index SCno ON SC
CREATE VIEW C_Student
/*建立计算机系学生的视图，进行修改和插入操作时只能对计算机系的学生操作*/ AS
SELECT Sno,Sname,Sex,Age FROM Student WHERE Sdept=\'计算机\' WITH CHECK OPTION
CREATE VIEW Student_Course_0013(Sno,Sname,Sex,Age,Grade) /*建立选择0013号课程并且成绩在80分以上的学生视图*/ AS
SELECT Student.Sno,Sname,Sex,Age,Grade FROM Student,SC
WHERE Student.Sno=SC.Sno AND Grade>=80 AND Cno=\'0013\'
CREATE VIEW Student_Birth(Sno,Sname,Sbirth)/*建立反应学生出生年份的视图*/ AS
SELECT Sno,Sname,2013-Age FROM Student
CREATE PROCEDURE student_count/*创建一个存储过程，返回选修某门课程的学生人数*/ @CourseName VARCHAR(20), @StudentSum INT OUTPUT AS SELECT @StudentSum=COUNT (*) FROM Course ,SC
WHERE Course .Cno=SC.Cno AND Cname =@CourseName;
drop procedure student_count; /*
DELCARE @StudentSum INT;
EXECUTE student_count \'专业英语\',@StudentSum output; SELECT \'the result is:\',@Student_Sum ;*/
CREATE PROCEDURE student_course1 /*通过参数@StudentName将指定学生的姓名传递给存储过程*/
@StudentName VARCHAR(10) AS
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno
AND Sname=@StudentName ;
EXECUTE
student_course1 \'小马\';/*调用存储过程*/
/*删除视图*/
DROP VIEW C_Student;
DROP VIEW Student_Course_0013; DROP VIEW Student_Birth;
CREATE TRIGGER student_change/*创建触发器，当学生表中的记录被更新时，显示表中的所有记录*/
ON Student AFTER INSERT,UPDATE,DELETE AS
SELECT * FROM Student ;
CREATE TRIGGER sc_insert3 ON SC
/*在SC表上创建触发器，当向SC表中添加学生的选课记录时，检查学生的学号是否存在。若不存在，不能插入改条记录*/ AFTER INSERT AS
IF(SELECT COUNT (*) FROM Student ,inserted WHERE Student.Sno=inserted.Sno)=0 BEGIN
PRINT\'学号不存在，不能插入记录\' ROLLBACK TRANSACTION END;
INSERT INTO SC
VALUES(\'04111098\',\'0015\',73)
ALTER TABLE SC
/*删除触发器*/
DISABLE TRIGGER sc_insert2
