Reference no: EM13949067
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.
|
Construct a scatterplot for these 10 values by hand
: When two variables are correlated (such as strength and running speed), it also means that they are associated with one another. But if they are associated with one another, then why doesn't one cause the other?
|
|
What is the bond yield to maturity
: Bonds of Riverhawk Sport Authority (RSA) are selling in the market for $957.10. These bonds carry a 9.50 percent coupon paid semiannually, and have 20 years remaining to maturity. What is the bond’s yield to maturity?
|
|
Bond outstanding with market price-coupon rate
: Riverhawk Corporation has a bond outstanding with a market price of $1,250.00. The bond has 10 years to maturity, pays interest semiannually, and has a yield to maturity of 9%. What is the bond’s coupon rate?
|
|
Who purchased them at the going out of business sale
: If TV Manufacturer has not been paid for the TV sets, would it have the legal right to recover them from individual customers who purchased them at the going-out-of-business sale?
|
|
Create a new table using the create table command
: 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.
|
|
Design a combinational logic circuit
: 1. Design a combinational logic circuit which will add two 4-bit binary numbers. 2. Minimize the expression described in the truth table of Figure P12.50, and draw the circuit.
|
|
Rate of return on investments in this risk class
: Riverhawk Sport Authority (RSA) will pay a dividend of $7 for each of the next 3 years, $8 for each of the years 4-6, $9 for each of the years 7-9, and a dividend of $10 for year 10. Thereafter, the company will pay no dividends. If you require 11 pe..
|
|
Faced conflicts of interest within our workplace
: Conflicts of interest arise in all organizations. This module shows that no organization, whether public or private, is exempt from issues related to conflicts of interest. Conflicts of interest result in being pulled in two different directions. ..
|
|
Importance and significance of these processes
: What has been the importance and significance of these processes and their cyclic interaction to the evolution and diversity of life?
|