Purchase Solution

Oracle Sqlplus

Not what you're looking for?

Ask Custom Question

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
Purchase this Solution

Solution Summary

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

Purchase this Solution


Free BrainMass Quizzes
Java loops

This quiz checks your knowledge of for and while loops in Java. For and while loops are essential building blocks for all Java programs. Having a solid understanding of these constructs is critical for success in programming Java.

Excel Introductory Quiz

This quiz tests your knowledge of basics of MS-Excel.

C++ Operators

This quiz tests a student's knowledge about C++ operators.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

C# variables and classes

This quiz contains questions about C# classes and variables.