Question

Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table....

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?

Homework Answers

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;

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Write a SQL statement which joins the parts table with the supplier table and lists the...
Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...
Assignment 2 Instructions You need to access to the database and write SQL queries on the...
Assignment 2 Instructions You need to access to the database and write SQL queries on the following site: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in 1. Use INNER JOIN to create a report. In this report, list each customer’s CustomerID, CustomerName, and their each OrderID and each order’s OrderDate, if the customer has ever placed an order. You should use the Customers table and the Orders table in the database to complete this query.   2. Use INNER JOIN to create a report. In this report, list...
Write a script to create the following tables with attributes as specified(SQL) Customer table with Customer’s...
Write a script to create the following tables with attributes as specified(SQL) Customer table with Customer’s id, name, address, city as varchar, customer’s date of birth as date type and zip code number where the customers id is the primary key in the table, name and date of birth are mandatory. Id has a 10-character limit, name, address and city have a 50-character limit, zip has a 5-character limit Product Table with Product id, description and finish as varchar, price...
1. We sell music  to our members. Employees recommend titles by rating them from1 to 5 with...
1. We sell music  to our members. Employees recommend titles by rating them from1 to 5 with 1 being not very good to 5 being very good What would your strategy be to write SQL code for the following query: "Show the members and the title and price for each of the products they bought." I would use a left outer join I would not use a join because the data comes from one table I would use an equality join...
EMPLOYEE Field Name EMP_ID EMP_LNAME EMP_MI EMP_FNAME EMP_SEX EMP_AGE EMP_SALARY EMP_HIREDATE DEPT_CODE Table: DEPARTMENT DEPT_CODE BIOL...
EMPLOYEE Field Name EMP_ID EMP_LNAME EMP_MI EMP_FNAME EMP_SEX EMP_AGE EMP_SALARY EMP_HIREDATE DEPT_CODE Table: DEPARTMENT DEPT_CODE BIOL CPTR HIST MATH RELB Data Type Text Text Text Text Text Number Currency Date/Time Text DEPT_TITLE Biology Computer Science History Mathematics Religion    Field Name DEPT_CODE DEPT_TITLE Data Type Text Text INSTRUCTIONS Use SQL commands to create the tables and enter the data shown above using MS Access. Write the following SQL statement given below: 1. 2. 3. 4. 5. 6. 7. 8. 9....
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1....
Class-In Assignment 3: Chapters 4&5 How to retrieve data from two or more tables Exercises 1. Write a SELECT statement that returns all columns from the Vendors table inner-joined with all columns from the Invoices table. 2. Write a SELECT statement that returns four columns: vendor_name vendor_name from the Vendors table invoice_number invoice_number from the Invoices table invoice_date invoice_date from the Invoices table balance_due invoice_total minus payment_total minus credit_total from the Invoices table The result set should have one row...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database: An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR). A research field...
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the...
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the below Relational Database. • Regarding the SQL queries: • Do not use SELECT * in any query. • Do not use any SQL features (including Nullif) that were not covered in this course. • Do not use subqueries IF joins can be used to answer a question. However, some questions may require the use of subqueries. The Movie Database Notes: TheaterNum, MovieNum, and ActorNum...
True False Select the most correct answer. Question 1 (1 point) Most databases are just in...
True False Select the most correct answer. Question 1 (1 point) Most databases are just in 1NF. Question 1 options: True False Question 2 (1 point) A subquery is always preferred over a join. Question 2 options: True False Question 3 (1 point) A database may consist only of multiple files. Question 3 options: True False Question 4 (1 point) A database may exist without a database management system. Question 4 options: True False Question 5 (1 point) The insert...
Description In this project you will practice what we have learned in class about Design, ER...
Description In this project you will practice what we have learned in class about Design, ER Diagrams, Relational Models, DDL, SQL, CRUD (Create, Update, Delete) operations, associated queries, and mock data population. The goal is to create a realistic professional database/development experience. This assignment will describe the requirements for the database as you might receive them. You will need to fill in the details as you work on it. You will find that your work may be iterative, and you...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT