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. Will the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)?

   CREATE TABLE EMPLOYEE_TABLE AS: 
      ( SSN           NUMBER(9)       NOT NULL,
       LAST_NAME      VARCHAR2(20)    NOT NULL,
       FIRST_NAME     VARCHAR2(20)    NOT NULL,
       MIDDLE_NAME    VARCHAR2(20)    NOT NULL,
       ST ADDRESS     VARCHAR2(30)    NOT NULL,
       CITY           CHAR(20)        NOT NULL,
       STATE          CHAR2)          NOT NULL,
       ZIP            NUMBER(4)       NOT NULL,
       date hired     date);
   

2. Can you drop a column from a table?

3. What happens if you do not include the STORAGE clause in the CREATE TABLE statement?

Exercises

1. Navigate to the folder on your computer where you installed MySQL. Navigate (double-click on) the bin folder, and then double-click on the mysql.exe executable to invoke MySQL.

2. At the mysql> command prompt, enter the following command to tell MySQL that you want to use the database you created previously:

   use learnsql; 
   

3. Now, go to Appendix D,"CREATE TABLE Statements for Book Examples," to get the DDL for the tables used in this book. At the mysql> prompt, enter each CREATE TABLE statement. Be sure to include a semicolon at the end of each CREATE TABLE statement. The tables that you create will be used throughout the book.

4. At the mysql> prompt, enter the following command to get a list of your tables:

   show tables; 
   

5. At the mysql> prompt, use the DESCRIBE command (desc for short) to list the columns and their attributes for each one of the tables you created. For example:

   describe employee_tbl; 
   
   describe employee_pay_tbl;
   

6. If you have any errors or typos, simply recreate the appropriate table(s). If the table was successfully created, but has typos (perhaps you did not properly define a column or forgot a column), drop the table, and issue the CREATE TABLE command again. The syntax of the DROP TABLE command is as follows:

   drop table orders_tbl;