-1
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

Thanks for helping enter image description here

0

1 Answer 1

1

change your data type int to bigint for column MANV,PHG

CREATE TABLE NHANVIEN
(
    HONV NVARCHAR(10),
    TENLOT NVARCHAR(10),
    TENNV NVARCHAR(10),
    MANV bigint,
    NGSINH DATE,
    DCHI NVARCHAR(50),
    PHAI NVARCHAR(5),
    LUONG INT,
    MA_NQL INT,
    PHG bigint,
    PRIMARY KEY (MANV),

)
Sign up to request clarification or add additional context in comments.

4 Comments

Msg 2714, Level 16, State 6, Line 2 There is already an object named 'NHANVIEN' in the database. I don't have any NHANVIEN in database
@ĐứcLong then drop the table and create again or alter the data type for those two column
Incorrect syntax near '('. I get one more bug in 'CREATE TABLE NHANVIEN'
@ĐứcLong remove last comma thar are showing after PRIMARY KEY (MANV)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.