面试的一点小题，自己收藏收藏做做_中国移动圈·繒,iii100%下午1:08-优快云博客

本文精选了多个SQL面试题目并提供了详细的解答，涵盖从基础到高级的查询技巧，包括分组、聚合、联接等核心概念，是准备SQL面试的理想资料。

面试的题自己记录一下

答案也在下面方便参考

1.
select
class,
max(score)as maxscore
from table1 group by class order by maxscore

2.
SELECT
   s.name,
   s.course,
   s.score
FROM student s WHERE s.name LIKE '张%'
GROUP BY s.name
HAVING AVG(score) > 75

3.
SELECT
ID
FROM student2
WHERE A > 60 AND B > 60 AND C > 60 AND D > 60 AND E > 60
GROUP BY ID
HAVING SUM(A+B+C+D+E) > 600

4.
SELECT
   t.name,
   t.com_name,
   t.number
FROM table2 t
WHERE t.number > 1

5.
SELECT
C1,
SUM(C2)
FROM table3 GROUP BY C1

6.
SELECT * FROM(
SELECT COUNT(*)AS '及格' FROM t2 WHERE person >60
)a
INNER JOIN
(
SELECT COUNT(*)AS '不及格' FROM t2 WHERE person <=60
)b

CREATE TABLE IF NOT EXISTS t2(
id VARCHAR (20),
person VARCHAR (10)
)

INSERT INTO `t2` VALUES('199801001','85'),
('199801002','76'),
('199801003','98'),
('199801004','54'),
('199801005','39'),
('199801006','61')

CREATE TABLE IF NOT EXISTS table3(
C1 VARCHAR (20),
C2 VARCHAR (10)
)
INSERT INTO `table3` VALUES('2005-01-01','1'),
('2005-01-01','3'),
('2005-01-02','5'),

CREATE TABLE IF NOT EXISTS table2(
`name` VARCHAR (10) ,
com_name VARCHAR (10),
number VARCHAR (4)
)
INSERT INTO `table2` VALUES('A','甲','2'),
('B','乙','4'),
('C','丙','1'),
('A','丁','2'),
('B','丙','5')

CREATE TABLE IF NOT EXISTS student2(
`ID` VARCHAR (10) ,
A VARCHAR (4),
B VARCHAR (4),
C VARCHAR (4),
D VARCHAR (4),
E VARCHAR (4)
)

insert into `student2` VALUES('2006002','149','105','110','142','129');

CREATE TABLE IF NOT EXISTS student(
`name` VARCHAR (5) COMMENT '学生姓名',
course VARCHAR (4),
score VARCHAR (4)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

insert into `student` VALUES('王华','数学','72')，
insert into `student` VALUES('张华','英语','81')，
insert into `student` VALUES('张青','物理','67')，
insert into `student` VALUES('李立','化学','98')，
insert into `student` VALUES('张燕','物理','70')，
insert into `student` VALUES('张青','化学','76');

insert into student values('张青','语文','72');