Cycle 01

Lab Cycle 1 — DDL Commands, Schema Management & Constraints

4 Questions
Question 01 of 04

DDL Schema Definition and Table Alterations

Problem Statement

Consider the following Schema: Supplier(SID, Sname, branch, city, phone), Part(PID, Pname, color, price), and Supplies(SID, PID, qty, date_supplied). Write SQL DDL commands to: 1. Create the above three tables with appropriate data types. 2. Add a new attribute 'state' in the Supplier table. 3. Remove the attribute 'city' from the Supplier table. 4. Modify the data type of the 'phone' attribute. 5. Change the name of the attribute 'state' (or city) to 'address'. 6. Change the table name 'Supplier' to 'sup'. 7. Use TRUNCATE to delete the contents of the 'Supplies' table. 8. Remove the 'Part' table from the database.

C++ 
-- 1. Create the schema tables
CREATE TABLE Supplier (
    SID INT PRIMARY KEY,
    Sname VARCHAR(50) NOT NULL,
    branch VARCHAR(50),
    city VARCHAR(50),
    phone VARCHAR(15)
);

CREATE TABLE Part (
    PID INT PRIMARY KEY,
    Pname VARCHAR(50) NOT NULL,
    color VARCHAR(20),
    price DECIMAL(10, 2)
);

CREATE TABLE Supplies (
    SID INT REFERENCES Supplier(SID) ON DELETE CASCADE,
    PID INT REFERENCES Part(PID) ON DELETE CASCADE,
    qty INT NOT NULL,
    date_supplied DATE NOT NULL,
    PRIMARY KEY (SID, PID, date_supplied)
);

-- 2. Add a new attribute 'state' in Supplier table
ALTER TABLE Supplier ADD COLUMN state VARCHAR(50);

-- 3. Remove attribute 'city' from Supplier table
ALTER TABLE Supplier DROP COLUMN city;

-- 4. Modify data type of 'phone' attribute
ALTER TABLE Supplier ALTER COLUMN phone TYPE VARCHAR(20);

-- 5. Rename attribute 'state' to 'address'
ALTER TABLE Supplier RENAME COLUMN state TO address;

-- 6. Change table name 'Supplier' to 'sup'
ALTER TABLE Supplier RENAME TO sup;
-- (Rename back to Supplier for downstream compatibility if needed: ALTER TABLE sup RENAME TO Supplier;)

-- 7. Truncate 'Supplies' table to delete all records instantly
TRUNCATE TABLE Supplies;

-- 8. Drop the 'Part' table (using CASCADE if referenced by foreign keys)
DROP TABLE Part CASCADE;
Question 1 of 4