java面试sql习题集


Oracle - SQL 习题集 数据定义语句 DDL create alter drop 数据控制语句 DCL grant deny revoke 数据操纵语句 DML select update insert delete 注意:SQL 语句不分大小写 建表语句 创建教师表 create table TEACHER( id number primary key, name varchar2(20), BIRTHDAY date, SEX varchar2(1), DEPARTMENT_ID varchar2(20), TITLE_ID varchar2(20) ); 列注释 comment on column TEACHER.id is '主键,自增 ID'; comment on column TEACHER.name is '姓名'; comment on column TEACHER.BIRTHDAY is '出生日期'; comment on column TEACHER.SEX is '性别 1:男;2:女'; comment on column TEACHER.DEPARTMENT_ID is '系 ID'; comment on column TEACHER.TITLE_ID is '职称 ID'; 创建自增主键 create sequence seq_teacher_id minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 50; --非大并发量时,可设置有序取得 SEQ alter sequence seq_teacher_id order; 插入数据 insert into TEACHER(id,name,BIRTHDAY,SEX,DEPARTMENT_ID,TITLE_ID) values(SEQ_TEACHER_ID.NEXTVAL,'李玲',TO_DATE('1985-04-23','yyyy-MM-dd'),2,1,3); insert into TEACHER(id,name,BIRTHDAY,SEX,DEPARTMENT_ID,TITLE_ID) values(SEQ_TEACHER_ID.NEXTVAL,'李大',TO_DATE('1985-05-13','yyyy-MM-dd'),1,2,1); insert into TEACHER(id,name,BIRTHDAY,SEX,DEPARTMENT_ID,TITLE_ID) values(SEQ_TEACHER_ID.NEXTVAL,'李二',TO_DATE('1975-07-24','yyyy-MM-dd'),1,1,2); insert into TEACHER(id,name,BIRTHDAY,SEX,DEPARTMENT_ID,TITLE_ID) values(SEQ_TEACHER_ID.NEXTVAL,'李小玉',TO_DATE('1985-12-04','yyyy-MM-dd'),2,2,3); insert into TEACHER(id,name,BIRTHDAY,SEX,DEPARTMENT_ID,TITLE_ID) values(SEQ_TEACHER_ID.NEXTVAL,'李天',null,2,1,3); 删除表 drop table TEACHER; drop talbe teacher purge; *purge 危险操作,一般不写,因为删除之后 rollback 也不能恢复删除的数据了。 删除自增 drop sequence seq_teacher_id; 提交操作 Commit; 回滚操作 Rollback; 查询语句 1、 查询所有字段(效率低,所有字段列出来且大写,效率较高) select * from 教师表 2、 列出教师的 ID 及姓名 Select t.id 教师 ID, t. name 教师姓名 From 教师表 t 教师表后的 “t” 为表别名,引用该表的字段时可以用 t.字段名。 字段后面的“教师 ID”及“教师姓名”为字段别名,中间省略了关键字“as”,即可写 为: Select t.id as 教师 ID, t.name as 教师姓名 From 教师表 t 3、 教师工资的 10% Select 工资*0.1 from 教师表 4、 本校师生的比例 Select (select count(1) from 教师表)/1.0/(select count(1) from 学生表) from dual; 5、 去掉重复记录 Select distinct 性别_id from 教师表 6、 在聚合统计函数中统计不重复指定字段 Select count(distinct 年级_id) from 学生表 7、 从教师表中查询,将性别_id 是 1 的显示为男,是 2 的显示为女 Case when then end 语句 select name,(case when sex = 1 then '男' when sex = 2 then '女' end) sex from teacher; decode 语句 –如果 sex 等于 1,输出“男”,否则输出“女” select name,decode(sex,'1','男','女') as sex from teacher; 横向查询男女人数 第一种方法: select SUM(case when SEX = 1 then 1 else 0 end) as 男 ,SUM(case when SEX = 2 then 1 else 0 end) as 女 from teacher; 第二种方法: select sum(decode(sex,1,1,0)) as 男,SUM(decode(sex,2,1,0)) as 女 from teacher; 10 可以重复列出表的的字段 select *,*,* from 教师 11 检索结果放常量 select name, ' 出生于'||TO_CHAR(BIRTHDAY,'yyyy')||' 年 '||TO_CHAR(BIRTHDAY,'MM')||'月'||TO_CHAR(BIRTHDAY,'dd')||'日' as 生辰 from TEACHER where BIRTHDAY is not null; ----------------------------------- From 变化集锦 1.从一个表中检索 select * from dbo.教师 2 从一个检索结果中再检索即从临时表中检索 select * from ( select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师 )lin –临时表通常要起别名 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 其它: --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 ') )+'**'-- trim().ltrim() select SUBSTRING('abcdefg',2,3) select REPLACE('aba','a','ccc') select STUFF( 'aaaaa',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()) %12 print convert(char(2),1+month(getdate()) %12)+'-' --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(ch ar(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 Table a Id A B 1 1 2 2 1 W 3 1 S 4 2 3 5 2 2 6 1 H 7 Q 2 8 1 2 9 1 2 10 1 2 11 ad dsa 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 7. --查询到少选修了学生 95002 选修的全部课程的学生号码 select distinct 学生号码 from 成绩 scx where not exists ( select * from 成绩 scy where scy. 学生号码=’95002’ and not exists ( select * from sc scz where scz. 学生号码=scx. 学生号码 and scz. 课程号=scy.课程号 ) ) select distinct 学生_ID from dbo.学生选课 scx where not exists ( select * from dbo.学生选课 scy where scy.学生_ID=1 and not exists ( select * from dbo.学生选课 scz where scz.学生_ID=scx.学生_ID and scz.学生_ID=scy.学生_ID ) ) 8. --查询选修所有全部课程的学生姓名 --查询学生姓名,条件:没有 1 门课不选 select 学生名 from 学生 where not exists ( select * from 课程 where not exists ( select * from 学生选课 where 学生选课.学生_ID=学生.学生_ID and 学生选课.课程_ID=课程.课程_ID ) ) 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.学生 44 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' ) ) 物理系都开过什么课 计算机系今年 3 科以上没及格的学生 列出全校所有学生数和物理系,计算机系的学生人数 老师平均年龄最大的系 外语系各科考试第一名的学生们 全校老师 2007 年课时量排行榜
还剩35页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

鱼片橙子

贡献于2017-05-22

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf