SQL经典面试50题.docx
- 文档编号:25906599
- 上传时间:2023-06-16
- 格式:DOCX
- 页数:23
- 大小:20.66KB
SQL经典面试50题.docx
《SQL经典面试50题.docx》由会员分享,可在线阅读,更多相关《SQL经典面试50题.docx(23页珍藏版)》请在冰豆网上搜索。
SQL经典面试50题
1.一般面试时考SQL,主要就是考你“统计分析”这一块,下面我们来看面试官经常采用的手段。
2.
3.
4.由4张简单的不能再简单的表,演变出50道SQL
5.
6.
7.哈哈哈哈,够这个面试官面个15,20个人,不带重复的了,而且每个SQL你真的不动动脑子还写不出呢,你别不服气,下面开始。
8.
9.表结构:
10.
11.
12.
13.表Student
14.
15.(S#,Sname,Sage,Ssex) 学生表
16.
17.
18.S# student_no
19.
20.Sage student_age
21.
22.Ssex student_sex
23.
24.
25.
26.表Course
27.
28.(C#,Cname,T#) 课程表
29.
30.
31.C# course_no
32.
33.Cname course_name
34.
35.T# teacher_no
36.
37.
38.
39.
40.
41.
42.表SC(学生与课程的分数mapping 表)
43.
44.(S#,C#,score) 成绩表
45.
46.
47.S# student_no
48.C# course_no
49.score 分数啦
50.
51.
52.
53.
54.
55.
56.表Teacher
57.
58.(T#,Tname) 教师表
59.
60.
61.T# teacher_no
62.Tname teacher_name
63.
64.
65.
66.
67.
68.
69.50道问题开始
70.
71.
72.
73.
74.
75.
76.1、查询“001”课程比“002”课程成绩高的所有学生的学号;
77.
78.
79. select # from (select s#,score from SC where C#='001') a,(select s#,score
80.
81. from SC where C#='002')
82.
83.
84.
85.
86. where > and #=#;
87.
88.
89.
90.
91.2、查询平均成绩大于60分的同学的学号和平均成绩;
92.
93. select S#,avg(score)
94.
95. from sc
96.
97. group by S# having avg(score) >60;
98.
99.
100.
101.
102.3、查询所有同学的学号、姓名、选课数、总成绩;
103.
104. select #,,count#),sum(score)
105.
106. from Student left Outer join SC on #=#
107.
108. group by #,Sname
109.
110.
111.
112.
113.4、查询姓“李”的老师的个数;
114.
115. select count(distinct(Tname))
116.
117. from Teacher
118.
119. where Tname like '李%';
120.
121.
122.
123.
124.5、查询没学过“叶平”老师课的同学的学号、姓名;
125.
126. select #,
127.
128. from Student
129.
130. where S# not in (select distinct( #) fromSC,Course,Teacher where #=#and #=# ='叶平');
131.
132.
133.
134.
135.6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
136.
137. select #, fromStudent,SC where #=# #='001'and exists( Select * from SC as SC_2 where #=# and #='002');
138.
139.
140.
141.
142.7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
143.
144. select S#,Sname
145.
146. from Student
147.
148. where S# in (select S# from SC,Course ,Teacher where #=# #=# and ='叶平'group by S# having count#)=(select count(C#) fromCourse,Teacher #=# and Tname='叶平'));
149.
150.
151.
152.
153.8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
154.
155. Select S#,Sname from (select #,,score ,(select score from SC SC_2 where #=#and #='002') score2
156.
157. from Student,SC where #=# andC#='001') S_2 where score2 158. 159. 160.9、查询所有课程成绩小于60分的同学的学号、姓名; 161. 162. select S#,Sname 163. 164. from Student 165. 166. where S# not in (select # fromStudent,SC where #=# andscore>60); 167. 168. 169. 170. 171.10、查询没有学全所有课的同学的学号、姓名; 172. 173. select #, 174. 175. from Student,SC 176. #=# group by #, having count(C#) <(select count(C#) from Course); 177. 178. 179. 180. 181. 182. 183.11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 184. 185. 186. select S#,Sname from Student,SC #=# and C# in select C# from SC where S#='1001'; 187. 188. 189. 190. 191.12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 192. 193. select distinct #,Sname 194. 195. from Student,SC 196. 197. where #=# and C# in(select C# from SC where S#='001'); 198. 199. 200. 201. 202.13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 203. 204. update SC set score=(select avg 205. 206. from SC SC_2 207. 208. where #=# ) fromCourse,Teacher where #=# #=# and ='叶平'); 209. 210. 211. 212. 213.14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; 214. 215. select S# from SC where C# in(select C# from SC where S#='1002') 216. 217. group by S# having count(*)=(select count(*) from SC where S#='1002'); 218. 219. 220. 221. 222.15、删除学习“叶平”老师课的SC表记录; 223. 224. DelectSC 225. 226. from course ,Teacher 227. 228. where #=# and #=# and Tname='叶平'; 229. 230. 231. 232. 233.16、向SC表中插入一些记录,这些记录要求符合以下条件: 没有上过编号“003”课程的同学学 234. 235.号、2号课的平均成绩; 236. 237. Insert SC select S#,'002',(Select avg(score) 238. 239. from SC where C#='002') from Student where S# notin (Select S# from SC where C#='002'); 240. 241. 242. 243. 244.17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按 245. 246.如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 247. 248. SELECT S# as 学生ID 249. 250. ,(SELECT score FROM SC WHERE #=#AND C#='004') AS 数据库 251. 252. ,(SELECT score FROM SC WHERE #=#AND C#='001') AS 企业管理 253. 254. ,(SELECT score FROM SC WHERE #=#AND C#='006') AS 英语 255. 256. ,COUNT(*) AS 有效课程数, AVG AS 平均成绩 257. 258. FROM SC AS t 259. 260. GROUP BY S# 261. 262. ORDER BY avg 263. 264. 265. 266. 267.18、查询各科成绩最高和最低的分: 以如下形式显示: 课程ID,最高分,最低分 268. 269. SELECT # As 课程ID, AS 最高分, AS 最低分 270. 271. FROM SC L ,SC AS R 272. 273. WHERE # = # and 274. 275. = (SELECT MAX 276. 277. FROM SC ASIL,Student AS IM 278. 279. WHERE # =# and #=# 280. 281. GROUP #) 282. 283. AND 284. 285. = (SELECT MIN 286. 287. FROM SC ASIR 288. 289. WHERE # =# 290. 291. GROUP BY # 292. 293. ); 294. 295. 296. 297. 298.19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 299. 300. SELECT # AS 课程号,maxAS 课程名,isnull(AVG(score),0) AS平均成绩 301. 302. ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 303. 304. FROM SC T,Course 305. 306. where #=# 307. 308. GROUP BY # 309. 310. ORDER BY 100* SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 311. 312. 313.20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 314. 315. 316.企业管理(001),马克思(002),OO&UML (003),数据库(004) 317. 318. 319. 320. 321. 322. SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 323. 324. ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 325. 326. ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 327. 328. ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 329. 330. ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 331. 332. ,100* SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数 333. 334. ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 335. 336. ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 337. FROM SC 338. 339. 340. 341. 342. 343.21、查询不同老师所教不同课程平均分从高到低显示 344. SELECT max#) AS 教师ID,MAX AS 教师姓名,# AS 课程ID,MAX AS 课程名称,AVG(Score) AS 平均成绩 345. FROM SC AS T,Course AS C ,Teacher AS Z 346. where #=# and #=# 347. GROUP BY # 348. ORDER BY AVG(Score) DESC 349. 350. 351.22、查询如下课程成绩第 3 名到第 6 名的学生成绩单: 352. 353.企业管理(001),马克思(002),UML (003),数据库(004) 354. 355.[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 356. SELECT DISTINCT top 3 357. # As 学生学号, 358. AS 学生姓名 , 359. AS 企业管理, 360. AS 马克思, 361. AS UML, 362. AS 数据库, 363. ISNULL,0) + ISNULL,0) + ISNULL,0) + ISNULL,0) as 总分 364. FROM Student,SC LEFT JOIN SC AS T1 365. ON # = # AND # = '001' 366. LEFT JOIN SC AS T2 367. ON # = # AND # = '002' 368. LEFT JOIN SC AS T3 369. ON # = # AND # = '003' 370. LEFT JOIN SC AS T4 371. ON # = # AND # = '004' 372. WHERE #=# and 373. ISNULL,0) + ISNULL,0) + ISNULL,0) + ISNULL,0) 374. NOT IN 375. (SELECT 376. DISTINCT 377. TOP 15 WITH TIES 378. ISNULL,0) + ISNULL,0) + ISNULL,0) + ISNULL,0) 379. FROM sc 380. LEFT JOIN sc AS T1 381. ON # = # AND # = 'k1' 382. LEFT JOIN sc AS T2 383. ON # = # AND # = 'k2' 384. LEFT JOIN sc AS T3 385. ON # = # AND # = 'k3' 386. LEFT JOIN sc AS T4 387. ON # = # AND # = 'k4' 388. ORDER BY ISNULL,0) + ISNULL,0) + ISNULL,0) + ISNULL,0) DESC); 389. 390. 391.23、统计列印各科成绩,各分数段人数: 课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 392. SELECT # as 课程ID, Cname as 课程名称 393. ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 394. ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 经典 面试 50