 | 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 3, "Managing Database Objects" Quiz Answers | 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); | | A1: | The CREATE TABLE statement will not work because there are several errors in the syntax. The corrected statement follows. A listing of what was incorrect follows a corrected statement. CREATE TABLE EMPLOYEE_TABLE (SSN NUMBER() NOT NULL, LAST_NAME VARCHAR2(20) NOT NULL, FIRST_NAME VARCHAR2(20) NOT NULL, MIDDLE_NAME VARCHAR2(20), ST_ADDRESS VARCHAR2(30) NOT NULL, CITY VARCHAR2(20) NOT NULL, STATE CHAR(2) NOT NULL, ZIP NUMBER(5) NOT NULL, DATE_HIRED DATE); The following needs to be done: -
The as: should not be in this CREATE TABLE statement. -
Missing a comma after the NOT NULL for the LAST_NAME column. -
The MIDDLE_NAME column should be NULL because not everyone has a middle name . -
The column ST ADDRESS should be ST_ADDRESS. Being two words, the database looked at ST as being the column name, which would make the database look for a valid data type, where it would find the word ADDRESS. -
The CITY column works, although it would be better to use the VARCHAR2 data type. If all city names were constant length, CHAR would be okay. -
The STATE column is missing a left parenthesis. -
The ZIP column length should be (5), not (4). -
The DATE HIRED column should be DATE_HIRED with an underscore to make the column name one continuous string. -
The comma after 3k in the STORAGE clause should not be there. | | 2: | Can you drop a column from a table? | | A2: | Yes. However, even though it is an ANSI standard, you must check your particular implementation to see if it has been accepted. | | 3: | What happens if you do not include the STORAGE clause in the CREATE TABLE statement? | | A3: | The CREATE TABLE statement should process, barring any syntax errors of course; however, most implementations have a default sizing. Check your particular implementation for the sizing. | Exercise Answers | 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; | |
| | | Team-Fly | | |
