--2. 33 Cap nhat lai gia tri truong Ngaychuyenhang cua nhung ban ghi co ngay chuyen hang chua xac dinh(NULL)

--trong bang Dondathang bang voi gia tri cua truong Ngaydathang

/*Update Dondathang

set ngaychuyenhang=ngaydathang

Where ngaychuyenhang is null*/

--2. 34 Tang so luong hang cua nhung mat hang do cong ty Vinamilk cung cap len gap doi.

/*Update mathang

Set soluong=soluong*2

From mathang, nhacungcap

Where mathang.macongty=nhacungcap.macongty and Tencongty like'Vinamilk'*/

--2. 35 Cap nhat gia tri cua truong Noigiaohang trong bang Dondathang bang dia chi cua khach hang

--doi voi nhung don dat hang chua xac dinh duoc noi giao hang (gia tri truong Noigiaohang ban Null)

/*Update Dondathang

Set Noigiaohang= Diachi

From Khachhang, Dondathang

Where Khachhang.makhachhang=dondathang.makhachhang and noigiaohang is null*/

--2. 36 Cap nhat lai du lieu trong bang Khachhang sao cho neu ten cong ty va ten giao dich cua khach hang trung voi ten cong ty

--va ten giao dich cua mot nha cung cap nao do thi dia chi, dien thoai, fax va email phai giong nhau.

/*Update Khachhang

Set khachhang.diachi=nhacungcap.diachi, khachhang.dienthoai=nhacungcap.dienthoai, khachhang.fax=nhacungcap.fax,

khachhang.email=nhacungcap.email

From Khachhang, Dondathang, Chitietdathang, Mathang, Nhacungcap

Where Khachhang.makhachhang=dondathang.makhachhang and dondathang.sohoadon=chitietdathang.sohoadon

and chitietdathang.mahang=mathang.mahang and mathang.macongty=nhacungcap.macongty

and khachhang.tencongty like nhacungcap.tencongty and khachhang.tengiaodich like nhacungcap.tengiaodich*/

--2. 37 Tang luong len gap ruoi cho nhung nhan vien ban duoc so luong hang nhieu hon 100 trong nam 2003.

/*Update nhanvien

Set luongcoban=luongcoban * 1.5

From nhanvien, dondathang, chitietdathang

Where nhanvien.manhanvien=dondathang.manhanvien and dondathang.sohoadon=chitietdathang.sohoadon

and soluong>100*/

--2. 38 Tang phu cap len bang 50% luong cho nhung nhan vien ban duoc hang nhieu nhat.

/*Update nhanvien

Set phucap=phucap+phucap*0.5

From nhanvien, dondathang, chitietdathang

Where nhanvien.manhanvien=dondathang.manhanvien and dondathang.sohoadon=chitietdathang.sohoadon

and soluong=(select max(soluong) from chitietdathang)*/

--2. 39 Giam 25% luong cua nhung nhan vien trong nam 2010 khong lap duoc bat ky don dat hang nao.

/*Update nhanvien

Set luongcoban=luongcoban-luongcoban*0.25

From nhanvien left join dondathang on nhanvien.manhanvien=dondathang.manhanvien

where year( ngaylamviec)like '2010' and dondathang.manhanvien is null*/

--2. 40 Gia su trong bang Dondathang co them truong Sotien cho biet so tien ma khach hang phai tra trong moi don dat hang.

--Hay tinh gia tri cho truong nay.

/*alter table dondathang

add sotien numeric*/

/*update dondathang

set sotien=soluong*giaban-soluong*giaban*mucgiamgia/100

from dondathang left join chitietdathang on dondathang.sohoadon=chitietdathang.sohoadon*/