常见面试sql

create table zp(rq varchar(10),shengfu nchar(1))


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


select * from zp;


select z.rq,count(z.shengfu)  from zp z group by z.rq


select rq,sum(decode(shengfu,'胜',1,0)) 胜,sum(decode(shengfu,'负',1,0)) 负 from zp  group by rq  order by rq ;


select z1.rq,z1.胜,z2.负 from 
(select rq,count(shengfu) 胜  from zp  where shengfu='胜' group by rq) z1,
(select rq,count(shengfu) 负 from zp where shengfu='负' group by rq) z2  where z1.rq=z2.rq


select z.rq,sum(case  when z.shengfu='胜' then 1 else 0 end) 胜,sum(case when z.shengfu='负' then 1else 0 end)负 from zp z group by rq




create table zp2(kemu varchar2(20),fenshu varchar2(20)) 
insert into zp2 values('语文','90');
insert into zp2 values('数学','70');
insert into zp2 values('英语','40');
insert into zp2 values('英文','40');
insert into zp2 values('英文呢','40');
select fenshu,count(*) from zp2 group by fenshu having count(*)>1


select * from zp2
select distinct a.kemu,b.kemu  from zp2 a,zp2 b where a.kemu=b.kemu




create table zp3(yuwen number(3),shuxue number(3),yingyu number(3))
insert into zp3 values(60,70,40)


select (case when yuwen>=60 then '及格' else '不及格' end) yuwen,(case when shuxue>=60 then '及格' else '不及格' end) shuxue,
(case when yingyu>=60 then '及格' else '不及格' end ) yingyu from
zp3


create table zp4(yuefen number(6),jixiao number(6))
insert into zp4 values (2010,1000);
insert into zp4 values (2011,1000);
insert into zp4 values (2012,1000);
insert into zp4 values (2013,1000);
insert into zp4 values (2014,1000);


select b.yuefen,sum(a.jixiao) from zp4 a,zp4 b where a.yuefen<=b.yuefen group by b.yuefen


select a.yuefen from zp4 a,zp4 b where a.yuefen<=b.yuefen group by b.yuefen
select a.yuefen,(select sum(jixiao) from zp4 b where b.yuefen<=a.yuefen) from zp4 a




create table zp5(
name varchar2(20),
kecheng varchar2(10),
fenshu number(3)


)


insert into zp5 values('张三','语文',80);
insert into zp5 values('张三','数学',75);
insert into zp5 values('李四','语文',76);
insert into zp5 values('李四','数学',90);
insert into zp5 values('王五','语文',81);
insert into zp5 values('王五','数学',90);
insert into zp5 values('王五','英语',100);




--查询每门学科成绩及格(Mark > 80)的学生的名字？
select distinct name from zp5 where name not in( select distinct name from zp5 z where  fenshu<80)
--查询出总分排名前三的学员名字?
select name from (select  name,sum(fenshu) from zp5 z group by name order by sum(fenshu) desc) a where rownum<4






update zp5 set name='王五' where fenshu=90 and
delete from zp5 where fenshu=100


select * from zp5


delete zp5 where rowid=(select max(rowid) from zp5 group by name,kecheng,fenshu having count(*)>1)


--用一条SQL语句查询出每门课都大于80分的学生姓名
select  distinct name from zp5 z1  where z1.name not in(
select z.name from zp5 z where z.fenshu<80)


create table zp6(
name varchar(2)
)


insert into zp6 values('a');
insert into zp6 values('b');
insert into zp6 values('c');
insert into zp6 values('d');
select  a.name,b.name from zp6 a,zp6 b where a.name<b.name




create table zp7(
year number,
month number,
amount number
)


insert into zp7 values(1991,1,1.1);
insert into zp7 values(1991,2,1.2);
insert into zp7 values(1991,3,1.3);
insert into zp7 values(1991,4,1.4);




insert into zp7 values(1992,1,2.1);
insert into zp7 values(1992,2,2.2);
insert into zp7 values(1992,3,2.3);
insert into zp7 values(1992,4,2.4);


select * from zp7
--year m1   m2   m3   m4
--1991 1.1 1.2 1.3 1.4


select distinct  z.year,a.amount,b.amount,c.amount,d.amount


from zp7 z,(select year,amount from zp7 where month=1 ) a ,
(select year,amount from zp7 where month=2 ) b ,
(select year,amount from zp7 where month=3 ) c ,
(select year,amount from zp7 where month=4 ) d  where z.year=a.year and z.year=b.year and z.year=c.year and z.year=d.year


select z.year,
(select amount from zp7 a where a.month=1 and a.year=z.year) ,
(select amount from zp7 a where a.month=2 and a.year=z.year) ,
(select amount from zp7 a where a.month=3 and a.year=z.year) ,
(select amount from zp7 a where a.month=4 and a.year=z.year) 
from zp7 z group by z.year


select z.year,
sum(decode(z.month,1,z.amount)),
sum(decode(z.month,2,z.amount)),
sum(decode(z.month,3,z.amount)),
sum(decode(z.month,4,z.amount))
 from zp7 z group by z.year




--写出SQL语句,查询选修了所有选修课程的学生； 伪代码   s学生表   c课程表     sc学生课程中间表
select * from s where (
select count(*) from sc where sc.sid=s.sid)=(select count(*) from c)


create table zp8(
id number,
name varchar2(10),
age number,
parent number
)


insert into zp8 values(1,'张0',40,0);


insert into zp8 values(2,'王1',25,1);
insert into zp8 values(3,'王2',26,1);


insert into zp8 values(4,'李1',21,2);
insert into zp8 values(5,'李2',22,2);
insert into zp8 values(6,'李3',21,3);
insert into zp8 values(7,'李4',22,3);
insert into zp8 values(8,'李5',31,3);
insert into zp8 values(9,'李6',32,3);


insert into zp8 values(10,'赵0',40,4);
insert into zp8 values(11,'王3',40,1)
insert into zp8 values(12,'王4',41,1)
--列出所有年龄比所属主管年龄大的人的ID和名字?
select * from zp8 z where z.age>(select b.age from zp8 b where b.id=z.parent)
--有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
update b set b.value= （
select b.key,a.value from A a.key=b.key） where b.id in (select b.id from b,a where b.key=a.key)