SQL语句操作练习（三）

题目

对于“Student”数据库的三个基本表：
S(Sno,Sname,Sage,Ssex,Sdept),C(Cno,Cname,Teacher),SC(Sno,Cno,Grade)

1.使用SQL Server Management Studio 中为 “学生”数据库的学生表（student）、课程表（course）、选课表(sc)添加记录。

数据库中表的信息：
（1）S表：

（2）C表：

（3）SC表：

2. 完成如下查询语句：

(1) 查询LIU老师所授课程的课程号和课程名。

[1]代码实现：

SELECT Cno, Cname 
FROM C 
WHERE Teacher = 'LIU老师'

[2]运行效果：

(2) 查询年龄大于23岁的男生的学号和姓名。

[1]代码实现：

SELECT Sno, Sname 
FROM S
WHERE Sage > 23 
AND Ssex = '男' 

[2]运行效果：

(3) 查询学号为S3学生所学课程的课程名与任课教师名。

[1]代码实现：

SELECT DISTINCT C.Cname, C.Teacher
FROM C
JOIN SC ON SC.Cno = C.Cno
WHERE SC.Sno = 'S3'

[2]运行效果：

(4) 查询至少选修LIU老师所授课程中一门课程的女学生姓名。

[1]代码实现：

SELECT DISTINCT SC.Sno
FROM SC
JOIN S ON S.Sno = SC.Sno
JOIN C ON C.Cno = SC.Cno
WHERE SC.Cno IN (SELECT Cno FROM C WHERE Teacher = 'LIU老师')
AND S.Ssex = '女'

[2]运行效果：

(5) 查询Wang同学不学的课程的课程号。

[1]代码实现：

SELECT DISTINCT C.Cno
FROM C
JOIN SC ON SC.Cno = C.Cno
WHERE C.Cno NOT IN 
(SELECT Cno FROM SC WHERE Sno IN 
(SELECT Sno FROM S WHERE Sname = 'Wang同学'))

[2]运行效果：

(6) 查询至少选修两门课程的学生学号。

[1]代码实现：

SELECT DISTINCT S.Sno
FROM S
JOIN SC ON SC.Sno = S.Sno
GROUP BY S.Sno
HAVING COUNT(*) >= 2

[2]运行效果：

(7) 查询全部学生都选修的课程的课程号与课程名。

[1]代码实现：

SELECT C.Cno, C.Cname
FROM SC
JOIN S ON S.Sno = SC.Sno
JOIN C ON C.Cno = SC.Cno
GROUP BY C.Cno, C.Cname
HAVING COUNT(S.Sno) = (SELECT COUNT(S.Sno) FROM S)

[2]运行效果：

(8) 查询选修课程包含LIU老师所授课程的学生学号。

[1]代码实现：

SELECT DISTINCT S.Sno
FROM S
JOIN SC ON SC.Sno = S.Sno
WHERE SC.Cno IN (SELECT Cno FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE Teacher = 'LIU老师'))

[2]运行效果：

(9) 统计有学生选修的课程门数。

[1]代码实现：

SELECT COUNT(DISTINCT Cno) AS '有学生选修的课程门数' FROM SC

[2]运行效果：

(10) 求选修C4课程的女学生的平均年龄。

[1]代码实现：

SELECT AVG(Sage) AS '选修C4课程的女学生的平均年龄'
FROM S
JOIN SC ON SC.Sno = S.Sno
WHERE S.Ssex = '女'
AND SC.Cno = 'C4'

[2]运行效果：

(11) 求LIU老师所授课程的每门课程的平均成绩。

[1]代码实现：

SELECT SC.Cno, AVG(Grade) AS '平均成绩'
FROM SC
JOIN C ON C.Cno = SC.Cno
WHERE SC.Cno IN(SELECT Cno FROM C WHERE Teacher = 'LIU老师')
GROUP BY SC.Cno

[2]运行效果：

(12) 统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

[1]代码实现：

SELECT Cno, COUNT(*) AS '选修人数'
FROM SC
GROUP BY Cno
ORDER BY COUNT(*) DESC, Cno

[2]运行效果：

(13) 检索学号比Wang同学大，而年龄比他小的学生姓名。

[1]代码实现：

SELECT Sno
FROM S
WHERE Sno > (SELECT Sno FROM S WHERE Sname = 'Wang同学')
AND Sage < (SELECT Sage FROM S WHERE Sname = 'Wang同学')

[2]运行效果：

(14) 在表SC中查询成绩为空值的学生学号和课程号。

[1]代码实现：

SELECT S.Sno, SC.Cno
FROM S
JOIN SC ON SC.Sno = S.Sno
WHERE SC.Grade IS NULL

[2]运行效果：

(15) 查询姓名以L打头的所有学生的姓名和年龄。

[1]代码实现：

SELECT Sname, Sage
FROM S
WHERE Sname LIKE 'W%'

[2]运行效果：

(16) 求年龄大于女同学平均年龄的男学生姓名和年龄。

[1]代码实现：

SELECT Sname, Sage
FROM S
WHERE Sage > (SELECT AVG(Sage) FROM S WHERE Ssex = '女')
AND Ssex = '男'

[2]运行效果：

(17) 求年龄大于所有女同学年龄的男学生姓名和年龄。

[1]代码实现：

SELECTSname, Sage
FROM S
WHERE Sage > (SELECT MAX(Sage) FROM S WHERE Ssex = '女')
AND Ssex = '男'

[2]运行效果：

考察

考察SELECT语句的使用方法：

1.基本格式

SELECT select_list[ INTO new_table ] 
FROM table_source [ WHERE search_condition ] 
[ GROUP BY group_by_expression ] 
[ HAVING search_condition ] 
[ ORDER BY order_expression [ ASC | DESC ] ]

2.例题：

例1：对学生成绩的查询。

/*方法一*/
select s.sname,sc.grade
from s,sc
where s.sno=sc.sno
/*方法二*/
select s.sname,sc.grade
from s JOIN sc
on s.sno=sc.sno

例2：查询结果只显示前n条记录

select top 3 sno,sname,sdept
from s

例3：分组查询、并改名

select sage as 学生年龄,count(*)as 学生数
from s
group by sage

例4：使用CASE函数分类查询

select * ,
     case cno
        when 1 then round((grade*1.03),-1)
        when 2 then round((grade*1.04),-1) 
        when 3 then round((grade*1.05),-1)
        else round((grade*1.01),-1)
     end as 期望成绩
from sc

例5：使用LIKE运算符

SELECT * FROM 学生 WHERE 姓名 LIKE '%李%'

例6：使用BETWEEN运算符

SELECT * FROM 学生 WHERE 入学成绩 BETWEEN 550 AND 580

例7：对查询结果分组

在“学生”表中按性别统计所有学生的最高成绩。

USE 学生管理
  SELECT CASE 性别
        WHEN 0 THEN '女'
        ELSE '男'
        END AS 性别, 
        MAX(入学成绩) AS 最高成绩
  FROM 学生
  GROUP BY 性别 

注意

在使用GROUP BY子句时，SELECT子句中每一个非聚合表达式内的所有列都应包含在GROUP BY列表中。否则将会返回错误信息。