Question

Lab 5 Queries with Multiple Tables

In this lab, we do queries more than one table. SQL provides two different techniques for querying data from multiple tables:

• The SQL subquery

• The SQL join

As you will learn, although both work with multiple tables, they are used for slightly different purposes.

We used WMCRM database which is what we created in Lab 4. Here is the summary of the database schema (where schema is used in its meaning of a summary of the database structure):

VEHICLE (InventoryID, Model, VIN)

SALESPERSON (NickName, LastName, FirstName, HireDate, WageRate, CommissionRate, OfficePhone, EmailAddress, InventoryID)

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress, NickName)

PHONE_NUMBER (CustomerID, PhoneNumber, PhoneType)

CONTACT(ContactID, CustomerID,ContactDate,ContactType,Remarks)

Where

InventoryID in SALESPERSON must exist in InventoryID in VEHICLE

NickName in CUSTOMER must exist in NickName in SALESPERSON

CustomerID in PHONE_NUMBER must exist in CustomerID in CUSTOMER

CustomerID in CONTACT must exist in CustomerID in CUSTOMER

Querying Multiple Tables with Subqueries

Question 1: Find first name and last name of the customer who contacted on “2018-07-19.”

Querying Multiple Tables with Joins

If we need to display data from two or more tables, we need to use an SQL join operation. The basic idea of a join is to form a new relation by connecting the contents of two or more other relations. In an SQL join operation, the SQL JOIN operator is used to combine two or more tables by concatenating (sticking together) the rows of one table with the rows of another table. If the JOIN operator is actually used as part of the SQL statement syntax, we refer to the join operation as an explicit join. If the JOIN operator itself does not appear in the SQL statement, we refer to the join operation as an implicit join.

For example, we can find the customer information and phone number from the tables of CUSTOMER and PHONE_NUMBER. You can use the following script (Script 4):

select *

from customer, phone_number

where customer.CustomerID = phone_number.CustomerID;

Please pay attention the above statement is different from the following one (Script 5):

select *

from customer, phone_number

This statement is known as a CROSS JOIN, and the result is what is mathematically known as the Cartesian product of the rows in the tables, which means that this statement will just stick every row of one table together with every row of the second table.

Question 2: Find the customer name and customer phone number from the tables of CUSTOMER and PHONE_NUMBER.

Question 3: Comparing the results of Script 4 and Script 5, how many records in Script 4 and how many records in Script 5? What is the difference between these two scripts? Why they are different?

Question 4: Find the distinct customer name and customer phone number from the tables of CUSTOMER and PHONE_NUMBER and sort the result by customer name.

The SQL JOIN ON Syntax

The SQL join you write in the previous section used the original, but older, form of the SQL join syntax. While it can still be used, today most SQL users prefer to use the SQL JOIN ON syntax. Therefore, we can rewrite the Script 4 into the following Script 6 with the SQL JOIN ON syntax:

select *

from customer join phone_number

on customer.CustomerID = phone_number.CustomerID;

Question 5: re-write Question 2 using JOIN ON syntax

Question 6: re-write Question 4 using JOIN ON syntax

Comparing Subqueries and Joins

Subqueries and joins both process multiple tables, but they differ slightly. A subquery can be used only to retrieve data from the top table, whereas a join can be used to obtain data from any number of tables. Thus, a join can do everything a subquery can do and more.

Question 7: Can you rewrite Question 1 using Joins syntax? If yes, please provide your code.

Question 8: Can you rewrite Question 2 using subqueries? If yes, please provide your code.

Inner Joins and Outer Joins

Though both inner and outer joins include rows from both tables when the match condition is successful, they differ in how they handle a false match condition. Inner joins don’t include nonmatching rows; whereas, outer joins do include them.

Let’s first insert one new record in Customer table using the following script (Script 7):

INSERT INTO CUSTOMER VALUES(NULL, 'Qi', 'LI', '234 New Paltz St.', 'New Paltz', NY', '12561', '[email protected]', 'Tina');

Now try Script 6.

Try the following Script 7 using INNER JOIN:

select *

from customer inner join phone_number

on customer.CustomerID = phone_number.CustomerID;

Try the following Script 8 using LEFT OUTER JOIN:

select *

from customer left outer join phone_number

on customer.CustomerID = phone_number.CustomerID;

Question 9: What is the output of Script 7? Is it different from Script 6? Why?

Question 10: Comparing Script 7 and Script 8, is the output different? Why?

Answer 1

1.

Select FirstName, LastName from Customer where CustomerId in (select customerId from contact where contacts are='2018-07-19';

2. Select FirstName, LastName, PhoneNumber from Customer c inner join phone-number p on

c.CustomerId= p.CustomerId;

3.

Script 4 will give records which are common in customer table and phone_number table.

Script 5 will give Cartesian product of the two table.

4. Select distinct (customerId), FirstName, LastName, distinct(phoneNumber) from Customer inner join phone_number

On customer.CustomerId=phone_number.CustomerId sort by FirstName asc;