SQL常见面试题（学生表_课程表_成绩表_教师表）

表架构

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

建表语句

CREATE TABLE student 
  ( 
     s#    INT, 
     sname nvarchar(32), 
     sage  INT, 
     ssex  nvarchar(8) 
  ) 

CREATE TABLE course 
  ( 
     c#    INT, 
     cname nvarchar(32), 
     t#    INT 
  ) 

CREATE TABLE sc 
  ( 
     s#    INT, 
     c#    INT, 
     score INT 
  ) 

CREATE TABLE teacher 
  ( 
     t#    INT, 
     tname nvarchar(16) 
  )

 

插入测试数据语句：

 1 insert into Student select 1,N'刘一',18,N'男' union all
 2  select 2,N'钱二',19,N'女' union all
 3  select 3,N'张三',17,N'男' union all
 4  select 4,N'李四',18,N'女' union all
 5  select 5,N'王五',17,N'男' union all
 6  select 6,N'赵六',19,N'女' 
 7  
 8  insert into Teacher select 1,N'叶平' union all
 9  select 2,N'贺高' union all
10  select 3,N'杨艳' union all
11  select 4,N'周磊'
12  
13  insert into Course select 1,N'语文',1 union all
14  select 2,N'数学',2 union all
15  select 3,N'英语',3 union all
16  select 4,N'物理',4
17  
18  insert into SC 
19  select 1,1,56 union all 
20  select 1,2,78 union all 
21  select 1,3,67 union all 
22  select 1,4,58 union all 
23  select 2,1,79 union all 
24  select 2,2,81 union all 
25  select 2,3,92 union all 
26  select 2,4,68 union all 
27  select 3,1,91 union all 
28  select 3,2,47 union all 
29  select 3,3,88 union all 
30  select 3,4,56 union all 
31  select 4,2,88 union all 
32  select 4,3,90 union all 
33  select 4,4,93 union all 
34  select 5,1,46 union all 
35  select 5,3,78 union all 
36  select 5,4,53 union all 
37  select 6,1,35 union all 
38  select 6,2,68 union all 
39  select 6,4,71

 

问题：