CREATE DATABASE Lab5
CREATE TABLE NHANVIEN
(
HONV NVARCHAR(10),
TENLOT NVARCHAR(10),
TENNV NVARCHAR(10),
MANV INT,
NGSINH DATE,
DCHI NVARCHAR(50),
PHAI NVARCHAR(5),
LUONG INT,
MA_NQL INT,
PHG INT,
PRIMARY KEY (MANV),
)
CREATE TABLE PHONGBAN
(
TENPHONG NVARCHAR(10),
MAPHG INT,
TRPHG INT,
NG_NHANCHUC DATE,
PRIMARY KEY (MAPHG),
)
CREATE TABLE DIADIEM_PHONG
(
MAPHG INT,
DIADIEM NVARCHAR(10),
FOREIGN KEY(MAPHG) REFERENCES dbo.PHONGBAN(MAPHG),
)
CREATE TABLE DEAN
(
TENDA NVARCHAR(15),
MADA INT,
DDIEM_DA NVARCHAR(10),
PHONG INT,
PRIMARY KEY (MADA),
FOREIGN KEY(PHONG) REFERENCES dbo.PHONGBAN(MAPHG),
)
CREATE TABLE PHANCONG
(
MA_NVIEN INT,
SODA INT,
THOIGIAN FLOAT(5),
PRIMARY KEY (MA_NVIEN),
FOREIGN KEY(SODA) REFERENCES dbo.DEAN(MADA),
)
CREATE TABLE THANNHAN
(
MA_NVIEN INT,
TENTN NVARCHAR(10),
PHAI NVARCHAR(5),
NGSINH DATE,
QUANHE NVARCHAR(10),
PRIMARY KEY(MA_NVIEN),
)
--Insert data
--Table NHANVIEN
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Dinh',N'BA',N'Tien',123456789,'19550109',N'731 Tran Hung Dao,Q1, TPHCM',N'Nam',30000,333445555,5 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Nguyen',N'Thanh',N'Tung',3333445555,'19451208',N'638 Nguyen Van Cu, Q5, TPHCM',N'Nam',40000,888665555,5 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Bui',N'Thuy',N'Vu',123456789,'19580719',N'332 Nguyen Thai Hoc, Q1, TPHCM',N'Nam',25000,9876654321,4 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Le',N'Thi',N'Nhan',987654321,'19310620',N'291 Ho Van Hue, QPN, TPCHM',N'Nu',43000,888665555,4 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Nguyen',N'Manh',N'Hung',666884444,'19520915',N'975 Ba Ria, Vung Tau',N'Nam',38000,333445555,5 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Tran',N'Thanh',N'Tam',453453453,'19620731',N'543 Mai Thi Luu,Q1,TPHCM',N'Nam',25000,333445555,5 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Tran',N'Hong',N'Quan',987987987,'19590329',N'980 Le Hong Phong, Q10, TPHCM',N'Nam',25000,987654321,4 )
INSERT INTO dbo.NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
VALUES(N'Vuong',N'Ngoc',N'Quyen',888665555,'19271010',N'450 Trung Vuong Ha Noi',N'Nu',55000,NULL,1)
--Table PHONGBAN
INSERT INTO dbo.PHONGBAN(TENPHONG,MAPHG,TRPHG,NG_NHANCHUC)
VALUES(N'Nghien cuu',5,333445555,'19780522')
INSERT INTO dbo.PHONGBAN(TENPHONG,MAPHG,TRPHG,NG_NHANCHUC)
VALUES(N'Dieu hanh',4,987987987,'19850101')
INSERT INTO dbo.PHONGBAN(TENPHONG,MAPHG,TRPHG,NG_NHANCHUC)
VALUES(N'Quan ly',1,888665555,'19710619')
--Table DIADIEM_PHG
INSERT INTO dbo.DIADIEM_PHONG(MAPHG,DIADIEM)
VALUES(1,N'TP HCM')
INSERT INTO dbo.DIADIEM_PHONG(MAPHG,DIADIEM)
VALUES(4,N'HA NOI')
INSERT INTO dbo.DIADIEM_PHONG(MAPHG,DIADIEM)
VALUES(5,N'VUNG TAU')
INSERT INTO dbo.DIADIEM_PHONG(MAPHG,DIADIEM)
VALUES(5,N'')
INSERT INTO dbo.DIADIEM_PHONG(MAPHG,DIADIEM)
VALUES(5,N'TP HCM')
--Table THANNHAN
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(333445555,N'Quang',N'Nu','19760405',N'Con gai')
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(333445555,N'Khang',N'Nam','19731025',N'Con trai')
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(333445555,N'Duong',N'Nu','19480503',N'Vo chong')
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(987654321,N'Dang',N'Nam','19320229',N'Vo chong')
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(123456789,N'Duy',N'Nam','19780101',N'Con trai')
INSERT INTO dbo.THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE)
VALUES(123456789,N'Chau',N'Nu','19781231',N'Con gai')
--Table PHANCONG
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(123456789,1,32.5)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(123456789,2,7.5)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(66688444,3,40.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(453453453,1,20.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(453453453,2,20.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(333444555,3,10.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(333444555,10,10.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(333444555,20,10.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(999888777,30,30.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(999888777,10,10.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(987987987,10,35.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(987987987,30,5.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(987654321,30,20.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(987654321,20,15.0)
INSERT INTO dbo.PHANCONG(MA_NVIEN,SODA,THOIGIAN)
VALUES(888665555,20,NULL)
--Table DEAN
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'San pham X',1,N'VUNG TAU',5)
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'San pham Y',2,N'NHA TRANG',5)
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'San pham Z',3,N'TP HCM',5)
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'Tin hoc hoa',10,N'HA NOI',4)
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'Cap quang',20,N'TP HCM',1)
INSERT INTO dbo.DEAN(TENDA,MADA,DDIEM_DA,PHONG)
VALUES(N'Dao tao',30,N'HA NOI',4)
When I run query it have bug in the picture.
Msg 8115, Level 16, State 2, Line 67
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
Msg 8115, Level 16, State 2, Line 69
Arithmetic overflow error converting expression to data type int.
The statement has been terminated
