Hour 14, "Using Subqueries to Define Unknown Data"

Quiz Answers

1: What is the function of a subquery when used with a SELECT statement?
A1: The main function of a subquery when used with a SELECT statement is to return data that the main query can use to resolve the query.
2: Can you update more than one column when using the UPDATE statement in conjunction with a subquery?
A2: Yes, you can update more than one column using the same UPDATE and subquery statement.
3: Are the following syntaxes correct? If not, what is the correct syntax?
  1. SELECT CUST_ID, CUST_NAME
        FROM CUSTOMER_TBL
        WHERE CUST_ID =
                       (SELECT CUST_ID
                               FROM ORDERS_TBL
                               WHERE ORD_NUM = '16C17'),
										
  2. SELECT EMP_ID, SALARY
       FROM EMPLOYEE_PAY_TBL
       WHERE SALARY BETWEEN '20000'
                    AND (SELECT SALARY
                         FROM EMPLOYEE_ID
                         WHERE SALARY = '40000'),
										
  3. UPDATE PRODUCTS_TBL
   SET COST = 1.15
   WHERE CUST_ID =
                  (SELECT CUST_ID
                   FROM ORDERS_TBL
                   WHERE ORD_NUM = '32A132'),
A3:

  1. Yes, this syntax is correct.

  2. No. The BETWEEN operator cannot be used in this format.

  3. Yes, this syntax is correct.

4: What would happen if the following statement were run?
DELETE FROM EMPLOYEE_TBL
WHERE EMP_ID IN
              (SELECT EMP_ID
              FROM EMPLOYEE_PAY_TBL);

A4: All rows that were retrieved from the EMPLOYEE_PAY_TBL would be used by the DELETE to remove them from the EMPLOYEE_TBL. A WHERE clause in the subquery is highly advised.

Exercise Answers

Use the following tables:

EMPLOYEE_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY
LAST_NAMEVARCHAR2(15)NOT NULL 
FIRST_NAMEVARCHAR2(15)NOT NULL 
MIDDLE_NAMEVARCHAR2(15)  
ADDRESSVARCHAR2(30)NOT NULL 
CITYVARCHAR2(15)NOT NULL 
STATECHAR(2)NOT NULL 
ZIPNUMBER(5)NOT NULL 
PHONECHAR(10)  
PAGERCHAR(10)  

EMPLOYEE_PAY_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY
POSITIONVARCHAR2(15)NOT NULL 
DATE_HIREDATE  
PAY_RATENYMBER(4,2)NOT NULL 
DATE_LAST_RAISEDATE  
SALARYNUMBER (6,2)  
BONUSNUMBER (4,2)  

CONSTRAINT EMP_FK FOREIGN KEY  (EMP_ID_ REFERENCES
  EMPLOYEE_TBL (EMP_ID)

CUSTOMER_TBL   
CUST_IDVARCHAR2(10)NOT NULLPRIMARY KEY
CUST_NAMEVARCHAR2(30)NOT NULL 
CUST_ADDRESSVARCHAR2(20)NOT NULL 
CUST_CITYVARCHAR2(15)NOT NULL 
CUST_STATECHAR(2)NOT NULL 
CUST_ZIPNUMBER(5)NOT NULL 
CUST_PHONENUMBER(10)  
CUST_FAXNUMBER(10)  

ORDERS_TBL   
ORD_NUMVARCHAR2(10)NOT NULLPRIMARY KEY
CUST_IDVARCHAR2(10)NOT NULL 
PROD_IDVARCHAR2(10)NOT NULL 
QTYNUMBER(6)NOT NULL 
ORD_DATEDATE  

PRODUCTS_TBL   
PROD_IDVARCHAR2(10)NOT NULLPRIMARY KEY
PROD_DESCVARCHAR2(40)NOT NULL 
COSTNUMBER(6,2)NOT NULL 

1: 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.
A1: The immediate supervisor should instigate the request process by completing a user ID request form, which contains all the information necessary to add the user to the database. The form should then be forwarded to the security officer. The user request is then routed to either the database administrator or the individual designated to assist the database administrator with security, so that the user can be added to the database. This is a general process that should be followed and modified accordingly for each company.
2: 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.
A2: 
SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME
   FROM EMPLOYEE_TBL E,
        EMPLOYEE_PAY_TBL P
   WHERE P.PAY_RATE > (SELECT PAY_RATE
                       FROM EMPLOYEE_PAY_TBL
                       WHERE EMP_ID = '343559876'),

3: Using a subquery, write a query that lists all products that cost more than the average cost of all products.
A3: 
SELECT PROD_DESC
   FROM PRODUCTS_TBL
   WHERE COST > (SELECT AVG(COST)
                 FROM PRODUCTS_TBL);

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset