如果是线上面试或者练习可以先建表再写语句,如果笔试的话就不用自己创建,
面试题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