Bảng NHACUNGCAP lưu trữ dữ liệu về các đối
tác cung cấp hàng cho công ty
Bảng MATHANG lưu trữ dữ liệu về các mặt hàng
hiện có trong công ty.
Bảng LOAIHANG phân loại các mặt hàng hiện có.
Bảng NHANVIEN có dữ liệu là thông tin về các
nhân viên làm việc trong công ty.
Bảng KHACHHANG được sử dụng để lưu giữ thông
tin về các khách hàng của công ty.
Khách hàng đặt hàng cho cty
thông qua các đơn đặt hàng. Thông tin chung về các đơn đạt hàng được lưu trữ
trong DONDATHANG (mỗi một đơn đặt hàng phải do một nhân viên của cty lập và do
đó bảng này có quan hệ với bảng NHANVIEN)
Thông tin chi tiết của các
đơn đặt hàng (đặt mua hàng gì, số lượng, giá cả, …) được lưu trữ chi tiết trong
bảng CHITIETDATHANG. Bảng này có quan hệ với DONDATHANG và MATHANG.
Sử dụng câu lệnh sau để tạo
cơ sở dữ liệu:
CREATE TABLE nhacungcap
(
macongty NVARCHAR(10) NOT NULL
CONSTRAINT
pk_nhacungcap
PRIMARY KEY(macongty),
tencongty NVARCHAR(40) NOT NULL,
tengiaodich NVARCHAR(30) NULL,
diachi NVARCHAR(60) NULL,
dienthoai NVARCHAR(20) NULL,
fax NVARCHAR(20) NULL,
email NVARCHAR(50) NULL
)
CREATE TABLE loaihang
(
maloaihang INT NOT NULL
CONSTRAINT
pk_loaihang
PRIMARY KEY(maloaihang),
tenloaihang NVARCHAR(15) NOT NULL
)
CREATE TABLE mathang
(
mahang NVARCHAR(10) NOT NULL
CONSTRAINT pk_mathang
PRIMARY KEY(mahang),
tenhang NVARCHAR(50) NOT NULL,
macongty NVARCHAR(10) NULL ,
maloaihang INT NULL ,
soluong INT NULL,
donvitinh NVARCHAR(20) NULL ,
giahang MONEY NULL
)
CREATE TABLE nhanvien
(
manhanvien NVARCHAR(10) NOT NULL
CONSTRAINT
pk_nhanvien
PRIMARY KEY(manhanvien),
ho NVARCHAR(20) NOT NULL ,
ten NVARCHAR(10) NOT NULL ,
ngaysinh DATETIME NULL ,
ngaylamviec DATETIME NULL ,
diachi NVARCHAR(50) NULL ,
dienthoai NVARCHAR(15) NULL ,
luongcoban MONEY NULL ,
phucap MONEY NULL
)
CREATE TABLE khachhang
(
makhachhang NVARCHAR(10) NOT NULL
CONSTRAINT
pk_khachhang
PRIMARY KEY(makhachhang),
tencongty NVARCHAR(50) NOT NULL ,
tengiaodich NVARCHAR(30) NOT NULL ,
diachi NVARCHAR(50) NULL ,
email NVARCHAR(30) NULL ,
dienthoai NVARCHAR(15) NULL ,
fax NVARCHAR(15) NULL
)
CREATE TABLE dondathang
(
sohoadon INT NOT NULL
CONSTRAINT
pk_dondathang
PRIMARY KEY(sohoadon),
makhachhang NVARCHAR(10) NULL ,
manhanvien NVARCHAR(10) NULL ,
ngaydathang SMALLDATETIME NULL ,
ngaygiaohang SMALLDATETIME NULL ,
ngaychuyenhang SMALLDATETIME NULL ,
noigiaohang NVARCHAR(50) NULL
)
CREATE TABLE chitietdathang
(
sohoadon INT NOT NULL ,
mahang NVARCHAR(10) NOT NULL ,
giaban MONEY NOT NULL ,
soluong SMALLINT NOT NULL ,
mucgiamgia REAL NOT NULL,
CONSTRAINT
pk_chitietdathang
PRIMARY KEY(sohoadon,mahang)
)
ALTER TABLE mathang
ADD
CONSTRAINT
fk_mathang_loaihang
FOREIGN KEY (maloaihang)
REFERENCES loaihang(maloaihang)
ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT
fk_mathang_nhacungcap
FOREIGN KEY (macongty)
REFERENCES nhacungcap(macongty)
ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE dondathang
ADD
CONSTRAINT
fk_dondathang_khachhang
FOREIGN KEY (makhachhang)
REFERENCES khachhang(makhachhang)
ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT
fk_dondathang_nhanvien
FOREIGN KEY (manhanvien)
REFERENCES nhanvien(manhanvien)
ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE chitietdathang
ADD
CONSTRAINT
fk_chitiet_dondathang
FOREIGN KEY (sohoadon)
REFERENCES dondathang(sohoadon)
ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT fk_chitiet_mathang
FOREIGN KEY (mahang)
REFERENCES mathang(mahang)
ON DELETE CASCADE
ON UPDATE CASCADE
1.
Hãy cho biết có những khách hàng nào lại chính là đối tác cung cấp
hàng của công ty (tức là có cùng tên giao dịch).
SELECT khachhang.tencongty, khachhang.tengiaodich,
khachhang.diachi
FROM khachhang INNER
JOIN nhacungcap ON
khachhang.tengiaodich =
nhacungcap.tengiaodich
/*2. Những đơn đặt hàng nào yêu cầu giao hàng ngay
tại cty đặt hàng và những đơn đó là của công ty nào? */
SELECT dondathang.sohoadon,noigiaohang,khachhang.tencongty,tengiaodich
FROM dondathang
INNER JOIN
khachhang
ON khachhang.makhachhang =
dondathang.makhachhang
WHERE khachhang.diachi = dondathang.noigiaohang
/*3. Những mặt hàng nào chưa từng được khách hàng đặt
mua?*/
SELECT mahang, tenhang FROM
mathang
WHERE NOT EXISTS (select mahang from
chitietdathang where chitietdathang.mahang=mathang.mahang)
/*4. Những nhân viên nào của công ty chưa từng lập
bất kỳ một hoá đơn đặt hàng nào? */
SELECT ho, ten FROM nhanvien
WHERE NOT EXISTS (select manhanvien from dondathang where
dondathang.manhanvien =
nhanvien.manhanvien)
/*5. Trong năm 2003, những mặt hàng nào chỉ được đặt
mua đúng một lần*/
SELECT mathang.mahang, tenhang
FROM (mathang INNER JOIN chitietdathang
ON mathang.mahang=chitietdathang.mahang)
INNER JOIN dondathang
ON chitietdathang.sohoadon=dondathang.sohoadon
WHERE year(ngaydathang)=2003
GROUP BY mathang.mahang, tenhang
HAVING COUNT (chitietdathang.mahang)=1
/*6. Hãy cho biết mỗi một khách hàng đã phải bỏ ra
bao nhiêu tiền để đặt mua hàng của công ty? */
SELECT khachhang.makhachhang,tencongty,tengiaodich,
SUM (soluong*giaban-soluong*giaban*mucgiamgia/100)AS giatien
FROM (khachhang INNER JOIN dondathang
ON khachhang.makhachhang=dondathang.makhachhang)
INNER JOIN chitietdathang
ON dondathang.sohoadon=chitietdathang.sohoadon
GROUP BY khachhang.makhachhang,tencongty,tengiaodich
/*7. Mỗi một nhân viên của công ty đã lập bao nhiêu
đơn đặt hàng (nếu nhân viên chưa hề lập một hoá đơn nào thì cho kết quả là 0)
*/
SELECT nhanvien.manhanvien,ho,ten,COUNT(sohoadon) AS tongsohoadon
FROM nhanvien LEFT OUTER JOIN dondathang
ON
nhanvien.manhanvien=dondathang.manhanvien
GROUP BY nhanvien.manhanvien,ho,ten
/*8. Cho biết tổng số tiền hàng mà cửa hàng thu được
trong mỗi tháng của năm 2003 (thời được gian tính theo ngày đặt hàng). */
SELECT MONTH(ngaydathang)AS thang,
SUM (soluong*giaban-soluong*giaban*mucgiamgia/100)AS sotien
FROM dondathang
INNER JOIN
chitietdathang
ON
dondathang.sohoadon=chitietdathang.sohoadon
WHERE YEAR(ngaydathang)=2003
GROUP BY month (ngaydathang)
/*9. Hãy cho biết tổng số lượng hàng của mỗi mặt
hàng mà cty đã có (tổng số lượng hàng hiện có và đã bán). */
SELECT mathang.mahang,tenhang,mathang.soluong+
CASE
WHEN SUM(chitietdathang.soluong) IS NULL THEN 0
ELSE SUM(chitietdathang.soluong)
END AS tongsoluong
FROM mathang LEFT OUTER JOIN chitietdathang
ON mathang.mahang=chitietdathang.mahang
GROUP BY mathang.mahang, tenhang, mathang.soluong
/*10. Nhân viên nào của cty bán được số lượng hàng
nhiều nhất và số lượng hàng bán được của nhân viên này là bao nhiêu?*/
SELECT nhanvien.manhanvien, ho,ten,SUM(soluong)as tongsoluong
FROM (nhanvien INNER JOIN dondathang
ON nhanvien.manhanvien=dondathang.manhanvien)
INNER JOIN chitietdathang
ON dondathang.sohoadon=chitietdathang.sohoadon
GROUP BY nhanvien.manhanvien,ho,ten
HAVING SUM(soluong)>=ALL
(SELECT sum(soluong)
FROM (nhanvien INNER JOIN dondathang
ON nhanvien.manhanvien=dondathang.manhanvien)
INNER JOIN
chitietdathang ON
dondathang.sohoadon=chitietdathang.sohoadon
GROUP BY nhanvien.manhanvien,ho,ten)
/*11. Mỗi một đơn đặt hàng đặt mua những mặt hàng nào
và tổng số tiền mà mỗi đơn đặt hàng phải trả là bao nhiêu? */
SELECT A.sohoadon,B.mahang,tenhang,
B.soluong*giaban-B.soluong*giaban*mucgiamgia/100
FROM (dondathang AS A INNER JOIN
chitietdathang AS B
ON A.sohoadon=B.sohoadon)
INNER JOIN mathang AS C ON B.mathang=C.mathang
ORDER BY A.sohoadon
COMPUTE SUM(B.soluong*giaban-B.soluong*giaban*mucgiamgia/100)
BY A.sohoadon
/*12. Hãy cho biết mỗi một loại hàng bao gồm những mặt
hàng nào, tổng số lượng hàng của mỗi loại và tổng số lượng của tất cả các mặt
hàng hiện có trong công ty là bao nhiêu? */
SELECT loaihang.maloaihang,tenloaihang,mahang,tenhang,soluong
FROM loaihang INNER JOIN mathang
ON
loaihang.maloaihang=mathang.maloaihang
ORDER BY loaihang.maloaihang
COMPUTE SUM(soluong)BY loaihang.maloaihang
COMPUTE SUM(soluong)
/*13. Thống kê xem trong năm 2003, mỗi một mặt hàng
trong mỗi tháng và trong cả năm bán được với số lượng bao nhiêu.*/
SELECT B.mahang,tenhang,
SUM(CASE MONTH(ngaydathang)WHEN 1 THEN B.soluong
ELSE 0 END) AS thang1,
SUM(CASE MONTH(ngaydathang)WHEN 2 THEN B.soluong
ELSE 0 END) AS thang2,
SUM(CASE MONTH(ngaydathang)WHEN 3 THEN B.soluong
ELSE 0 END) AS thang3,
SUM(CASE MONTH(ngaydathang)WHEN 4 THEN B.soluong
ELSE 0 END) AS thang4,
SUM(CASE MONTH(ngaydathang)WHEN 5 THEN B.soluong
ELSE 0 END) AS thang5,
SUM(CASE MONTH(ngaydathang)WHEN 6 THEN B.soluong
ELSE 0 END) AS thang6,
SUM(CASE MONTH(ngaydathang)WHEN 7 THEN B.soluong
ELSE 0 END) AS thang7,
SUM(CASE MONTH(ngaydathang)WHEN 8 THEN B.soluong
ELSE 0 END) AS thang8,
SUM(CASE MONTH(ngaydathang)WHEN 9 THEN B.soluong
ELSE 0 END) AS thang9,
SUM(CASE MONTH(ngaydathang)WHEN 10 THEN B.soluong
ELSE 0 END) AS thang10,
SUM(CASE MONTH(ngaydathang)WHEN 11 THEN B.soluong
ELSE 0 END) AS thang11,
SUM(CASE MONTH(ngaydathang)WHEN 12 THEN B.soluong
ELSE 0 END) AS thang12,
SUM (B.soluong) AS canam
FROM (dondathang AS A INNER JOIN
chitietdathang AS B
ON A.sohoadon=B.sohoadon)
INNER JOIN mathang AS C ON B.mahang=C.mahang
WHERE YEAR(ngaydathang)=2003
GROUP BY B.mathang,tenhang
/*14. Cập nhật lại giá trị NGAYCHUYENHANG của những bản
ghi có giá trị NGAYCHUYENHANG chưa xác định (NULL) trong bảng DONDATHANG bằng với
giá trị của trường NGAYDATHANG.*/
UPDATE dondathang
SET
ngaychuyenhang=ngaydathang
WHERE
ngaychuyenhang IS NULL
/*15. Cập nhật giá trị của trường NOIGIAOHANG trong bảng
DONDATHANG bằng địa chỉ của khách hàng đối với những đơn đặt hàng chưa xác định
được nơi giao hàng (có giá trị trường NOIGIAOHANG bằng NULL)*/
UPDATE dondathang
SET
noigiaohang=diachi
FROM khachhang
WHERE dondathang.makhachhang=khachhang.makhachhang
AND noigiaohang IS NULL
/*16. Cập nhật lại dữ liệu trong bảng KHACHHANG sao
cho nếu tên công ty và tên giao dịch của khách hàng trùng với tên công ty và
tên giao dịch của một nhà cung cấp nào đó thì địa chỉ, điện thoại, fax và email
phải giống nhau.*/
UPDATE khachhang
SET khachhang.diachi=nhacungcap.diachi,
khachhang.dienthoai=nhacungcap.dienthoai,
khachhang.fax=nhacungcap.fax,
khachhang.email=nhacungcap.email
FROM nhacungcap
WHERE khachhang.tencongty=nhacungcap.tencongty
AND khachhang.tengiaodich=nhacungcap.tengiaodich
/*17. Tăng lương lên gấp rưỡi cho những nhân viên bán
được số lượng hàng nhiều hơn 100 trong năm 2003 */
UPDATE nhanvien
SET luongcoban=luongcoban*1.5
WHERE manhanvien=
(SELECT manhanvien
FROM dondathang INNER
JOIN chitietdathang
ON
dondathang.sohoadon=chitietdathang.sohoadon
WHERE
manhanvien=nhanvien.manhanvien
GROUP BY manhanvien
HAVING SUM(soluong)>100 AND YEAR (ngaygiaohang)=2003)
*18. Tăng phụ cấp lên bằng 50% lương cho những nhân
viên bán được hàng nhiều nhất.*/
UPDATE nhanvien
SET phucap=luongcoban/2
WHERE manhanvien
IN
(select manhanvien
from dondathang, chitietdathang
where dondathang.sohoadon=chitietdathang.sohoadon
group by manhanvien
having sum (soluong)>= all
(select sum (soluong)from dondathang,chitietdathang
where dondathang.sohoadon=chitietdathang.sohoadon
group by manhanvien))
/*19. Giảm 25% lương của những nhân viên trong năm 2003 ko lập được bất kỳ
đơn đặt hàng nào */
UPDATE nhanvien
SET luongcoban= luongcoban-luongcoban*0.25
WHERE NOT EXISTS (select manhanvien from dondathang where
dondathang.manhanvien=nhanvien.manhanvien)
/*20. Giả sử trong bảng DONDATHANG có them trường SOTIEN cho biết số tiền
mà khách hàng phải trả trong mỗi dơn đặt hàng. Hãy tính giá trị cho trường
này.*/
UPDATE dondathang
SET sotien = (select SUM(soluong*giaban- soluong*giaban*mucgiamgia)
from chitietdathang where
dondathang.sohoadon=chitietdathang.sohoadon)
/*21. Xoá khỏi bảng MATHANG những mặt hàng có số lượng
bằng 0 và không được đặt mua trong bất kỳ đơn đặt hàng nào.*/
DELETE FROM mathang
WHERE NOT EXISTS (select mahang from
chitietdathang where chitietdathang.mahang=mathang.mahang) AND mathang.soluong =0