java面试题网

普通会员

15

帖子

1

回复

33

积分

楼主
发表于 2018-06-06 17:50:40 | 查看: 356| 回复: 0

题目:

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

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

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

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

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

  6. 触发器的作用?

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

  8. 如何从现有表中创建一个空表?

  9. 如何从两个表中获取公共记录?

  10. 如何从表中交替提取记录?


答案:

  1. select MAX(TotalEmployees) from

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

    from EMPLOYEE

    group by YEAR(HireDate)) as tmp

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

  3. SELECT OUTEREMPLOYEE.*, OUTEREMPLOYEE.Salary

    FROM EMPLOYEE OUTEREMPLOYEE

    WHERE OUTEREMPLOYEE.Salary >

    (SELECT AVG(INNEREMPLOYEE.Salary)

    FROM EMPLOYEE INNEREMPLOYEE

    WHERE OUTEREMPLOYEE.DepartmentID = INNEREMPLOYEE.DepartmentID)

  4. SELECT EMPLOYEE.*,

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

    FROM EMPLOYEE

  5. UPDATE EMPLOYEE

    SET EMPLOYEE.Gender =

    CASE EMPLOYEE.Gender

    WHEN 'Male' THEN 'Female'

    WHEN 'female' THEN 'Male'

    END

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

  7. select DepartmentID, SUM(Salary) as totalSalary

    from #EMPLOYEE

    group by DepartmentID

    having COUNT(EmployeeID) > 2

  8. Select * into studentcopy from student where 1 = 2

  9. Select StudentID from student

    INTERSECT

    Select StudentID from Exam

  10. 显示偶数:

    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建站系统提供技术支持V2.1 网站地图 © 2016-2018