Hour 5, Manipulating Data

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Appendix C.  Answers to Quizzes and Exercises


Hour 5, "Manipulating Data"

Quiz Answers

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'; 
A1:
  1.  INSERT INTO EMPLOYEE_TBL  (''JACKSON', 'STEVE', '313546078', '3178523443'); 

    The INSERT statement would not run because the keyword VALUES is missing in the syntax.

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

    One row would be inserted into the EMPLOYEE_TBL.

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

    One row would be inserted into the EMPLOYEE_TBL, with a NULL value in the PHONE column.

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

    The INSERT statement would not process because the FIRST_NAME column is NOT NULL.

  5.  DELETE FROM RMPLOYEE_TBL; 

    All rows in the EMPLOYEE_TBL would be deleted.

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

    All employees with the last name of SMITH would be deleted from the EMPLOYEE_TBL.

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

    Only JOHN SMITH would be deleted from the EMPLOYEE_TBL.

  8.  UPDATE EMPLOYEE_TBL  SET LAST_NAME  'CONRAD'; 

    All last names would be changed to CONRAD.

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

    Both JOHN and SUE SMITH would now be JOHN and SUE CONRAD.

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

    All employees are now LARRY CONRAD.

  11.  UPDATE EMPLOYEE_TBL  SET LAST_NAME = 'CONRAD', FIRST_NAME = 'LARRY' WHERE SSN = '312456788'; 

    JOHN SMITH is now LARRY CONRAD.

Exercise Answers

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.

A2:
  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

     INSERT INTO PRODUCTS_TBL VALUES  ('301','FIREMAN COSTUME',24.99); INSERT INTO PRODUCTS_TBL VALUES ('302','POLICEMAN COSTUME',24.99); INSERT INTO PRODUCTS_TBL VALUES ('303','KIDDIE GRAB BAG',4.99); 
  2. 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.

     UPDATE PRODUCTS TBL  SET COST = 29.99 WHERE PROD_ID = '301'; UPDATE PRODUCTS TBL SET COST = 29.99 WHERE PROD_ID = '302'; 
  3. Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.

     DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '301';  DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '302'; DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '303'; 

Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net