create database QL_NV
use QL_NV
create table NHANVIEN(
MaNV char(10) primary key,
Hoten varchar(30),
DiaChi varchar(50),
SDT char(10),
NgaySinh date,
GT char(3),
HSL float
)
create table Hang(
MaHang char(10) primary key,
TenHang char(30),
NhaSX char(30),
TGianBaoHanh date
)
create table KHACHHANG(
MaKH char(10) primary key,
TenKH char(30),
CMT char(12),
DiaChi varchar(50),
SoDienThoai char(10),
Email varchar(30)
)
create table HOADONXUAT(
MaHD char(10),
MaKH char(10),
NgayLapHD datetime,
MaNV char(10),
PhuongThucTT varchar(30)
constraint R_HOA primary key (MaHD)
foreign key (MaKH) references KHACHHANG(MaKH),
foreign key (MaNV) references NHANVIEN(MaNV)
)
create table CT_HOADON(
MaHD char(10),
MaHang char(10),
SoLuongMua int,
DonGia int
foreign key (MaHD) references HOADONXUAT(MaHD),
foreign key (MaHang) references HANG(MaHang),
)
insert into NHANVIEN values
('1','Tran Van A','Thai Binh','0123456789','2000-10-10','Nam',1.5),
('2','Tran Van B','Thai Binh','9876543210','2000-10-10','Nam',2)
insert into HANG values
('at1','a','abc','2025-1-1'),
('at2','b','abc','2025-1-1')
insert into KHACHHANG values
('1','Tran Van C','111111','Thai Binh','1111111111','
[email protected]'),
('2','Tran Van D','222222','Thai Binh','2222222222','
[email protected]')
insert into HOADONXUAT values
('1','1','2024-12-1','1','Tien mat'),
('2','1','2024-12-1','1','Tien mat')
insert into CT_HOADON values
('1','at1',2,100000),
('1','at1',2,100000)
CREATE VIEW thongtin_nv_nu as
select * from NHANVIEN where GT='Nu'
create view thongtin(manv,hoten,gioitinh,tuoi) as
select MaNV,Hoten,GT,datediff(year,ngaysinh,getdate()) from NHANVIEN
create view thongtin_KH_mua as
select TenKH from (KHACHHANG join HOADONXUAT on KHACHHANG.MaKH=HOADONXUAT.MaKH)
join CT_HOADON on HOADONXUAT.MaHD=CT_HOADON.MaHD
where DonGia > 10000000
create view thongtin_nv_ban as
select NHANVIEN.MaNV,Hoten from (NHANVIEN join HOADONXUAT on
NHANVIEN.MaNV=HOADONXUAT.MaNV)
join CT_HOADON on HOADONXUAT.MaHD=CT_HOADON.MaHD
group by NHANVIEN.MaNV,Hoten
having sum(DonGia) > 20000000
