Share
Explore BrainMass

Oracle Sqlplus

I need some help in this exercise:
Write and execute an SQL query in SQL*Plus

A. Table Creation and Management
1. Create a new table using the CREATE TABLE command. Use your first name and your last name for the name of the table (for instance, for John Smith the name of the table will be JOHN_SMITH). Make sure to include at least four different data types (CHAR, VARCHAR2, NUMBER, DATE) for the columns. Use the DESCRIBE command to verify that the columns have been defined correctly.

2. Obtain the columns names and data types of the view USER_TABLES using the DESCRIBE command. Retrieve
the names of all the tables of a user's database tables using the SELECT table_name FROM user_tables; command.

3. Use the ALTER TABLE ... ADD command to add a column to the table created in Problem 1.
4. Use the ALTER TABLE ... MODIFY command to change the size of any column in the table created in Problem 1.
5. Use the ALTER TABLE ... DROP COLUMN command to drop a column in the table created in Problem 1.
6. Rename the table created in Problem 1 using the RENAME ... TO command.
7. Drop the table created in Problem 1 using the DROP TABLE command.
8. Restore the table created in Problem 1 using the FLASHBACK TABLE command.
9. Use the DROP TABLE ... PURGE command to delete your table permanently. Use the SELECT object_name, original_name FROM recyclebin; command to confirm that the table is not in the recycle bin anymore.

B. Constraints:
1. Write an SQL code to create the STUDENT table. The information about the STUDENT table is provided below. When creating the table, make sure to include appropriate constraints defined in the description. (see table in attachment)

2. Write an SQL code to create the VENDOR table. The information about the VENDOR table is provided below. When creating the table, make sure to include appropriate constraints defined in the description. (see table in attachment)

3. Write an SQL code to create the PRODUCT table. The information about the PRODUCT table is provided below. When creating the table, make sure to include appropriate constraints defined in the description. (see table in attachment)

4. Assuming that each product is manufactured by many vendors and each vendor makes many products, create a bridge table between VENDOR and PRODUCT. When creating the table, make sure to include appropriate constraints (primary key, foreign keys, etc.).

5. Use the ALTER TABLE ... ADD CONSTRAINT command to add the UNIQUE constraint to the PROD_DESC column in the PRODUCT table.

6. Use the ALTER TABLE ... ADD CONSTRAINT command to add the CHECK constraint to the ST_DOB column to verify that all dates of birth are before 01/01/2000.

Attachments

Solution Summary

This solution provides the answers in the oracle sqlplus format, following the commands in the problem description.

$2.19