-- Mã lệnh tạo CSDL:
-- create database dvda default character set utf8 default collate utf8_general_ci;
-- use dvda;

-- Tạo bảng và liên kết
create table DONVI(MaDV varchar(16), TenDV varchar(128),
	MaNV varchar(16), NgayBatDau date, primary key(MaDV));
create table DUAN(MaDA varchar(16), TenDA varchar(256),
	DiaDiem varchar(128), MaDV varchar(16), primary key(MaDA));
alter table DUAN add constraint FK_DUAN_DONVI
	foreign key (MaDV) references DONVI(MaDV);

-- Xem cấu trúc bảng - Lưu ý: PostgreSQL không phân biệt chữ hoa và chữ thường
desc DONVI;
desc DUAN;

-- Nhập dữ liệu ban đầu
insert into DONVI (MaDV, TenDV, MaNV, NgayBatDau) values
	('DV1', 'Tài chính', 'NV1', '2020-01-09'),
	('DV2', 'Nhân sự', 'NV5', '2017-07-15'),
	('DV3', 'Kinh doanh', 'NV2', '2018-09-10'),
	('DV4', 'Quản trị', 'NV8', '2018-09-10');
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values
	('DA1', 'Tuyển dụng', 'Cần Thơ', 'DV2'),
	('DA2', 'Quảng cáo trực tuyến', 'Toàn quốc', 'DV3'),
	('DA3', 'Xây dựng phần mềm tài chính', 'Hà Nội', 'DV1'),
	('DA4', 'Qui trình nghiệp vụ', 'Hà Nội', NULL);

-- Xem dữ liệu đã nhập
select * from DONVI;
select * from DUAN;

-- Phép chèn
-- vi phạm ràng buộc miền
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values ('DA10', repeat('Ten du an', 30),'Đà Nẵng','DV3');
-- vi phạm ràng buộc toàn vẹn thực thể
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values (NULL, 'Phát tờ rơi','Đà Nẵng','DV3');
-- vi phạm ràng buộc khóa
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values ('DA2', 'Phát tờ rơi','Đà Nẵng','DV3');
-- vi phạm ràng buộc tham chiếu
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values ('DA5', 'Phát tờ rơi','Đà Nẵng','DV7');
-- phép chèn ok
insert into DUAN (MaDA, TenDA, DiaDiem, MaDV) values ('DA5','Phát tờ rơi','Đà Nẵng','DV3');
-- xem dữ liệu
select * from DUAN;

-- Phép xóa
-- vi phạm toàn vẹn tham chiếu
delete from DONVI where MaDV='DV3';
-- phép xóa ok
delete from DONVI where MaDV='DV4';
-- xem dữ liệu
select * from DONVI;

-- khôi phục trạng thái các quan hệ
delete from DUAN where MaDA='DA5';
insert into DONVI (MaDV, TenDV, MaNV, NgayBatDau) values
	('DV4', 'Quản trị', 'NV8', '2018-09-10');

-- Phép sửa
-- sửa thuộc tính không phải khóa chính hoặc khóa ngoài
update DONVI set NgayBatDau='2021-09-31' where MaDV='DV3'; -- dữ liệu sai, không thuộc miền
update DONVI set NgayBatDau='2021-09-30' where MaDV='DV3'; -- ok
update DONVI set NgayBatDau='2018-09-10' where MaDV='DV3'; -- ok
-- sửa giá trị khóa chính
update DONVI set MaDV='DV6' where MaDV='DV3'; -- vi phạm ràng buộc khóa ngoài
update DONVI set MaDV='DV5' where MaDV='DV4'; -- ok
update DONVI set MaDV='DV4' where MaDV='DV5'; -- ok
-- sửa giá trị khóa ngoài
update DUAN set MaDV='DV7' where MaDA='DA1'; -- vi phạm ràng buộc khóa ngoài
update DUAN set MaDV=NULL where MaDA='DA1'; -- ok
update DUAN set MaDV='DV2' where MaDA='DA1'; -- ok

-- Phép chọn
select * from DUAN where DiaDiem='Hà Nội';

-- Phép chiếu
select TenDA, DiaDiem from DUAN;

-- Sắp xếp kết quả phép chọn và chiếu
select mada, diadiem, tenda from DUAN order by diadiem asc, tenda asc;
select mada, diadiem, tenda from DUAN order by diadiem asc, tenda desc;
select mada, diadiem, tenda from DUAN order by length(diadiem) asc, tenda desc;

-- Đổi tên quan hệ
alter table DONVI rename to DV;
alter table DV rename to DONVI;

-- Đổi tên thuộc tính
alter table DONVI rename column MaDV to MaDonVi;
alter table DONVI rename column MaDonVi to MaDV;

-- Đổi tên kết quả một phép toán quan hệ
create temporary table TenDuAn as select MaDA, TenDA from DUAN where DiaDiem='Hà Nội';
select * from TenDuAn;

-- Tạo quan hệ R
create table R as select * from DONVI where extract(year from ngaybatdau) < 2019;
-- Tạo quan hệ S
create table S as select * from DONVI where extract(year from ngaybatdau) > 2017;
-- xem dữ liệu của R, S
select * from R;
select * from S;

-- Phép hợp
select * from R union select * from S;
-- Phép giao
select * from R intersect select * from S;
-- Phép trừ
select * from R except select * from S;

-- Tích Đề-các
select * from DONVI, DUAN;

-- Phép nối
select * from DONVI inner join DUAN on DONVI.MaDV=DUAN.MaDV;

-- Phép nối tự nhiên
select * from DONVI natural join DUAN;

-- Phép nối ngoài trái
select * from DONVI left join DUAN on DONVI.MaDV=DUAN.MaDV;

-- Phép nối ngoài phải
select * from DONVI right join DUAN on DONVI.MaDV=DUAN.MaDV;

-- Phép nối ngoài đầy đủ
-- 2021-09-02:
-- Lưu ý: MySQL không hỗ trợ phép nối ngoài đầy đủ, tuy nhiên có thể thực hiện phép toán này
-- trong MySQL bằng phép hợp kết quả của các phép toán nối ngoài trái và nối ngoài phải
select * from DONVI left join DUAN on DONVI.MaDV=DUAN.MaDV
union
select * from DONVI right join DUAN on DONVI.MaDV=DUAN.MaDV;

-- Hàm nhóm và phép nhóm
select DiaDiem, count(*) from DUAN group by DIADIEM;
select DiaDiem, count(*) from DUAN group by DIADIEM having count(*)>1;

-- Xóa bảng (nếu cần) để thử lại từ đầu
-- drop table if exists DUAN;
-- drop table if exists DONVI;
-- drop table if exists R;
-- drop table if exists S;
-- drop table if exists TenDuAn;
