SQL习题集

SQL习题集

数据库拆表原则

1. 简单字段中的复合字段：解决方法：向上打通，或拆成两表

2. 完全依赖：知道a就能知道b,返过来就不一定.实现：将同一个主题的东西放在一起 3. 无传递依赖。将传递依赖转为直接依赖。原因：减少插入冗余。减少增删异常。 4. 当出现一对多时不能横拆，一定拆成两个表，特别的固定范围可以横拆(签到管理) 5. 多对多必有中间表

6. 代码表，大量重复的或枚举型可以用代码表. 7. 能计算的字段不要 数据定义语句DDL create alter drop 数据控制语句DCL grant deny revoke 数据操纵语句DML

select update insert delete 一.SQL Server 2000 Select 变化集锦

1． 查询所有字段(效率低)

查询所有的老师 select *

from dbo.教师 2．字段枚举 查询教师的ID，及姓名两项

select 教师_ID,教师名 from dbo.教师

3．字段取别名方法有二

1）as可以省略

select 教师名 as teacher from dbo.教师 2）

select teacher = 教师名 from dbo.教师

4.字段的可计算性 1) 简单字段计算

教师工资的10% select 工资*0.1 from dbo.教师 2)把一个检索结果作为查询字段

本学校的师生比例 select ( select count(*) from dbo.教师 ) /1.0/

( select count(*) from dbo.学生 )

5 取检索结果的前几个

select top 3 教师_ID from dbo.教师

6 取检索结果总数的百分比

select top 30 percent 教师_ID from dbo.教师

7 去掉重复记录 去掉的是完全重复

select distinct 性别_ID from dbo.教师

8 在聚合统计函数中统计不重复指定字段

select count(distinct 系_ID ) from dbo.教师

9 case when then else end在seelct中的应用

从教师表中查询，将性别_id是1的显示为男,是2的显示为女 select ( case when 性别_ID=1 then '男' when 性别_ID=2 then '女' end )

from dbo.教师 横向查询男女人数

select sum( case when 性别_ID=1 then 1 else 0 end ) as 'nan' , sum( case when 性别_ID=2 then 1 else 0 end ) as 'nv'

from dbo.教师

10 可以重复列出表的的字段

select *,*,* from 教师 11 检索结果放常量

select 教师名, '出生于',出生日期 from dbo.教师

-----------------------------------

From变化集锦 1．从一个表中检索 select *

from dbo.教师

2 从一个检索结果中再检索即从临时表中检索

select * from ( select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

)lin –sqlserver2000中临时表通常要起别名 where lin.教师_ID <4

3.从多表中检索，构成迪卡尔乘积（效率低）

select dbo.教师.*,系名 from dbo.教师,dbo.系

where dbo.教师.系_ID=dbo.系.系_ID 4 多表的联接查询 1)左连接（尊重左边）

select *

from dbo.系 left join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID

2）右连接（尊重右边）

select *

from dbo.系 right join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID

3)内连接（都不尊重,即两边都得有）下例为两个以上表的连接写法 select *

from dbo.系 inner join dbo.教师

on dbo.系.系_ID=dbo.教师.系_ID inner join dbo.教师认课 on dbo.教师.教师_ID=dbo.教师认课.教师_ID 4)全连接（都尊重）

select * from dbo.系 full join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID

5. 给被检索表起别名 注意：起了别名就一定用别名引用字段

错误的

select dbo.教师.教师名 from dbo.教师 js 正确的

select js.教师名 from dbo.教师 js where集锦

1． 常规关系运算 >、 >=、 <、 <=、 !=、 <>、 =

select 教师_ID from dbo.教师 where 教师_ID<>1 只检索表结构不要任何数据

Select * From dbo.教师 Where 1!=1 2． 常规逻辑运算 not 、and 、or

select 教师_ID from dbo.教师

where not( 教师_ID>=1 and 教师_ID<=8 )

3.区间 教师_ID>=1 并且 教师_ID<=8

select 教师_ID from dbo.教师

where 教师_ID between 1 and 8

4.检索null值

select 教师_ID from dbo.教师

where 出生日期 is null

5.关于字符的模糊查询 1)姓张的所有老师 select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID

from dbo.教师

where 教师名 like '%张' --%代表任意多个字 2)姓张的但名字只有两个字的老师

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID

from dbo.教师

where 教师名 like '_张' --_ 只代表一个字 3)名字中含有 国 字的老师

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

where 教师名 like '%国%'

4)教师名的第一个字母在a-m之间的教师

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

where 教师名 like '[a-m]%'

5)教师名的第一个字母在 不在 a-m之间的教师

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

where 教师名 like '[^a-m]%'

6)教师名的第一个字母是a或b或m的教师

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

where 教师名 like '[a,b,m]%'

7) 教师名的第一个字母是a到k之间或o到x之间的教师 select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

where 教师名 like '[a-k,o-x]%'

6．关于IN的用法 1)枚举型

select *

from dbo.教师

where 教师_ID in ( 1,3,5)

select *

from dbo.教师

where 教师_ID not in ( 1,3,5)

2)嵌套子查询型 有课上的老师 select *

from dbo.教师 where 教师_ID in ( select 教师_ID from dbo.教师认课 )

7. exists 判断子查询是否有结果（强调有无，不关心具体是什么）

select *

from dbo.教师

where exists --存在 (

select *

from dbo.教师认课

where dbo.教师认课.教师_ID=dbo.教师.教师_ID )

8. any 其中一个

教师ID大于所有 教师ID中任意一个的教师(相当于大于最小的教师ID) select *

from dbo.教师

where 教师_ID > any ( select 教师_ID from dbo.教师认课 )

9. all 全部

相当于 大于最大max select *

from dbo.教师

where 教师_ID > all (

select 教师_ID

from dbo.教师认课 ) Group by 集锦

1.group by 通常与聚合函数(avg ,sum ,count,max,min)配合使用

查询出学生表中男女生人数 select count(*) as renshu from dbo.学生 group by 性别_ID 2针对多字段的分组

每个系的男女生人数

select count(*) as renshu from dbo.学生

group by 性别_ID,系_ID 3 多表查询中的分组应用 select count(*) as rs,系名

from dbo.学生 xs inner join dbo.系 xi on xs.系_ID = xi.系_ID

group by 系名

4 对于计算字段的分组应用 查询出计算机系学生数和其它系的学生数

select count(*),( case when 系_ID=1 then 'jsjx' else 'qt' end )

from dbo.学生 group by ( case when 系_ID=1 then 'jsjx' else 'qt' end )

5 分组条件having的用法 系人数在10人以上的系

select count(*) as shu ,系_ID from dbo.学生 group by 系_ID

having count(*) >10 或写成 select * from (

select count(*) as shu ,系_ID from dbo.学生 group by 系_ID ) linshi

where linshi.shu >10

order by集锦

1. 对单个字段的排序(asc升序可省略 desc降序)

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师 order by 教师名

2.多字段的排序（先按第一个字段排序，如果相同再按第二个字段排序） 写法1。

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

order by 性别_ID, 系_ID desc 写法2。

select 教师_ID , 教师名, 出生日期, 系_ID as xxx , 职称_ID from dbo.教师

order by 4, 性别_ID desc –4代表select 后面 第四个字段 3.按笔画排序

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

order by 教师名 Collate Chinese_PRC_Stroke_ci_as desc

4.order by 可按给定表达式计算结果排序 按教师年龄大小降序排序

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师

order by DATEDIFF( yy, 出生日期,getdate() ) desc 将本年入学新生排在前面，老生排在后面

select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级, ( case when 届 = 2006 then 1 else 0 end )

from dbo.学生 order by ( case when 届 = 2006 then 1 else 0 end ) desc

5.随机排序

order by newid() 其它：

--union 合并

将本校学生与教师名字全部查询出来（去掉重复）。 select 教师名 from dbo.教师 union select 姓名 from dbo.学生

将本校学生与教师名字全部查询出来（不去掉重复）。 select 姓名 from dbo.学生 union all select 姓名 from dbo.学生 常用函数 --数学函数 select ABS(-9) select CEILING(9.00001) select FLOOR(9.9) select PI() select POWER(2,3) select SQRT(9) select SIGN(0) select ROUND( 892.35,1,7 ) select ROUND( 892.35,1,0 )

--绝对值

--比输入值大的最小整数 --比输入值小的最大整数 --派

--第一个参数的第二个参数次方 --平方根

--返回参数的正负

select 1+FLOOR( LOG10(48091) ) --随机数

select FLOOR(RAND()*10)

-----字符串函数------------------------------------------------------------------ select LEFT('aaaaa',2) --左截取 select RIGHT('123456',2) --右截取 select LEN('1') --字符长度 select LOWER('aaBN') --全小写 select UPPER('aaBN') --全大写 select rtrim( LTRIM(' aaa ') )+'**' --从右去掉空格,再从左去掉空格 select SUBSTRING('abcdefg',2,3) --从第2位开始截取3个字符 select REPLACE('aba','a','ccc') --把A换成CCC select STUFF( 'aaaaa',1,2,'bbbbbb' ) --从第1位开始2位换成BBBBBB ---------------------日期时间---------------------------- select GETDATE()

select YEAR( GETDATE() ) select month( GETDATE() ) select day( GETDATE() )

select DATEADD( mm,100 ,'1990-10-10' ) select DATEDIFF( yy, '1988-8-8',getdate() )

select 教师名 , DATEDIFF( yy, 出生日期, getdate() ) as 年龄 from dbo.教师

select DATENAME( yy,getdate() )+'年'

select DATEPART( yy, getdate() )

select DATENAME( yy,getdate() )+'年'+DATENAME( mm,getdate() )+'月'+ DATENAME( dd,getdate() )+'日' --本周/月/季/年的一 是 哪一天

select dateadd(day, 2- DATEPART(weekday,getdate()) ,getdate() )

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()) , 0) SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()) , 0) SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()) , 0) SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()) , 0) --上周/月/季/年 最后一天

SELECT dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) SELECT dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)) SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--本月的第一个星期一

select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

--上个月 多少天 --上月 最后一天 declare @a datetime

set @a= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

print 1+month(getdate()) 

print convert(char(2),1+month(getdate()) )+'-' --select dateadd(mm, DATEDIFF(mm,0,getdate()) ,0)

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

--通用时间差

declare @a datetime

set @a= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

declare @b datetime

set @b= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0))

print datediff(day, @b,@a);

-------转换函数-------------------------------------------------------------------------- select cast( '666' as int )+100

select convert(varchar(13),999 )+'***'--Integer.parseInt() select

convert(char(4),year( getdate() ))+'-'+convert(char(2),month( getdate() ))+'-'+convert(char(2),day( getdate() ) )

insert into 用法

1.向教师表中加入一个记录，字段的顺序与数目和表中一致 insert into 教师 values( ); 2. 字段的数目与顺序自己指定

Insert into 教师 (姓名，性别_id) values ( ‘张’,1) 3 . 从select检索中插入成批记录 insert into dbo.学生 ( 姓名) select 教师名 from dbo.教师

delete from dbo.xuesheng where ming like 'z%' update用法

1. 更新一个字段 Update 教师

Set 教师名=’李四’ Where 教师_id = 3; 教师基本工资上调5% Update 教师

Set 基本工资=基本工资*(1.05) 2. 多字段更新，每一个用 ，号隔开 Update 教师 Set 教师名=’李四’ , 性别_id = 1

Where 教师_id = 3;

集合论 交集 条件A AND 条件B select from A表 where exist （B查询）

SELECT语句A INTERSECT SELECT语句B 并集 条件A OR 条件B SELECT语句A UNION SELECT语句B 补集 NOT 条件A [HAVING] NOT 统计分组条件 SELECT语句A UNION SELECT语句B 差集 select from A表 where not exist （B查询）

--------------------------------------------------------------------------------------------------------------------------------------- 集合相等

集合包含 A包含B B中的元素 一定在A中 不在A中的元素 一定不在B中 1.用一句SQL语句获取 学生表中年龄最大的学生

--1.是一句SQL语句，

--2.绝对不能使用任何统计函数，如sum()，max()，min()，avg()，count()以及所有DBMS提供的函数。 方法一

select top 1 年龄 from 学生

order by 年龄 desc 方法二

select 年龄 from 学生

where 年龄 >= all (select 年龄 from 学生) 2.- 学生表中年龄第三大的学生

select top 1 年龄 from 学生

where 年龄 not in (

select top 2 年龄 from 学生

order by 年龄 desc )

order by 年龄 desc 3．分页sql

4. 删除除了自动编号不同,其他都相同的学生冗余信息

自动编号 学号 姓名 课程编号 课程名称 分数 1 2005001 张三 0001 数学 69 2 2005002 李四 0001 数学 89 3 2005001 张三 0001 数学 69

delete from tablename where 自动编号 not in (

select min(自动编号) from tablename

group by 学号,姓名,课程编号,课程名称,分数

)

5 . 有学生信息表{学号,班级,姓名,学科,成绩},

请写出每班语文成绩在前10名并且数学成绩不在倒数10名的学生的个人信息

select 学号,班级,姓名,学科,成绩 from 学生 where 学号 in ( select top 10 学号 from 学生 where 学科='语文' order by 成绩 desc )

and 学号 not in ( select top 10 学号 from 学生 where 学科='数学' order by 成绩 asc )

6.查询出每门课都大于80分的学生姓名

name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90

select distinct name from table

where name not in (

select distinct name from table

where fenshu<=80 ) 或者

select name from table12 group by name

having min(fenshu)>80

9.查询所有选修了1号课程的学生姓名

select 学生名 from 学生 where exists (

select *

from 学生选课

where 学生选课.学生_ID=学生.学生_ID and 课程_ID=1 )

10. --2005年选了郭德刚课程的学生名单

select distinct 学生名 from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 where 年度=2005 and 课程_ID in ( select 课程_ID from dbo.教师任课 where 年度=2005 and 教师_ID= ( select 教师_ID from dbo.教师 where 教师姓名='郭德刚' ) ) )

11.--2006年，选了 男老师课程 成绩不及格的男学生

select distinct 学生名 from 学生

where 性别_ID=1 and 学生_ID in ( select distinct 学生_ID from 学生选课 where 成绩<60 and 年度=2006 and 课程_ID in ( select distinct 课程_ID from 教师任课 where 年度=2006 and 教师_ID in ( select 教师_ID from 教师 where 性别_ID=1 ) ) )

12. --每科 都及格的学生

--变换：没有一科 不及格的学生

select 学生_ID,学生名 from 学生

where 学生_ID not in ( select distinct 学生_ID

from 学生选课 where 成绩<60 )

13.张三丰教过的 姓张的物理系的学生

select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级 from dbo.学生

where 姓名 like '张%' and 系_ID in ( select 系_ID from dbo.系 where 系名 like '%物理系%' )

and 学生_ID in ( select 学生_ID from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.教师认课 where 教师_ID in ( select 教师_ID from dbo.教师 where 教师名='张三丰' ) ) )

14.(2003年) 在(911) 教室上过课的[老师]

select 教师名 from dbo.教师 where 教师_ID in (

select 教师_ID from dbo.教师认课 where 开课_ID in ( select 开课_ID from dbo.开课 where 年度='2003' )

and 开课_ID in ( select 开课_ID from dbo.课时安排 where 教室号=911 ) )

15.--(女老师)教过的(不及格)的(男)[学生]？

select 姓名 from dbo.学生 where 性别_ID in ( select 性别_ID from dbo.代码_性别 where 性别名='男' )

and 学生_ID in( select 学生_ID from dbo.学生选课 where 成绩<60 )

and 学生_ID in ( select 学生_ID from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.教师认课 where 教师_ID in ( select 教师_ID from dbo.教师 where 性别_ID in ( select 性别_ID from dbo.代码_性别 where 性别名='女' ) ) ) )

16--(毕业年级)中学分(不够)(12学分) 的[学生]？

select 姓名 from dbo.学生

where 届=year(getdate())-3 and 学生_ID in ( select 学生_ID from dbo.学生选课 a,dbo.课程 b, dbo.开课 c where a.开课_ID = c.开课_ID and b.课程_ID= c.课程_ID group by 学生_ID having sum(学分)<12 )

17--(计算机系)开过至少两门课的[老师]？

select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师 where 教师_ID in (

select 教师_ID from dbo.教师认课 group by 教师_ID having count(*)>=2 )

and 系_ID in (

select 系_ID from dbo.系

where 系名 like '%计算机%' )

18.本校中[老师]或[学生]重名的名字

select * from (

select 姓名 from dbo.学生 union all

select 教师名 from dbo.教师 ) lin

group by lin.姓名 having count(*)>=2

19 05年开课数最少的系？（用临时表）

select 系名 from dbo.系 w, (

select top 1 系_ID,count(*) as shu from dbo.课程 a,dbo.开课 b

where a.课程_ID=b.课程_ID and 年度=2005 group by 系_ID order by shu asc )lin

where w.系_ID = lin.系_ID 20.各系中教师学生比例最低的 select w.系_ID, 系名 from dbo.系 w, ( select top 1 l1.系_ID , l1.lss/1.0/xss as bz from ( select 系_ID, count(*) as lss from dbo.教师 group by 系_ID )l1

, ( select 系_ID, count(*) xss from dbo.学生 group by 系_ID )l2 where l1.系_ID=l2.系_ID order by bz ) lin where w.系_ID = lin.系_ID 21.全都及格了的学生

select 学生_ID, 姓名, 出生日期, 性别_ID, 籍贯_ID, 系_ID, 届, 班级 from dbo.学生 where 学生_ID in (

select 学生_ID from dbo.学生选课 group by 学生_ID having min(成绩)>=60 )

22.求选了跟[李宇春课程数量] 相同 的[学生]？

select 姓名 from dbo.学生 where 学生_ID in (

select 学生_ID from dbo.学生选课 group by 学生_ID having count(*)= ( select shu from ( select 学生_ID,count(*) shu from dbo.学生选课 group by 学生_ID )lin where 学生_ID = ( select 学生_ID from dbo.学生 where 姓名 = '李宇春' ) ) )

and 姓名 != '李宇春' 23 与java 学时 相同的 课程

select 课程_ID, 课程名, 学分, 学时, 系_ID, 年级, 学期

from dbo.课程 where 学时 = ( select 学时 from dbo.课程 where 课程名='java' )

and 课程名!='java'

select b.*

from dbo.课程 a , dbo.课程 b where a.学时=b.学时

and a.课程名='java' and b.课程名!='java' 24课时安排冲突的记录

select distinct a.开课_ID

from dbo.课时安排 a,dbo.课时安排 b where a.星期=b.星期 and a.节=b.节

and a.教室号=b.教室号 and a.开课_ID!=b.开课_ID 25 --张无忌 老师都开过什么课了 select 课程_ID,课程名 from dbo.课程

where 课程_ID in ( select 课程_ID from dbo.开课 where 开课_ID in ( select 开课_ID from dbo.教师认课 where 教师_ID in ( select 教师_ID from dbo.教师 where 教师名='张无忌' ) ) )

select 课程_ID,课程名

from dbo.课程,dbo.开课,dbo.教师认课,dbo.教师 where dbo.课程. 课程_ID= dbo.开课.课程_ID and dbo.开课.开课_ID=dbo.教师认课.开课_ID and dbo.教师认课.教师_ID=dbo.教师.教师_ID and dbo.教师.教师名='张无忌' 26--平均分比 那英 高的学生名单

--查名单[ 平均分 >[ 那英的平均分] ]

select 姓名 from dbo.学生

where 学生_ID in ( select 学生_ID from dbo.学生选课 group by 学生_ID having avg(成绩)> ( select avg(成绩) from dbo.学生选课 where 学生_ID= ( select 学生_ID from dbo.学生 where 姓名='那英' ) ) )

27 --本年度选课 少于3门的学生

select 学生_ID, 姓名 from dbo.学生 where 学生_ID in (

select 学生_ID from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.开课 where 年度=year(getdate()) )

group by 学生_ID having count(*) <3 )

28 --全及格了的学生

select *

from dbo.学生

where 学生_ID not in (

select 学生_ID from dbo.学生选课 where 成绩<60 )

29. --2006 2007年度 开课的门数

select count(case 年度 when 2006 then 1 end), count(case 年度 when 2007 then 1 end) from dbo.开课

30. --比物理系所有老师 年龄都大的 外语系老师

select *

from dbo.教师

where datediff(yy,出生日期,getdate()) >all ( select datediff(yy,出生日期,getdate()) from dbo.教师 where 系_ID= ( select 系_ID from dbo.系 where 系名='物理系' ) )

and 系_ID= ( select 系_ID from dbo.系 where 系名='外语系' )

31. --java前5名，VC后5名 的学生

select *

from dbo.学生 where 学生_ID in (

select top 5 学生_ID

from dbo.课程 a,dbo.开课 b,dbo.学生选课 c

where c.开课_ID=b.开课_ID and b.课程_ID=a.课程_ID and 课程名='java' order by 成绩 desc )

and 学生_ID in (

select top 5 学生_ID

from dbo.课程 a,dbo.开课 b,dbo.学生选课 c

where c.开课_ID=b.开课_ID and b.课程_ID=a.课程_ID and 课程名='vc' order by 成绩 asc )

32.--2006年选了课的学生

select *

from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 a,dbo.开课 b where a.开课_ID=b.开课_ID )

select *

from dbo.学生 x where exists

( )

select *

from dbo.学生选课 a,dbo.开课 b where a.开课_ID=b.开课_ID and x.学生_ID=a.学生_ID

select *

from dbo.学生 x where 学生_ID in (

select distinct 学生_ID from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.开课 where 年度=2006 ) )

select distinct x.*

from dbo.学生 x,dbo.学生选课 a,dbo.开课 b where x.学生_ID=a.学生_ID and a.开课_ID=b.开课_ID and b.年度=2006

------------------------------------------------------------------------------- 33.--25岁以下的 选了java课的男生

select *

from dbo.学生

where 性别_ID=1 and datediff(year,出生日期,getdate())<=25 and 学生_ID in (

select 学生_ID

from dbo.学生选课 a , dbo.开课 b , dbo.课程 c

where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and c.课程名 like '%java%' )

----------------------------------------------------------------------------------- 34 --大四的总学分小于5的学生

select *

from dbo.学生 where 学生_ID in (

select *

from dbo.学生选课 a ,dbo.开课 b ,dbo.课程 c where not(成绩 is null or 成绩<60) and a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID

group by 学生_ID having sum(学分)<5 )

and year(getdate())-届=4

-------------------------------------------------------------------------------------- 35--年龄大于 学校老师平均年龄的 外语系老师

select *

from dbo.教师 j ,dbo.系 x

where datediff(year,出生日期,getdate())> ( select avg( datediff(year,出生日期,getdate()) ) from dbo.教师 )

and j.系_ID=x.系_ID and 系名 like '%外语%'

------------------------------------------------------------------------------------------ 36----教过张靓影的老师 select *

from dbo.教师 where 教师_ID in ( select distinct 教师_ID from dbo.教师认课 where 开课_ID in ( select 开课_ID from dbo.学生选课 where 学生_ID in ( select 学生_ID from dbo.学生 where 姓名 like '%张靓影%' ) ) )

-------------------------------------------------------------- 37--java在前两名 而vc在后两名的 学生 select *

from dbo.学生 where 学生_ID in ( select top 2 学生_ID from dbo.学生选课 a ,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and c.课程名 ='java' order by 成绩 desc ) and

学生_ID in ( select top 2 学生_ID from dbo.学生选课 a ,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and UPPER(c.课程名) ='VC' order by 成绩 asc )

------------------------------------------------------------------ 38 --查询选修 所有计算机系课程 的学生姓名 select 姓名

from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 a,dbo.开课 b,dbo.课程 c where a.开课_ID =b.开课_ID and b.课程_ID =c.课程_ID and 系_ID=1 group by 学生_ID having count(*)= ( select count(*) from dbo.课程 where 系_ID=1 ) )

39.--选修了2号学生 所有课程 的学生 select distinct 学生_ID from dbo.学生选课 x

where x.学生_ID!=2 and not exists ( select * from dbo.学生选课 y where y.学生_ID=2 and not exists ( select * from dbo.学生选课 z where z.学生_ID=x.学生_ID and z.开课_ID=y.开课_ID ) )

40--每科 都及格的学生

--变换：没有一科 不及格的学生 select * from 学生

where 学生_ID not in

( select distinct 学生_ID from 学生选课 where 成绩<60 )

--区别

select distinct 学生_ID from 学生选课 a where not exists ( select 学生_ID from 学生选课 b where a.学生_ID=b.学生_ID and b.成绩<60 )

41 --物理系的课程 总学分 比外语系 多多少 select ( select sum(学分) from dbo.课程 where 系_ID=3 ) - ( select sum(学分) from dbo.课程 where 系_ID=2 )

42 -- 至少选3门课的学生 select *

from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 group by 学生_ID having count(*)>=3 )

43--按年龄段，统计学生人数(行转列)

select

count(*),(year(getdate())-year(出生日期))/10*10

from dbo.学生

group by (year(getdate())-year(出生日期))/10*10 --(行转列) select

count(case when (year(getdate())-year(出生日期)) between 20 and 30 then 班级 end) as '20-30', count(case when (year(getdate())-year(出生日期)) between 30 and 40 then 班级 end) as '30-40', count(case when (year(getdate())-year(出生日期)) between 40 and 50 then 班级 end) as '40-50', count(case when (year(getdate())-year(出生日期)) between 50 and 60 then 班级 end) as '50-60', count(case when (year(getdate())-year(出生日期)) between 60 and 70 then 班级 end) as '60-70' from dbo.学生

10.试用SQL查询语句表达下列对教学数据库3个基本表S,C,SC的查询.

S(S#, Sn, Sa, Sex)

C(C#, Cn, TEACHER) SC(S#, C#, GRADE)

(1)在表C中统计开设课程的教师人数. (2)求选修C4课程的女学生的平均年龄.

(3)求每个学生都选修课程(已有成绩)的门数和平均成绩.

(4)统计每个学生选修课程的门数(超过5门的学生才统计).要求输出学生学号和选修门数,查询结果按门数降序排列,若门数相同,按学号升序排列.

(5)检索学号比WANG同学大,而年龄比他小的学生姓名. (6)在表SC中检索成绩为空值的学生学号和课程号. (7)检索姓名以L开头的所有学生的姓名和年龄. (8)求年龄大于女同学平均年龄的男学生姓名和年龄. (9)求年龄大于所有女同学年龄的男学生的姓名和年龄. 参考答案：

(1)在表C中统计开设课程的教师人数. Select Count(distinct TEACHER) from C

(2)求选修C4课程的女学生的平均年龄. Select AVG(SA) from S

where Sex=0 and S# in (select S# from SC

where C#='C4')

(3)求每个学生都选修课程(已有成绩)的门数和平均成绩. Select S#, AVG(GRADE), COUNT(C#) from SC Group by S#

(4)统计每个学生选修课程的门数(超过5门的学生才统计).要求输出学生学号和选修门数,查询结果按门数降序排列,若门数相同,按学号升序排列. Select COUNT(C#), S# from SC

GROUP BY S#

HAVING COUNT(*)>5

ORDER BY COUNT(*) DESC, SC.S# ASC

(5)检索学号比WANG同学大,而年龄比他小的学生姓名. 方法一： Select Sn from S

where S.S#>(select S1.S# from S as S1

where S1.Sn='WANG') and S.Sa< ( select S2.Sa from S as S2

where S2.Sn='WANG') 方法二： Select Sn from S as S1

where Exists (select *

from S as S2

where S2.Sn='WANG') and S1.S#>S2.S# and S1.Sa (Select AVG(S1.Sa) from S as S1

where S1.Sex=’女’)

(9)求年龄大于所有女同学年龄的男学生的姓名和年龄. 方法一： Select S1.Sn, S1.Sa from S S1

where S1.Sex=’男’ and S1.Sa> ( select Max(S2.Sa) from S S2

where S2.Sex=’女’ ) 方法二：

Select S1.Sn, S1.Sa from S S1

where S1.Sex=’男’ and S1.Sa>ALL (select S1.Sa from S S2

where S2.Sex=’女’ )

作业

--1学校学生中最大的姓（姓人数最多的学生） select top 1 count(*) as xing1 from (

select left(姓名,1) as xing from dbo.学生 )lin

group by lin.xing order by xing1 desc

--物理系姓王的<22的学生 select *

from dbo.学生

where left(姓名,1)='王' and 学生_ID in ( select 学生_ID from dbo.系 where 系名='物理系' )

and year(getdate())-year(出生日期)<22

--物理系的老师比计算机系所有老师年龄都大的老师 --3. select *

from dbo.教师 where 系_ID= ( select 系_ID from dbo.系 where 系名='物理系' )

and year(getdate())-year(出生日期)>all ( select year(getdate())-year(出生日期) from dbo.教师 where 系_ID= ( select 系_ID from dbo.系 where 系名='计算机' ) )

--4.--没讲过任何课的老师 select *

from dbo.教师

where 教师_ID not in ( select 教师_ID from dbo.教师认课 )

--5.--计算机系03年开课的总学分 select sum(学分) from dbo.课程 where 课程_ID in ( select 课程_ID from dbo.开课 where 年度=2003 and 开课_ID in ( select 开课_ID from dbo.学生选课 xk,dbo.学生 xs,dbo.系 x where xk.学生_ID=xs.学生_ID and xs.系_ID=x.系_ID and 系名='计算机' ) )

--6.--2003年平均成绩在全校前50%的学生 select top 50 percent avg(成绩) from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.开课 where 年度=2003

)

group by 学生_ID

--7.--同时显示姓刘的老师和姓刘的学生的人数 select lin1.jsshu,lin2.xsshu from (

select count(*) as jsshu from dbo.教师

where left(教师名,1)='刘' )lin1 , (

select count(*) as xsshu from dbo.学生

where left(姓名,1)='刘' )lin2

--8.--在星期3第2节课991号教室上过课的学生的总人数 select count(姓名) from dbo.学生 where 学生_ID in ( select 学生_ID from dbo.学生选课 where 开课_ID in ( select 开课_ID from dbo.课时安排 where 星期=3 and 节='2' and 教室号='911' ) )

本文来源：https://www.bwwdw.com/article/p3pg.html