SQL查询面试题【中】

糖逗

参考链接：https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect

1. #1.要求在剔除访问次数前20%的用户后，每类用户的平均访问次数。
   
2. USE train;
   
3. #建表
   
4. DROP TABLE IF EXISTS 访问;
   
5. CREATE TABLE 访问(
   
6.         用户编号 INT,
   
7.         用户类型 VARCHAR(255),
   
8.         访问量 INT
   
9. );
   
10. INSERT INTO 访问
    
11. VALUES(10, 'A', 352),
    
12.         (6, 'C', 209),
    
13.         (7, 'C', 110),
    
14.         (4, 'E', 101),
    
15.         (2, 'B', 53),
    
16.         (20, 'A', 53),
    
17.         (11, 'C', 33),
    
18.         (1, 'A', 30),
    
19.         (9, 'E', 29),
    
20.         (8, 'B', 6);
    
21. 
    
22. #剔除
    
23. DROP TABLE IF EXISTS temp;
    
24. CREATE TABLE temp AS
    
25. SELECT *, ROW_NUMBER() OVER(ORDER BY 访问量 DESC) AS ranking
    
26. FROM 访问;
    
27. #查询
    
28. SELECT 用户类型, AVG(访问量) AS 平均访问次数
    
29. FROM temp
    
30. WHERE ranking > (SELECT MAX(ranking) FROM temp) * 0.2
    
31. GROUP BY 用户类型;
    
32. 
    
33. 
    
34. #2.查询每门课程的前3高成绩
    
35. #建表
    
36. DROP TABLE IF EXISTS 原始表;
    
37. CREATE TABLE 原始表(
    
38.         课程号 INT,
    
39.         学号 INT,
    
40.         成绩 INT
    
41. );
    
42. INSERT INTO 原始表
    
43. VALUES(0001, 0001, 80),
    
44.         (0001, 0003, 80),
    
45.         (0002, 0001, 90),
    
46.         (0002, 0003, 80),
    
47.         (0002, 0002, 60),
    
48.         (0002, 0004, 55),
    
49.         (0003, 0001, 99),
    
50.         (0003, 0002, 80),
    
51.         (0003, 0003, 80);
    
52. 
    
53. 
    
54. SELECT 课程号,学号,成绩, 排名
    
55. FROM (
    
56.         SELECT *, DENSE_RANK() OVER(PARTITION BY 课程号 ORDER BY 成绩 DESC) AS 排名
    
57.         FROM 原始表
    
58. 
    
59. ) AS temp
    
60. WHERE 排名 <= 3
    
61. 
    
62. 
    
63. #3.查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
    
64. DROP TABLE IF EXISTS 学生表;
    
65. CREATE TABLE 学生表(
    
66.         学号 VARCHAR(255) PRIMARY KEY,
    
67.         姓名 VARCHAR(255) NOT NULL,
    
68.         出生日期 DATE NOT NULL,
    
69.         性别 VARCHAR(255) NOT NULL
    
70. );
    
71. DESC 学生表;
    
72. 
    
73. DROP TABLE IF EXISTS 成绩表;
    
74. CREATE TABLE 成绩表(
    
75.         学号 VARCHAR(255),
    
76.         课程号 VARCHAR(255),
    
77.         成绩 FLOAT(3) NOT NULL,
    
78.         PRIMARY KEY(学号, 课程号)
    
79. );
    
80. DESC 成绩表;
    
81. 
    
82. DROP TABLE IF EXISTS 课程表;
    
83. CREATE TABLE 课程表(
    
84.         课程号 VARCHAR(255) PRIMARY KEY,
    
85.         课程名称 VARCHAR(255) NOT NULL,
    
86.         教师号 VARCHAR(255) NOT NULL
    
87. );
    
88. DESC 课程表;
    
89. 
    
90. #向表中添加数据
    
91. INSERT INTO 学生表
    
92. VALUES('0001' , '猴子' , '1989-01-01' , '男'),
    
93. ('0002' , '猴子' , '1990-12-21' , '女'),
    
94. ('0003' , '马云' , '1991-12-21' , '男'),
    
95. ('0004' , '王思聪' , '1990-05-20' , '男');
    
96. 
    
97. INSERT INTO 成绩表
    
98. VALUES('0001' , '0001' , 87),
    
99. ('0001' , '0002' , 45),
    
100. ('0001' , '0003' , 45),
     
101. ('0002' , '0002' , 99),
     
102. ('0002' , '0003' , 98),
     
103. ('0003' , '0001' , 88),
     
104. ('0003' , '0002' , 80),
     
105. ('0003' , '0003' , 80);
     
106. ('0003' , '0003' , 23);
     
107. 
     
108. 
     
109. INSERT INTO 课程表
     
110. VALUES('0001' , '语文' , '0002'),
     
111. ('0002' , '数学' , '0001'),
     
112. ('0003' , '英语' , '0003');
     
113. #查询
     
114. SELECT c.学号, c.姓名, b.课程名称, a.成绩
     
115. FROM 成绩表 a
     
116.         LEFT JOIN 课程表 b ON a.课程号 = b.课程号
     
117.         LEFT JOIN 学生表 c ON a.学号 = c.学号
     
118. WHERE (a.课程号, a.成绩) IN
     
119. (SELECT 课程号,MAX(成绩) FROM 成绩表 GROUP BY 课程号);
     
120. 
     
121. #4.生成一张临时表（表名：用户登录表），表中呈现四列数据分别为：姓名，最后登录时间，登录时间排名，登录天数排名
     
122. #建表
     
123. DROP TABLE IF EXISTS 用户登记时间表;
     
124. CREATE TABLE 用户登记时间表(
     
125.         id INT PRIMARY KEY,
     
126.         姓名 VARCHAR(255),
     
127.         邮箱地址 VARCHAR(255),
     
128.         最后登录时间 DATETIME
     
129. );
     
130. INSERT INTO 用户登记时间表
     
131. VALUES (100, 'test4', 'test4@yahoo.cn', '2007/11/25 16:31:00'),
     
132.         (13, 'test1', 'test4@yahoo.cn', '2007/03/22 16:27:00'),
     
133.         (19, 'test1', 'test4@yahoo.cn', '2007/10/25 14:13:00'),
     
134.         (42, 'test1', 'test4@yahoo.cn', '2007/10/25 14:20:00'),
     
135.         (45, 'test2', 'test4@yahoo.cn', '2007/04/25 14:17:00'),
     
136.         (49, 'test2', 'test4@yahoo.cn', '2007/05/25 14:22:00');
     
137. #查询
     
138. SELECT 姓名,最后登录时间,
     
139.        ROW_NUMBER() OVER (PARTITION BY 姓名
     
140.                           ORDER BY 最后登录时间 ASC) AS 登录时间排名,
     
141.        DENSE_RANK() OVER (PARTITION BY 姓名
     
142.                           ORDER BY DATE_FORMAT( 最后登录时间,'%Y%m&d' ) ASC) AS 登录天数排名
     
143. FROM  用户登记时间表 ;
     
144. 
     
145. #5.找出每门课程中成绩最差的学生
     
146. DROP TABLE IF EXISTS 成绩表;
     
147. CREATE TABLE 成绩表(
     
148.         学号 INT,
     
149.         课程号 INT,
     
150.         成绩 INT
     
151. );
     
152. 
     
153. INSERT INTO 成绩表
     
154. VALUES('0001' , '0001' , 80),
     
155.         ('0001' , '0002' , 90),
     
156.         ('0001' , '0003' , 99),
     
157.         ('0002' , '0002' , 60),
     
158.         ('0002' , '0003' , 80),
     
159.         ('0003' , '0001' , 80),
     
160.         ('0003' , '0002' , 80),
     
161.         ('0003' , '0003' , 80);
     
162. 
     
163. #查询
     
164. SELECT *
     
165. FROM 成绩表
     
166. WHERE (课程号, 成绩) IN (
     
167.         SELECT 课程号, MIN(成绩) AS 成绩
     
168.         FROM 成绩表
     
169.         GROUP BY 课程号
     
170. );
     
171. 
     

复制代码