Oracle day14

/*create table f0810(
id number
,times varchar2(50));
insert into f0810 values(1,'2019-12-25 11:01');
insert into f0810 values(2,'2019-12-25 11:03');
insert into f0810 values(3,'2019-12-25 11:05');
insert into f0810 values(4,'2019-12-25 11:09');
insert into f0810 values(5,'2019-12-25 11:17');
insert into f0810 values(6,'2019-12-25 11:19');
insert into f0810 values(7,'2019-12-25 11:29');
insert into f0810 values(8,'2019-12-25 11:37');*/
select id,substr(to_char(times),-5,5) mi from f0810;

with tt as(
select sum(case when t.mi<'15' then 1 else 0 end) first,
sum(case when t.mi<'30' and t.mi > '15' then 1 else 0 end) second,
sum(case when t.mi>'30' then 1 else 0 end) third
from (select id,substr(to_char(times),-2) mi from f0810) t)
select '2019-12-25 11:15:00' "Times",first cnt from tt
union all
select '2019-12-25 11:15:00' ,second from tt
union all
select '2019-12-25 11:15:00' ,third from tt
 
-----------------------------QTing-XLJ-------------------------------
with a as(
(select sum(case when mi<=15 then 1 end) "2019-12-25 11:g15",
sum(case when mi>15 and mi<=30 then 1 end) "2019-12-25 11:30",
sum(case when mi>30 and mi<=45 then 1 end) "2019-12-25 11:45"
from
(select id,substr(times,15) mi from f0810)))
select '2019-12-25 11:15:00' as times,"2019-12-25 11:15" as cnt from a
union all
select '2019-12-25 11:30:00',"2019-12-25 11:30" from a
union all
select '2019-12-25 11:45:00',"2019-12-25 11:45" from a
-----------------------------QTing-LJQ-------------------------------
with ff as(
(select sum(case when s between 0 and 15 then 1 end) a,
sum(case when s between 16 and 30 then 1 end) b,
sum(case when s between 31 and 45 then 1 end) c
from
(select id,substr(times,instr(times,':')+1,2) s from f0810)))

select '2019-12-25 11:15:00' times,a cnt from ff
union all
select '2019-12-25 11:30:00',b from ff
union all
select '2019-12-25 11:45:00',c from ff

-----------------------------查看索引-----------------------
select * from user_indexes where table_name = 'EMP_BAK';

-----创建B树索引
create index index_emp_bak_empno on emp_bak(empno);

-----创建位图索引
create bitmap index index_emp_bak_job on emp_bak(job);

-----创建函数索引
create index emp_bak_lower_ename on emp_bak(lower(ename));

-----创建唯一索引
create unique index index_unique_empno on emp_bak(empno);

-----创建复合索引
create index index_empno_ename_job on emp_bak(empno,ename,job);

select * from user_indexes where table_name = 'DEPT_BAK';

--在scott.dept表的dname字段上创建一个具有唯一性的B树索引
create unique index index_dept_bak_dname on dept_bak(dname);

--在scott.emp表的ename和deptno字段上创建一个复合索引。
create index index_ename_deptno on emp_bak(ename,deptno);

--在scott.emp表的sal字段上创建一个名为index_sal的B树索引，
--按字段值的降序排列。
create index index_sal on emp_bak(sal desc);
drop index index_sal;

-----创建视图

 
--4.创建一个 只读 视图v_emp_salesman，内容是所有的SALESMAN信息：
create view v_emp_salesman
as select * from emp where job = 'SALESMAN'
with read only;
select * from v_emp_salesman

--5.创建一个视图v_mgr_salary，列出每个主管的名字，
--以及他的下属中的最高薪水和最低薪水：
create view v_mg_salary 
as select e2.ename,max(e1.sal) 最高薪水,min(e1.sal) 最低薪水
 from emp e1 
join emp e2 on e1.mgr = e2.empno group by e2.ename;
select * from v_mg_salary

--6.创建一个视图，通过该视图可以查看每个部门的名称，
--最低工资，最高工资，平均工资：
create view v_dname_salary as
select d.dname,min(e.sal) 最低工资,max(e.sal) 最高工资,
avg(e.sal) 平均工资 from emp e 
join dept d on d.deptno = e.deptno group by d.dname;

select * from v_dname_salary;
-------------------------------------------------------------------------
/*create table card( --借书卡
 cno number  --卡号
 ,name varchar2(30)  --姓名
 ,class varchar2(30));  --班级
insert into card(cno, name, class)values(01, 'Tony','C01');
insert into card(cno, name, class)values(02, 'Jack', 'A02');
insert into card(cno, name, class)values(03, 'Lisa', 'B03');
insert into card(cno, name, class)values(04, 'TOM', 'B01');
insert into card(cno, name, class)values(05, 'JERRY', 'A03');
insert into card(cno, name, class)values(06, 'YIFAN', 'C02');*/
--2---------------------------------------------------
drop table borrow;
/*create table borrow(  --借书记录。 
cno number  -- 借书卡号，
,bno number  -- 书号，
,rdate date  --借书日期
,getdate date);  --还书日期
insert into borrow
values(01, 111, to_date('2022-09-25','yyyy-mm-dd'),'');
insert into borrow
values(01, 131, to_date('2022-09-25','yyyy-mm-dd'),'');
insert into borrow
values(03, 131, to_date('2022-05-24','yyyy-mm-dd'),'');
insert into borrow
values(03, 161, to_date('2022-05-24','yyyy-mm-dd'),'');
insert into borrow
values(04, 151, to_date('2022-04-13','yyyy-mm-dd'),'');
insert into borrow
values(04, 161, to_date('2022-04-13','yyyy-mm-dd'),'');
insert into borrow
values(05, 171, to_date('2022-12-23','yyyy-mm-dd'),'');
insert into borrow
values(06, 181, to_date('2022-08-26','yyyy-mm-dd'),'');*/
/*create table bookss(   --图书
 bno number  --书号
 ,bname varchar2(30)  --书名
 ,author varchar2(30) --作者
 ,price varchar2(30) --单价
 ,quantity number);  --库存册数
insert into bookss(bno, bname, author, price, quantity)
values(111, '水浒', 'qwe', '35', 5);
insert into bookss(bno, bname, author, price, quantity)
values(121, '网络安全', 'asd', '40', 3);
insert into bookss(bno, bname, author, price, quantity)
values(131, '计算方法', 'zxc', '78', 4);
insert into bookss(bno, bname, author, price, quantity)
values(141, '计算方法习题集', 'qaz', '24', 8);
insert into bookss(bno, bname, author, price, quantity)
values(151, '数据库技术及应用', 'wsx', '28', 6);
insert into bookss(bno, bname, author, price, quantity)
values(161, '组合数学', 'wsx', '56', 7);
insert into bookss(bno, bname, author, price, quantity)
values(171, '高等数学', 'edc', '99', 3);
insert into bookss(bno, bname, author, price, quantity)
values(181, '石岩水库', 'rfv', '47', 2);*/
select * from card;
select * from borrow;
select * from bookss;
--备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
--1.假设建立BORROW表时没有定义主外键，
--要求单独定义其主键约束和外键约束：
alter table bookss add constraint bno_pri primary key(bno);
alter table card add constraint card_pri primary key(cno);
alter table borrow add constraint fk_bno foreign key(bno) references bookss;
alter table borrow add constraint fk_cno foreign key(cno) references card;

--2.找出借书超过5本的读者，输出借书卡号及所借图书册数：
select b.cno,count(*) from borrow b group by b.cno having count(*) > 5;

--3.查询借阅了"水浒"一书的读者，输出姓名及班级：
select name,class from card where cno in
(select cno from borrow where bno in
(select bno from bookss where bname = '水浒'));

--4.查询过期未还图书，输出借阅者（卡号）、书号及还书日期：
select cno,bno,getdate from borrow where getdate is null;

--5.查询书名包括"网络"关键词的图书，输出书号、书名、作者：
select bno,bname,author from bookss where bname like '%网络%';

--6.查询现有图书中价格最高的图书，输出书名及作者：
select bname,author from bookss 
where price = (select max(price) from bookss);

--7.查询当前借了"计算方法"但没有借"计算方法习题集"的读者，
--输出其借书卡号，并按卡号降序排序输出：
select cno from borrow
where bno in 
(select bno from bookss where bname = '计算方法') 
and
bno not in (select bno from bookss where bname = '计算方法习题集')
order by cno desc;

--8.将"C01"班同学所借图书的还期都延长一周：
update borrow set getdate=getdate+7 where cno in
(select cno from card where class = 'C01');

--9.从BOOKS表中删除当前无人借阅的图书记录：
delete from bookss where bno not in
(select bno from borrow);

--10.如果经常按书名查询图书信息，请建立合适的索引：
create index index_bookss_bname on bookss(bname);
select * from user_indexes where table_name = 'BOOKSS';

--11.建立一个视图，显示"C01"班学生的借书信息，只要求显示姓名和书名：
create view v_name_bname as
select c.name,bo.bname from card c,borrow b, bookss bo 
where c.cno = b.cno and b.bno = bo.bno and c.class = 'C01';
select * from v_name_bname;

--12.查询当前同时借有"计算方法"和"组合数学"两本书的读者，
--输出其借书卡号，并按卡号升序排序输出：
select cno,count(*) from borrow where bno in
(select bno from bookss where bname in ('计算方法','组合数学'))
group by cno having count(*) = 2 order by cno ;

--13.假定在建BOOKS表时没有定义主键，
--写出为BOOKS表追加定义主键的语句：
alter table bookss add constraint bno_pri primary key(bno);

--14.将CARD 表中的CNAME最大列宽增加到10个字符：（假定原为6个字符）
alter table card modify cname char(10);

--15.为BOOKS表增加1列XNAME（系名），可变长，最大30个字符：
alter table bookss add xname varchar2(30);