Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

Workshop

The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, "Answers to Quizzes and Exercises," for answers.

Quiz

  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?

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

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

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

  5. Use the following tables:

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

     

     


  7. Is the following syntax correct for using an OUTER JOIN?

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

Exercises

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

  2. SELECT E.LAST_NAME, E.FIRST_NAME, EP.DATE_HIRE
    FROM EMPLOYEE_TBL E,
       EMPLOYEE_PAY_TBL EP;
  3. Type the following code to properly join the EMPLOYEE_TBL and EMPLOYEE_PAY_TBL tables:

  4. 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;
  5. 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.

  6. What is the average employee salary per city?

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

  • + Share This
  • 🔖 Save To Your Account