Create a new table using the create table command

Assignment Help Database Management System
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.

Reference no: EM13949067

Questions Cloud

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?

Reviews

Write a Review

 

Database Management System Questions & Answers

  What is candidate key and database is a set of one or more

question 1a candidate key isrequired to be unique.used to represent rows in relationships.a candidate to be the primary

  Goals and implement an effective deployment plan

To establish project goals and implement an effective deployment plan, To gain insight into your company's transactions with data mining

  Create two uml diagrams showing a conceptual view

Create two (2) UML diagrams, one (1) showing a conceptual view and one (1) showing a process view of the architectures for each of the two (2) following systems (for a total of four [4] diagrams) through the use of Microsoft Visio

  Design a normalized relational database management system

Design a completely Normalized Relational Database Management System that meets both the high-level and technical requirements of the web-based project management system.

  When should we use relational database

If the problem is simple one should use a sequential or relational file and if the problem is complex one should choose to use a database?

  Draw an orm diagram for lineiteminvoice

Draw an ORM diagram for LineItemInvoice. Note, this diagram should be connected to the previous diagrams. You will need a nested object.

  Display full details of all attributes in the author table

Display full details of all the attributes in the AUTHOR table. Display the last name and first name for all the authors in the AUTHOR table and sort all records by last name order ascending.

  Create a new table in which to store publisher information

In the following project, you will open a database containing the inventory of a bookstore, create a new table in which to store publisher information, add records, and then sort the table

  Compare the four dbmss on different parameters

Explore and research the latest versions of the following database management systems (DBMSs) on the market: Oracle 11G, Microsoft SQL Server, MySQL, and IBM DB2. Compare the four DBMSs on different parameters, including pros and cons

  Drawing active directory hierarchy in terms of forests

Draw Active Directory hierarchy in terms of forests, trees, domains, organizational units, and sites which are most suitable for this company and their security concerns.

  Explore and investigate technical problems of dbm systems

Your topic could come from a sub-problem of a cutting-edge research problem about these techniques (if you want to investigate and solve a technical problem), or a successful (or a planned) implementation in one of the above DBMSs (if you want to ..

  Create a crow''s foot erd using a specialization hierarchy

Given the following business scenario, create a Crow's Foot ERD using a specialization hierarchy if appropriate. Tiny Hospital keeps information on patients and hospital rooms

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd