首页 / 面试 / oracle面试经典题
oracle面试经典题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle面试经典题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9406字,纯文字阅读大概需要14分钟。
内容图文
问题描述: 为管理岗位业务培训信息,建立3个表: 2 3 S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 4 5 C (C#,CN ) C#,CN 分别代表课程编号、课程名称 6 7 SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 8 9 要求实现如下5个处理: 10 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 11 --实现代码: 12 SELECT SN,SD FROM S 13 WHERE [S#] IN( 14 SELECT [S#] FROM C,SC 15 WHERE C.[C#]=SC.[C#] 16 AND CN=N‘税收基础‘) 17 18 19 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 20 --实现代码: 21 SELECT S.SN,S.SD FROM S,SC 22 WHERE S.[S#]=SC.[S#] 23 AND SC.[C#]=‘C2‘ 24 25 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 26 --实现代码: 27 SELECT SN,SD FROM S 28 WHERE [S#] NOT IN( 29 SELECT [S#] FROM SC 30 WHERE [C#]=‘C5‘) 31 32 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位 33 --实现代码: 34 SELECT SN,SD FROM S 35 WHERE [S#] IN( 36 SELECT [S#] FROM SC 37 RIGHT JOIN C ON SC.[C#]=C.[C#] 38 GROUP BY [S#] 39 HAVING COUNT(*)=COUNT(DISTINCT [S#])) 40 41 5. 查询选修了课程的学员人数 42 --实现代码: 43 SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC 44 45 6. 查询选修课程超过5门的学员学号和所属单位 46 --实现代码: 47 SELECT SN,SD FROM S 48 WHERE [S#] IN( 49 SELECT [S#] FROM SC 50 GROUP BY [S#] 51 HAVING COUNT(DISTINCT [C#])>5) 管理岗位业务培训信息
要求实现如下5个处理:
1 sql面试题(学生表_课程表_成绩表_教师表) 2 3 表架构 4 5 Student(S#,Sname,Sage,Ssex) 学生表 6 Course(C#,Cname,T#) 课程表 7 SC(S#,C#,score) 成绩表 8 Teacher(T#,Tname) 教师表 9 10 建表语句 11 12 CREATE TABLE student 13 ( 14 s# INT, 15 sname nvarchar(32), 16 sage INT, 17 ssex nvarchar(8) 18 ) 19 20 CREATE TABLE course 21 ( 22 c# INT, 23 cname nvarchar(32), 24 t# INT 25 ) 26 27 CREATE TABLE sc 28 ( 29 s# INT, 30 c# INT, 31 score INT 32 ) 33 34 CREATE TABLE teacher 35 ( 36 t# INT, 37 tname nvarchar(16) 38 ) 39 40 41 插入测试数据语句 42 insert into Student select 1,N‘刘一‘,18,N‘男‘ union all 43 select 2,N‘钱二‘,19,N‘女‘ union all 44 select 3,N‘张三‘,17,N‘男‘ union all 45 select 4,N‘李四‘,18,N‘女‘ union all 46 select 5,N‘王五‘,17,N‘男‘ union all 47 select 6,N‘赵六‘,19,N‘女‘ 48 49 insert into Teacher select 1,N‘叶平‘ union all 50 select 2,N‘贺高‘ union all 51 select 3,N‘杨艳‘ union all 52 select 4,N‘周磊‘ 53 54 insert into Course select 1,N‘语文‘,1 union all 55 select 2,N‘数学‘,2 union all 56 select 3,N‘英语‘,3 union all 57 select 4,N‘物理‘,4 58 59 insert into SC 60 select 1,1,56 union all 61 select 1,2,78 union all 62 select 1,3,67 union all 63 select 1,4,58 union all 64 select 2,1,79 union all 65 select 2,2,81 union all 66 select 2,3,92 union all 67 select 2,4,68 union all 68 select 3,1,91 union all 69 select 3,2,47 union all 70 select 3,3,88 union all 71 select 3,4,56 union all 72 select 4,2,88 union all 73 select 4,3,90 union all 74 select 4,4,93 union all 75 select 5,1,46 union all 76 select 5,3,78 union all 77 select 5,4,53 union all 78 select 6,1,35 union all 79 select 6,2,68 union all 80 select 6,4,71 81 82 问题: 83 1、查询“001”课程比“002”课程成绩高的所有学生的学号; 84 select a.S# from (select s#,score from SC where C#=‘001‘) a,(select 85 86 s#,score 87 from SC where C#=‘002‘) b 88 where a.score>b.score and a.s#=b.s#; 89 2、查询平均成绩大于60分的同学的学号和平均成绩; 90 select S#,avg(score) 91 from sc 92 group by S# having avg(score) >60; 93 3、查询所有同学的学号、姓名、选课数、总成绩; 94 select Student.S#,Student.Sname,count(SC.C#),sum(score) 95 from Student left Outer join SC on Student.S#=SC.S# 96 group by Student.S#,Sname 97 4、查询姓“李”的老师的个数; 98 select count(distinct(Tname)) 99 from Teacher 100 where Tname like ‘李%‘; 101 5、查询没学过“叶平”老师课的同学的学号、姓名; 102 select Student.S#,Student.Sname 103 from Student 104 where S# not in (select distinct( SC.S#) from SC,Course,Teacher 105 106 where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶 107 108 平‘); 109 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 110 select Student.S#,Student.Sname from Student,SC where 111 112 Student.S#=SC.S# and SC.C#=‘001‘and exists( Select * from SC as SC_2 113 114 where SC_2.S#=SC.S# and SC_2.C#=‘002‘); 115 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 116 select S#,Sname 117 from Student 118 where S# in (select S# from SC ,Course ,Teacher where 119 120 SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平‘ 121 122 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher 123 124 where Teacher.T#=Course.T# and Tname=‘叶平‘)); 125 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、 126 127 姓名; 128 Select S#,Sname from (select Student.S#,Student.Sname,score ,(select 129 130 score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=‘002‘) score2 131 from Student,SC where Student.S#=SC.S# and C#=‘001‘) S_2 where 132 133 score2 <score; 134 9、查询所有课程成绩小于60分的同学的学号、姓名; 135 select S#,Sname 136 from Student 137 where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# 138 139 and score>60); 140 10、查询没有学全所有课的同学的学号、姓名; 141 select Student.S#,Student.Sname 142 from Student,SC 143 where Student.S#=SC.S# group by Student.S#,Student.Sname having 144 145 count(C#) <(select count(C#) from Course); 146 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 147 select distinct S#,Sname from Student,SC where Student.S#=SC.S# 148 149 and SC.C# in (select C# from SC where S#=‘1001‘); 150 12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 151 select distinct SC.S#,Sname 152 from Student,SC 153 where Student.S#=SC.S# and C# in (select C# from SC where 154 155 S#=‘001‘); 156 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 157 update SC set score=(select avg(SC_2.score) 158 from SC SC_2 159 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# 160 161 and Course.T#=Teacher.T# and Teacher.Tname=‘叶平‘); 162 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; 163 select S# from SC where C# in (select C# from SC where S#=‘1002‘) 164 group by S# having count(*)=(select count(*) from SC where 165 166 S#=‘1002‘); 167 15、删除学习“叶平”老师课的SC表记录; 168 Delect SC 169 from course ,Teacher 170 where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=‘叶平‘; 171 16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003 172 173 ”课程的同学学号、2、 174 号课的平均成绩; 175 Insert SC select S#,‘002‘,(Select avg(score) 176 from SC where C#=‘002‘) from Student where S# not in (Select S# 177 178 from SC where C#=‘002‘); 179 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语” 180 181 三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程 182 183 数,有效平均分 184 SELECT S# as 学生ID 185 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘004‘) AS 数据 186 187 库 188 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘001‘) AS 企业 189 190 管理 191 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘006‘) AS 英语 192 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 193 FROM SC AS t 194 GROUP BY S# 195 ORDER BY avg(t.score) 196 18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 197 SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 198 FROM SC L ,SC AS R 199 WHERE L.C# = R.C# and 200 L.score = (SELECT MAX(IL.score) 201 FROM SC AS IL,Student AS IM 202 WHERE L.C# = IL.C# and IM.S#=IL.S# 203 GROUP BY IL.C#) 204 AND 205 R.Score = (SELECT MIN(IR.score) 206 FROM SC AS IR 207 WHERE R.C# = IR.C# 208 GROUP BY IR.C# 209 ); 210 自己写的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from 211 212 dbo.sc group by c# 213 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 214 SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG 215 216 (score),0) AS 平均成绩 217 ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 218 219 END)/COUNT(*) AS 及格百分数 220 FROM SC T,Course 221 where t.C#=course.C# 222 GROUP BY t.C# 223 ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 224 225 END)/COUNT(*) DESC 226 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001) 227 228 ,马克思(002),OO&UML (003),数据库(004) 229 SELECT SUM(CASE WHEN C# =‘001‘ THEN score ELSE 0 END)/SUM(CASE C# 230 231 WHEN ‘001‘ THEN 1 ELSE 0 END) AS 企业管理平均分 232 ,100 * SUM(CASE WHEN C# = ‘001‘ AND score >= 60 THEN 1 ELSE 0 233 234 END)/SUM(CASE WHEN C# = ‘001‘ THEN 1 ELSE 0 END) AS 企业管理及格百分数 235 ,SUM(CASE WHEN C# = ‘002‘ THEN score EL 本文系统来源:http://www.cnblogs.com/java2job/p/4811707.html
内容总结
以上是互联网集市为您收集整理的oracle面试经典题全部内容,希望文章能够帮你解决oracle面试经典题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。