Consider the following Schema
Supplier(SID, Sname, branch, city, phone)
Part(PID, Pname, color, price)
Supplies(SID, PID, qty, date_supplied)
DDL Commands
1. Create the above tables
CREATE TABLE Supplier (SID INT PRIMARY KEY, Sname VARCHAR(20),
branch VARCHAR(20), city VARCHAR(30), phone NUMBER(10));
Table Created.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
CITY VARCHAR2(30)
PHONE NUMBER(10)
CREATE TABLE Part (PID INT PRIMARY KEY, Pname VARCHAR(20), color
VARCHAR(20),price DECIMAL(10, 2));
Table Created.
DESC Part;
Name Null? Type
PID NOT NULL NUMBER(38)
PNAME VARCHAR2(20)
COLOR VARCHAR2(20)
PRICE NUMBER(10,2)
CREATE TABLE Supplies (SID INT, PID INT, qty INT, date_supplied DATE,
FOREIGN KEY (SID) REFERENCES Supplier(SID), FOREIGN KEY (PID)
REFERENCES Part(PID));
Table Created.
DESC Supplies;
Name Null? Type
SID NUMBER(38)
PID NUMBER(38)
QTY NUMBER(38)
DATE_SUPPLIED DATE
2. Add a new attribute state in supplier table
ALTER TABLE Supplier ADD state VARCHAR(20);
Table Altered.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
CITY VARCHAR2(30)
PHONE NUMBER(10)
STATE VARCHAR2(20)
3. Remove attribute city from supplier table
ALTER TABLE Supplier DROP COLUMN city;
Table Altered.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
PHONE NUMBER(10)
STATE VARCHAR2(20)
4. Modify the data type of phone attribute
ALTER TABLE Supplier MODIFY phone VARCHAR(15);
Table Altered.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
PHONE VARCHAR2(15)
STATE VARCHAR2(20)
5. Change the name of attribute city to address
ALTER TABLE Supplier ADD city varchar(20);
Table Altered.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
PHONE VARCHAR2(15)
STATE VARCHAR2(20)
CITY VARCHAR2(20)
ALTER TABLE Supplier RENAME COLUMN city to address;
Table Altered.
DESC Supplier;
Name Null? Type
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
BRANCH VARCHAR2(20)
PHONE VARCHAR2(15)
STATE VARCHAR2(20)
ADDRESS VARCHAR2(20)
6. Change a table’s name, supplier to
sup ALTER TABLE Supplier
RENAME to Sup;
Table Altered.
7. Remove the part table from
database DROP TABLE Supplies;
Table dropped.
DROP TABLE Part;
Table dropped.
