Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
    • What Are Database Objects?
    • What Is a Schema?
    • A Table: The Primary Storage for Data
    • Integrity Constraints
    • Summary
    • Q&A
    • Workshop
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
  • Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

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; 
   

Share This