ADB_Labs

-- Q.NO.1
CREATE TABLE DEPARTMENT(
    ID INT IDENTITY(1,1),
    Dept_Name VARCHAR(50) NOT NULL,
    Dept_Location VARCHAR(50) NOT NULL,
    CONSTRAINT PK_DEPARTMENT_DNUMBER PRIMARY KEY(ID)
)
GO

-- Insert data in departments
INSERT INTO DEPARTMENT (Dept_Name, Dept_Location)
VALUES
    ('Human Resources', 'Kathmandu'),
    ('Marketing', 'Pokhara'),
    ('Finance', 'Biratnagar'),
    ('Engineering', 'Lalitpur');

GO

CREATE TABLE EMPLOYEE(
    ID INT IDENTITY(1,1),
    Fname VARCHAR(50) NOT NULL,
    Mname VARCHAR(50),
    Lname VARCHAR(50) NOT NULL,
    FullName AS CONCAT(Fname, ' ', Lname),
    Gender CHAR(1) NOT NULL,
    DOB DATE NOT NULL,
    [Address] VARCHAR(50) NOT NULL,
    Salary DECIMAL(18,2) NOT NULL,
    Supervisor_ID INT NOT NULL,
    Dept_ID INT,
    CONSTRAINT PK_EMPLOYEE_SSN PRIMARY KEY(ID),
    CONSTRAINT FK_EMPLOYEE_DEPT_ID FOREIGN KEY(Dept_ID) REFERENCES DEPARTMENT(ID)
)
GO

-- Insert data in employee
INSERT INTO EMPLOYEE (Fname, Mname, Lname, Gender, DOB, [Address], Salary, Supervisor_ID, Dept_ID)
VALUES
    ('Ramesh', 'Kumar', 'Shrestha', 'M', '1990-05-15', 'Kathmandu, Nepal', 55000.00, 1, 1)
GO

ALTER TABLE EMPLOYEE
ADD CONSTRAINT FK_EMPLOYEE_SUPERVISOR_ID FOREIGN KEY(Supervisor_ID) REFERENCES EMPLOYEE(ID)
GO

INSERT INTO EMPLOYEE (Fname, Mname, Lname, Gender, DOB, [Address], Salary, Supervisor_ID, Dept_ID)
VALUES
    ('Sita', '', 'Rai', 'F', '1985-12-10', 'Pokhara, Nepal', 48000.00, 1, 2),
    ('Hari', 'Bahadur', 'Tamang', 'M', '1993-08-25', 'Lalitpur, Nepal', 60000.00, 1, 3),
    ('Gita', 'Devi', 'Thapa', 'F', '1988-03-20', 'Biratnagar, Nepal', 52000.00, 1, 4),
    ('Nabin', 'Shankar', 'Bista', 'M', '1995-06-05', 'Kathmandu, Nepal', 56000.00, 1, 1);
GO

CREATE TABLE PROJECT(
    ID INT IDENTITY(1,1),
    Pname VARCHAR(50) NOT NULL,
    Dept_ID INT,
    CONSTRAINT PK_PROJECT_PNUM_PLOC PRIMARY KEY(ID),
    CONSTRAINT FK_PROJECT_DEPT_ID FOREIGN KEY(Dept_ID) REFERENCES DEPARTMENT(ID)
)
GO

-- Inserting values into the PROJECT table
INSERT INTO PROJECT (Pname, Dept_ID)
VALUES
    ('Project A', 1),
    ('Project B', 2),
    ('Project C', 1),
    ('Project D', 3);
GO

CREATE TABLE [DEPENDENT](
    ID INT IDENTITY(1,1),
    EMP_ID INT NOT NULL,
    Dependent_Name VARCHAR(50) NOT NULL,
    Gender CHAR(1) NOT NULL,
    DOB DATE NOT NULL,
    Relationship VARCHAR(50) NOT NULL,
    CONSTRAINT PK_DEPENDENT_ID PRIMARY KEY(ID),
    CONSTRAINT FK_DEPENDENT_EMP_ID FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(ID)
)
GO

-- Inserting one dependent for each employee
INSERT INTO [DEPENDENT] (EMP_ID, Dependent_Name, Gender, DOB, Relationship)
VALUES
    (1, 'Sarita Shrestha', 'F', '2010-02-28', 'Daughter'),
    (2, 'Amit Rai', 'M', '2008-07-15', 'Son'),
    (3, 'Sushma Tamang', 'F', '2015-09-10', 'Daughter'),
    (4, 'Rohan Thapa', 'M', '2012-04-05', 'Son'),
    (5, 'Sima Bista', 'F', '2017-11-20', 'Daughter');
GO

SELECT TOP 1 FullName, D.Dept_Name, P.Pname, E.Salary
FROM EMPLOYEE E
        INNER JOIN DEPARTMENT D ON E.Dept_ID = D.ID
        INNER JOIN PROJECT P ON P.Dept_ID = D.ID
ORDER BY E.Salary DESC

==========================================================================================================================

-- Q.NO. 2

CREATE TABLE EMPLOYEE(
    ID INT IDENTITY(1,1)
    , [NAME] VARCHAR(50)
    , OCCUPATION VARCHAR(50)
    , WORKING_DATE DATE
    , WORKING_HOUR INT
    , CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY(ID)
)
GO


CREATE TRIGGER BEFORE_EMPLOYEE_INSERT
ON EMPLOYEE
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EMPLOYEE([NAME], OCCUPATION, WORKING_DATE, WORKING_HOUR)
    SELECT i.[name], i.OCCUPATION, i.WORKING_DATE, (CASE WHEN i.WORKING_HOUR >0 THEN i.WORKING_HOUR ELSE 0 END)
    FROM inserted i
END
GO

SELECT * FROM EMPLOYEE

INSERT INTO EMPLOYEE([NAME], OCCUPATION, WORKING_DATE, WORKING_HOUR)
VALUES  ('John Doe', 'Engineer', '2023-09-28', 8),
        ('Jane Smith', 'Manager', '2023-09-28', -2),
        ('Alice Johnson', 'Designer', '2023-09-27', 6),
        ('Bob Brown', 'Technician', '2023-09-27', 0),
        ('Eva Davis', 'Analyst', '2023-09-26', -4);