Scribd
Upload
2 views3 pages

sql

The document contains SQL commands for inserting data into tables related to departments, employees, projects, and assignments. It also includes queries to select employees assigned to projects, create a view for departments with the maximum number of employees, and define a function to calculate total salaries for a specific project. Additionally, a stored procedure is created to retrieve employee details based on their ID.

Uploaded by

dattien5643
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as ODT, PDF, TXT or read online on Scribd
2 views3 pages

sql

The document contains SQL commands for inserting data into tables related to departments, employees, projects, and assignments. It also includes queries to select employees assigned to projects, create a view for departments with the maximum number of employees, and define a function to calculate total salaries for a specific project. Additionally, a stored procedure is created to retrieve employee details based on their ID.

Uploaded by

dattien5643
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as ODT, PDF, TXT or read online on Scribd

INSERT INTO PHONGBAN VALUES

('P01', N'Kế toán', 'NV001'),


('P02', N'Nhân sự', 'NV003'),
('P03', N'Kỹ thuật', 'NV005');

INSERT INTO NHANVIEN VALUES


('NV001', N'Nguyễn', N'An', 3000, 'P01'),
('NV002', N'Trần', N'Bình', 2800, 'P01'),
('NV003', N'Lê', N'Chi', 3200, 'P02'),
('NV004', N'Phạm', N'Dũng', 2600, 'P03'),
('NV005', N'Hoàng', N'Em', 3500, 'P03');

INSERT INTO DEAN VALUES


('DA01', N'Phần mềm A'),
('DA02', N'Website B'),
('DA03', N'Hệ thống C');

INSERT INTO PHANCONG VALUES


('NV001', 'DA01'),
('NV002', 'DA01'),
('NV003', 'DA02'),
('NV005', 'DA03');
SELECT DISTINCT [Link], [Link], [Link]
FROM NHANVIEN NV
JOIN PHANCONG PC ON [Link] = [Link]
ORDER BY [Link] ASC;
SELECT MANV
FROM NHANVIEN
WHERE MANV NOT IN (
SELECT MANV FROM PHANCONG
);
CREATE VIEW PHONG_MAX_NV
AS
SELECT [Link], [Link], COUNT([Link]) AS TONG_NV
FROM PHONGBAN PB
JOIN NHANVIEN NV ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) >= ALL (
SELECT COUNT(*)
FROM NHANVIEN
GROUP BY MAPHONG
);
SELECT [Link], [Link],
[Link],
[Link] + ' ' + [Link] AS HOTEN
FROM PHONG_MAX_NV PM
JOIN PHONGBAN PB ON [Link] = [Link]
JOIN NHANVIEN NV ON [Link] = [Link];

CREATE FUNCTION FN_TONG_LUONG_DA (@MADA CHAR(5))


RETURNS INT
AS
BEGIN
DECLARE @TONG INT;

SELECT @TONG = SUM([Link])


FROM NHANVIEN NV
JOIN PHANCONG PC ON [Link] = [Link]
WHERE [Link] = @MADA;

RETURN @TONG;
END;
SELECT dbo.FN_TONG_LUONG_DA('DA03') AS TONG_CHI_PHI;

CREATE PROCEDURE SP_TIM_NV


@MANV CHAR(5)
AS
BEGIN
SELECT *
FROM NHANVIEN
WHERE MANV = @MANV;
END;
EXEC SP_TIM_NV 'NV005';

You might also like