-- Oracle Database 12c Complete Reference
-- sample SQL statements from book
drop user hr cascade;
grant connect, resource, create view, unlimited tablespace to practice identified by hr;
-- Chapter 12: grouping
select category_name, count(*)
from bookshelf
group by category_name;
select category_name, count(*) from bookshelf;
select category_name, count(*)
from bookshelf
group by category_name
having count(*) > 5;
select category_name, count(*), avg(rating)
from bookshelf
group by category_name;
select avg(rating) from bookshelf;
select category_name, count(*), avg(rating)
from bookshelf
group by category_name
having avg(rating) >
(select avg(rating) from bookshelf);
select category_name, count(*)
from bookshelf
group by category_name
order by category_name desc;
select category_name, count(*)
from bookshelf
group by category_name
order by count(*) desc;
select category_name, count(*) as counter
from bookshelf
group by category_name
having counter > 1
order by count(*) desc;
select category_name, count(*), avg(rating)
from bookshelf
where rating > 1
group by category_name
having category_name like 'A%'
order by count(*) desc;
select category_name, count(*), avg(rating)
from bookshelf
where rating > 1
and category_name like 'A%'
group by category_name
order by count(*) desc;
select * from invasion;
create or replace view category_count as
select category_name, count(*) as counter
from bookshelf
group by category_name;
desc category_count
select * from category_count;
create or replace view book_count as
select count(*) book_count from bookshelf;
select book_count from book_count;
select category_name, counter, (counter/book_count)*100 as percent
from category_count, book_count
order by category_name;
select category_name, count(*),
(count(*)/max(book_count))*100 as percent
from bookshelf, book_count
group by category_name
order by category_name;
select category_name, counter, (counter/book_count)*100 as percent
from category_count,
(select count(*) as book_count from bookshelf)
order by category_name;
create view bookshelf_sorted
as select * from bookshelf
order by title;
select title from bookshelf_sorted
where rownum < 10;
select category_name, count(*),
(count(*)/max(book_count))*100 as percent
from bookshelf,book_count
group by category_name
having avg(rating) > 4
order by category_name;
select author_name, count(*)
from author
group by author_name
having count(*) > 1
order by author_name;
column title format a40
select title, count(*)
from bookshelf_author
group by title
having count(*) > 1;
column title format a40
column author_name format a30
select title, author_name
from bookshelf_author,
(select title as grouped_title, count(*) as title_counter
from bookshelf_author
group by title
having count(*) > 1)
where title=grouped_title
order by title, author_name;
column title format a40
column author_name format a30
select title, author_name
from bookshelf_author,
(select title as grouped_title, count(*) as title_counter
from bookshelf_author
group by title
having count(*) > 1)
where title=grouped_title
order by title_counter desc,title, author_name;
-- Chapter 13: Advanced subqueries
select distinct c.parent_category, c.sub_category
from category c, bookshelf b, bookshelf_checkout bc
where c.category_name = b.category_name
and b.title = bc.title;
select distinct c.parent_category, c.sub_category
from category c
where category_name in
(select category_name from bookshelf
where title in
(select title from bookshelf_checkout)
);
select title from bookshelf_author
where title in
(select title from bookshelf
where author_name = 'Stephen Jay Gould');
select title from bookshelf
where author_name = 'Stephen Jay Gould';
select title from bookshelf_author ba
where title in
(select title from bookshelf
where ba.author_name = 'Stephen Jay Gould');
select distinct author_name from bookshelf_author
where title in
(select title from bookshelf
where category_name in
(select distinct category_name from bookshelf
where title in
(select title
from bookshelf_checkout bc
where bc.name = 'Fred Fuller')));
select distinct author_name
from bookshelf_author ba, bookshelf_checkout bc
where ba.title = bc.title
and bc.name = 'Fred Fuller';
select distinct author_name from bookshelf_author
where title in
(select title from bookshelf
where category_name in
(select distinct category_name from bookshelf
where title in
(select title
from bookshelf_checkout bc
where bc.name = 'Fred Fuller')))
and author_name not in
(select author_name
from bookshelf_author ba, bookshelf_checkout bc
where ba.title = bc.title
and bc.name = 'Fred Fuller');
column author_name format a25
select author_name, count(*)
from bookshelf_author
group by author_name
having count(*) > 1;
select author_name, title, count(*)
from bookshelf_author
group by author_name, title
having count(*) > 1;
column AuthorName format a25
column Title format a30
select author_name, title
from bookshelf_author ba
where exists
(select 'x'
from bookshelf_author ba2
where ba.author_name = ba2.author_name
group by ba2.author_name
having count(ba2.title) > 1)
order by author_name, title;
select author_name, title
from bookshelf_author ba
where author_name in
(select author_name
from bookshelf_author
group by author_name
having count(title) > 1)
order by author_name, title;
column title format a40
select distinct title
from bookshelf_checkout;
select distinct b.title
from bookshelf_checkout bc, bookshelf b
where bc.title = b.title;
select b.title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc, bookshelf b
where bc.title(+) = b.title
group by b.title;
select b.title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc, bookshelf b
where bc.title(+) = b.title
group by b.title;
select b.title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc
right outer join bookshelf b
on bc.title = b.title
group by b.title;
select title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc
right outer join bookshelf b
using (title)
group by title;
select b.title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc
left outer join bookshelf b
on bc.title = b.title
group by b.title;
select b.title, max(bc.returned_date - bc.checkout_date)
"Most Days Out"
from bookshelf_checkout bc
full outer join bookshelf b
on bc.title = b.title
group by b.title;
select title
from bookshelf
where title not in
(select title from bookshelf_checkout)
order by title;
select distinct b.title
from bookshelf_checkout bc
right outer join bookshelf b
on bc.title = b.title
where bc.title is null
order by b.title;
select b.title
from bookshelf b
where not exists
(select 'x'
from bookshelf_checkout bc
where bc.title = b.title)
order by b.title;
select title
from book_order
natural join bookshelf;
select bo.title
from book_order bo, bookshelf
where bo.title = bookshelf.title
and bo.publisher = bookshelf.publisher
and bo.category_name = bookshelf.category_name;
select bo.title
from book_order bo
inner join bookshelf b
on bo.title = b.title;
select title
from bookshelf
where title < 'M';
select title from b
Oracle Database 12c The Complete Reference.7z （3个子文件） 
Oracle Database 12c The Complete Reference.docx 46.17MB
0071801758 
Examples.sql 44KB Tables.sql 40KB资源评论
yellowjdd2016-10-27资料不完整，浪费了积分- resccl2017-05-21资料不完整，坑啊
aha2130
- 粉丝: 7
- 资源: 60
最新资源
- C# 西门子S7 TCP协议客户端设计工程源码带注释，开源dll文件，包括打包完的安装包
- 电网行测冲刺讲义-学生版-纯图版
- 基于磁链锁相环控制的双向逆变器Simulink仿真，无需 电压采样进行锁相控制
- 工程管理：长沙理工大学2021级工程造价咨询综合实践指导-课程设计实施方案及细则
- 黑龙江省各市、县、区及街镇网页版SVG图
- json-c-0.17.tar.gz
- 前端全套面试题资料，包含js、css、vue等相关资料
- C#上位机 APP监控西门子S7-1200 C#全套源代码 1，C#开发上位机手机APP，自己写的程序可提供部分 2，通过VS2019开发安卓手机app 3，全套源代码，现场运行设备实测有效 4
- 综合能源耦合微网优化程序matlab 程序基于冷热电联供综合能源耦合模型，采用cchp，并且含有压缩空气储能，采用粒子群优化求解
- 2025电网行测基础讲义-学生版-纯图版
- chromedriver-win64_133.0.6939.0.zip
- chromedriver-win64_133.0.6941.0.zip
- FOC电机控制，一份基于国产风机量产程序，包含龙博格电机状态观测器，SVPWM，顺逆风启动，五段式与七段式调制等源码，完全可以移植到别的MCU平台 适合电机算法研究
- chromedriver-win64_133.0.6943.2.zip
- chromedriver-win64_133.0.6942.0.zip
- chromedriver-win64_133.0.6943.0.zip
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
