| 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 | | |