Assignment 2

-- Create Project table
CREATE TABLE Project (
    pno NUMBER PRIMARY KEY,
    pname VARCHAR2(50) NOT NULL,
    manager_no NUMBER,
    CONSTRAINT FK_Manager FOREIGN KEY (manager_no) REFERENCES Manager_details(manager_no)
);

-- Create Manager_details table
CREATE TABLE Manager_details (
    manager_no NUMBER PRIMARY KEY,
    manager_name VARCHAR2(50)
);

INSERT INTO Manager_details (manager_no, manager_name) VALUES (101, 'John Manager');
INSERT INTO Manager_details (manager_no, manager_name) VALUES (102, 'Jane Manager');

-- Ensure that the manager_no in Project references existing manager_no in Manager_details
INSERT INTO Project (pno, pname, manager_no) VALUES (1, 'Project A', 101);
INSERT INTO Project (pno, pname, manager_no) VALUES (2, 'Project B', 102);
-- Try to insert a record with a non-existent manager_no, which will be restricted
INSERT INTO Project (pno, pname, manager_no) VALUES (3, 'Project C', 103); -- This should fail

-- Alter the EMP table to add 'commission' attribute and modify 'sal' datatype
ALTER TABLE EMP ADD commission NUMBER(7, 2);

-- Modify the datatype of 'sal'
ALTER TABLE EMP MODIFY sal NUMBER(7, 2);

-- Insert a new record with all attributes
INSERT INTO EMP (empid, name, date_of_birth, city, salary, commission, dob, designation, dependence)
VALUES (201, 'New Employee', TO_DATE('1990-01-01', 'YYYY-MM-DD'), 'City X', 80000.00, 1000.00, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'Analyst', 1);

-- Add 'designation' attribute to EMP table
ALTER TABLE EMP ADD designation VARCHAR2(50);

-- Update designation for existing records
UPDATE EMP SET designation = 'Programmer' WHERE empid IN (101, 102, 103);

-- Add 'manager_no' attribute to EMP table
ALTER TABLE EMP ADD manager_no NUMBER(10);

-- Update 'manager_no' for existing records
UPDATE EMP SET manager_no = 1001 WHERE empid = 101;
UPDATE EMP SET manager_no = 1002 WHERE empid = 102;