常见关系型数据库（sql server/oracle）查询语句面试题

1.获取所有员工中工资第二高的薪金:

ID	Salary
1	1000
2	3000
3	2000

（Employee 表）

        解法1:

                SELECT MAX(Salary)

                FROM Employee

                WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee ); 

        解法2:

                select MAX(Salary) from Employee

                WHERE Salary <> (select MAX(Salary) from Employee ) 

2.获取偶数行数据：同上表

        解法：

        SELECT *

        FROM(SELECT rownum, ID FROM Employee)

        WHERE MOD(rownum,2)=0

3.找到每个部门的最高新：

（Employee 表）

ID	Salary	DeptID
1	1000	2
2	3000	3
3	2000	2

(Department 表)

ID	DeptName
1	市场部
2	技术支持部
3	财政部

        解法1：

                SELECT d.DeptName, MAX(e.Salary)

                FROM Department d LEFT OUTER JOIN Employee e

                ON e.ID = d.ID

                GROUP BY DeptName

          解法2:

                   SELECT DeptID,MAX(Salary)

                FROM Employee

                GROUP BY DeptID

4.获取员工里有相同姓名和邮箱地址的人:

（Employee 表）

ID	Name	Email
1	ZhangSan	zzss
2	LiSi	lll
3	ZhangSan	zzss

        解法：

                SELECT Name,Email,COUNT(*)

                FROM Employee

                GROUP BY Name,Email

                HAVING

                COUNT(*)>1

5.获取第n高的薪金：

（同员工表）

        解法1：

                SELECT *

                FROM Employee emp1

                WHERE (n-1) = (

                SELECT COUNT(DISTINCT(emp2.Salary))

                FROM Employee emp2

                WHERE emp2.Salary > emp1.Salary)

        解法2:

                SELECT * FROM(

                SELECT emp.*,

                Row_number() over(ORDER BY Salary DESC) rnum

                FROM Employee emp)

                WHERE rnum = n;

6.获取10个奇数ID的员工：

（同员工表）

        解法：

                SELECT TOP 10 ID FROM Employee WHERE ID%2=1;

7.获取在1990/01/01 到2000/12/31之间出生的员工：

        解法：

                SELECT Name

                FROM Employee

                WHERE birth_date BETWEEN ‘01/01/1990’ AND ‘31/12/2000’

8.获取重复邮箱地址：

        解法：

                SELECT Name, COUNT(Email)

                FROM Employee

                GROUP BY Email

                HAVING (COUNT(Email)>1)

9.获取员工姓名包含‘SAN’，忽略大小写：

        解法：

                SELECT *

                FROM Employee

                WHERE UPPER(Name) like ‘%SAN%’

10.获取员工姓名，员工部门经理ID，以及员工所在部门人数

ID	MGRID	DepID	Name
1	3	10	ZHANGSAN
2	3	10	LISI
3	4	20	WANGWU

        解法：

                WITH d_count AS(

                SELECT deptID, COUNT(*) AS d_count

                FROM Employee

                GROUP BY deptId)

                SELECT e.Name AS EmployeeName,

                m.Name AS ManagerName

                dc.d_count AS DeptCount

                FROM Employee e, d_count dc, Employee m

                WHERE e.deptID = dc.deptID

                AND e.MGRID = m.ID

11.获取重复的记录：

        解法：

                SELECT col1,col2,count(*)

                FROM table

                GROUP BY col1,col2

                HAVING count(*) > 1

12.删除重复记录：

        解法：

                DELETE FROM

                Table a

                WHERE a.rowid > ANY(SELECT b.rowid FROM table b WHRE a.col = b.col)

13.获取同学名字和同一年级的人数：

ID	Name	Grade
1	ZHANGSAN	1
2	LISI	2

        解法：

                WITH grade_count AS(

                SELECT grade, COUNT(*) AS grade_count

                FROM student

                GROUP BY grade)

                SELECT s.name AS stduent_name,

                Gc.grade_count AS grade_count

                FROM student s,

                Grade_count gc

                WHERE e.grade = gc.grade;

14.获取总分比平均分高的年级：

Student 表：（name，gradeID，score）

Grade 表：（ID，gradeNum）

        解法：

                WITH   grade_score AS(

                SELECT gradeNum,SUM(s.score) grade_total

                FROM student s, grade g

                WHERE s.gradeID = g.ID

                GROUP BY gradeNum),

                Avg_score AS(

                SELECT SUM(grade_total)/COUNT(*) avg

                FROM grade_score)

                SELECT * FROM grade_score

               WHERE grade_total > (SELECT avg FROM avg_score)

               ORDER BY gradeNum;

15.获取商品的名称，不能重复且不用DISTINCT

        解法：

                SELECT prodName

                FROM Product

                GROUP BY prodName

16.获取邮编数最大的记录且不用MAX或MIN

        解法：

                SELECT DISTINCT Zipcode

                FROM Zipcodes

                WHERE Zidpcode NOT IN(

                SELECT small.Zipcode

                FROM zipcodes as large

                JOIN zipcodes as small

                ON small.zipcode < large.zipcode

                )        

17.按年级将学生名打印出来，用；隔开：

grade	name
1	ZHANGSAN
2	LISI
2	WNAGWU

        解法：

                SELECT grade, LISTAGG(name,’,’)WITHIN GROUP(ORDER BY name)

                AS student

                GROUP BY grade;