实验四T-SQL高级查询.doc

实验四 T-SQL高级查询（1） [实验目的] 通过本实验掌握使用SELECT语句进行分组与多表连接查询的技能。 [实验内容] 使用查询设计工具连接至master数据库； 使用如下SQL语句创建Student、Course和SC数据表： CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5) NOT NULL, SID CHAR(18) UNIQUE, Ssex NCHAR(1) DEFAULT 男, Sage TINYINT CHECK(Sage=15 AND Sage=45), Sdept NVARCHAR(20) ); CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname NVARCHAR(20) NOT NULL, Credit NUMERIC(3,1) CHECK(Credit0), Semester TINYINT ); CREATE TABLE SC ( Sno CHAR(7) NOT NULL, Cno CHAR(6) NOT NULL, Grade TINYINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ) 并在数据表中加入测试数据。 CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5) NOT NULL, SID CHAR(18) UNIQUE, Ssex NCHAR(1) DEFAULT 男, Sage TINYINT CHECK(Sage=15 AND Sage=45), Sdept NVARCHAR(20) ); insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000001,张三,1,男,20,计算机) insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000002,张三,2,男,22,计算机) insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000003,张三,3,女,23,计算机) insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000004,张三,4,男,23,计算机) insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000005,张三,5,女,22,计算机) insert into Student(Sno,Sname,SID,Ssex,Sage,Sdept) values (000006,张三,6,男,25,计算机) CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname NVARCHAR(20) NOT NULL, Credit NUMERIC(3,1) CHECK(Credit0), Semester TINYINT ); insert into Course(Cno,Cname,Credit,Semester) values(001,计算机基础,3,1) insert into Course(Cno,Cname,Credit,Semester) values(002,C++程序设计,3,2) insert into Course(Cno,Cname,Credit,Semester) values(003,数据结构,3,1) insert into Course(Cno,Cname,Credit,Semester) values(004,Java程序设计,3,2) if(exists(select * from sysobjects where name=SC and type=U)) DROP TABLE SC CREATE TABLE SC ( Sno CHAR(7) NOT NULL, Cno CHAR(6) NOT NULL,