SQL面试试题

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

面试题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）定义语文和数学总分大于等于12