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

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?

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

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

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

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

```
delete from employee_tbl; 
```

delete from employee_tbl 
where last_name = 'SMITH';

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

update employee_tbl 
set last_name = 'CONRAD';

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

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

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

Exercises

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.

Use the PRODUCTS_TBL for the next exercise.

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:

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

Hour 6. Managing Database Transactions | Next Section

Previous Section