SQLQueryQLDA.sql

CREATE DATABASE QLDA 
GO
USE QLDA
GO
---CREATE TABLES
CREATE TABLE DEAN
(	
	TEN_DEAN NVARCHAR(15),
	MA_DEAN INT NOT NULL PRIMARY KEY,
	DIADIEM_DEAN NVARCHAR(15) NOT NULL,
	PHONG INT NOT NULL,
)

CREATE TABLE PHONGBAN
(
	TEN_PHONG NVARCHAR(15),
	MA_PHONG INT NOT NULL PRIMARY KEY,
	TRUONG_PHONG NVARCHAR(9),
	NGAY_NHAN_CHUC DATE
)

CREATE TABLE DIADIEM_PHONG
(
	MA_PHONG INT NOT NULL,
	DIADIEM NVARCHAR(15),
	PRIMARY KEY (MA_PHONG, DIADIEM)
)

CREATE TABLE NHANVIEN
(
	HO_NHANVIEN NVARCHAR(15),
	TENLOT NVARCHAR(15),
	TEN_NHANVIEN NVARCHAR(15),
	MA_NHANVIEN NVARCHAR(9) NOT NULL PRIMARY KEY,
	NGAYSINH DATE,
	DIACHI NVARCHAR(30),
	PHAI NVARCHAR(3),
	EMAIL NVARCHAR(30),
	SO_DIENTHOAI NVARCHAR(10),
	LUONG FLOAT,
	MA_NQL NVARCHAR(9),
	PHONG INT NOT NULL
)

CREATE TABLE THANNHAN
(
	MA_NHANVIEN NVARCHAR(9) NOT NULL,
	TEN_THANNHAN NVARCHAR(15),
	PHAI NVARCHAR(3),
	NGAYSINH DATE,
	QUANHE NVARCHAR(15),
)

CREATE TABLE CONGVIEC
(
	MA_DEAN INT NOT NULL,
	STT INT NOT NULL,
	TEN_CONG_VIEC NVARCHAR(50),
	PRIMARY KEY (MA_DEAN, STT)
)

CREATE TABLE PHANCONG
(
	MA_NHANVIEN NVARCHAR(9) NOT NULL,
	MA_DEAN INT NOT NULL,
	STT INT NOT NULL,
	THOIGIAN FLOAT,
	PRIMARY KEY (MA_NHANVIEN, MA_DEAN, STT)
)

---Cai dat rang buoc khoa ngoai cho cac bang
ALTER TABLE DEAN
ADD CONSTRAINT FK_DEAN_PHONG FOREIGN KEY (PHONG) REFERENCES PHONGBAN (MA_PHONG)

ALTER TABLE PHONGBAN
ADD CONSTRAINT FK_PHONGBAN_NHANVIEN FOREIGN KEY (TRUONG_PHONG) REFERENCES NHANVIEN (MA_NHANVIEN)

ALTER TABLE DIADIEM_PHONG
ADD CONSTRAINT FK_DIADIEM_PHONG_PHONGBAN FOREIGN KEY (MA_PHONG) REFERENCES PHONGBAN (MA_PHONG)

ALTER TABLE CONGVIEC
ADD CONSTRAINT FK_CONGVIEC_DEAN FOREIGN KEY (MA_DEAN) REFERENCES DEAN (MA_DEAN)

ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NHANVIEN_NHANVIEN FOREIGN KEY (MA_NQL) REFERENCES NHANVIEN(MA_NHANVIEN)

ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NHANVIEN_PHONGBAN FOREIGN KEY (PHONG) REFERENCES PHONGBAN (MA_PHONG)

ALTER TABLE PHANCONG 
ADD CONSTRAINT FK_PHANCONG_DEAN FOREIGN KEY (MA_DEAN) REFERENCES DEAN (MA_DEAN)

ALTER TABLE PHANCONG
ADD CONSTRAINT FK_PHANCONG_NHANVIEN FOREIGN KEY (MA_NHANVIEN) REFERENCES NHANVIEN (MA_NHANVIEN)

ALTER TABLE THANNHAN
ADD CONSTRAINT FK_THANNHAN_NHANVIEN FOREIGN KEY (MA_NHANVIEN) REFERENCES NHANVIEN (MA_NHANVIEN)

BEGIN /** NHANVIEN **/
	ALTER TABLE NHANVIEN
	NOCHECK CONSTRAINT ALL
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Đinh', N'Bá', N'Tiến', '009', '02/11/1990', N'119, Cống Quỳnh, TP.HCM', N'Nam', 'dinhtien@gmail.com','0123357441', 30000000, '005', 5)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Nguyễn', N'Thanh', N'Tùng', '005', '08/20/1992', N'222, Nguyễn Văn Cừ, TP.HCM', N'Nam', 'thanhtung@gmail.com','0147852654', 40000000, '006', 5)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Bùi', N'Ngọc', N'Hằng', '007', '03/11/1984', N'332, Nguyễn Thái Học, TP.HCM', N'Nam', 'hangbui@gmail.com','0258741325', 25000000, '001', 4)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Lê', N'Quỳnh', N'Như', '001', '02/01/1997', N'291, Hồ Văn Huê, TP.HCM', N'Nữ', 'lequynhnhu@gmail.com','0268753268', 43000000, '006', 4)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Nguyễn', N'Mạnh', N'Hùng', '004', '03/04/1997', N'95, Bà Rịa - Vũng Tàu', N'Nam', 'hungnguyen@gmail.com','0984523456', 38000000, '005', 5)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Trần', N'Thanh', N'Tâm', '003', '05/04/1987', N'34, Mai Thị Lự, TP.HCM', N'Nam', 'thanhtamtran@gmail.com','0758663215', 25000000, '005', 5)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, MA_NQL, PHONG)
	VALUES (N'Trần', N'Hồng', N'Quang', '008', '09/01/1997', N'45, Lê Hồng Phong, TP.HCM', N'Nam', 'hongquang@gmail.com','0817456885', 25000000, '001', 4)
INSERT INTO NHANVIEN (HO_NHANVIEN, TENLOT, TEN_NHANVIEN, MA_NHANVIEN, NGAYSINH, DIACHI, PHAI, EMAIL, SO_DIENTHOAI, LUONG, PHONG)
	VALUES (N'Phạm', N'Văn', N'Vinh', '006', '01/01/1995', N'45, Trưng Vương',N'Nam','vinhpham@gmail.com','0975686853', 55000000, 1)
	ALTER TABLE NHANVIEN
	CHECK CONSTRAINT ALL
END

BEGIN /** PHONGBAN **/
	ALTER TABLE PHONGBAN
	NOCHECK CONSTRAINT ALL
	INSERT INTO PHONGBAN (TEN_PHONG, MA_PHONG, TRUONG_PHONG, NGAY_NHAN_CHUC)
	VALUES (N'Nghiên cứu', 5, '005', '05/22/2021')
	INSERT INTO PHONGBAN (TEN_PHONG, MA_PHONG, TRUONG_PHONG, NGAY_NHAN_CHUC)
	VALUES (N'Điều hành', 4, '008', '01/01/2020')
	INSERT INTO PHONGBAN (TEN_PHONG, MA_PHONG, TRUONG_PHONG, NGAY_NHAN_CHUC)
	VALUES (N'Quản lý', 1, '006', '06/19/2020')
	ALTER TABLE PHONGBAN
	CHECK CONSTRAINT ALL
END

BEGIN /** DEAN **/
	ALTER TABLE DEAN
	NOCHECK CONSTRAINT ALL
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Sản phẩm X', 1, N'Vũng Tàu', 5)
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Sản phẩm Y', 2, N'Nha Trang', 5)
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Sản phẩm Z', 3, N'TP.HCM', 5)
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Tin học hóa', 10, N'Hà Nội', 4)
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Cáp quang', 20, N'TP.HCM', 1)
	INSERT INTO DEAN (TEN_DEAN, MA_DEAN, DIADIEM_DEAN, PHONG)
	VALUES (N'Đào tạo', 30, N'Hà Nội', 4)
	ALTER TABLE DEAN
	CHECK CONSTRAINT ALL
END

BEGIN /** THANNHAN **/
	ALTER TABLE THANNHAN
	NOCHECK CONSTRAINT ALL
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('005', N'Trinh', N'Nữ', '04/05/2013', N'Con gái')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('005', N'Khang', N'Nam', '10/25/2014', N'Con trai')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('005', N'Phương', N'Nữ', '05/03/1993', N'Vợ chồng')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('001', N'Minh', N'Nam', '02/29/1995', N'Vợ chồng')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('009', N'Tiến', N'Nam', '01/01/2014', N'Con trai')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('009', N'Châu', N'Nữ', '12/30/2015', N'Con gái')
	INSERT INTO THANNHAN (MA_NHANVIEN,TEN_THANNHAN, PHAI, NGAYSINH, QUANHE)
	VALUES ('009', N'Phương', N'Nữ', '05/05/1989', N'Vợ chồng')
	ALTER TABLE THANNHAN
	CHECK CONSTRAINT ALL
END

BEGIN /** DIADIEM_PHONG **/
	ALTER TABLE DIADIEM_PHONG
	NOCHECK CONSTRAINT ALL
	INSERT INTO DIADIEM_PHONG(MA_PHONG, DIADIEM)
	VALUES (1, N'TP.HCM')
	INSERT INTO DIADIEM_PHONG(MA_PHONG, DIADIEM)
	VALUES (4, N'Hà Nội')
	INSERT INTO DIADIEM_PHONG(MA_PHONG, DIADIEM)
	VALUES (5, N'Vũng Tàu')
	INSERT INTO DIADIEM_PHONG(MA_PHONG, DIADIEM)
	VALUES (5, N'Nha Trang')
	INSERT INTO DIADIEM_PHONG(MA_PHONG, DIADIEM)
	VALUES (5, N'TP.HCM')
	ALTER TABLE DIADIEM_PHONG
	CHECK CONSTRAINT ALL
END

BEGIN /** PHANCONG **/
	ALTER TABLE PHANCONG
	NOCHECK CONSTRAINT ALL
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('009', 1, 1, 32)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('009', 2, 2, 8)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('004', 3, 1, 40)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('003', 1, 2, 20.0)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('003', 2, 1, 20.0)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('008', 10, 1, 35)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('008', 30, 2, 5)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('001', 30, 1, 20)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('001', 20, 1, 15)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('006', 20, 1, 30)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('005', 3, 1, 10)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('005', 10, 2, 10)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('005', 20, 1, 10)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('007', 30, 2, 30)
	INSERT INTO PHANCONG (MA_NHANVIEN, MA_DEAN, STT, THOIGIAN)
	VALUES ('007', 10, 2, 10)
	ALTER TABLE PHANCONG
	CHECK CONSTRAINT ALL
END

BEGIN /** CONGVIEC **/
	ALTER TABLE CONGVIEC
	NOCHECK CONSTRAINT ALL
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (1, 1, N'Thiết kế sản phẩm X')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (1, 2, N'Thử nghiệm sản phẩm X')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (2, 1, N'Sản xuất sản phẩm Y')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (2, 2, N'Quảng cáo sản phẩm Y')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (3, 1, N'Khuyến mãi sản phẩm Z')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (10, 1, N'Tin học hóa phòng nhân sự')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (10, 2, N'Tin học hóa phòng kinh doanh')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (20, 1, N'Lắp đặt cáp quang')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (30, 1, N'Đào tạo nhân viên Marketing')
	INSERT INTO CONGVIEC (MA_DEAN, STT, TEN_CONG_VIEC)
	VALUES (30, 2, N'Đào tạo chuyên viên thiết kế')
	ALTER TABLE CONGVIEC
	CHECK CONSTRAINT ALL
END