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.
|
Attribute
|
Data Type
|
Constraints
|
|
ST_NUMBER
|
NUMBER(6)
|
Primary Key
|
|
ST_LAST
|
VARCHAR2(35)
|
NOT NULL
|
|
ST_FIRST
|
VARCHAR2(25)
|
|
|
ST_DOB
|
DATE
|
|
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.
|
Attribute
|
Data Type
|
Constraints
|
|
VEND_NUMBER
|
NUMBER(5)
|
Primary Key
|
|
VEND_NAME
|
VARCHAR2(35)
|
NOT NULL
|
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.
|
Attribute
|
Data Type
|
Constraints
|
|
PROD_CODE
|
NUMBER(5)
|
Primary Key
|
|
PROD_DESC
|
VARCHAR2(35)
|
|
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.