Kteam Q&A Community

Cộng đồng hỏi đáp về các vấn đề trong lập trình, công nghệ thông tin.

1

HELP!!! bài tập SQL SERVER

Giúp mình bài tập này với ạ. Mình không biết cách đưa cách giải vào trong code vì đề bài hơi rối chút ạ.

Đề bài: Tính trung bình số lượng đề án mỗi người theo từng phòng ban

Source code:

create database QuanLyNhanVien
create table NhanVien 
(
MaNhanVien char (5),
TenNhanVien nvarchar (30),
NgaySinh datetime,
Luong float,
MaPhongBan char (5),
primary key (MaNhanVien)
);

create table PhongBan
(
MaPhongBan char(5),
TenPhongBan nvarchar (30),
MaTruongPhong char (5),
primary key (MaPhongBan),
);

create table DeAn
(
MaDeAn char(5),
TenDeAn nvarchar (50),
NganSach float,
MaPhongBan char(5),
primary key (MaDeAn)
);

create table ThamGia 
(
MaNhanVien char (5),
MaDeAn char (5),
NhiemVu nvarchar (50),
ThoiGian int,
primary key (MaNhanVien, MaDeAn)
);



alter table DeAn add constraint FK_DeAn_PhongBan
foreign key (MaPhongBan) references PhongBan(MaPhongBan);

alter table ThamGia add constraint FK_ThamGia_NhanVien
foreign key (MaNhanVien) references NhanVien(MaNhanVien);

alter table ThamGia add constraint FK_ThamGia_DeAn
foreign key (MaDeAn) references DeAn(MaDeAn);

insert into PhongBan values ('PB1', N'Tổ chức hành chính', 'NV03');
insert into PhongBan values ('PB2', N'Công tác sinh viên', 'NV05');
insert into PhongBan values ('PB3', N'Đào tạo', 'NV01');

insert into NhanVien values ('NV01', N'Đặng Cao Trí', '02/13/1978', 3000000, 'PB1');
insert into NhanVien values ('NV02', N'Hoàng Nhật Nam', '06/28/1957', 2500000, 'PB1');
insert into NhanVien values ('NV03', N'Lê Minh Trí', '03/30/1967', 2500000, 'PB2');
insert into NhanVien values ('NV04', N'Trương Thế Khải', '08/06/1980', 2500000, 'PB2');
insert into NhanVien values ('NV05', N'Vũ Bình Nguyên', '10/11/1981', 3500000, 'PB3');
insert into NhanVien values ('NV06', N'Trần Ngọc Thuy Dương', '02/01/1968', 8000000, 'PB3');
insert into NhanVien values ('NV07', N'Nguyễn Thị Thanh', '05/09/1971', 5500000, 'PB1');
insert into NhanVien values ('NV08', N'Lê Mai Nguyên', '11/09/1978', 4500000, 'PB3');
insert into NhanVien values ('NV09', N'Dương Hà Anh', '05/13/1983', 3000000, 'PB1');
insert into NhanVien values ('NV10', N'Lương Thanh Minh', '02/28/1980', 2500000, 'PB2');

insert into DeAn values ('DA01', N'Đào tạo cán bộ tại nước ngoài', 10000000000, 'PB3');
insert into DeAn values ('DA02', N'Nâng cấp phòng máy thực hành', 500000000, 'PB2');
insert into DeAn values ('DA03', N'Mở hệ đào tạo ITECH', 100000000, 'PB1');

insert into ThamGia values ('NV05', 'DA01', N'Chỉ trì dự án', 12);
insert into ThamGia values ('NV06', 'DA01', N'Thư ký', 12);
insert into ThamGia values ('NV03', 'DA02', N'Chỉ trì dự án', 36);
insert into ThamGia values ('NV04', 'DA02', N'Thư ký', 36);
insert into ThamGia values ('NV01', 'DA03', N'Chỉ trì dự án', 12);
insert into ThamGia values ('NV02', 'DA03', N'Thư ký', 12);
alter table NhanVien add constraint FK_NhanVien_PhongBan
foreign key (MaPhongBan) references PhongBan (MaPhongBan);

alter table PhongBan add constraint FK_PhongBan_NhanVien
foreign key (MaTruongPhong) references NhanVien(MaNhanVien);

1 câu trả lời Thêm câu trả lời

0
Lê Thông đã trả lời 2017-04-07 22:41:07

-- Tìm số đề án của từng phòng ban
SELECT a.MaPhongBan
      ,SUM(ISNULL(t.TongDA,0)) AS SLDA 
FROM NhanVien a join (SELECT MaNhanVien,COUNT(MaDeAn) as TongDA FROM ThamGia 
GROUP BY MaNhanVien) t on a.MaNhanVien = t.MaNhanVien 
group by a.MaPhongBan

-- Tìm số Nv của từng phòng ban
SELECT MaPhongBan,COUNT(MaNhanVien) as SLNhanVien
  FROM NhanVien GROUP BY MaPhongBan

-- Tính trung bình
SELECT a.MaPhongBan,CONVERT(float, TongDA.SLDA)/Convert(float,TongNV.SLNhanVien) as TrungBinh FROM PhongBan a left join 
(SELECT a.MaPhongBan
      ,SUM(ISNULL(t.SLDA,0)) AS SLDA 
FROM NhanVien a join (SELECT MaNhanVien,COUNT(MaDeAn) as SLDA FROM ThamGia 
GROUP BY MaNhanVien) t on a.MaNhanVien = t.MaNhanVien 
group by a.MaPhongBan) TongDA on a.MaPhongBan = TongDA.MaPhongBan
LEFT join (SELECT MaPhongBan,COUNT(MaNhanVien) as SLNhanVien
  FROM NhanVien GROUP BY MaPhongBan) TongNV on a.MaPhongBan = TongNV.MaPhongBan
 

khanhpro20145 đã bình luận 2017-04-08 13:36:03

Cám ơn bạn nhiều nha . Mình chưa được học hàm convert. Không biết là còn cách nào để giải quyết bài này không bạn.

0 bình chọn
Lê Thông đã bình luận 2017-04-10 08:59:02

Cái đó chỉ để cho nó ra số lẻ thôi mà nếu ko nó sẽ ra 0

0 bình chọn

Câu trả lời của bạn

Bạn có thể trả lời câu hỏi này? Hãy chia sẻ nó cho mọi người.

Hủy bỏ hoặc

Chiến dịch

Kteam - Howkteam Free Education