InformIT: Sams Teach Yourself SQL in 24 Hours

Table of Contents

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

What is the function of a subquery when used with a SELECT statement?
Can you update more than one column when using the UPDATE statement in conjunction with a subquery?

Are the following syntax(s) correct? If not, what is the correct syntax?

SELECT CUST_ID, CUST_NAME 
        FROM CUSTOMER_TBL
        WHERE CUST_ID =
                       (SELECT CUST_ID
                               FROM ORDERS_TBL
                               WHERE ORD_NUM = '16C17');

SELECT EMP_ID, SALARY 
       FROM EMPLOYEE_PAY_TBL
       WHERE SALARY BETWEEN '20000'
                    AND (SELECT SALARY
                         FROM EMPLOYEE_ID
                         WHERE SALARY = '40000');

UPDATE PRODUCTS_TBL 
   SET COST = 1.15
   WHERE CUST_ID =
                  (SELECT CUST_ID
                   FROM ORDERS_TBL
                   WHERE ORD_NUM = '32A132');

What would happen if the following statement were run?

DELETE FROM EMPLOYEE_TBL 
WHERE EMP_ID IN
              (SELECT EMP_ID
              FROM EMPLOYEE_PAY_TBL);

Exercises

In this exercise section, refer to the Oracle syntax of suqueries covered in this hour. Write the Oracle SQL code for the requested subqueries by hand on a sheet of paper and compare your results to ours. Use the following tables to complete the exercises:

`EMPLOYEE_TBL`
`EMP_ID`	`VARCHAR(9)`	`NOT NULL`	`PRIMARY KEY`
`LAST_NAME`	`VARCHAR(15)`	`NOT NULL`
`FIRST_NAME`	`VARCHAR(15)`	`NOT NULL`
`MIDDLE_NAME`	`VARCHAR(15)`
`ADDRESS`	`VARCHAR(30)`	`NOT NULL`
`CITY`	`VARCHAR(15)`	`NOT NULL`
`STATE`	`VARCHAR(2)`	`NOT NULL`
`ZIP`	`INTEGER(5)`	`NOT NULL`
`PHONE`	`VARCHAR(10)`
`PAGER`	`VARCHAR(10)`

`EMPLOYEE_PAY_TBL`
`EMP_ID`	`VARCHAR(9)`	`NOT NULL`	`PRIMARY KEY`
`POSITION`	`VARCHAR(15)`	`NOT NULL`
`DATE_HIRE`	`DATETIME`
`PAY_RATE`	`DECIMAL(4,2)`	`NOT NULL`
`DATE_LAST_RAISE DATETIME`
`CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID_ REFERENCES EMPLOYEE_TBL (EMP_ID)`

`CUSTOMER_TBL`
`CUST_ID`	`VARCHAR(10)`	`NOT NULL`	`PRIMARY KEY`
`CUST_NAME`	`VARCHAR(30)`	`NOT NULL`
`CUST_ADDRESS`	`VARCHAR(20)`	`NOT NULL`
`CUST_CITY`	`VARCHAR(15)`	`NOT NULL`
`CUST_STATE`	VAR`CHAR(2)`	`NOT NULL`
`CUST_ZIP`	`INTEGER(5)`	`NOT NULL`
`CUST_PHONE`	`INTEGER(10)`
`CUST_FAX`	`INTEGER(10)`

`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(6,2)`	`NOT NULL`

Using a subquery, write an SQL statement to update the CUSTOMER_TBL table, changing the customer name to DAVIDS MARKET, who has an order with order number 23E934.
Using a subquery, write a query that returns all the names of all employees who have a pay rate greater than JOHN DOE, whose employee identification number is 343559876.
Using a subquery, write a query that lists all products that cost more than the average cost of all products.

Hour 15. Combining Multiple Queries into One | Next Section

Previous Section