CREATE DATABASE EmploymentManagement
GO

USE EmploymentManagement
GO

CREATE TABLE Department
(
    DepartmentID CHAR(5) PRIMARY KEY NOT NULL IDENTITY,
    Name VARCHAR(50) NOT NULL
)

CREATE TABLE Employee
(
    EmployeeID CHAR(5) PRIMARY KEY NOT NULL IDENTITY,
    Name VARCHAR(50) NOT NULL,
    DepartmentFK CHAR(5) FOREIGN KEY REFERENCES dbo.Department(DepartmentID),
    BirthDay DATETIME DEFAULT('1980-01-10')
)

DROP TABLE dbo.Department
DROP TABLE dbo.Employee
-- INSERT RECORDS
INSERT dbo.Department ( Name )
VALUES  ('Name'  ),
        ('Humman resource'  ),
        ('Research'  ),
        ('Business D'  )

INSERT dbo.Employee(Name ,DepartmentFK ,BirthDay)
VALUES  ( 'Nguyen Dai Hao' , 'DPM01' , '1999-09-09' ),
        ( 'Bùi Kim Quyên' , 'DPM01' , '1989-11-12' ),
        ( 'Võ An Phước Thiện' , 'DPM02' , '1985-09-23' ),
        ( 'Phan Vinh Bính' , 'DPM02' , '1989-05-18' ),
        ( 'Phan Huỳnh Ngọc Dung' , 'DPM03' , '1986-10-19' ),
        ( 'Nguyễn Thiện Toàn' , 'DPM03' , '1986-03-09' ),
        ( 'Huỳnh Kim Hoàng' , 'DPM04' , '1989-10-19' )

/*
4) Tạo 1 view có tên ViewEmployees hiển thị thông tin của tất cả nhân viên,
gồm EmployeeID, Name, BirthDate và DepartmentName (DepartmentName là tên phòng ban mà nhân viên đó làm việc).
Sau đó, hiển thị dữ liệu của view này với 1 cột DepartmentName được sắp xếp tăng dần.
*/
CREATE VIEW [ViewEmployees]
AS SELECT TOP (100) PERCENT E.EmployeeID, E.Name, E.BirthDay, D.Name AS 'DepartmentName' FROM dbo.Employee E, dbo.Department D
WHERE E.DepartmentFK = D.DepartmentID
ORDER BY DepartmentName ASC

DROP VIEW [ViewEmployees]

SELECT * FROM [ViewEmployees]

-- 5) Tạo 1 câu lệnh select để hiển thị  EmployeeID, Name và Age của tất cả nhân viên mà sinh sau 1975.
SELECT EmployeeID, Name, (YEAR(GETDATE()) - YEAR(BirthDay)) AS 'Age' FROM dbo.Employee

-- 6) Tạo 1 hàm thủ tục  stored procedure có tên ProcDeleteDepartment với tham số là @departmentId.
--    Hàm thủ tục này xóa phòng ban với DepartmentID mà được nhập vào từ tham số  @departmentId. Viết lời gọi thủ tục
ALTER PROC ProcDeleteDepartment @departmentID CHAR(5)
AS
BEGIN
    IF EXISTS(SELECT * FROM dbo.Department WHERE DepartmentID = @departmentID)
        BEGIN
            BEGIN TRY
                BEGIN TRAN
                    DELETE FROM dbo.Employee WHERE DepartmentFK = @departmentID
                    DELETE FROM dbo.Department WHERE DepartmentID = @DepartmentID
                    COMMIT
                    PRINT 'Đã xóa thành công Department ID là ' + @departmentID
            END TRY
            BEGIN CATCH
                ROLLBACK
            END CATCH
        END
    ELSE
        BEGIN
            PRINT N'○ Mã Department này không tồn tại!'
        END
END
GO


EXEC ProcDeleteDepartment '1'

SELECT * FROM dbo.Employee
SELECT * FROM dbo.Department