常用面试SQL

最新推荐文章于 2024-07-26 15:30:26 发布

牛逸凡

最新推荐文章于 2024-07-26 15:30:26 发布

阅读量527

点赞数

分类专栏：面试题文章标签： sql 面试sql

本文链接：https://blog.csdn.net/weixin_42685589/article/details/81144374

版权

本文通过一系列SQL查询实例，涵盖了面试中常见的问题，包括查询特定条件的学员信息、统计选修课程数量、处理重复记录、按条件聚合数据、时间序列分析以及复杂条件下的数据筛选等。这些例子旨在帮助读者掌握SQL查询技巧，适用于面试准备。

摘要由CSDN通过智能技术生成

S(S#,SN,SD,SA) 【分别代表学号，学员姓名，所属单位，学员年龄】
C(C#,CN) 【分别代表课程编号，课程名称】
SC(S#,C#,G) 【分别代表学号，所选的课程编号，学习成绩】

insert into sc values(s1,c1,10);
insert into sc values(s1,c2,10);

insert into sc values(s2,c1,10);
insert into sc values(s2,c2,10);
insert into sc values(s2,c3,10);
insert into sc values(s2,c4,10);
insert into sc values(s2,c5,10);
insert into sc values(s2,c6,10);

insert into sc values(s3,c1,10);

(1)查询选修课程名称为’税收基础’的学员学号和姓名?
select s# ,sn from s where S# in (select S# from c,sc where c.c#=sc.c# and cn=’税收基础’);
select s# ,sn from s where S# in (select S# from sc where sc.c#=(select C# from C where cn=’税收基础’));

(2)查询选修课程编号为’C2’的学员姓名和所属单位?
select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’;
select sn,sd from s where s.s# in (select s# from sc where sc.c#=’c2’);

(3)查询不选修课程编号为’C5’的学员姓名和所属单位?
select sn,sd from s,sc where s.s#=sc.s# and sc.c#<>’c5’;
select sn,sd from s,sc where s.s#=sc.s# and sc.c#!=’c5’;
select sn,sd from s where s# not in(select s# from sc where c#=’c5’);

(4)查询选修了课程的学员人数
select count(distinct s#) from sc;

(5)查询选修课程超过5门的学员学号和所属单位?
答：select sn,sd from s where s# in(select s# from sc group by s# having count(s#)>5)

###########################################################################
查询emp表中第3至10条记录，empno作为主键(此列值全是字符串，无任何规律)
1 2 3 4......30
select * from emp where empno>=3 and empno<=10;

SELECT *
FROM (SELECT A.*, ROWNUM RN FROM emp A WHERE ROWNUM <= 10)
WHERE RN >= 3;

###########################################################################
查询表HAHA中存在ID重复三次以上的记录,完整的查询语句如下：

create table haha(id number,name varchar2(100));
insert into haha values(1,'a');
insert into haha values(1,'b');
insert into haha values(1,'c');
insert into haha values(1,'d');

insert into haha values(2,'e');

insert into haha values(3,'f');
commit;

select * from haha where id in (select id from haha group by id having count(*)>3);

查询表HAHA中存在ID重复三次以上的记录,把对应的id查询出来

select id from (select id from haha group by id having count(*)>3) s;

###########################################################################

create table tab3(rq varchar2(100),shengfu varchar2(100));

insert into tab3 values('2005-05-09','胜');
insert into tab3 values('2005-05-09','胜');
insert into tab3 values('2005-05-09','负');
insert into tab3 values('2005-05-09','负');
insert into tab3 values('2005-05-10','胜');
insert into tab3 values('2005-05-10','负');
insert into tab3 values('2005-05-10','负');
commit;

要求显示如下：

比赛日期胜负
--------------------
2005-05-09 2 2
2005-05-10 1 2

1、
select rq as 比赛日期,
sum(case when shengfu='胜' then 1 else 0 end) as 胜,
sum(case when shengfu='负' then 1 else 0 end) as 负
from tab3 group by rq order by rq;

2、
select a.rq 比赛日期,a.胜,b.负 from
(select rq,count(*) 胜 from tab3 where shengfu='胜'group by rq) a
inner join
(select rq,count(*) 负 from tab3 where shengfu='负'group by rq) b
on a.rq=b.rq order by a.rq;

3、
select a.rq 比赛日期,a.胜,b.负 from
(select rq,count(*) 胜 from tab3 where shengfu='胜' group by rq) a,
(select rq,count(*) 负 from tab3 where shengfu='负' group by rq) b
where a.rq=b.rq order by a.rq;

4、
select rq as 比赛日期,
sum(decode(shengfu,'胜',1,0)) 胜,
sum(decode(shengfu,'负',1,0)) 负
from tab3
group by rq order by rq;

###########################################################################

drop table student;
create table student(id number,name varchar2(100),age number,sex varchar2(100));

insert into student values(1,'张三',1986,'男');
insert into student values(2,'张四',1986,'男');
insert into student values(3,'张五',1986,'男');
insert into student values(4,'张六',1987,'男');
insert into student values(5,'张七',1987,'男');
insert into student values(6,'张八',1987,'女');
insert into student values(7,'张就',1987,'女');
insert into student values(8,'张oy',1986,'女');
commit;

查询