Hour 13, Joining Tables in Queries

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Appendix C.  Answers to Quizzes and Exercises


Hour 13, "Joining Tables in Queries"

Quiz Answers

1:

What type of join would you use to return records from one table, regardless of the existence of associated records in the related table?

A1:

You would use an OUTER JOIN.

2:

The join conditions are located in what part of the SQL statement?

A2:

The JOIN conditions are located in the WHERE clause.

3:

What type of join do you use to evaluate equality among rows of related tables?

A3:

You would use an EQUIJOIN.

4:

What happens if you select from two different tables but fail to join the tables?

A4:

You receive a Cartesian product by not joining the tables (this is also called a cross join ).

5:

Use the following tables:

ORDERS_TBL

 

ORD_NUM

VARCHAR(10)

NOT NULL

PRIMARY KEY

CUST_ID

VARCHAR(10)

NOT NULL

 

PROD_ID

VARCHAR(10)

NOT NULL

 

QTY

Integer(6) NOT NULL

   

ORD_DATE

DATETIME

   

PRODUCTS_TBL

 

PROD_ID

VARCHAR(10)

NOT NULL

PRIMARY KEY

PROD_DESC

VARCHAR(40)

NOT NULL

 

COST

DECIMAL(,2) NOT NULL

   
A5:

No, the syntax is not correct. The (+) operator should only follow the O.CUST_ID column in the WHERE clause. The correct syntax is

 SELECT C.CUST_ID, C.CUST_NAME, O.ORD_NUM  FROM CUSTOMER_TBL C, ORDERS_TBL O WHERE C.CUST_ID = O.CUST_ID(+); 
6:

Is the following syntax correct for using an OUTER JOIN?

 SELECT C.CUST_ID, C.CUST_NAME, O.ORD_NUM  FROM CUSTOMER_TBL C, ORDERS_TBL O WHERE C.CUST_ID(+) = O.CUST_ID(+) 

Exercise Answers

1:

Invoke MySQL, point to your learnsql database, and type the following code and study the result set (Cartesian product):

  SELECT E.LAST_NAME, E.FIRST_NAME, EP.DATE_HIRE   FROM EMPLOYEE_TBL E,   EMPLOYEE_PAY_TBL EP;  
2:

Type the following code to properly join the EMPLOYEE_TBL and EMPLOYEE_PAY_TBL tables:

  SELECT E.LAST_NAME, E.FIRST_NAME, EP.DATE_HIRE   FROM EMPLOYEE_TBL E,   EMPLOYEE_PAY_TBL EP   WHERE E.EMP_ID = EP.EMP_ID;  
3:

Write a SQL statement to return the EMP_ID, LAST_NAME, and FIRST_NAME from the EMPLOYEE_TBL and SALARY and BONUS from the EMPLOYEE_PAY_TBL.

A3:
 SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY, EP.BONUS  FROM EMPLOYEE_TBL E,      EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID = EP.EMP_ID; 
4:

What is the average employee salary per city?

A4:
 SELECT E.CITY, AVG(SALARY)  FROM EMPLOYEE_TBL E,      EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID = EP.EMP_ID; 
5:

Try writing a few queries with join operations on your own.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net