Workshop

Team-Fly

---

 

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents
	Hour  5.  Manipulating Data

---

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.

---

Team-Fly

Top