1. 首页首页
  2. 编程汇总编程汇总

SQL语句面试题目_sql基础知识面试题

编程汇总 • • 阅读 61

SQL语句面试题目_sql基础知识面试题我自己做的答案 倒序排列的 用换行分隔每个题 都是对的 标准 https wenku baidu com view cda288f1b90d html 9 select s1 sname s1 sage from student s1 where s1 ssex N 男 and s1 sageall select sage from

我自己做的答案（倒序排列的，用换行分隔每个题），都是对的
（标准https://wenku.baidu.com/view/cda288f1b90d6c85ed3ac671.html）：

--9

select s1.sname,s1.sage 

from student s1

where s1.ssex=N'男' and s1.sage>all(select sage from student where ssex=N'女')





--8

select s1.sname,s1.sage 

from student s1

where s1.ssex=N'男' and s1.sage>(select avg(sage) from student where ssex=N'女')





--7

select s#,c# from sc where score is null





--6

select sname,sage from student where sname like N'李%'





--5

--方法一

select sname from student 

where s#>(select s# from student where sname=N'李四')

and sage<(select sage from student where sname=N'李四')

--方法二--把一个表拆分两个相同的表，后表锁定为“李四”，前表来比较，最后返回前表对应的值

select s1.sname from student s1,student s2 

where s2.sname=N'李四' and s1.s#>s2.s# and s1.sage




--4

select sc.c#,count(sc.s#) from sc group by sc.c# having count(sc.s#)>2 order by count(sc.s#) desc,sc.c#;





--3

--贺高老师每门课程的学生平均成绩

----思路：先贺高老师的所有课程，再每组平均成绩

select sc.c#,avg(score) from teacher,course,sc 

where teacher.t#=course.t# and course.c#=sc.c# --老师找到课程+课程找到成绩=老师找到成绩

and teacher.tname=N'贺高'

group by sc.c#





--2

--方法一

select avg(Student.sage) from Student,sc where Student.s#=sc.s# and sc.c#=4 

--方法二

select avg(Student.sage) from Student where Student.s#

in(select sc.s# from sc where Student.s#=sc.s# and sc.c#=4)





--1

select count(distinct(c#)) from sc;

              



--查询表中数据                     

--select * from Student;

--select * from course;

--select * from sc;

--select * from teacher;

题目二：

问题： 

1、查询“001”课程比“002”课程成绩高的所有学生的学号； 

  select a.S# from (select s#,score from SC where C#='001') a,(select s#,score 

  from SC where C#='002') b 

  where a.score>b.score and a.s#=b.s#; 

2、查询平均成绩大于60分的同学的学号和平均成绩； 

    select S#,avg(score) 

    from sc 

    group by S# having avg(score) >60; 

3、查询所有同学的学号、姓名、选课数、总成绩； 

  select Student.S#,Student.Sname,count(SC.C#),sum(score) 

  from Student left Outer join SC on Student.S#=SC.S# 

  group by Student.S#,Sname 

4、查询姓“李”的老师的个数； 

  select count(distinct(Tname)) 

  from Teacher 

  where Tname like '李%'; 

5、查询没学过“叶平”老师课的同学的学号、姓名； 

    select Student.S#,Student.Sname 

    from Student  

    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 

  select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 

  select S#,Sname 

  from Student 

  where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平')); 

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 

  Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 

  from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 
9、查询所有课程成绩小于60分的同学的学号、姓名； 

  select S#,Sname 

  from Student 

  where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# and score>60); 

10、查询没有学全所有课的同学的学号、姓名； 

    select Student.S#,Student.Sname 

    from Student,SC 

    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； 

    select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001'); 

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； 

    select distinct SC.S#,Sname 

    from Student,SC 

    where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； 

    update SC set score=(select avg(SC_2.score) 

    from SC SC_2 

    where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； 

    select S# from SC where C# in (select C# from SC where S#='1002') 

    group by S# having count(*)=(select count(*) from SC where S#='1002'); 

15、删除学习“叶平”老师课的SC表记录； 

    Delect SC 

    from course ,Teacher  

    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平'; 

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、 

    号课的平均成绩； 

    Insert SC select S#,'002',(Select avg(score) 

    from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 

    SELECT S# as 学生ID 

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库 

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理 

        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语 

        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 

    FROM SC AS t 

    GROUP BY S# 

    ORDER BY avg(t.score)  

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 

    SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 

    FROM SC L ,SC AS R 

    WHERE L.C# = R.C# and 

        L.score = (SELECT MAX(IL.score) 

                      FROM SC AS IL,Student AS IM 

                      WHERE L.C# = IL.C# and IM.S#=IL.S# 

                      GROUP BY IL.C#) 

        AND 

        R.Score = (SELECT MIN(IR.score) 

                      FROM SC AS IR 

                      WHERE R.C# = IR.C# 

                  GROUP BY IR.C# 

                    ); 

自己写的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from dbo.sc  group by c#

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 

    SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 

        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 

    FROM SC T,Course 

    where t.C#=course.C# 

    GROUP BY t.C# 

    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004） 

    SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 

        ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 

        ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 

        ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 

        ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 

        ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数 

        ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 

        ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 

  FROM SC

第二类：部门工资前三高的员工、部门工资最高的员工

表结构，节选自：https://www.cnblogs.com/an5456/p/10478949.html
题目，节选自：https://www.cnblogs.com/an5456/p/10478949.html
答案灵感来自于：
《sql分组(orderBy、GroupBy)获取每组前一(几)条数据》的“5、根据Name分组取最大的两个(N个)Val” 。
地址：https://www.cnblogs.com/linJie1930906722/p/5983159.html
也可以查看我转载的博客：《sql分组(orderBy、GroupBy)获取每组前一(几)条数据》

（为了满足“题目”查询条件，在原文的基础上，插入的测试语句中”增加了几条Employee，修改了Employee的部分薪资）

建议使用在线数据库调试：http://sqlfiddle.com/，非常方便！！

选择数据库类型为MySQL

把建表语句和插入的语句都放入 “Build Schema” Build 一下即成功建立数据库表

再到 “Run SQL” 中执行查询语句即可，非常方便

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);

Create table If Not Exists Department (Id int, Name varchar(255));

Truncate table Employee;

insert into Employee (Id, Name, Salary,DepartmentId) values ('1', 'Joe', '70000', '1');

insert into Employee (Id, Name, Salary,DepartmentId) values ('2', 'Henry', '85000', '2');

insert into Employee (Id, Name, Salary,DepartmentId) values ('3', 'Sam', '60000', '2');

insert into Employee (Id, Name, Salary,DepartmentId) values ('4', 'Max', '90000', '1');

insert into Employee (Id, Name, Salary,DepartmentId) values ('6', 'Henry2', '85000', '2');

insert into Employee (Id, Name, Salary,DepartmentId) values ('7', 'Henry3', '83000', '2');



insert into Employee (Id, Name, Salary,DepartmentId) values ('8', 'Randy2', '87000', '1');

insert into Employee (Id, Name, Salary,DepartmentId) values ('5', 'Randy', '85000', '1');

Truncate table Department;

insert into Department (Id, Name) values('1', 'IT');

insert into Department (Id, Name) values('2', 'Sales');

1. 每个部门工资最高的员工（如果有两个薪资相同的会显示出来）
（不明白“相关子查询”的朋友请看：https://blog.csdn.net/HD243608836/article/details/88832509）：

select * FROM Employee e WHERE e.Salary = (

    select max(Salary) from Employee where DepartmentId=e.DepartmentId) order by e.DepartmentId;



--select * from Employee;

结果：

IdNameSalaryDepartmentId
4Max900001
2Henry850002
6Henry2850002

2.1 求每个部门的最高工资（不显示重复的薪资）

select DepartmentId,max(Salary) from Employee group by DepartmentId;



--select * from Employee;

结果：

departmentIdmax(salary)
190000
285000

2.2 求每个部门的最高工资（显示重复的薪资）
（就是把题目“1”的select的显示条件从星号“*”换成了具体字段）

select e.DepartmentId,e.Salary FROM Employee e WHERE e.Salary = (

select max(Salary) from Employee where DepartmentId=e.DepartmentId) order by e.DepartmentId;



--select * from Employee;

结果：

departmentIdsalary
190000
285000
285000

3. 每个部门工资前三高的员工
（用到了“相关子查询”的知识，不明白的请参看：https://blog.csdn.net/HD243608836/article/details/88832509，
不然保证你看不懂！！）

方法一（必考重点）：

select e.* from Employee e where 3>(

    select count(*) from Employee where DepartmentId=e.DepartmentId and Salary>e.Salary)         

    order by DepartmentId,Salary;





语句构造解析：



表结构：

|Id | Name   | Salary |DepartmentId|

+——————————————————————————————————+

|'1'| 'Joe'  | '70000'| '1'        |(第一条)

|'2'| 'Henry'| '85000'| '2'        |(第二条)……

        ……以此类推……



因为语句中，子查询中使用到了父查询的字段，所以一定是“相关子查询”！！



1.先取得数据库中的第一个元组（即第一行数据），把第一个元组中的字段作为参数，传入子查询中

select count(*) from Employee where DepartmentId=1 and Salary>70000;

-->返回结果为3，where后的条件“3>3”返回false，则该条元组不进入汇总表。

（PS：返回0个证明是工资第一高，1个是第二高，2个是第三高……）



2.再取得数据库中的第二个元组

select count(*) from Employee where DepartmentId=2 and Salary>85000;

-->返回结果为1，where后的条件“3>1”返回true，则该条元组进入汇总表。



……以此类推……



最后，返回汇总表。

结果：

IdNameSalaryDepartmentId
5Randy850001
8Randy2870001
4Max900001
7Henry3830002
2Henry850002
6Henry2850002

方法二：

（和方法一差不多，也是“相关子查询”，只不过用了exists和having）

select e.* from Employee e where exists (

    select count(*) from Employee where DepartmentId=e.DepartmentId and Salary>e.Salary having count(*)<3 ) 

    order by DepartmentId,Salary;

方法三（只适用于SQL Server中，可用“TOP”关键字）：

（和方法一差不多，也是“相关子查询”，只不过用了top关键字，只适用于SQL Server，MySQL和Oracle没有top）

select e.* from Employee e where e.Salary in (

    select top 2 Salary from Employee where DepartmentId=e.DepartmentId order by Salary desc)

    order by DepartmentId,Salary;





解析：

一个接一个的部门的参数传入子查询，每个部门都取得最前面的两个

以上都是我辛苦整整两天，汇总整理的，真挺累啊！！希望对你们也有帮助！

觉得有用的朋友就赞一下吧，也是对我的一种鼓励与肯定！！

编程小号
上一篇 2025-06-29 12:40
下一篇 2025-09-21 21:11

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/hz/129493.html