This chapter is from the book
This chapter is from the book
This chapter is from the book
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
-
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 char(2) not null, zip number(4) not null, date hired date); -
Can you drop a column from a table?
-
What statement would you issue in order to create a primary key constraint on the preceding EMPLOYEE_TABLE?
-
What statement would you issue on the preceding EMPLOYEE_TABLE to allow the MIDDLE_NAME column to accept NULL values?
-
What statement would you use to restrict the people added into the preceding EMPLOYEE_TABLE to only reside in the state of New York ('NY')?
-
What statement would you use to add an auto-incrementing column called EMPID to the preceding EMPLOYEE_TABLE?
Exercises
- Bring up a command prompt and use the following syntax to log onto your local MySQL instance, replacing username with your username and password with your password. Ensure that you do not leave a space between -p and your password.
Mysql -h localhost -u username -ppassword - At the mysql> command prompt, enter the following command to tell MySQL that you want to use the database you created previously:
use learnsql; - 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.
- At the mysql> prompt, enter the following command to get a list of your tables:
show tables; - 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;
- If you have any errors or typos, simply re-create 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;