Homework SourseI am only requiring the SQL Queries 1 Identify two related t
I am only requiring the SQL Queries.
1. Identify two related tables in the Just Lee Books database. Identify the common field between the two tables. Decide which columns you would like to display in the output. Write a SQL code to join identified tables using the WHERE statement. Make sure to include qualifiers for columns that appear in both tables. Since only two tables are joined, make sure to include one join condition in the WHERE statement. Explain what the query is intended to do.
2. Repeat problem 1 but remove the WHERE statement. What happened? Why?
3. Repeat problem 1 using the JOIN … USING keywords.
4. Repeat problem 1 using the JOIN … ON keywords.
5. Repeat problem 1 but add a condition in the WHERE statement. Use logical operators to combine multiple conditions. Explain what the query is intended to do.
1. Repeat problem 1 but add two more conditions in the WHERE statement. Use logical operators to combine multiple conditions. Explain what the query is intended to do.
2. Identify three related tables in the JustLee Books database. Identify common fields between the tables. Decide which columns you would like to display in the output. Write a SQL code to join identified tables using the WHERE statement. Make sure to include qualifiers for columns that appear in multiple tables. Since three tables are joined, make sure to include two join conditions in the WHERE statement. Explain what the query is intended to do.
3. Repeat problem 7 using the JOIN … USING keywords and add two conditions. Explain what the query is intended to do.
4. Repeat problem 7 using the JOIN … ON keywords and add two conditions. Explain what the query is intended to do.
5. Use set operators UNION, INION ALL, INTERSECT, and MINUS to combine the results of two queries. Make sure the column list in both queries is consistent. Explain the result.
6. Identify a table in the JustLee Books database where a self-join can be used and write a corresponding SQL query. Explain what the query is intended to do.
The database in question is below
CREATE TABLE Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Discount NUMBER (4,2),
Category VARCHAR2(12),
CONSTRAINT books_isbn_pk PRIMARY KEY(isbn),
CONSTRAINT books_pubid_fk FOREIGN KEY (pubid)
REFERENCES publisher (pubid));
INSERT INTO BOOKS
VALUES (\'1059831198\',\'BODYBUILD IN 10 MINUTES A DAY\',TO_DATE(\'21-JAN-05\',\'DD-MON-YY\'),4,18.75,30.95, NULL, \'FITNESS\');
INSERT INTO BOOKS
VALUES (\'0401140733\',\'REVENGE OF MICKEY\',TO_DATE(\'14-DEC-05\',\'DD-MON-YY\'),1,14.20,22.00, NULL, \'FAMILY LIFE\');
INSERT INTO BOOKS
VALUES (\'4981341710\',\'BUILDING A CAR WITH TOOTHPICKS\',TO_DATE(\'18-MAR-06\',\'DD-MON-YY\'),2,37.80,59.95, 3.00, \'CHILDREN\');
INSERT INTO BOOKS
VALUES (\'8843172113\',\'DATABASE IMPLEMENTATION\',TO_DATE(\'04-JUN-03\',\'DD-MON-YY\'),3,31.40,55.95, NULL, \'COMPUTER\');
INSERT INTO BOOKS
VALUES (\'3437212490\',\'COOKING WITH MUSHROOMS\',TO_DATE(\'28-FEB-04\',\'DD-MON-YY\'),4,12.50,19.95, NULL, \'COOKING\');
INSERT INTO BOOKS
VALUES (\'3957136468\',\'HOLY GRAIL OF ORACLE\',TO_DATE(\'31-DEC-05\',\'DD-MON-YY\'),3,47.25,75.95, 3.80, \'COMPUTER\');
INSERT INTO BOOKS
VALUES (\'1915762492\',\'HANDCRANKED COMPUTERS\',TO_DATE(\'21-JAN-05\',\'DD-MON-YY\'),3,21.80,25.00, NULL, \'COMPUTER\');
INSERT INTO BOOKS
VALUES (\'9959789321\',\'E-BUSINESS THE EASY WAY\',TO_DATE(\'01-MAR-06\',\'DD-MON-YY\'),2,37.90,54.50, NULL, \'COMPUTER\');
INSERT INTO BOOKS
VALUES (\'2491748320\',\'PAINLESS CHILD-REARING\',TO_DATE(\'17-JUL-04\',\'DD-MON-YY\'),5,48.00,89.95, 4.50, \'FAMILY LIFE\');
INSERT INTO BOOKS
VALUES (\'0299282519\',\'THE WOK WAY TO COOK\',TO_DATE(\'11-SEP-04\',\'DD-MON-YY\'),4,19.00,28.75, NULL, \'COOKING\');
INSERT INTO BOOKS
VALUES (\'8117949391\',\'BIG BEAR AND LITTLE DOVE\',TO_DATE(\'08-NOV-05\',\'DD-MON-YY\'),5,5.32,8.95, NULL, \'CHILDREN\');
INSERT INTO BOOKS
VALUES (\'0132149871\',\'HOW TO GET FASTER PIZZA\',TO_DATE(\'11-NOV-06\',\'DD-MON-YY\'),4,17.85,29.95, 1.50, \'SELF HELP\');
INSERT INTO BOOKS
VALUES (\'9247381001\',\'HOW TO MANAGE THE MANAGER\',TO_DATE(\'09-MAY-03\',\'DD-MON-YY\'),1,15.40,31.95, NULL, \'BUSINESS\');
INSERT INTO BOOKS
VALUES (\'2147428890\',\'SHORTEST POEMS\',TO_DATE(\'01-MAY-05\',\'DD-MON-YY\'),5,21.85,39.95, NULL, \'LITERATURE\');
CREATE TABLE ORDERITEMS
( Order# NUMBER(4),
Item# NUMBER(2),
ISBN VARCHAR2(10),
Quantity NUMBER(3) NOT NULL,
PaidEach NUMBER(5,2) NOT NULL,
CONSTRAINT orderitems_pk PRIMARY KEY (order#, item#),
CONSTRAINT orderitems_order#_fk FOREIGN KEY (order#)
REFERENCES orders (order#) ,
CONSTRAINT orderitems_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn) ,
CONSTRAINT oderitems_quantity_ck CHECK (quantity > 0) );
INSERT INTO ORDERITEMS
VALUES (1000,1,\'3437212490\',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1001,1,\'9247381001\',1,31.95);
INSERT INTO ORDERITEMS
VALUES (1001,2,\'2491748320\',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1002,1,\'8843172113\',2,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,1,\'8843172113\',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,2,\'1059831198\',1,30.95);
INSERT INTO ORDERITEMS
VALUES (1003,3,\'3437212490\',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1004,1,\'2491748320\',2,85.45);
INSERT INTO ORDERITEMS
VALUES (1005,1,\'2147428890\',1,39.95);
INSERT INTO ORDERITEMS
VALUES (1006,1,\'9959789321\',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,1,\'3957136468\',3,72.15);
INSERT INTO ORDERITEMS
VALUES (1007,2,\'9959789321\',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,3,\'8117949391\',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1007,4,\'8843172113\',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1008,1,\'3437212490\',2,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,1,\'3437212490\',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,2,\'0401140733\',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1010,1,\'8843172113\',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1011,1,\'2491748320\',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,1,\'8117949391\',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1012,2,\'1915762492\',2,25.00);
INSERT INTO ORDERITEMS
VALUES (1012,3,\'2491748320\',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,4,\'0401140733\',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1013,1,\'8843172113\',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1014,1,\'0401140733\',2,22.00);
INSERT INTO ORDERITEMS
VALUES (1015,1,\'3437212490\',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1016,1,\'2491748320\',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1017,1,\'8117949391\',2,8.95);
INSERT INTO ORDERITEMS
VALUES (1018,1,\'3437212490\',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1018,2,\'8843172113\',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1019,1,\'0401140733\',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1020,1,\'3437212490\',1,19.95);
CREATE TABLE BOOKAUTHOR
(ISBN VARCHAR2(10),
AuthorID VARCHAR2(4),
CONSTRAINT bookauthor_pk PRIMARY KEY (isbn, authorid),
CONSTRAINT bookauthor_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn),
CONSTRAINT bookauthor_authorid_fk FOREIGN KEY (authorid)
REFERENCES author (authorid));
INSERT INTO BOOKAUTHOR
VALUES (\'1059831198\',\'S100\');
INSERT INTO BOOKAUTHOR
VALUES (\'1059831198\',\'P100\');
INSERT INTO BOOKAUTHOR
VALUES (\'0401140733\',\'J100\');
INSERT INTO BOOKAUTHOR
VALUES (\'4981341710\',\'K100\');
INSERT INTO BOOKAUTHOR
VALUES (\'8843172113\',\'P105\');
INSERT INTO BOOKAUTHOR
VALUES (\'8843172113\',\'A100\');
INSERT INTO BOOKAUTHOR
VALUES (\'8843172113\',\'A105\');
INSERT INTO BOOKAUTHOR
VALUES (\'3437212490\',\'B100\');
INSERT INTO BOOKAUTHOR
VALUES (\'3957136468\',\'A100\');
INSERT INTO BOOKAUTHOR
VALUES (\'1915762492\',\'W100\');
INSERT INTO BOOKAUTHOR
VALUES (\'1915762492\',\'W105\');
INSERT INTO BOOKAUTHOR
VALUES (\'9959789321\',\'J100\');
INSERT INTO BOOKAUTHOR
VALUES (\'2491748320\',\'R100\');
INSERT INTO BOOKAUTHOR
VALUES (\'2491748320\',\'F100\');
INSERT INTO BOOKAUTHOR
VALUES (\'2491748320\',\'B100\');
INSERT INTO BOOKAUTHOR
VALUES (\'0299282519\',\'S100\');
INSERT INTO BOOKAUTHOR
VALUES (\'8117949391\',\'R100\');
INSERT INTO BOOKAUTHOR
VALUES (\'0132149871\',\'S100\');
INSERT INTO BOOKAUTHOR
VALUES (\'9247381001\',\'W100\');
INSERT INTO BOOKAUTHOR
VALUES (\'2147428890\',\'W105\');
CREATE TABLE promotion
(Gift varchar2(15),
Minretail number(5,2),
Maxretail number(5,2));
INSERT into promotion
VALUES (\'BOOKMARKER\', 0, 12);
INSERT into promotion
VALUES (\'BOOK LABELS\', 12.01, 25);
INSERT into promotion
VALUES (\'BOOK COVER\', 25.01, 56);
INSERT into promotion
VALUES (\'FREE SHIPPING\', 56.01, 999.99);
COMMIT;
CREATE TABLE acctmanager
(amid VARCHAR2(4) PRIMARY KEY,
amfirst VARCHAR2(12) NOT NULL,
amlast VARCHAR2(12) NOT NULL,
amedate DATE DEFAULT SYSDATE,
region CHAR(2) NOT NULL);
CREATE TABLE acctmanager2
(amid CHAR(4),
amfirst VARCHAR2(12) NOT NULL,
amlast VARCHAR2(12) NOT NULL,
amedate DATE DEFAULT SYSDATE,
region CHAR(2),
CONSTRAINT acctmanager2_amid_pk PRIMARY KEY (amid),
CONSTRAINT acctmanager2_region_ck
CHECK (region IN (\'N\', \'NW\', \'NE\', \'S\', \'SE\', \'SW\', \'W\', \'E\')));
Solution
The two related table are:
i) Books Table
ii) BOOKAUTHOR Table
The common field between the two tables are:
i) ISBN attribute
The columns that i would like to display are:
Title, ISBN, AuthorID, PubID, PubDate, Cost, Retail, Discount, Category
Sql Code to join tables using where cluase
select t1.Title, t1.ISBN, t2.AuthorID, t1.PubID, t1.PubDate, t1.Cost, t1.Retail, t1.Discount, t1.Category from Books t1 INNER JOIN BOOKAUTHOR t2 ON t1.ISBN = t2.ISBN where t1.ISBN = 0401140733
The above query will dispaly the attributes of table \"Books\" and of table \"BOOKAUTHOR\" for book ISBN 0401140733
Repeat problem 1 but remove the WHERE statement
After removing the where condition we will have following join query
select t1.Title, t1.ISBN, t2.AuthorID, t1.PubID, t1.PubDate, t1.Cost, t1.Retail, t1.Discount, t1.Category from Books t1 INNER JOIN BOOKAUTHOR t2 ON t1.ISBN = t2.ISBN
The above query will display all the mapping data of table \"Books\" and of Table \"BOOKAUTHOR\"
