Question

Please help me on the SQL queries, thank you so much.

1. Write a query to display the title and publisher as well as the publisher contact for each book using JOIN...USING clause.
2. Write a query to show the first and last names of customers who have ordered cooking books. Use the WHERE clause to join the tables.
3. Write a query to show the title, cost and ISBN of each book in the books table. If the book has been ordered, show the order number and order date. Use the OUTER JOIN keywords to join the tables. Sort the titles in alphabetical order.
4. Write a query to show the gift a customer will receive if he or she orders Database Implementation.   Use either the WHERE clause or a JOIN keyword to join the tables.
5. Write a query to show the profit of each book sold to Leila Smith. Calculate the profit by subtracting the cost from the paideach column in the orderitems table. Use the WHERE clause to the join the tables.

ORDERS CUSTOMERS |Order# Customer# Orderdate Shipdate meShipstreet Shipcity Shipstate Shipzip Shipcost Customer# Lastname Firstname Email Address Ci Sta Zip Referred BOOKAUTHOR AUTHOR BOOKS ISBN AuthorlD AuthonD ISBN ORDERITEMSte Pubdate Lname Region Fname Order# PubID Cost l tema ISBN Quantity Paideach Discount Category PublD Name PROMOTION | Contact Phone Gift Minretail

SCRIPT:

CREATE TABLE warehouses
(wh_id NUMBER(2),
   location VARCHAR(12) );
INSERT INTO warehouses
VALUES (10, 'Boston');
INSERT INTO warehouses
VALUES (20, 'Norfolk');
INSERT INTO warehouses
VALUES (30, 'San Diego');
COMMIT;
Create Table Publisher2
(ID NUMBER(2),
Name VarCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12),
CONSTRAINT publisher2_pubid_pk PRIMARY KEY(id));
INSERT INTO PUBLISHER2
VALUES(1,'PRINTING IS US','TOMMIE SEYMOUR','000-714-8321');
INSERT INTO PUBLISHER2
VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010');
INSERT INTO PUBLISHER2
VALUES(3,'AMERICAN PUBLISHING','DAVID DAVIDSON','800-555-1211');
INSERT INTO PUBLISHER2
VALUES(4,'READING MATERIALS INC.','RENEE SMITH','800-555-9743');
INSERT INTO PUBLISHER2
VALUES(5,'REED-N-RITE','SEBASTIAN JONES','800-555-8284');
commit;
Create Table Publisher3
(ID NUMBER(2),
Name VarCHAR2(23),
Contact VARCHAR2(15),
Phone VARCHAR2(12),
CONSTRAINT publisher3_pubid_pk PRIMARY KEY(id));
INSERT INTO PUBLISHER3
VALUES(2,'PUBLISH OUR WAY','JANE TOMLIN','010-410-0010');
INSERT INTO PUBLISHER3
VALUES(3,'AMERICAN PUB','DAVID DAVIDSON','800-555-1211');
INSERT INTO PUBLISHER3
VALUES(6,'PRINTING HERE','SAM HUNT','000-714-8321');
INSERT INTO PUBLISHER3
VALUES(7,'PRINT THERE','CINDY TIKE','010-410-0010');
commit;
CREATE TABLE Employees (
EMPNO               NUMBER(4),
LNAME               VARCHAR2(20),
FNAME               VARCHAR2(15),
JOB                 VARCHAR2(9),
HIREDATE            DATE,
DEPTNO              NUMBER(2) NOT NULL,
MTHSAL                 NUMBER(7,2),
MGR                 NUMBER(4),
CONSTRAINT employees_empno_PK PRIMARY KEY (EMPNO));
INSERT INTO employees VALUES (7839,'KING','BEN', 'GTECH2','17-NOV-91',10,6000,NULL);
INSERT INTO employees VALUES (8888,'JONES','LARRY','MTech2','17-NOV-98',10,4200,7839);
INSERT INTO employees VALUES (7344,'SMITH','SAM','GTech1','17-NOV-95',20,4900,7839);
INSERT INTO employees VALUES (7355,'POTTS','JIM','GTech1','17-NOV-95',20,4900,7839);
INSERT INTO employees VALUES (8844,'STUART','SUE','MTech1','17-NOV-98',10,3700,8888);
COMMIT;

Answer 1

Write a query to display the title and publisher as well as the publisher contact for each book using JOIN...USING clause.

Query: SELECT Title, Name, Contact FROM BOOKS b JOIN PUBLISHER p USING PubID;

Write a query to show the first and last names of customers who have ordered cooking books. Use the WHERE clause to join the tables.

Query: SELECT FirstName, LastName FROM CUSTOMERS c, ORDERS o, ORDERITEMS oi, BOOKS b WHERE c.Customer# = o.Customer# and o.Order# = oi.Order# and oi.ISBN = b.ISBN and b.Title = 'Cooking';

Write a query to show the title, cost and ISBN of each book in the books table. If the book has been ordered, show the order number and order date. Use the OUTER JOIN keywords to join the tables. Sort the titles in alphabetical order.

Query: SELECT b.Title, b.Cost, b.ISBN, o.Order#, o.OrderDate FROM BOOKS b OUTER JOIN ORDERITEMS oi ON oi.Order# and oi.ISBN = b.ISBN OUTER JOIN  ORDERS o ON o.Order# = oi.Order#;

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 3 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.