Using Oracle SQL Developer

QUESTION

a.Create a table called supplier with the following fields :

supplier_idnumeric(10) - > Primary key with constraint name supplier_name varchar2(50) - >unique name

contact_name varchar2(50)

city varchar2(10)

Region > should accept only ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')

-

b. Insert 5 records into the supplier table. c. Add a phone number column in the supplier table using DDL command d. Write a sql command to make city as unused column e. Delete the unused column in the supplier table f. Rename the table "supplier" to "supplier_contact ". g. Write a sql command to delete all the records in the supplier table. h. Write a sql command to Retrieve the deleted table i. Write a sql command to permanently delete the table , so that it is not moved to recyclebin

WHAT I HAVE

CREATE TABLE Supplier (supplier_id NUMERIC(10)PRIMARY KEY CONSTRAINT constraint_supplier_id not null, supplier_name VARCHAR2(50) Unique, contact_name VARCHAR2(50), city VARCHAR2(10), Region char(2) CHECK(Region IN ('N','NW','NE','S','SE','SW','W','E')));

insert into Supplier values(1,'John','W.','Toronto','N'); insert into Supplier values(2,'Stacy','F.','London','S'); insert into Supplier values(3,'Amy','B.','New York','N'); insert into Supplier values(4,'Brad','R.','Sydney','E'); insert into Supplier values(5,'Steve','O.','Beijing','NW');

ALTER TABLE Supplier ADD phoneNumber integer;

ALTER TABLE Supplier SET UNUSED COLUMN city;

ALTER TABLE Supplier DROP UNUSED COLUMNS;

ALTER TABLE SUPPLIER RENAME TO supplierContact;

DELETE FROM supplierContact;

SELECT * FROM supplierContact as of timestamp(sysdate - .5/1440);

DROP TABLE SupplierContact;