[sql面试经验] 经典sql面试题及答案第3期

经典sql面试题及答案第三期

题目：

编写查询，从EMPLOYEE表中找出哪一年最多员工加入公司，总共多少人加入。

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

编写查询，从EMPLOYEE表中查找所有员工，他们的收入高于他们部门的平均工资。

列出员工的HireDate服务年限和月份。

如何在单查询中将EMPLOYEE表的性别Gender从“Male”更新为“Female”和从“Female”更新为“Male”？

触发器的作用？

计算超过2名员工的部门总薪水。

如何从现有表中创建一个空表？

如何从两个表中获取公共记录？

如何从表中交替提取记录？

答案：

select MAX(TotalEmployees) from

(select YEAR(HireDate) as Year, COUNT(EmployeeID) as TotalEmployees

from EMPLOYEE

group by YEAR(HireDate)) as tmp

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

SELECt OUTEREMPLOYEE.*, OUTEREMPLOYEE.Salary

FROM EMPLOYEE OUTEREMPLOYEE

WHERe OUTEREMPLOYEE.Salary >

(SELECt AVG(INNEREMPLOYEE.Salary)

FROM EMPLOYEE INNEREMPLOYEE

WHERe OUTEREMPLOYEE.DepartmentID = INNEREMPLOYEE.DepartmentID)

SELECt EMPLOYEE.*,

YEAR(HireDate) AS Years, MonTH(HireDate) AS Months

FROM EMPLOYEE

UPDATe EMPLOYEE

SET EMPLOYEE.Gender =

CASE EMPLOYEE.Gender

WHEN 'Male' THEN 'Female'

WHEN 'female' THEN 'Male'

END

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

select DepartmentID, SUM(Salary) as totalSalary

from #EMPLOYEE

group by DepartmentID

having COUNT(EmployeeID) > 2

Select * into studentcopy from student where 1 = 2

Select StudentID from student

INTERSECT

Select StudentID from Exam

显示偶数：

Select studentId

from (Select rowno, studentId from student) as t

where mod(rowno,2) = 0

显示奇数：

Select studentId

from (Select rowno, studentId from student)

where mod(rowno,2) = 1

说明：本文限于篇幅，故而只展示部分的面试内容，完整的Java面试学习文档小编已经帮你整理好了，有需要的朋友点赞+关注私信我免费领取Java、大厂面试学习资料哦！