2. 1 Cho biết danh sách các đối tác cung cấp hàng cho công ty.

SELECT macongty,tencongty,tengiaodich

FROM nhacungcap

2.2 Mã hàng, tên hàng và số lượng của các mặt hàng hiện có trong công ty.

SELECT mahang,tenhang,soluong

FROM mathang

2. 3 Họ tên và địa chỉ và năm bắt đầu làm việc của các nhân viên trong công ty

SELECT ho,ten,year(ngaylamviec) AS namlamviec

FROM nhanvien

2. 4 Địa chỉ và điện thoại của nhà cung cấp có tên giao dịch VINAMILK là gì?

SELECT diachi,dienthoai

FROM nhacungcap

WHERE tengiaodich='VINAMILK'

2. 5 Cho biết mã và tên của các mặt hàng có giá lớn hơn 100000 và số lượng hiện có ít hơn 50.

SELECT mahang,tenhang

FROM mathang

WHERE giahang>100000 AND soluong<50

2. 6 Cho biết mỗi mặt hàng trong công ty do ai cung cấp.

SELECT mahang,tenhang,

nhacungcap.macongty,tencongty,tengiaodich

FROM mathang INNER JOIN nhacungcap

ON mathang.macongty=nhacungcap.macongty

2. 7 Công ty Việt Tiến đã cung cấp những mặt hàng nào?

SELECT mahang,tenhang

FROM mathang INNER JOIN nhacungcap

ON mathang.macongty=nhacungcap.macongty

WHERE tencongty='Việt Tiến'

2. 8 Loại hàng thực phẩm do những công ty nào cung cấp và địa chỉ của các công ty đó là gì?

SELECT DISTINCT nhacungcap.macongty,tencongty,diachi

FROM (loaihang INNER JOIN mathang

ON loaihang.maloaihang=mathang.maloaihang)

INNER JOIN nhacungcap

ON mathang.macongty=nhacungcap.macongty

WHERE tenloaihang='Thực phẩm'

2. 9 Những khách hàng nào (tên giao dịch) đã đặt mua mặt hàng Sữa hộp XYZ của công ty?

SELECT DISTINCT tengiaodich

FROM ((mathang INNER JOIN chitietdathang

ON mathang.mahang=chitietdathang.mahang)

INNER JOIN dondathang

ON chitietdathang.sohoadon=dondathang.sohoadon)

INNER JOIN khachhang

ON dondathang.makhachhang=khachhang.makhachhang

WHERE tenhang='Sữa hộp'

2. 10 Đơn đặt hàng số 1 do ai đặt và do nhân viên nào lập, thời gian và địa điểm giao hàng là ở đâu?

SELECT dondathang.manhanvien,ho,ten,

ngaygiaohang,noigiaohang

FROM nhanvien INNER JOIN dondathang

ON nhanvien.manhanvien=dondathang.manhanvien

WHERE sohoadon=1

2. 11 Hãy cho biết số tiền lương mà công ty phải trả cho mỗi nhân viên là bao nhiêu (lương = lương cơ bản + phụ cấp).

SELECT manhanvien,ho,ten,luongcoban + CASE

WHEN phucap IS NULL THEN 0

ELSE phucap

END AS luong

FROM nhanvien

2. 12 Trong đơn đặt hàng số 3 đặt mua những mặt hàng nào và số tiền mà khách hàng phải trả cho mỗi mặt hàng là bao nhiêu (số tiền phải trả được tính theo công thức SOLUONGGIABAN - SOLUONGGIABANMUCGIAMGIA/100)

SELECT a.mahang,tenhang,

a.soluong*giaban*(1-mucgiamgia/100) AS sotien

FROM chitietdathang AS a INNER JOIN mathang AS b

ON a.mahang=b.mahang

2. 13 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 makhachhang,khachhang.tencongty,

khachhang.tengiaodich

FROM khachhang INNER JOIN nhacungcap

ON khachhang.tengiaodich=nhacungcap.tengiaodich

2. 14 Trong công ty có những nhân viên nào có cùng ngày sinh?

SELECT a.ho,a.ten,b.ho,b.ten,b.ngaysinh

FROM nhanvien a INNER JOIN nhanvien b

ON a.ngaysinh=b.ngaysinh AND

a.manhanvien<>b.manhanvien

2. 15 Những đơn đặt hàng nào yêu cầu giao hàng ngay tại công ty đặt hàng và những đơn đó là của công ty nào?' SELECT sohoadon,tencongty,tengiaodich,

ngaydathang,noigiaohang

FROM dondathang INNER JOIN khachhang

ON dondathang.noigiaohang=khachhang.diachi

2. 16 Cho biết tên công ty, tên giao dịch, địa chỉ và điện thoại của các khách hàng và các nhà cung cấp hàng cho công ty

SELECT tencongty,tengiaodich,diachi,dienthoai

FROM khachhang

UNION ALL

SELECT tencongty,tengiaodich,diachi,dienthoai

FROM nhacungcap

2. 17 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 mahang=mathang.mahang)

2. 18 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 manhanvien,ho,ten

FROM nhanvien

WHERE NOT EXISTS (SELECT manhanvien FROM dondathang

WHERE manhanvien=nhanvien.manhanvien)

2. 19 Những nhân viên nào của công ty có lương cơ bản cao nhất?

SELECT manhanvien,ho,ten,luongcoban

FROM nhanvien

WHERE luongcoban=(SELECT MAX(luongcoban) FROM nhanvien)

2. 20 Tổng số tiền mà khách hàng phải trả cho mỗi đơn đặt hàng là bao nhiêu?

SELECT dondathang.sohoadon,dondathang.makhachhang,

tencongty,tengiaodich,

SUM(soluong*giaban-soluong*giaban*mucgiamgia/100)

FROM (khachhang INNER JOIN dondathang

ON khachhang.makhachhang=dondathang.makhachhang)

INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY dondathang.makhachhang,tencongty,

tengiaodich,dondathang.sohoadon

2. 21 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

2. 22 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)

FROM (khachhang INNER JOIN dondathang

ON khachhang.makhachhang = dondathang.makhachhang)

INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY khachhang.makhachhang,tencongty,tengiaodich

2. 23 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)

FROM nhanvien LEFT OUTER JOIN dondathang

ON nhanvien.manhanvien=dondathang.manhanvien

GROUP BY nhanvien.manhanvien,ho,ten

2. 24 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)

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

WHERE year(ngaydathang)=2003

GROUP BY month(ngaydathang)

2. 25 Hãy cho biết tổng số tiền lời mà công ty thu được từ mỗi mặt hàng trong năm 2003.

SELECT c.mahang,tenhang,

SUM(b.soluong*giaban-b.soluong*giaban*mucgiamgia/100)-

SUM(b.soluong*giahang)

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 c.mahang,tenhang

2. 26 Hãy cho biết tổng số lượng hàng của mỗi mặt hàng mà công ty đã 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

2. 27 Nhân viên nào của công ty bán được số lượng hàng nhiều nhất và số lượng hàng bán được của những nhân viên này là bao nhiêu?

SELECT nhanvien.manhanvien,ho,ten,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

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)

2. 28 Đơn đặt hàng nào có số lượng hàng được đặt mua ít nhất?

SELECT dondathang.sohoadon,SUM(soluong)

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY dondathang.sohoadon

HAVING sum(soluong)<=ALL(SELECT sum(soluong)

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY dondathang.sohoadon)

2. 29 Số tiền nhiều nhất mà mỗi khách hàng đã từng bỏ ra để đặt hàng trong các đơn đặt hàng là bao nhiêu?

SELECT TOP 1

SUM(soluong*giaban-soluong*giaban*mucgiamgia/100)

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

ORDER BY 1 DESC

2. 30 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.mahang = c.mahang

ORDER BY a.sohoadon

COMPUTE SUM(b.soluong*giaban-

b.soluong*giaban*mucgiamgia/100) BY a.sohoadon

2. 31 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)

2. 32 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

Yêu cầu: Kết quả được hiển thị dưới dạng bảng, hai cột cột đầu là mã hàng và tên hàng, các cột còn lại tương ứng với các tháng từ 1 đến 12 và cả năm. Như vậy mỗi dòng trong kết quả cho biết số lượng hàng bán được mỗi tháng và trong cả năm của mỗi mặt hàng.

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)=1996

GROUP BY b.mahang,tenhang

=======================================================================

2. 33 Cập nhật lại giá trị trường NGAYCHUYENHANG của những bản ghi có 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

2. 34 Tăng số lượng hàng của những mặt hàng do công ty VINAMILK cung cấp lên gấp đôi.

UPDATE mathang

SET soluong=soluong*2

FROM nhacungcap

WHERE nhacungcap.macongty=mathang.macongty AND

tencongty='VINAMILK'

2. 35 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 (giá trị trường NOIGIAOHANG bằng NULL).

UPDATE dondathang

SET noigiaohang=diachi

FROM khachhang

WHERE dondathang.makhachhang=khachhang.makhachang AND

noigiaohang IS NULL

2. 36 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à e-mail 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

2. 37 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)

2. 38 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 INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY manhanvien

HAVING SUM(soluong)>=ALL

(SELECT SUM(soluong)

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon=chitietdathang.sohoadon

GROUP BY manhanvien))

2. 39 Giảm 25% lương của những nhân viên trong năm 2003 không lập được bất kỳ đơn đặt hàng nào

UPDATE nhanvien

SET luongcoban=luongcoban*0.85

WHERE NOT EXISTS (SELECT manhanvien

FROM dondathang

WHERE manhanvien=nhanvien.manhanvien)

2. 40 Giả sử trong bảng DONDATHANG có thêm trường SOTIEN cho biết số tiền mà khách hàng phải trả trong mỗi đơn đặt hàng. Hãy tính giá trị cho trường

UPDATE dondathang

SET sotien =

(SELECT SUM(soluong*giaban+soluong*giaban*mucgiamgia)

FROM chitietdathang

WHERE sohoadon=dondathang.sohoadon

GROUP BY sohoadon)

==========================================================================

2. 41 Xoá khỏi bảng NHANVIEN những nhân viên đã làm việc trong công ty quá 40 năm.

DELETE FROM nhanvien

WHERE DATEDIFF(YY,ngaylamviec,GETDATE())>40

2. 42 Xoá những đơn đặt hàng trước năm 2000 ra khỏi cơ sở dữ liệu.

DELETE FROM dondathang

WHERE ngaydathang<'1/1/2000'

2. 43 Xoá khỏi bảng LOAIHANG những loại hàng hiện không có mặt hàng

DELETE FROM loaihang

WHERE NOT EXISTS (SELECT mahang

FROM mathang

WHERE maloaihang=loaihang.maloaihang)

2. 44 Xoá khỏi bảng KHACHHANG những khách hàng hiện không có bất kỳ đơn đặt hàng nào cho công ty.

DELETE FROM khachhang

WHERE NOT EXISTS (SELECT sohoadon FROM dondathang

WHERE makhachhang=khachhang.makhachhang)

2. 45 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 soluong=0 AND

NOT EXISTS (SELECT sohoadon

FROM chitietdathang

WHERE mahang=mathang.mahang)