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
-
Does the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)? Are there limitations as to what database implementation it works in (MySQL, Oracle, SQL Server)?
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 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 using both the MySQL and SQL Server syntax?
Exercises
In the following exercise, you will be creating all the tables in the database to set up the environment for later. Additionally, you will be executing several commands that will allow you to investigate the table structure in an existing database. For thoroughness we have provided instructions for each of the three implementations (MySQL, Microsoft SQL Server, and Oracle) because each is slightly different in its approach.
-
MySQL
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 -ppasswordAt 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 are 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 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;
-
Microsoft SQL Server
Bring up a command prompt and use the following syntax to log onto your local SQL Server instance, replacing username with your username and password with your password. Ensure that you do not leave a space between –p and your password.
SQLCMD -S localhost –U username -Ppassword
At the 1> command prompt, enter the following command to tell SQL Server that you want to use the database you created previously. Remember that with SQLCMD you must use the keyword GO to tell the command tool that you want the previous lines to execute.
1>use learnsql; 2>GO
Now go to Appendix D to get the DDL for the tables used in this book. At the 1> prompt, enter each CREATE TABLE statement. Be sure to include a semicolon at the end of each CREATE TABLE statement and follow up with the keyword GO to have your statement execute. The tables that you create are used throughout the book.
At the 1> prompt, enter the following command to get a list of your tables. Follow this command with the keyword GO:
Select name from sys.tables;
At the 1> prompt, use the sp_help stored procedure to list the columns and their attributes for each one of the tables you created. For example:
Sp_help_ employee_tbl; Sp_help employee_pay_tbl;
If you have 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;
-
Oracle
Bring up a command prompt, and use the following syntax to log onto your local Oracle instance. You are prompted to enter your username and password.
sqlplus
Now go to Appendix D to get the DDL for the tables used in this book. At the SQL> 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 are used throughout the book.
At the SQL> prompt, enter the following command to get a list of your tables:
Select * from cat;
At the SQL> 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 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;