1. The minimum and maximum cost of all books in the database published by Pearso

Question

1.    The minimum and maximum cost of all books in the database published by Pearson Publishing

2.   The UNION of all titles published by "Wiley" and all titles published by "Pearson".

3. Create a view called vbook which will list the title, author(last name) of all books priced at or below $50.00. Write a query that uses the vbook view to return all titles with the word "Java" in it.

4. The title and the number of columns in the title of all the books costing more than $50.00
(MUST USE THE IN OPERATOR)

Books ISBN varchar2 key) title varchar2 editionNumber number(10) publisherId varchar2 price number (6.2) Publishers publisher varchar2 (key) publisher Name varchar2 Author Book ISBN varchar2 authorid varchar2 Key is ISBN and authorid. Authors authorid varchar2 (key) first name varchar2

Explanation / Answer

drop table books;
drop table publishers;
drop table author_book;
drop table authors;

CREATE TABLE books (
isbn varchar2(100),
title varchar2(100),
editionNumber INT,
publisherId varchar2(100),
price int
);

CREATE TABLE publishers (
publisherId varchar2(100),
publisherName varchar2(100)
);

CREATE TABLE author_book (
isbn varchar2(100),
authorId varchar2(100)
);

CREATE TABLE authors (
authorId varchar2(100),
firstname varchar2(100),
lastname varchar2(100)
);

insert into books values('I1','Java',0, 'P1', 40);
insert into books values('I2','T2',0, 'P2', 300);
insert into books values('I3','T3',0, 'P1', 200);

insert into publishers values('P1','Pu1');
insert into publishers values('P2','Pu2');

insert into author_book values('I1','A1');
insert into author_book values('I2','A1');
insert into author_book values('I3','A2');

insert into authors values('A1','f1', 'f2');
insert into authors values('A2','f11', 'f22');

Queries:

-- Question 1
select p.publisherName, min(b.price) as min_price, max(b.price) as max_price
from publishers p, books b
where
p.publisherId = b.publisherId
and p.publisherName='Pu1'
group by p.publisherName;

-- Question 2
select * from publishers p, books b where p.publisherId = b.publisherId and p.publisherName='Pu1'
union
select * from publishers p, books b where p.publisherId = b.publisherId and p.publisherName='Pu2';

-- Question 3
drop view vbook;
CREATE VIEW vbook AS
select b.title, a.lastname
from author_book ab, authors a, books b
where
b.price <= 50 and ab.isbn = b.isbn and ab.authorId=a.authorId;

select * from vbook where title like '%Java%';

-- Question 4
-- Although, i haven't got your question completely
-- by what do you mean by number of columns
select title, price from books where isbn in (
select isbn from books where price > 50
);

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.