SQL面试题（常见题型）

SQL

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name kecheng fenshu

张三 语文 81

张三 数学 75

李四 语文 76

李四 数学 90

王五 语文 81

王五 数学 100

王五 英语 90

2.删除除了自动编号不同, 其他都相同的学生冗余信息

自动编号 学号 姓名 课程编号 课程名称 分数

1 2005001 张三 0001 数学 69

2 2005002 李四 0001 数学 89

3 2005001 张三 0001 数学 69

3.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.

select a.name,b.name from team as a, team as b where a.name<b.name  order by a.name,b.name
           

4.请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －12 月份的发生额。

AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。

数据库名：JcyAudit ，数据集：Select * from TestDB

select testdb.accid from testdb,
(select *  from testdb where Accid=101) as a 
where testdb.Occmonth  = a.Occmonth  and a.debitOccur < testdb.debitOccur
group by testdb.AccID HAVING COUNT(testdb.AccID)=12
           

5.面试题：怎么把这样一个表转置

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

查成这样一个结果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

# case1 
SELECT year,(SELECT amount from test as t WHERE month=1 and t.year= test.year ) as m1
from test GROUP BY year

# case2 
SELECT year,max(case month WHEN 1 THEN amount else 0 END) as m1 ,
max(case month WHEN 2 THEN amount else 0 END) as m2,
max(case month WHEN 3 THEN amount else 0 END) as m3,
max(case month WHEN 4 THEN amount else 0 END) as m4
from test GROUP BY year 
           

6.按照所列表格字段和SQL查询结果，写出SQL语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W5GGfP50-1619417896351)(C:\Users\86132\AppData\Roaming\Typora\typora-user-images\image-20210422195545508.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rl8CVQkj-1619417896357)(C:\Users\86132\AppData\Roaming\Typora\typora-user-images\image-20210422195704778.png)]

SELECT	TF.flats_num AS "公寓编号",	
COUNT(CASE WHEN TR.`status` = 0 THEN TR.id END) AS "未出租数量",	
COUNT(TR.id) AS "该公寓所有房间数量",	
(CASE WHEN TF.is_private = 1 THEN '是' ELSE '否' END) AS "公寓是否是自营",	CONCAT(TC.`name`, '-', TC.street) AS "小区地址"
FROM	t_flats TF LEFT JOIN t_community TC ON (TF.community_id = TC.id)LEFT JOIN t_room TR ON (TR.flats_id = TF.id)GROUP BY TF.flats_num;
           

7.两张关联表，删除主表中已经有，在附表中没有的 信息

8.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value替换为A中对应的value

--方法1
update B b set b.value=(select max(a.value) from A a where b.key=a.key)
where exists(select 1 from A c where b.key=c.key)

--方法2
UPDATE A a,(select a.`key`,b.`value` from A INNER JOIN B on a.`key`=b.`key`) b 
SET a.`value` = b.`value` WHERE a.`key` = b.`key`