ECMS.sql

CREATE TABLE Departments (
    Department_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Department_Name VARCHAR2(100) NOT NULL
);
CREATE TABLE Employees (
    Employee_ID VARCHAR2(20) PRIMARY KEY,
    Employee_Name VARCHAR2(100) NOT NULL,
    Email VARCHAR2(100) UNIQUE NOT NULL,
    Password VARCHAR2(100) NOT NULL,
    Department_ID NUMBER,
    Role VARCHAR2(20) NOT NULL,
    CONSTRAINT fk_department
        FOREIGN KEY (Department_ID)
        REFERENCES Departments(Department_ID)
);



CREATE TABLE Complaints (
    Complaint_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Employee_ID VARCHAR2(20) NOT NULL,
    Issue_Title VARCHAR2(200) NOT NULL,
    Category VARCHAR2(50) NOT NULL,
    Priority VARCHAR2(20) NOT NULL,
    Store_Location VARCHAR2(150) NOT NULL,
    Contact_Number VARCHAR2(20) NOT NULL,
    Device_Name VARCHAR2(100),
    Description CLOB NOT NULL,
    Attachment VARCHAR2(255),
    Status VARCHAR2(30) DEFAULT 'Pending',
    Remarks CLOB,
    Date_Submitted DATE DEFAULT SYSDATE,
    CONSTRAINT fk_employee
        FOREIGN KEY (Employee_ID)
        REFERENCES Employees(Employee_ID)
);

INSERT INTO Departments (Department_Name)
VALUES ('IT');
INSERT INTO Departments (Department_Name)
VALUES ('Human Resources');
INSERT INTO Departments (Department_Name)
VALUES ('Sales');
INSERT INTO Departments (Department_Name)
VALUES ('Finance');
INSERT INTO Departments (Department_Name)
VALUES ('Marketing');
COMMIT;

INSERT INTO Employees
(Employee_ID,
Employee_Name,
Email,
Password,
Department_ID,
Role)

VALUES
(
'EMP001',
'Maryam Bano',
'employee@bonanza.com',
'12345',
3,
'Employee'
);


INSERT INTO Employees
(Employee_ID,
Employee_Name,
Email,
Password,
Department_ID,
Role)

VALUES
(
'ADM001',
'IT Administrator',
'admin@bonanza.com',
'admin123',
1,
'Admin'
);

COMMIT;
SELECT * FROM Departments;
SELECT * FROM EMPLOYEES;
SELECT * FROM Complaints;
SELECT Complaint_ID
FROM Complaints
ORDER BY Complaint_ID;

DELETE FROM Complaints;
COMMIT;

DESC Employees;
DESC Complaints;

INSERT INTO Employees
(
    Employee_ID,
    Employee_Name,
    Email,
    Password,
    Department_ID,
    Role
)
VALUES
(
    'EMP002',
    'Ahmed Khan',
    'ahmed@bonanza.com',
    '12345',
    4,
    'Employee'
);


INSERT INTO Employees
(
    Employee_ID,
    Employee_Name,
    Email,
    Password,
    Department_ID,
    Role
)
VALUES
(
    'EMP003',
    'Sara Ali',
    'sara@bonanza.com',
    '12345',
    2,
    'Employee'
);


INSERT INTO Employees
(
    Employee_ID,
    Employee_Name,
    Email,
    Password,
    Department_ID,
    Role
)
VALUES
(
    'EMP004',
    'Hassan Raza',
    'hassan@bonanza.com',
    '12345',
    5,
    'Employee'
);



INSERT INTO Employees
(
    Employee_ID,
    Employee_Name,
    Email,
    Password,
    Department_ID,
    Role
)
VALUES
(
    'EMP005',
    'Ayesha Noor',
    'ayesha@bonanza.com',
    '12345',
    3,
    'Employee'
);


COMMIT;

select * from employees;



INSERT INTO Employees
(
    Employee_ID,
    Employee_Name,
    Email,
    Password,
    Department_ID,
    Role
)
VALUES
(
    'EMP006',
    'Abdul Raza',
    'abdul@bonanza.com',
    '12345',
    1,
    'Employee'
);
COMMIT;
select 
DESC COMPLAINTS;

SELECT * from complaints;