Yam Code

New Paste Browse

USE master
GO
CREATE DATABASE NML02
GO
USE NML02
GO

CREATE TABLE PHONGBAN
(
	MAPHG			INT NOT NULL,
	MAPHG_QUANLY	INT,
	TENPHG			NVARCHAR(50),
	SONV			INT,
	PRIMARY KEY		(MAPHG)
)
GO

CREATE TABLE NHANVIEN
(
	MANV		INT NOT NULL,
	MA_NQL		INT,
	HONV		NCHAR(10),
	TENLOT		NCHAR(10),
	TENNV		NCHAR(10),
	PHONG		INT	
	PRIMARY KEY	(MANV)
)
GO

ALTER TABLE PHONGBAN
ADD CONSTRAINT FK_PHONGBAN_QUANLY	FOREIGN KEY(MAPHG_QUANLY)	REFERENCES PHONGBAN(MAPHG)
GO

ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NHANVIEN_PHONG	FOREIGN KEY (PHONG)		REFERENCES PHONGBAN(MAPHG),
	CONSTRAINT FK_NHANVIEN_QUANLY	FOREIGN KEY (MA_NQL)	REFERENCES NHANVIEN(MANV)
GO

INSERT INTO PHONGBAN VALUES (1,null,N'P.Giám Đốc',1)
INSERT INTO PHONGBAN VALUES (2,1,N'PP.Giám Đốc',1)
INSERT INTO PHONGBAN VALUES (3,1,N'P.Kế Toán',10)
INSERT INTO PHONGBAN VALUES (4,1,N'P.Bán Hàng',50)
INSERT INTO PHONGBAN VALUES (5,1,N'P.Tổ Chức',20)
INSERT INTO PHONGBAN VALUES (6,4,N'P.Bảo Vệ',10)
INSERT INTO PHONGBAN VALUES (7,3,N'P.Tiếp Thị',15)
INSERT INTO PHONGBAN VALUES (8,3,N'P.Bán Hàng',30)
INSERT INTO PHONGBAN VALUES (9,2,N'P.Ngoại Giao',5)
INSERT INTO PHONGBAN VALUES (10,null,N'T.Giám Đốc',1)
GO

INSERT INTO NHANVIEN VALUES (123,null,N'Đinh',N'Bá',N'Tiến',5)
INSERT INTO NHANVIEN VALUES (321,null,N'Trần',N'Anh',N'Minh',5)
INSERT INTO NHANVIEN VALUES (333,null,N'Nguyễn',N'Thanh',N'Tùng',5)
INSERT INTO NHANVIEN VALUES (453,null,N'Trần',N'Thanh',N'Tâm',5)
INSERT INTO NHANVIEN VALUES (454,null,N'Phạm',N'Bình',N'Minh',5)
INSERT INTO NHANVIEN VALUES (666,null,N'Nguyễn',N'Mạnh',N'Hùng',5)
INSERT INTO NHANVIEN VALUES (667,null,N'Nguyễn',N'Thanh',N'Bình',5)
INSERT INTO NHANVIEN VALUES (677,null,N'Phạm',N'Thanh',N'Nguyên',5)
INSERT INTO NHANVIEN VALUES (678,null,N'Nguyễn',N'Xuân',N'Lâm',5)
INSERT INTO NHANVIEN VALUES (777,null,N'Trần',N'Hồng',N'Quang',4)
INSERT INTO NHANVIEN VALUES (888,null,N'Vương',N'Ngọc',N'Quyên',1)
INSERT INTO NHANVIEN VALUES (899,null,N'Phạm',N'Văn',N'Đăng',3)
INSERT INTO NHANVIEN VALUES (987,null,N'Lê',N'Thị',N'Nhàn',4)
INSERT INTO NHANVIEN VALUES (989,null,N'Nguyễn',N'Thị Thu',N'Thảo',3)
INSERT INTO NHANVIEN VALUES (998,null,N'Phạm',N'Thảo',N'Nguyên',3)
INSERT INTO NHANVIEN VALUES (999,null,N'Bùi',N'Thúy',N'Vũ',4)
GO

UPDATE NHANVIEN
SET MA_NQL=333
WHERE MANV=123
GO

UPDATE NHANVIEN
SET MA_NQL=123
WHERE MANV=321
GO

UPDATE NHANVIEN
SET MA_NQL=888
WHERE MANV=333
GO

UPDATE NHANVIEN
SET MA_NQL=333
WHERE MANV=453
GO

UPDATE NHANVIEN
SET MA_NQL=453
WHERE MANV=454
GO

UPDATE NHANVIEN
SET MA_NQL=333
WHERE MANV=666
GO

UPDATE NHANVIEN
SET MA_NQL=666
WHERE MANV=667
GO

UPDATE NHANVIEN
SET MA_NQL=667
WHERE MANV=677
GO

UPDATE NHANVIEN
SET MA_NQL=667
WHERE MANV=678
GO

UPDATE NHANVIEN
SET MA_NQL=987
WHERE MANV=777
GO

UPDATE NHANVIEN
SET MA_NQL=998
WHERE MANV=899
GO

UPDATE NHANVIEN
SET MA_NQL=888
WHERE MANV=987
GO

UPDATE NHANVIEN
SET MA_NQL=998
WHERE MANV=989
GO

UPDATE NHANVIEN
SET MA_NQL=888
WHERE MANV=998
GO

UPDATE NHANVIEN
SET MA_NQL=987
WHERE MANV=999
GO

--Câu 1: tìm nhân viên có mã quản lý null
select MANV, RTRIM(HONV) + ' ' + RTRIM(TENLOT) + ' ' + RTRIM(TENNV) AS HOTEN from NHANVIEN where MA_NQL IS NULL

Paste Settings

Paste Title : [Optional]

Paste Folder : [Optional]

Syntax Highlighting : [Optional]

Paste Expiration : [Optional]

Paste Status : [Optional]

Password : [Optional]

Description: [Optional]

Tags: [Optional]

Encrypt Paste (?)

You are currently not logged in, this means you can not edit or delete anything you paste. Sign Up or Login

Paste Settings

Site Languages