Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

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. Use the EMPLOYEE_TBL with the following structure:

   Column	Data Type	(NOT)NULL
   LAST_NAME	VARCHAR2(20)	NOT NULL
   FIRST_NAME	VARCHAR2(20)	NOT NULL
   SSN	CHAR(9)	NOT NULL
   PHONE	NUMBER(10)	NULL

   LAST_NAME	FIRST_NAME	SSN	PHONE
   SMITH	JOHN	312456788	3174549923
   ROBERTS	LISA	232118857	3175452321
   SMITH	SUE	443221989	3178398712
   PIERCE	BILLY	310239856	3176763990

   What would happen if the following statements were run?

   1. insert into employee_tbl 
      ('JACKSON', 'STEVE', '313546078', '3178523443');
      

   2. insert into employee_tbl values 
      ('JACKSON', 'STEVE', '313546078', '3178523443');
      

   3. insert into employee_tbl values 
      ('MILLER', 'DANIEL', '230980012', NULL);
      

   4. insert into employee_tbl values 
      ('TAYLOR', NULL, '445761212', '3179221331');
      

   5. delete from employee_tbl; 
      

   6. delete from employee_tbl 
      where last_name = 'SMITH';
      

   7. delete from employee_tbl 
      where last_name = 'SMITH'
      and first_name = 'JOHN';
      

   8. update employee_tbl 
      set last_name = 'CONRAD';
      

   9. update employee_tbl 
      set last_name = 'CONRAD'
      where last_name = 'SMITH';
      

   10. update employee_tbl 
       set last_name = 'CONRAD',
       first_name = 'LARRY';
       

   11. update employee_tbl 
       set last_name = 'CONRAD'
       first_name = 'LARRY'
       where ssn = '313546078';
       

Exercises

1. Go to Appendix E of this book, "INSERT Statements for Data in Book Examples." Invoke MySQL as you have done in previous exercises.

   Now you need to insert the data into the tables that you created in Hour 3. There are two ways to do this. The first method is to type each INSERT statement that is found in Appendix E at the mysql> command prompt. This method is recommended if you have the time to do so. The second method is to download the file tysql24_data.sql from the Web site for this book and execute the file from the mysql> command prompt. The syntax to execute tysql24_data.sql at the command prompt is as follows:

   source tysql24_data.sql 
   

   If you downloaded the file tysql24_data.sql to the mysql folder on your computer, the syntax to execute this file would be as follows:

   source c:\mysql\tysql24_data.sql 
   

   Once you have executed the file tysql24_data.sql, your tables will be populated with data and you can proceed with the exercises in the rest of this book. If you executed the file tysql24_data.sql, then you do not have to manually type the INSERT statements at the mysql> command prompt.

2. Use the PRODUCTS_TBL for the next exercise.

   1. Add the following products to the product table:

      PROD_ID	PROD_DESC	COST
      301	FIREMAN COSTUME	24.99
      302	POLICEMAN COSTUME	24.99
      303	KIDDIE GRAB BAG	4.99

      Write DML to accomplish the following:

   2. Correct the cost of the two costumes added. The cost should be the same as the witch's costume.
   3. Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.