SQL常见练习题

/*CREATE DATABASE SQL_TEST
USE SQL_TEST
GO
CREATE TABLE Student
(
Sno VARCHAR(20) not null PRIMARY KEY ,--学号
Sname VARCHAR(20) not null,--学生姓名
Ssex VARCHAR(20) not null, --学生性别
Sbirthday DATETIME,--学生出生年月
Class VARCHAR(20)--学生所在班级
)
GO
CREATE TABLE Teacher--老师
(
Tno VARCHAR(20) not null PRIMARY KEY ,--教工编号（主码）
Tname VARCHAR(20) not null,--教工姓名
Tsex VARCHAR(20) not null, --教工性别
Tbirthday DATETIME,--教工出生年月
Prof VARCHAR(20),--职称
Depart VARCHAR(20) not null--教工所在部门
)
GO
CREATE TABLE Course--课程
(
Cno VARCHAR(20) not null PRIMARY KEY ,--课程号
Cname VARCHAR(20) not null,--课程名称
Tno VARCHAR(20) not null REFERENCES teacher(Tno), --教工编号（外码）
)
GO
CREATE TABLE Score--分数
(

Sno VARCHAR(20) not null REFERENCES student(Sno), --学号（外码）
Cno VARCHAR(20) not null REFERENCES Course(Cno), --课程号（外码）
PRIMARY KEY(Sno,Cno),
Degree DECIMAL(4,1),--成绩

)
*/ 

INSERT INTO Student VALUES(101,'小一','男',1991-01-01,95033)
INSERT INTO Student VALUES(102,'大二','女',1992-02-01,95033)
INSERT INTO Student VALUES(103,'张三','男',1993-03-01,95034)
INSERT INTO Student VALUES(104,'李四','女',1994-04-01,95034)
INSERT INTO Student VALUES(105,'王五','男',1995-05-01,95035)
INSERT INTO Student VALUES(106,'啊六','女',1996-06-01,95035)
INSERT INTO Student VALUES(107,'姚明','男',1997-07-01,95036)
INSERT INTO Student VALUES(108,'曾华','女',1999-08-01,95036)
SELECT * FROM Student

INSERT INTO Teacher VALUES('T1','李嘉诚','男',1958-12-02,'副教授','计算机系')
INSERT INTO Teacher VALUES('T2','王健林','男',1968-07-02,'讲师','电子工程系')
INSERT INTO Teacher VALUES('T3','王思聪','男',1988-07-22,'讲师','土木工程系')
INSERT INTO Teacher VALUES('T4','马云','男',1968-07-02,'助教','编程系')
SELECT * FROM Teacher

INSERT INTO Course VALUES('1-1','计算机科学与技术','T1')
INSERT INTO Course VALUES('1-2','土木工程','T2')
INSERT INTO Course VALUES('1-3','电子信息工程','T3')
INSERT INTO Course VALUES('1-4','.NET