Oracle writing queries in SQL?

Question

Write this query in sql using select

Using the BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS, and BOOKS tables, create a query using traditional join conditions based on comparisons between primary and foreign keys that will list the customer number, first and last name, and book title. Limit your listing to only those books in the ‘FITNESS’ category.


Here are the tables:

Create table Book_customer
(Customer# NUMBER(4) CONSTRAINT PK_BOOK_CUSTOMER_CUSTOMER# PRIMARY KEY,
LastName VARCHAR2(10),
FirstName VARCHAR2(10),
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4));


Create Table Book_order
(Order# NUMBER(4) CONSTRAINT PF_BOOK_ORDER_ORDER# PRIMARY KEY,
Customer# NUMBER(4),
OrderDate DATE,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5));


Create Table Publisher
(PubID NUMBER(2) CONSTRAINT PK_PUBLISHER_PUBID PRIMARY KEY,
Name VarCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12));

Create Table Author
(AuthorID Varchar2(4) CONSTRAINT PK_AUTHOR_AUTHORID PRIMARY KEY,
Lname VARCHAR2(10),
Fname VARCHAR2(10));


Create table Books
(ISBN VARCHAR2(10) CONSTRAINT PK_BOOKS_ISBN PRIMARY KEY,
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Category VARCHAR2(12));

CREATE TABLE ORDERITEMS
(ORDER# NUMBER(4) NOT NULL,
ITEM# NUMBER(2) NOT NULL,
ISBN VARCHAR2(10),
QUANTITY NUMBER(3),
constraint pk_orderitems PRIMARY KEY (order#, item#));

Answer 1

As jon said this is pretty hard to answer without more details such as the structure of the tables! We can help you complete your homework, but you must at least do a mimimum of work ... Nevertheless, I can imagine that your schema will look somewhat like this: create table BOOKS ( book_id number primary key, book_title varchar2(30), book_category varchar2(30) ); create table BOOK_CUSTOMER ( customer_id number primary key, customer_name varchar2(30) ); create table BOOK_ORDER ( order_id number primary key, customer_id number references BOOK_CUSTOMER, order_date date ); create table ORDERITEMS ( order_id number references BOOK_ORDER, item_id number, book_id number references BOOKS, quantity number, primary key (order_id, item_id) ); Let's load it with some sample data: -- Load books insert into BOOKS (book_id, book_title, book_category) values (1, 'The Wise Investors Guide', 'FINANCE'); insert into BOOKS (book_id, book_title, book_category) values (2, 'Get Slim', 'FITNESS'); commit; -- Load customers insert into BOOK_CUSTOMER (customer_id, customer_name) values (1, 'Alice'); insert into BOOK_CUSTOMER (customer_id, customer_name) values (2, 'Bob'); commit; -- Load orders insert into BOOK_ORDER (order_id, customer_id, order_date) values (1, 1, current_date); insert into BOOK_ORDER (order_id, customer_id, order_date) values (2, 2, current_date); commit; -- Load order items insert into ORDERITEMS (order_id, item_id, book_id, quantity) values (1,1,2,2); insert into ORDERITEMS (order_id, item_id, book_id, quantity) values (1,2,1,1); insert into ORDERITEMS (order_id, item_id, book_id, quantity) values (2,1,2,3); commit; A query that provides your answer would be like this: select c.customer_id, c.customer_name, b.book_title from BOOK_CUSTOMER c, BOOK_ORDER o, ORDERITEMS i, BOOKS b where c.customer_id = o.customer_id and o.order_id = i.order_id and i.book_id = b.book_id and b.book_category = 'FITNESS'; The result will be: CUSTOMER_ID CUSTOMER_NAME BOOK_TITLE ----------- ------------------------------ ----------- 1 Alice Get Slim 2 Bob Get Slim 2 rows selected. I am not sure what you (or your teacher) means by "traditional join". Maybe it is the above technique ? Or maybe it is the ISO syntax ? Here is he query using the ISO syntax: select c.customer_id, c.customer_name, b.book_title from BOOK_CUSTOMER c join BOOK_ORDER o on (c.customer_id = o.customer_id) join ORDERITEMS i on (o.order_id = i.order_id) join BOOKS b on (i.book_id = b.book_id) where b.book_category = 'FITNESS'; Or even the simpler "natural join" syntax: select customer_id, customer_name, book_title from BOOK_CUSTOMER natural join BOOK_ORDER natural join ORDERITEMS natural join BOOKS where book_category = 'FITNESS'; The result will be the same in all cases.

Answer 2

Someone else asked the very same question the other day. I guess you guys all attend the same class and get the same howework ? At least you thought about posting your table structures. Still you may want to at least check existing questions before asking again.



The answer is the same as to the other question:



select c.customer#, c.lastname, c.firstname, b.title

from BOOK_CUSTOMER c, BOOK_ORDER o, ORDERITEMS i, BOOKS b

where c.customer# = o.customer#

and o.order# = i.order#

and i.isbn = b.isbn

and b.category = 'FITNESS';

Answer 3

The other answer will give you duplicate rows if the customer orders the same books multiple times. However using a distinct will remove the duplicate rows.



select distinct c.customer#, c.lastname, c.firstname, b.title

from BOOK_CUSTOMER c, BOOK_ORDER o, ORDERITEMS i, BOOKS b

where c.customer# = o.customer#

and o.order# = i.order#

and i.isbn = b.isbn

and b.category = 'FITNESS';