几条常用的sql语句(2)

1，　Student(S#,Sname,Sage,Ssex) 　　 学生表
　　　　Course(C#,Cname,T#) 　　　　　　 课程表
　　　　SC(S#,C#,score) 　　　　　　　　　 成绩表
　　　　Teacher(T#,Tname) 　　　　　　　 教师表

新建表：
CREATE TABLE Student (
S int,
Sname varchar(22),
Sage int,
Ssex varchar(8)
)
==========
这里先去掉了# ，正确的是S# 句子为：
CREATE TABLE SStudent (
S# int,
Sname varchar(22),
Sage int,
Ssex varchar(8)
)
============

CREATE TABLE Course
(
C INT,
Cname varchar(32),
T INT
)

CREATE TABLE Sc
(
S INT,
C INT,
score INT
)

CREATE TABLE Teacher
(
T INT,
Tname nvarchar(16)
)

插入语句：
insert into Student values (1,'刘一',18,'男') ;
insert into Student values ( 2,N'钱二',19,N'女' ) ;
insert into Student values ( 3,N'张三',17,N'男' ) ;
insert into Student values ( 4,N'李四',18,N'女' ) ;
insert into Student values ( 5,N'王五',17,N'男' ) ;
insert into Student values ( 6,N'赵六',19,N'女' ) ;

insert into Teacher values ( 1,N'叶平') ;
insert into Teacher values ( 2,N'贺高' ) ;
insert into Teacher values ( 3,N'杨艳' ) ;
insert into Teacher values ( 4,N'周磊') ;

insert into Course values ( 1,N'语文',1 ) ;
insert into Course values ( 2,N'数学',2 ) ;
insert into Course values ( 3,N'英语',3 ) ;
insert into Course values ( 4,N'物理',4) ;

insert into SC values ( 1,1,56) ;
insert into Course values ( 1,2,78 ) ;
insert into Course values ( 1,3,67 ) ;
insert into Course values ( 1,4,58 ) ;
insert into Course values ( 2,1,79) ;
insert into Course values ( 2,2,81 ) ;
insert into Course values ( 2,3,92 ) ;
insert into Course values ( 2,4,68 ) ;
insert into Course values ( 3,1,91) ;
insert into Course values ( 3,2,47) ;
insert into Course values ( 3,3,88 ) ;
insert into Course values ( 3,4,56 ) ;
insert into Course values ( 4,2,88 ) ;
insert into Course values ( 4,3,90 ) ;
insert into Course values ( 4,4,93 ) ;
insert into Course values ( 5,1,46 ) ;
insert into Course values ( 5,3,78 ) ;
insert into Course values ( 5,4,53 ) ;
insert into Course values ( 6,1,35 ) ;
insert into Course values ( 6,2,68 ) ;
insert into Course values ( 6,4,71) ;

1，查询平均成绩大于60分的同学的学号和平均成绩

select S,avg(score) from SC group by S having avg(score)>60;

2，查询学过“贺高”所教的课的同学的学号和姓名

select S,Sname from Student where S in (select S from SC where C in (select C from Course where T in (select T from Teacher where Tname='贺高')))