- 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;
经典SQL练习题
最新推荐文章于 2020-10-12 14:42:30 发布