SQL面试题及答案

1.一道SQL语句面试题，关于group by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

            胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')

1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001

 

 

 

--创建表
create table student (sid number,sname varchar2(10),age number,sex varchar2(2));
create table course (cid varchar2(5),cname varchar2(10),teaid varchar2(5));
create table score (sid number,cid varchar2(5),score number);
create table tearcher (teaid varchar2(5),teaname varchar2(10));

---插入学生表的数据
insert into student values (1,'张三',18,'男');
insert into student values (2,'李莉',19,'女');
insert into student values (3,'王五',20,'男');
insert into student values (4,'李红',21,'女');
commit;

--插入课程表的数据
insert into course values ('001','语文','te01');
insert into course values ('002','数学','te02');
insert into course values ('003','英语','te03');
insert into course values ('004','计算机','te04');
commit;

--插入成绩表的数据

insert into score values (1,'001',80);
insert into score values (1,'002',90);
insert into score values (1,'003',100);
insert into score values (1,'004',90);

insert into score values (2,'001',80);
insert into score values (2,'002',70);
insert into score values (2,'003',600);

insert into score values (3,'001',60);
insert into score values (3,'002',90);
insert into score values (3,'003',800);
insert into score values (3,'004',90);

insert into score values (4,'001',100);
insert into score values (4,'004',70);
commit;

--插入老师表的数据
insert into tearcher values('te01','陈宇');
insert into tearcher values('te02','何山');
insert into tearcher values('te03','王璐');
insert into tearcher values('te04','李四');
commit;

---查询表中的数据
select * from student
select * from course
select * from score
select * from tearcher

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

误理解成：

select score  from score a where a.cid='001';
select score  from score b where b.cid='002';

正确的SQL：

select * from student a where
(select score from score b where b.sid=a.id and b.cid='001')>
(select score from score c where c.sid=a.id and c.cid='002')

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

select a.id,name,count(b.cid),sum(score)
from student a,score b
where a.id=b.sid
group by a.id,name;

select a.id,name,(select count(cid) from score where sid=a.id)as kcs,
(select sum(score) from score where sid=a.id)as zfs from student a

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