-- 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
