Chapter 3 Solutions

Review Questions

1. Which command is used to create a table based upon data already contained in an existing table? CREATE TABLE command with a subquery
2. List four datatypes supported by Oracle10g, and provide an example of data that could be stored by each datatype. DATE – 20-JAN-99, VARCHAR2 – HELLO, NUMBER – 5.23, CHAR - HELLO
3. What are the guidelines you should follow when naming tables and columns in Oracle10g? A maximum of 30 characters are allowed, the first character must be a letter, no blank spaces or special symbols other than underscore and dollar signs cannot be included. Also, reserve words cannot be used as a table or column name.
4. What is the difference between dropping a column and setting a column as unused? When dropped, the storage space is released immediately. When set as unused, the storage space is not released until a later time.
5. How many columns can be dropped in one ALTER TABLE command? Only one at a time
6. What happens to the existing rows of a table if the DEFAULT value of a column is changed? Nothing – they are not affected
7. Explain the difference between truncating a table and deleting a table. Truncating a table will remove all the data, but retain the table structure while deleting (dropping) a table will remove the data and the table structure from the database.
8. If you add a new column to an existing table, where will the column appear relative to the existing columns? It will appear as the last column in the table.
9. What happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field? You cannot change the scale or precision of a NUMBER column that contains data.
10. Is a table and the data data contained in the table permanently erased from the system if a DROP TABLE command is issued on the table? The table is permanently erased only if the PURGE option is used in the DROP TABLE command. Otherwise, the table will be moved to the recyclebin.

Multiple Choice

1. c
2. c
3. a
4. b
5. d
6. d
7. b
8. d
9. b
10. a
11. d
12. a
13. a
14. c
15. d
16. d
17. b
18. d
19. b
20. b

Hands-on Assignments

1.
CREATE TABLE category
(catcode VARCHAR2(2), catdesc VARCHAR2(10));

2.
CREATE TABLE employees
(emp# NUMBER(5), lastname VARCHAR2(15), firstname VARCHAR2(10), job_class VARCHAR2(4) );

3.
ALTER TABLE employees add (empdate DATE DEFAULT SYSDATE, enddate DATE);

4.
ALTER TABLE employees modify job_class VARCHAR2(2);

5.
ALTER TABLE employees
DROP column enddate;

6.
RENAME employees TO jl_emps;

7.
CREATE TABLE book_pricing (id, cost, retail, category)
AS (SELECT isbn, cost, retail, category FROM books);

8.
ALTER TABLE book_pricing
SET UNUSED (category);

SELECT * FROM book_pricing;

9.
TRUNCATE TABLE book_pricing;

SELECT * FROM book_pricing;

10.
DROP TABLE book_pricing PURGE;

DROP TABLE jl_emps;

FLASHBACK TABLE jl_emps TO BEFORE DROP;

SELECT * FROM jl_emps;

Advanced Challenge

ALTER TABLE customers
ADD region VARCHAR2(2);

CREATE TABLE commrate (rate NUMBER(2,2), minprice NUMBER(5,2), maxprice NUMBER(5,2));

Case Study: City Jail
Notes:
In Section A, students will need to develop create table statements for each of the tables listed. The listing indicates DEFAULT column values where applicable. The listing entitled “Coding Key for selected columns” is only provided to help the student visualize what type of data will be entered into selected code columns. This will be a valuable reference for accomplishing queries in later chapters.

In Section B, students will need to use the ALTER TABLE command to make the requested table modifications.

Warning: If you modify this assignment consider how this will affect the case study assignments in the