SQL面试试题_用having查询出只加入了一个社团的学生的社团 id,结果输出学号、社团id-CSDN博客

本文链接：https://blog.csdn.net/ruyigongfang/article/details/141598483

如果是线上面试或者练习可以先建表再写语句，如果笔试的话就不用自己创建，

面试题1

1-题目

所需文件 std_id(学号) club_id(社团 ID) club_name(社团名) main_club_flg (主社团标志，Y表示主社团) 题目要求： 1）用HAVING查询出只加入了一个社团的学生的社团 ID，结果输出学号、社团ID 2）查询加入了多个社团的学生的主社团 ID，结果输出学号、主社团ID

std_id club_id club_name main_club_flg

1001 1 篮球 Y

1001 2 羽毛球 N

1002 2 羽毛球 N

1002 3 兵乓球 Y

1002 4 游泳 N

1002 5 足球 N

1003 3 兵乓球 Y

1003 1 篮球 N

1004 5 足球 Y

1005 6 棒球 Y

--先自己创建表
create table yundong 
(std_id number(4),
club_id number(4),
club_name varchar2(30),
main_club_flg varchar2(30));

select * from yundong;

insert into yundong(std_id,club_id,club_name,main_club_flg) values (1001,1,'篮球','Y');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1001,2,'羽毛球','N');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1002,2,'羽毛球','N');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1002,3,'乒乓球','Y');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1002,4,'游泳','N');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1002,5,'足球','N');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1003,3,'乒乓球','Y');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1003,1,'篮球','N');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1004,5,'足球','Y');
insert into yundong(std_id,club_id,club_name,main_club_flg) values (1005,6,'棒球','Y');
std_id(学号) club_id(社团 ID) club_name(社团名) main_club_flg (主社团标志，Y表示主社团)

1）用HAVING查询出只加入了一个社团的学生的社团 ID，结果输出学号、社团ID

select  club_id, std_id
  from (select club_id,
               std_id,
               count(club_id) over(partition by std_id) as m --参加的社团数目
          from yundong)
 group by club_id, std_id, m
having m = 1

2）查询加入了多个社团的学生的主社团 ID，结果输出学号、主社团ID

select  distinct std_id,main_club_flg
  from (select club_id,
               std_id,
               main_club_flg,
               count(club_id) over(partition by std_id) as m --参加的社团数目
          from yundong)
where m>1 and main_club_flg='Y'

2-题目

所需文件 CLASSNO(班级)STUDESTNO(学号)CHINESE(语文成绩) MATH(数学成绩)GRADE (等级) 题目要求： 1）定义语文和数学成绩都大于等于60分视为合格，只允许使用一次SELECT,查询合格人数和不合格人数，结果输出合格人数、不合格人数 2）定义语文和数学总分大于等于120分视为合格，大于等于160分视为良好，大于等于180分视为优秀，其余为不合格，只允许使用一次update，按总分更新GRADE字段的值

CLASSNO STUDENTNO CHINESE MATH GRADE

CLASS1 1001 86 81

CLASS1 1002 60 54

CLASS1 1003 85 57

CLASS1 1004 73 75

CLASS1 1005 95 98

CLASS1 1006 61 75

CLASS1 1007 77 76

CLASS1 1008 71 58

CLASS1 1009 61 73

CLASS1 1010 78 55

CLASSNO STUDENTNO CHINESE MATH  GRADE
CLASS1  1001  86  81  
CLASS1  1002  60  54  
CLASS1  1003  85  57  
CLASS1  1004  73  75  
CLASS1  1005  95  98  
CLASS1  1006  61  75  
CLASS1  1007  77  76  
CLASS1  1008  71  58  
CLASS1  1009  61  73  
CLASS1  1010  78  55  

create table class_sc (
CLASSNO varchar2(6),
STUDESTNO number(4),
CHINESE number(2),
 MATH number(2),
GRADE varchar2(10))

select * from class_sc 

insert into class_sc(classno,studestno,chinese,math) values ('class1',1001,86,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1002,60,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1003,85,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1004,73,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1005,95,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1006,61,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1007,77,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1008,71,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1009,61,81);
insert into class_sc(classno,studestno,chinese,math) values ('class1',1010,78,81);

--CLASSNO(班级)STUDESTNO(学号)CHINESE(语文成绩) MATH(数学成绩)GRADE (等级)

1）定义语文和数学成绩都大于等于60分视为合格，只允许使用一次SELECT,查询合格人数和不合格人数,结果输出合格人数、不合格人数

select 
  sum(case when CHINESE >= 60 and MATH >= 60 then 1 else 0 end) as 合格人数,
  Sum(Case when CHINESE < 60 or MATH < 60 then 1 else 0 end) as 不合格人数
from class_sc

2）定义语文和数学总分大于等于120分视为合格，大于等于160分视为良好，大于等于180分视为优秀其余为不合格，只允许使用一次update，按总分更新GRADE字段的

update cj
   set grade= case
                 when chinese + math >=120 and chinese + math < 160 then
                  '合格'
                 when chinese + math >= 160 and chinese + math < 180 then
                  '良好'
                 when chinese + math >=180 then
                  '优秀'
                 else
                  '不合格'
               end;
               merge into class_sc t1
using (select classno,
        studestno,
        chinese,
        math,
        m,
        case
          when m > 180 then
           '优秀'
          when m < 180 and m > 160 then
           '良好'
          when m < 160 and m > 120 then
           '合格'
          else
           '不合格'
        end as n --等级表
   from (select classno,
                studestno,
                chinese,
                math,
                chinese + math as m
           from class_sc)) t2
on (t1.studestno = t2.studestno)
when matched then
  update set t1.grade=t2.n

  select * from class_sc

3-题目

所需文件 TNAME(水果名称) PRICE(价格) 题目要求： 1）查询价格从高到低的顺序，结果输出如图1所示 2）假设小明有100元，需要购买两种水果，利用SQL查询有几种购买方案？

tname price

苹果 50.00

橘子 100.00

葡萄 50.00

西瓜 70.00

梨 30.00

香蕉 30.00

柠檬 50.00

create table shuiguo
(tname varchar2(10),
price number(5));
select * from shuiguo for update;

1）查询价格从高到低的顺序，结果输出如图1所示

select tname,price ,rank()over(order by price desc)as "rank_1" from shuiguo

2）假设小明有100元，需要购买两种水果，利用SQL查询有几种购买方案？

select t1.tname, t1.price, t1.n, t2.tname, t2.price, t2.n, count(1)over()
  from (select tname, price, row_number() over(order by price) as n
          from shuiguo) t1
 inner join (select tname, price, row_number() over(order by price) as n
               from shuiguo) t2
    on t1.price + t2.price <= 100
   and t1.n <t2.n
   select count(1)
    from a3 t1
   inner join a3 t2
      on t1.price + t2.price <= 100
     and t1.tname > t2.tname;
		 
		 select ascii('苹果'),ascii('葡萄') from dual;

4-题目

所需文件上级ID显示的是上级部门的ID 题目要求：该公司总共有三个层级，请用SQL查询出公司的组织架构情况，结果输出一级部门名称、二级部门名称、三级部门名称

部门ID 上级ID 部门名称

1 总部

13 1 财务部

131 13 部长

132 13 会计

133 13 出纳

14 1 生产部

141 14 部长

142 14 车间主任

143 14 调度

144 14 领班

15 1 技术部

151 15 部长

152 15 技术员

153 15 工艺员

154 15 测试员

16 1 采购物流部

161 16 部长

11 1 人力资源部

111 11 部长

112 11 人力资源管理员

113 11 人力资源文员

12 1 市场部

121 12 部长

122 12 业务经理

123 12 业务员

124 12 销售内勤

13 1 财务部

131 13 部长

132 13 会计

133 13 出纳

14 1 生产部

141 14 部长

142 14 车间主任

143 14 调度

144 14 领班

15 1 技术部

151 15 部长

152 15 技术员

153 15 工艺员

154 15 测试员

16 1 采购物流部

161 16 部长

162 16 采购员

163 16 采购内勤

164 16 物流主任

165 16 送货员

166 16 驾驶员

167 16 仓库管理员

create table emp4(
deptno number(3),
spdno number(2),
sname varchar2(30))
select * from emp4 for update;

select distinct deptno,
                spdno,
                case
                  when spdno is null then
                   '一级部门'
                  when spdno = (select deptno from emp4 where spdno is null) then
                   '二级部门'
                  else
                   '三级部门'
                end as 组织架构
  from emp4
 order by 组织架构

5-题目

所需文件 TYEAR(年份) TAMOUNT(销量) 题目要求：当年销量-历史最临近年份的销量=增长销量，请查询哪年的增长销量最多？结果输出年份、增长销量

TYEAR TAMOUNT

1995 3000.00

1997 2000.00

1998 3000.00

2000 7000.00

2001 7000.00

2002 8000.00

create table xiaoshou(
tyear number(4),
tamount number(7))
select * from xiaoshou for update

select *
  from (select tyear,
               增长销量,
               row_number() over(order by 增长销量 desc) as xh
          from (select tyear,
                       nvl(tamount - lag(tamount) over(order by tyear), 0) as 增长销量
                  from xiaoshou))
 where xh = 1;

6.题目

所需文件 TYEAR(年份) TYEAR(月份) TAMOUNT(销量) 题目要求：查询每年每月的当月销量、当年累计销量、总累计销量，结果按时间从小到大排序，注意结果截图需截完整

TYEAR TMONTH TAMOUNT

1996 7 2543.00

1996 8 9234.00

1996 9 3678.00

1996 10 4723.00

1996 11 3452.00

1995 12 8000.00

1996 1 5478.00

1996 2 5988.00

1996 3 3555.00

1996 4 6547.00

1996 5 7734.00

1996 6 1133.00

1995 1 3000.00

1995 2 2000.00

1995 3 3000.00

1995 4 7000.00

1995 5 7000.00

1995 6 8000.00

1995 7 3000.00

1995 8 2000.00

1995 9 3000.00

1995 10 7000.00

1995 11 7000.00

1996 12 2134.00

create table xiaoguan(tyear number(8),tmonth number(4),tamount number(9,4))
select * from xiaoguan for update
insert into xiaoguan(tyear,tmonth,tamount) values (1996,7,2543.00);
drop  table xiaoguan

select tyear,
       tmonth,
       tamount as 当月销量,
       sum(tamount) over(partition by tyear order by tmonth asc) as 当年销量,
       sum(tamount) over(order by tyear, tmonth asc) as 总累计销量
  from xiaoguan
 order by tyear asc, tmonth asc;

面试2

1.数据来源：自建，说明所用数据库（限定使用YSQL/ORACLE/SQLSERVER）

1)查询各个年龄段的人数，结果输出年龄段及对应的人数，按年龄段升序排序

select 年龄段,count(1) as 人数 
from test008 group by 年龄段 order by 年龄段 asc;

2)查询每个工龄区间的人数，结果输出工龄区间及对应的人数，按人数降序排序

三年以下（工龄3）

三年七年（3<=工龄7）

七年十年（7<=工龄10）

十年十五年（10<=工龄15）

十五年及以上（15<=工龄）

select distinct 工龄区间,
                count(工龄) over(partition by 工龄区间) as 工龄区间人数
  from (select 工龄,
               case
                 when 工龄 < 3 then
                  '三年以下'
                 when 工龄 < 7 and 工龄 >= 3 then
                  '三年七年'
                 when 工龄 < 10 and 工龄 > = 7 then
                  '七年十年'
                 when 工龄 < 15 and 工龄 > = 10 then
                  '十年十五年'
                 when 工龄 >= 15 then
                  '十五年及以上'
                 else
                  null
               end as 工龄区间
        
          from b2)
 order by 工龄区间人数 desc;

3)查询姓名重复三次的人员ID，结果以人员ID降序排序

select id,人员姓名
  from table1 t1
 where 3 = (select count(1) from table1 where 人员姓名 = t1.人员姓名)
 order by id;

4)查询每个公司岗位类别为‘一般管理’的人员中，各个学历的占比，结果输出，公司、学历、占比

--1：开窗实现：
select 公司简称,
       学历,
       round(count(1) over(partition by 公司简称, 学历) / count(1)
             over(partition by 公司简称) * 100,
             2) || '%'
  from test008
 where 岗位类别 = '一般管理';
 
 
select 公司简称,
       学历,
       round(公司某学历人数 / 公司人数, 4) * 100 || '%' as 占比
  from (select distinct 公司简称,
                        学历,
                        count(学历) over(partition by 学历, 公司简称) as 公司某学历人数,
                        count(学历) over(partition by 公司简称) as 公司人数
          from b2
         where 岗位类别 = '一般管理'
         order by 公司简称);
 
--2：多表连接实现：
select t2.公司简称, t2.学历, rs / zrs
  from (select 公司简称, count(1) zrs
          from tab
         where 岗位类别 = '一般管理'
         group by 公司简称) t1
  join (select 公司简称, 学历, count(1) rs
          from tab
         where 岗位类别 = '一般管理'
         group by 公司简称, 学历) t2
    on t1.公司简称 = t2.公司简称;

--3：相关子查询实现：
select 公司简称,
       学历,
       count(1) / (select count(1)
                     from table1
                    where 岗位类别 = '一般管理'
                      and t.公司简称 = 公司简称) 占比
  from table1 t
 where 岗位类别 = '一般管理'
 group by 公司简称, 学历;

5)查询去掉最大年龄，最小年龄后人员的平均年龄

select avg(年龄) 
from table1 
where 年龄!=(select max(年龄)
           
           from table1) and 年龄!=(select min(年龄) ；                                                                        from table1);

2.数据来源：根据题目所用表格导入自建，说明所用数据库（限定使用YSQL/ORACLE/SQLSERVER）

1)查询新进类型中，不同原因的人数以及人数排名，结果输出原因、人数、排名

select t.*, rownum as 排名
  from (select 变动原因，count(1) as 人数
          from table2
          where 类型='新进'
         group by 变动原因
         order by count(1) desc) t;

2)查询009年度，每个月的公司变化人数（新进人数-离职人数），结果输出年度、月度、变化人数，结果根据年度、月度升序排序

--多表连接实现：
select t1.年度, t1.月度, 离职人数,新进人数,nvl(新进人数, 0) - 离职人数 as 变化人数
  from (select 年度, 月度, count(1) 离职人数
          from table2
         where 年度 = 2009
           and 类型 = '离职'
         group by 月度, 年度) t1
  left join (select 年度, 月度, count(1) 新进人数
               from table2
              where 年度 = 2009
                and 类型 = '新进'
              group by 月度, 年度) t2
    on t1.月度 = t2.月度
 order by t1.年度, t1.月度;
 

select t1.年度, t1.月度, rs1 - rs2 变化人数
  from (select distinct 年度,
                        月度,
                        类型,
                        nvl(count(1) over(partition by 月度, 类型), 0) rs1
          from table22
         where 类型 = '新进'
           and 年度 = 2009) t1
  right join (select distinct 年度,
                        月度,
                        类型,
                        count(1) over(partition by 月度, 类型) rs2
          from table22
         where 类型 = '离职'
           and 年度 = 2009) t2
    on t2.月度 = t1.月度
 order by t1.年度, t1.月度;

--相关子查询实现：
select 年度,
       月度,
       (select count(1)
          from table2
         where 月度 = t.月度
           and 年度 = 2009
           and 类型 = '新进') - (select count(1)
                               from table2
                              where 月度 = t.月度
                                and 年度 = 2009
                                and 类型 = '离职') as 变化人数
  from table2 t
 where 年度 = 2009
 group by 年度, 月度
 order by 年度, 月度;

3)查询累计到每个年度的离职人数结果输出年度、累计离职人数（注意是累计不是合计）

--1：先去每年合计人数再去累计人数
select 年度, sum(人数) over(order by 年度) as 累计离职人数
  from (select 年度，count(1) 人数
          from table2
         where 类型 = '离职'
         group by 年度) t;

--2：直接取累计      
select  distinct 年度, count(1) over(order by 年度) as 累计离职人数 
from table2
 where 类型 = '离职'
 order by 年度;

4)查询009年度，每个月的离职人数以及环比增长率，结果根据年度、月度升序排序

select t.*,
       (人数 - lag(人数, 1, 0) over(order by 月度)) / lag(人数, 1, null) over(order by 月度) as 环比增长率
  from (select 年度, 月度, count(1) as 人数
          from table2
         where 年度 = '2009'
           and 类型 = '离职'
         group by 年度, 月度) t
 order by 年度, 月度;

--：
select 年度,
       月度,
       round((月离职人数 - lag(月离职人数) over(order by 月度)) / lag(月离职人数)
             over(order by 月度) * 100,
             4) || '%'
  from (select distinct 年度,
                        月度,
                        count(1) over(partition by 月度) 月离职人数
          from table2
         where 年度 = '2009')
 order by 年度, 月度;

3.数据来源：自建，说明所用数据库（限定使用YSQL/ORACLE/SQLSERVER）

1)随机建个含有主键的表，输出建表语句

create table table10 
(sno number(4) primary key,  ---行级约束
sname varchar2(18) unique,
 sage number(2));

2)插入条数据，输出插入语句

insert into table10 values(1001,'张三',20);
insert into table10 values(1002,'李四',21);
insert into table10 values(1003,'王五',21);
insert into table10 values(1004,'赵六',20);

3)删除两条输出，输出删除语句

delete from table10 where sno=1001 or sno=1002;

4)根据主键更新两条数据，输出更新语句

update table10 set sname='小七',sage=26 where sno=1004;

5)查询一条数据，建成视图，输出建视图语句

create view abc as select sno from table10;

试题3

面试题3

1.表C结构如下

字段名称	字段类型	中文描述	说明
company	varchar(10)	公司名称	主键
agencyid	varchar(10)	员工工号	主键
orderno	varchar(8)	订单号	主键
productid	varchar(3)	产品代码	主键
producttype1	varchar(2)
producttype2	varchar(2)

要求编写SQL语句查询(company, agencyid, orderno)组合键值重复的记录明细.

Select company, agencycid, orderno, count(1)
  from c
 group by company, agencycid, orderno
having count(1) > 1;

select distinct company, agencyid, orderno, 重复数量
  from (select company,
               agencyid,
               orderno,
               count(1) over(partition by company, agencyid, orderno) 重复数量
          from 表C)
 where 重复数量 > 1;

2、表D结构如下a

字段名称	字段类型	中文描述	说明
company	varchar(10)	公司名称	主键
agencyid	varchar(10)	员工工号	主键
name	varchar(20)	员工姓名
dtmonth	int	年月	主键
duty	varchar(20)	员工职级

表E结构如下：

字段名称	字段类型	中文描述	说明
province	varchar(20)	省份
company	varchar(10)	公司名称	主键
adearea	varchar(8)	办事处名称	主键

表D与表E通过company关联，要求编写SQL语句查询每个员工工号所属的省份。

select t1.*,t2.province  
from 表D t1 inner join 表E t2 on t1.company=t2.company;

3、存在表T(a,b,c,d)，要根据字段c倒序排列后取第21~30条记录显示，请给出SQL。

select *
  from (select a, b, c, d, row_number() over(order by c desc) as m from t)
 where m >= 21
   and m <= 30

4、有一张员工工资表(EMP)，表有两列：员工编号(ID)，工资(SALARY)，请给出SQL删除ID重复的记录，只保留第一条，不需要考虑表中存在完全相同记录的情况。

delete from emp
        where (deptno, sal) not in (select deptno, sal
                                      from (select t.*,
                                                   row_number() over(partition by deptno order by sal) as xh
                                              from emp t)
                                     where xh = 1);

5、已知一张表S的结构如左下表，怎样通过select语句把它变成右下表的结构，即将竖表转换为横表。

姓名	科目	成绩
张三	语文	20
张三	数学	30
张三	英语	50
李四	语文	70
李四	数学	60
李四	英语	90

姓名	语文	数学	英语
张三	20	30	50
李四	70	60	90

select *
  from (select 姓名, 科目, 成绩 from s)
pivot(sum(成绩)
   for 科目 in('语文', '数学', '英语'));

6、请用SQL 语句实现：从数据表Test中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。说明：表Test 中有很多科目，都有1~12 月份的发生额，有3个字段， AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。

select accid
  from test t1
  join (select * from test where accid = 101) t2
    on t1.occmonth = t2.occmonth
   and t1.debitoccur > t2.debitoccur
 group by accid
having count(1) = 12;