Using SQL Developer to execute the following queries. Copy and paste the query c
ID: 3755648 • Letter: U
Question
Using SQL Developer to execute the following queries. Copy and paste the query code into the submission box. This assignment should be completed by September 21.
Write a query to display the title and publisher for each book using JOIN...USING clause.
Write a query to show the first and last names of customers who have ordered computer books. Use the WHERE clause to join the tables.
Write a query to show the title 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.
Write a query to show the gift a customer will receive if he or she orders Holy Grail of Oracle. Use either the WHERE clause or a JOIN keyword to join the tables.
Write a query to show the profit of each book sold to Jake Lucas. Calculate the profit by subtracting the cost from the paideach column in the orderitems table. Use one of the JOIN clauses to the join the tables.
Script for assignment:
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;
Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT TITLE, NAME, LNAME, FNAME
FROM PUBLISHER JOIN BOOKS USING(PUBID) JOIN BOOKAUTHOR USING(ISBN) JOIN AUTHOR USING(AUTHORID);
-- 2)
SELECT LASTNAME, FIRSTNAME
FROM CUSTOMERS C, ORDERS O, BOOKS B, ORDERITEMS OI
WHERE C.CUSTOMER# = O.CUSTOMER# AND O.ORDER# = OI.ORDER# AND OI.ISBN = B.ISBN;
-- 3)
SELECT TITLE, ISBN, ORDER#, ORDERDATE
FROM BOOKS B OUTER JOIN ORDERITEMS OI ON B.ISBN = OI.ISBN OUTER JOIN ORDERS O ON O.ORDER# = OI.ORDER#
ORDER BY TITLE;
-- 4)
SELECT LASTNAME, FIRSTNAME, GIFT
FROM BOOK B, PROMOTION P, ORDER O, ORDERITEMS OI
WHERE O.ORDER# = OI.ORDER# AND B.ISBN = OI.ISBN AND B.TITLE = 'Holy Grail of Oracle'
HAVING SUM(QUANTITY*PAIDEACH)>MINRETAIL AND SUM(QUANTITY*PAIDEACH)<MAXRETAIL;
-- 5)
SELECT ((COST-PAIDEACH)*QUANTITY) Profit
FROM CUSTOMERS C JOIN ORDERS O ON C.CUSTOMER# = O.CUSTOMER# JOIN ORDERITEMS OI ON O.ORDER# = OI.ORDER# JOIN BOOKS B ON B.ISBN = OI.ISBN
WHERE LASTNAME='Jake' AND FIRSTNAME = 'Lukas';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.