Lab05 and Lab06
ieas2010
lab_6.rtfPage 5 of 7
Delete this text and type your name here
This file will become rather large due to your screen shots. I encourage you to compress this file (zip) before submitting it.
Lab 6: 40 Total Points Possible
You will need to log into Apex at https://iacademy.oracle.com " https://iacademy.oracle.com in order to complete this assignment.
Sections 7- 8 -- Programming with SQL
Section 7- 8 Objectives : Working with D ML and D DL Statements
Creating and Modifying Tables
Vocabulary :
Directions: Identify the vocabulary word(s) for each definition below. (1 Point each)
1. Command used to make a new table.
Answer:
2. A collection of objects that are the logical structures that directly refer to the data in the database. Answer:
3. Specifies a preset value if a value is omitted in the INSERT statement.
Answer:
4. Stores data; basic unit of storage composed of rows and columns.
Answer:
5. Created and maintained by the Oracle Server and contains information about the database.
Answer:
Try It / Solve It :
Log into Apex. Execute the following CREATE TABLE SQL statement:
CREATE TABLE grad_candidates
(student_id NUMBER(6),
last_name VARCHAR2(15),
first_name VARCHAR2(15),
credits NUMBER (3),
graduation_date DATE);
After executing the above SQL statement, you should receive a ‘Table Created ‘message.
1. Create an SQL statement that will describe the structure of the table object called grad_candidates. Provide a screen shot of your table properties as shown below. (2 Points):
Select Statement You Used :
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT . ( 1 Point)
2. Create a new table using a subquery. Name the new table your last name – e.g., herbert_table. Using a subquery, copy grad_candidates into herbert_table. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used :
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT . ( 1 Point)
3. Insert your personal information into the HERBERT_TABLE (or whatever name you gave it). Hint: DATE and VARCHAR2 data type values need to have an apostrophe surrounding each value ('). (2 Points)
Select Statement You Used :
My example of table content results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN . ( 1 Point)
3. Create an SQL statement using the ALTER TABLE command. Alter the HERBERT_TABLE (or whatever name you gave it) and insert a new column called e_mail_address with a VARCHAR2 data type that will hold 80 characters. (2 Points)
Select Statement You Used :
4. Create an SQL statement that will describe the structure of the table object you just inserted the e_mail_address column into. Provide a screen shot of the table structure. (2 Point)
Select Statement You Used :
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT . ( 1 Point)
5. You decide you no longer need the e_mail_address column after all. Create an SQL statement that will drop this column from the table. (2 Points)
Select Statement You Used :
6. Create an SQL statement that will describe the structure of the table object you just altered, removing the e_mail_address column. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used :
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT . ( 1 Point)
7. Dropping a column from a large table can take a long time. What is a quicker alternative? (1 Point)
Answer:
8. Create an SQL statement that will modify the name of HERBERT_TABLE (or whatever name you gave it). Change the name of the table to PRACTICE_TABLE. Do not copy the table. (2 Points)
Select Statement You Used :
9. Create an SQL statement that will describe the structure of the table object PRACTICE_TABLE. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used :
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
10. You no longer use the PRACTICE_TABLE and need to remove it from your schema. Create an SQL statement that will remove this table. (2 Point)
Select Statement You Used :
11. You just discovered you removed the wrong table! Opppps! Execute the following SQL statement to see what is in your recycle bin.
SELECT *
FROM user_recyclebin
My example of table structure results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
12. Your table should be in the recycle bin. Now, create an SQL statement that recovers that table. (2 Points)
Select Statement You Used :
Execute the following SQL statement to view the PRACTICE_TABLE contents.
SELECT * from PRACTICE_TABLE
My example of table contents from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
13. What is the difference between the TRUNCATE and the DROP command? (1 Points)
Answer:
14. Create an SQL statement that will add the comment 'This is a practice table ' to the PRACTICE_TABLE. (2 Points)
Select Statement You Used :
Execute the following SQL statement to view all schema table comments in the data dictionary.
SELECT *
FROM user_tab_comments;
My example of table comments from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
:
