几个经典sql练习题

1.
------------------------------------------------------------
*
1
2
3
5
5
7
7
8
9
只显示，5，5,7，7出来，不显示别的。
*/

/*
	测试数据
*/

Create Table Num
(
	num number,
)
;

insert into Num values(1);
insert into Num values(2);
insert into Num values(3);
insert into Num values(5);
insert into Num values(5);
insert into Num values(7);
insert into Num values(7);
insert into Num values(8);
insert into Num values(9);

/*
answer:

select num from num
where num in 
(select num from num group by num having count(num) > 1);
*/
2. 
-------------------------------------------------------------

Create table Table_A
(
       UserType varchar2(50),
       LoginDate varchar2(50),
       Address varchar2(50),
       IsCertified varchar2(50)
);

insert into Table_A values('管理员','20050510','城市','是');
insert into Table_A values('一般用户','20050515','郊区','是');
insert into Table_A values('普通管理员','20050610','郊区','是');
insert into Table_A values('一般用户','20050622','城市','是');
insert into Table_A values('普通管理员','20050620','郊区','是');

/*
select * from Table_A

管理员   普通管理员   一般用户       
  1          2           2
*/

/*
answer:

select (select count(*) from table_a where usertype = '管理员') 管理员,
(select count(*) from table_a where userType = '普通管理员') 普通管理员,
(select count(*) from table_a where userType = '一般用户') 一般用户
from table_a
where rownum < 2;
*/

3.

---------------------------------------------------------

Create table Employee
(
       empID number,
       empName varchar2(20),
       SB varchar2(10),
       XB varchar2(10),
       eDate date
);



insert into Employee values(1,'大傻', '迟到','早退',date'2005-10-17');
insert into Employee values(2,'二傻','按时','早退',Date'2005-10-17');
insert into Employee values(4,'大傻','按时','早退',Date'2005-10-16');
insert into Employee values(5,'二傻','按时','按时',Date'2005-10-17');

select * from Employee

/*
用户名  月份  上班迟到次数  下班早退次数
大傻     10     1            2
二傻     10     0            1
*/

/*
answer:

select distinct empname 用户名,extract(month from edate) 月份,
(select count(*) from employee e2 where sb = '迟到' and e1.empname = e2.empname) 迟到次数,
(select count(*) from employee e2 where xb = '早退' and e1.empname = e2.empname) 早退次数 
from employee e1
*/
3.
表名 销售表：

   NO ,  NO2, 　数量 　　日期
  1234　567890　 33.5  2004-12-21
  1234　598701 　44.8  2004-11-21
  1234　598701 　45.2  2004-10-01
  1234　567890 　66.5  2004-9-21
  3456　789065 　22.5   2004-10-01
  3456　789065 　77.5   2004-10-27
  3456　678901　 48.5   2004-12-21

  按月统计销售表中货物的销售量数
查询结果如下：
  No， No2 ，   九月， 　十月，十一月，十二月
 1234，567890， 　66.5 ，　 0 ， 　0  ，  33.5
 1234，598701，   0   ，　45.2，　44.8，　　0
 3456，789065，　　0  ，　100， 　0   ，　0
 3456，678901，　　0　，    0，   0  ， 48.5


create table sales
(
	empid number,
	proid number,
	num   number(10,2),
	saDate date
)
;
insert into sales values(1234,567890,33.5,date'2004-12-21')
;
insert into sales values(1234,598701,44.8,date'2004-11-21')
;
insert into sales values(1234,598701,45.2,date'2004-10-01')
;
insert into sales values(1234,567890,66.5,date'2004-9-21')
;
insert into sales values(3456,789065,22.5,date'2004-10-01')
;
insert into sales values(3456,789065,77.5,date'2004-10-27')
;
insert into sales values(3456,678901,48.5,date'2004-12-21')
;
Result(答案):
select empid,proid,sum(case when extract(month from sadate)='9' then num else 0 end) 九月,sum(case when extract(month from sadate)='10' then num else 0 end) 十月,sum(case when extract(month from sadate)='11' then num else 0 end) 十一月,sum(case when extract(month from sadate)='12' then num else 0 end) 十二月
from sales group by empid,proid;