Assigned: 2/3/2014Due: 2/3/2014
Student Name: ______
Figures 1-24 and 1-25 (p. 25-29 in the book) show the data and the relationships within the Clearwater database. Read the information on page 25-29 to familiarize yourself with the database.You will be using the information to create three of the Clearwater tables
Before you start typing SQL commands, make sure you create a Notepad file called oralab01.txt in the OraSolutions\Chapter2\Cases folder on your flash drive. You will be saving your SQL commands and their output in that file. As you did in Oracle Lab 00, you may go back and forth between Notepad and SQL Plus. Once your SQL command runs successfully, you should copy the command and its outputto the oralab01.txt file. Note that you should copy/paste the command and the output along with the line numbers from SQL Plus to the oralab01.txt file.
1)Use the appropriate SQL command to create the following table. If you are not sure what command and syntax to use, checking the Oracle Lab 00 assignment (and the book if necessary) may help.
CUSTOMER
Column / field / Data type / Commentc_id (primary key) / number 5 / Define the constraint as part of the column declaration. An example for the Location table is shown on p. 54 of the book
c_last / variable char 30
c_first / variable char 30
c_mi / fixed char 1
c_birthdate / date
c_address / variable char 30
c_city / variable char 30
c_state / fixed char 2 / Define the constraint so that the default value is IL. See p. 57 for an example.
c_zip / variable char 10
c_landphone / variable char 10
c_cellphone / variable char 10
c_userid / variable char 50 / Define a constraint that forces this column to be unique. See p. 58 for an example.
c_password / variable char 15
************** SEE BACK ***************
2)Use the appropriate SQL command to create the following table. If you are not sure what command and syntax to use, checking the Oracle lab 00 assignment and, if necessary, the book may help.
ORDER_SOURCE
Column / field / Data type / Commentos_id (primary key) / number 3 / Define the constraint at the end of the CREATE TABLE code.
os_desc / variable char 30
3)Use the appropriate SQL command to create the following table.
ORDERS
Column / field / Data type / Commento_id (primary key) / number 8 / Define this constraint at the end of the CREATE TABLE code.
o_date / date
o_methpmt / variable char 10
c_id (foreign key) / number 5 / Define this constraint at the end of the CREATE TABLE code.
os_id (foreign key) / number 3 / Define this constraint at the end of the CREATE TABLE code.
4)Use the DESCRIBE command to show the structure of each of the tables you just created.That means you need to use three separate DESCRIBE commands.
5)Use a SELECT command to show the constraint name, the table name, and the constraint type for each of the individual tables you just created.The constraints must be listed for each table separately. Note: if needed, see example in Ora Lab 00 (step 10) or Figure 2-11 in book.
6)Use the appropriate command to change the name of the c_birthdate field of the CUSTOMER table to c_dob. [If needed, page 76 shows an example]. Then, use the appropriate command to display the structure of the CUSTOMER table showing the change. Make sure you have a copy of all commands run in this step in your Notepad file.
7)Use the appropriate command to change the data type for the c_zip column of the CUSTOMERS table to make its size 8 variablecharacters instead of 10. [If needed, p. 75/76 shows an example].
8)Make sure that your oralab01.txt file includes each SQL command along with its output, as well as the line numbers.
9)Type in your name at the top of the oralab01.txt file. Then, print the file.
10)Print a submission sheet (see Assignments section of class website). Complete the sheet (name, date, etc.)
11)Staple the completed submission sheet and the printout of your oralab01.txt file, and make sure that the submission sheet is on the top and that the SQL commands are in the order they are issued and run in your assignment. That means, the command used in step 1 above must appear first, then step 2, etc. You may lose 15% for an assignment that is not stapled and/or not in order.
OraLab01.doc1 of 2