CSDL - Bài tập củng cố kiến thức Đại số quan hệ - SQL.

Database Diagram CsdlCongty:

Script csdl công ty:

--drop database if exists CsdlCongty;
create database CsdlCongty;
use CsdlCongty;

create table NHANVIEN (
 HoNV varchar(15),
 Tenlot varchar(15),
 TenNV varchar(15),
 MaNV char(9),
 NgSinh date,
 DChi varchar(30),
 Phai varchar(3),
 Luong real,
 MaNQL char(9),
 Phg int,
 constraint NHANVIEN_PK primary key (MaNV),
 constraint NHANVIEN_FK_NHANVIEN foreign key (MaNQL) references NHANVIEN (MaNV),
 constraint NHANVIEN_DM_Phai check (Phai in ('F', 'f', 'M', 'm'))
);

create table PHONGBAN (
 TenPhg varchar(15),
 MaPhg int,
 TrPhg char(9),
 NgNhanChuc date,
 constraint PHONGBAN_PK primary key (MaPhg),
 constraint PHONGBAN_FK_NHANVIEN foreign key (TrPhg) references NHANVIEN (MaNV)
);

alter table NHANVIEN
add constraint NHANVIEN_FK_PHONGBAN foreign key (Phg) references PHONGBAN (MaPhg);

create table DIADIEM_PHG (
 MaPhg int,
 Diadiem varchar(15),
 constraint DIADIEM_PHG_PK primary key (MaPhg, Diadiem),
 constraint DIADIEM_PHG_FK_PHONGBAN foreign key (MaPhg) references PHONGBAN (MaPhg)
);

create table DUAN (
 TenDA varchar(15),
 MaDA int,
 Diadiem varchar(15),
 Phong int,
 constraint DUAN_PK primary key (MaDA),
 constraint DUAN_FK_PHONGBAN foreign key (Phong) references PHONGBAN (MaPhg)
);

create table PHANCONG (
 MaNV char(9),
 MaDA int,
 Thoigian decimal(6,1),
 constraint PHANCONG_PK primary key (MaNV, MaDA),
 constraint PHANCONG_FK_NHANVIEN foreign key (MaNV) references NHANVIEN (MaNV),
 constraint PHANCONG_FK_DUAN foreign key (MaDA) references DUAN (MaDA)
);

create table THANNHAN (
 MaNV char(9),
 TenTN varchar(15),
 Phai varchar(3),
 NgSinh date,
 Quanhe varchar(8),
 constraint THANNHAN_PK primary key (MaNV, TenTN),
 constraint THANNHAN_FK_NHANVIEN foreign key (MaNV) references NHANVIEN (MaNV),
 constraint THANNHAN_DM_Phai check (Phai in ('M', 'm', 'F', 'f'))
);

-- chen du lieu PHONGBAN
insert into PHONGBAN values ('Nghien cuu',5,null,null);
insert into PHONGBAN values ('Dieu hanh',4,null,null);
insert into PHONGBAN values ('Quan ly',1,null,null);

-- chen du lieu NHANVIEN
insert into NHANVIEN values ('Le','Van','Bo','888665555','1937-11-10','450 Trung Vuong, Ha Noi','M',55000,null,1);
insert into NHANVIEN values ('Phan','Van','Nghia','333445555','1955-12-08','638 Nguyen Van Cu, Q5, TpHCM','M',40000,'888665555',5);
insert into NHANVIEN values ('Nguyen','Bao','Hung','123456789','1965-01-09','731 Tran Hung Dao, Q1, TpHCM','M',30000,'333445555',5);
insert into NHANVIEN values ('Tran',null,'Nam','666884444','1962-09-15','975 Ba Ria Vung Tau','M',38000,'333445555',5);
insert into NHANVIEN values ('Hoang','Kim','Yen','453453453','1972-07-31','543 Mai Thi Luu, Q1, TpHCM','F',25000,'333445555',5);
insert into NHANVIEN values ('Du','Thi','Hau','987654321','1951-06-20','291 Ho Van Hue, QPN, TpHCM','F',43000,'888665555',4);
insert into NHANVIEN values ('Au',null,'Vuong','999887777','1968-07-19','332 Nguyen Thai Hoc, Q1, TpHCM','F',25000,'987654321',4);
insert into NHANVIEN values ('Nguyen','Van','Giap','987987987','1969-03-09','980 Le Hong Phong, Q10, TpHCM','M',25000,'987654321',4);

-- chinh sua du lieu PHONGBAN
update PHONGBAN
set TrPhg='888665555',NgNhanChuc='1981-06-19'
where MaPhg=1;
update PHONGBAN
set TrPhg='987987987',NgNhanChuc='1995-01-01'
where MaPhg=4;
update PHONGBAN
set TrPhg='333445555',NgNhanChuc='1988-05-22'
where MaPhg=5;

-- chen du lieu DIADIEM_PHG
insert into DIADIEM_PHG values (1,'Phu Nhuan');
insert into DIADIEM_PHG values (4,'Go Vap');
insert into DIADIEM_PHG values (5,'Tan Binh');
insert into DIADIEM_PHG values (5,'Phu Nhuan');
insert into DIADIEM_PHG values (5,'Thu Duc');

-- chen du lieu DUAN
insert into DUAN values ('San pham X',1,'Tan Binh',5);
insert into DUAN values ('San pham Y',2,'Thu Duc',5);
insert into DUAN values ('San pham Z',3,'Phu Nhuan',5);
insert into DUAN values ('Tin hoc hoa',10,'Go Vap',4);
insert into DUAN values ('Tai to chuc',20,'Phu Nhuan',1);
insert into DUAN values ('Phuc loi',30,'Go Vap',4);

-- chen du lieu PHANCONG
insert into PHANCONG values ('123456789',1,32.5);
insert into PHANCONG values ('123456789',2,7.5);
insert into PHANCONG values ('666884444',3,40);
insert into PHANCONG values ('453453453',1,20);
insert into PHANCONG values ('453453453',2,20);
insert into PHANCONG values ('333445555',2,10);
insert into PHANCONG values ('333445555',3,10);
insert into PHANCONG values ('333445555',10,10);
insert into PHANCONG values ('333445555',20,10);
insert into PHANCONG values ('999887777',30,30);
insert into PHANCONG values ('999887777',10,10);
insert into PHANCONG values ('987987987',10,35);
insert into PHANCONG values ('987987987',30,5);
insert into PHANCONG values ('987654321',30,20);
insert into PHANCONG values ('987654321',20,15);
insert into PHANCONG values ('888665555',20,null);

-- chen du lieu THANNHAN
insert into THANNHAN values ('333445555','Anh','F','1986-04-05','Con gai');
insert into THANNHAN values ('333445555','The','M','1983-10-25','Con trai');
insert into THANNHAN values ('333445555','Loi','F','1958-05-03','Vo');
insert into THANNHAN values ('987654321','An','M','1942-02-28','Chong');
insert into THANNHAN values ('123456789','Minh','M','1988-01-01','Con trai');
insert into THANNHAN values ('123456789','Anh','F','1988-12-30','Con gai');
insert into THANNHAN values ('123456789','Yen','F','1967-05-05','Vo');

Câu 1. Chiếu: Cho ds nhân viên gồm họ tên, phái:
→ ∏MANV, HONV, TENLOT, TENNV, PHAI (NHANVIEN)
→  SELECT MANV, HONV, TENLOT, TENNV, PHAI
FROM NHANVIEN

Câu 2. Chọn: Cho ds nhân viên gồm họ tên, phái của các nv thuộc phòng số 5.
→ ∏MANV, HONV, TENLOT, TENNV, PHAI (σPHG=5 (NHANVIEN))
→ SELECT MANV, HONV, TENLOT, TENNV, PHAI
FROM NHANVIEN
WHERE PHG = 5
{t.MANV, t.HONV, t.TENNV, t.PHAI| NHANVIEN(t) ∧ t.PHG = 5}
Danh sach ho ten phai cua cac nv thuoc phong ‘nghien cuu’
{t.MANV, t.HONV, t.TENNV, t.PHAI| NHANVIEN(t) ∧ (∃pb) PHONGBAN (pb) ∧
pb.TENPHG = ‘NGHIEN CUU’ ∧ pb.MAPHG = t.PHG}
Câu 3. Hội: Cho ds các mã nhân viên có tham gia đề án số 4 hoặc 5.
∏MANV (σMADA =4 (PHANCONG)) ∪ ∏MANV (σMADA =5 (PHANCONG))
(SELECT MANV
FROM PHANCONG
WHERE MADA = 4)
UNION
(SELECT MANV
FROM PHANCONG
WHERE MADA = 5)
SELECT MANV
FROM PHANCONG
WHERE (MADA = 4 OR MADA = 5)
{p.MA_NVIEN | PHANCONG (p) ∧ (p.MADA = 4 ∨ p.MADA = 5)}
Câu 4. Giao: Cho ds các mã nhân viên vừa có tham gia đề án số 4 vừa có tham gia đề ánsố 5.
∏MANV (σMADA =4 (PHANCONG)) ∩ ∏MANV (σMADA =5 (PHANCONG))

... sẽ cập nhật