Before starting these problems, update the JustLee Books database by executing the JLDB$\_$Build$\_9.$sql script, otherwise, your solutions might not work!

Note: In this module, it is assumed that students already have JLDB installed so they need to run JLDB$\_$Build$\_9.$sql to add the three tables to the existing DB$.$ If you do not have it installed, you need to run JLDB$\_$Build$\_8.$sql first and then JLDB$\_$Build$\_9.$sql$.$

Write a SQL code to join two tables using the WHERE statement. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.

Repeat Problem $1$ above but remove the WHERE statement. What happened? Why?

Write an SQL query to join three related tables using JOIN $...$ ON in the JustLee Books. Select four$-$five columns that you would like to display in the output $($do not use SELECT $*).$ Include one condition with a special comparison operator. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.

Write an SQL query to join three related tables using JOIN $...$ USING in the JustLee Books. Select four$-$five columns that you would like to display in the output $($do not use SELECT $*).$ Include one condition with a special comparison operator and a logical operator. Do not use the same set of tables as before. Explain what the query is intended to do in a complete, coherent sentence with no SQL terminology.

Write an SQL query to join two tables using JOIN...ON and JOIN...USING $($use SELECT $*).$ Did you receive the same output? Why?

Write an SQL query that lists customer$$s last name and first name if he$/$she does not have the same address and shipping street and whose orders include books that have the letter $$a$$ in the title. In addition, consider only the following authors: Peterson, Adams, and Austin. Explain why some customers' names appear multiple times.

Write an SQL query to create a list of employees and their managers. Use columns$$ aliases to clearly identify mangers and employees in the output.

Modify Problem $7$ above to list all customers along with those who referred them $($if any$).$

Write an SQL query to display a list of books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides.

Use set operators UNION, INION ALL, INTERSECT, and MINUS to combine the results of two queries. Explain what queries are intended to do in complete, coherent sentences with no SQL terminology.

CREATE TABLE warehouses

$($wh$\_$id NUMBER$(2),$

location VARCHAR$(12))$

Statement $2$

INSERT INTO warehouses

VALUES $(10,$ 'Boston'$)$

Statement $3$

INSERT INTO warehouses

VALUES $(20,$ 'Norfolk'$)$

Statement $4$

INSERT INTO warehouses

VALUES $(30,$ 'San Diego'$)$

Statement $5$

COMMIT

Statement $6$

Create Table Publisher$2$

$($ID NUMBER$(2),$

Name VarCHAR$2(23),$

Contact VARCHAR$2(15),$

Phone VARCHAR$2(12),$

CONSTRAINT publisher$2\_$pubid$\_$pk PRIMARY KEY$($id$))$

Statement $7$

INSERT INTO PUBLISHER$2$

VALUES$(1,$'PRINTING IS US$\text{'},$'TOMMIE SEYMOUR',$\text{'}000-714-8321\text{'})$

Statement $8$

INSERT INTO PUBLISHER$2$

VALUES$(2,$'PUBLISH OUR WAY','JANE TOMLIN',$\text{'}010-410-0010\text{'})$

Statement $9$

INSERT INTO PUBLISHER$2$

VALUES$(3,$'AMERICAN PUBLISHING','DAVID DAVIDSON',$\text{'}800-555-1211\text{'})$

Statement $10$

INSERT INTO PUBLISHER$2$

VALUES$(4,$'READING MATERIALS INC.$\text{'},$'RENEE SMITH',$\text{'}800-555-9743\text{'})$

Statement $11$

INSERT INTO PUBLISHER$2$

VALUES$(5,$'REED$-$N$-$RITE','SEBASTIAN JONES',$\text{'}800-555-8284\text{'})$

Statement $12$

commit

Statement $13$

Create Table Publisher$3$

$($ID NUMBER$(2),$

Name VarCHAR$2(23),$

Contact VARCHAR$2(15),$

Phone VARCHAR$2(12),$

CONSTRAINT publisher$3\_$pubid$\_$pk PRIMARY KEY$($id$))$

Statement $14$

INSERT INTO PUBLISHER$3$

VALUES$(2,$'PUBLISH OUR WAY','JANE TOMLIN',$\text{'}010-410-0010\text{'})$

Statement $15$

INSERT INTO PUBLISHER$3$

VALUES$(3,$'AMERICAN PUB','DAVID DAVIDSON',$\text{'}800-555-1211\text{'})$

Statement $16$

INSERT INTO PUBLISHER$3$

VALUES$(6,$'PRINTING HERE','SAM HUNT',$\text{'}000-714-8321\text{'})$

Statement $17$

INSERT INTO PUBLISHER$3$

VALUES$(7,$'PRINT THERE','CINDY TIKE',$\text{'}010-410-0010\text{'})$

Statement $18$

commit

Statement $19$

CREATE TABLE Employees $($

EMPNO NUMBER$(4),$

LNAME VARCHAR$2(20),$

FNAME VARCHAR$2(15),$

JOB VARCHAR$2(9),$

HIREDATE DATE,

DEPTNO NUMBER$(2)$ NOT NULL,

MTHSAL NUMBER$(7,2),$

MGR NUMBER$(4),$

CONSTRAINT employees$\_$empno$\_$PK PRIMARY KEY $($EMPNO$))$

Statement $20$

INSERT INTO employees VALUES $(7839,$'KING','BEN', 'GTECH$2\text{'},$TO$\_$DATE$(\text{'}17-$NOV$-91\text{'},\text{'}$DD$-$MON$-$YY$\text{'}),10,6000,$NULL$)$

Statement $21$

INSERT INTO employees VALUES $(8888,$'JONES','LARRY','MTech$2\text{'},$TO$\_$DATE$(\text{'}17-$NOV$-98\text{'},\text{'}$DD$-$MON$-$YY$\text{'}),10,4200,7839)$

Statement $22$

INSERT INTO employees VALUES $(7344,$'SMITH','SAM','GTech$1\text{'},$TO$\_$DATE$(\text{'}17-$NOV$-95\text{'},\text{'}$DD$-$MON$-$YY$\text{'}),20,4900,7839)$

Statement $23$

INSERT INTO employees VALUES $(7355,$'POTTS','JIM','GTech$1\text{'},$TO$\_$DATE$(\text{'}17-$NOV$-95\text{'},\text{'}$DD$-$MON$-$YY$\text{'}),20,4900,7839)$

Statement $24$

INSERT INTO employees VALUES $(8844,$'STUART','SUE','MTech$1\text{'},$TO$\_$D