Mã:
//1.Hien thi thong tin trong bang CTPNHAP co them cot thanh tien (Thanh tien=SLNhap*DgNhap)
select sopn, mavtu, slnhap, dgnhap, slnhap*dgnhap N'Thành tiền'
from ctpnhap
//2. Hien thi danh sach cac nha cung cap trong ban Nhacc co cot dia chi o Quan 1 TP HCM, sxep thu tu theo ho ten tang dan và sdt giam dan
select *
from nhacc
where diachi like N'Quận 1 HCM%'
order by tennhacc desc,dienthoai
//3. Hien thi danh sach cac phien xuat có ngay xuat nam trong khoang [1/1/2009, 31/1/2009]
select *
from pxuat
where month(NgayXuat) = 1 and year(NgayXuat)=2009
//4. Hien thi danh sach cac chi tiet phieu xuat co them cac cot vat tu, ngay xuat. Loc theo so luong xuat lon hon 5 va ngay xuat trong thang 1/2009
SELECT ctpxuat.SoPx, ctpxuat.MaVTu, TenVTu, NgayXuat, SlXuat, DgXuat
FROM VATTU INNER JOIN
CTPXUAT ON vattu.MaVTu = ctpxuat.MaVTu INNER JOIN
PXUAT ON ctpxuat.SoPx = pxuat.SoPx
WHERE (MONTH(NgayXuat) = 1 AND YEAR(NgayXuat) = 2009) AND (SlXuat > 5)
//5. Hien thi ds cac nha cc gom cac thong tin sau: ma nha cung cap, ten nha cung cap da co dat hang. Khong dc trung lap du lieu.
SELECT distinct MaNhaCC, TenNhaCC
FROM dbo.NHACC
WHERE (MaNhaCC IN
(SELECT MaNhaCC
FROM dbo.DONDH))
//6. Hien thi cac don dat hang gan day nhat trong bang don dat hang
select *
from dondh
where ngaydh = (select max(ngaydh) from dondh)
//7. Hien thi ds nha cung cap chua co don dat hang
SELECT MaNhaCC, TenNhaCC
FROM dbo.NHACC
WHERE (MaNhaCC NOT IN (SELECT MaNhaCC FROM dbo.DONDH))
//8. Hien thi danh sach cac nha cung cap gom cac cot: ma nha cc, ten nhacc, tong so don dat hang. Loc ra nha cc co ong so don dat hang > 1
SELECT nhacc.MaNhaCC, TenNhaCC, SUM(SlDat) AS TongSLDat
FROM NHACC INNER JOIN DONDH ON dondh.MaNhaCC = nhacc.MaNhaCC INNER JOIN
CTDONDH ON ctdondh.SoDh = dondh.SoDh
GROUP BY nhacc.MaNhaCC, TenNhaCC
HAVING(SUM(SlDat) > 1)
//9. Hien thi thong tin trong ban pnhap co them cot tong thanh tien. Tong thanh tien = slnhap x dgia nhap cua cac mau tin chi tiet trong bang ctpnhap
select *, slnhap * dgnhap thanhtien
from pnhap inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
//10. Hien thi danh sach cac phieu xuat hang gom cac cot: so phieu xuat va tong thanh tien. Trong do sxep theo thu tu tong thanh tien giam dan
select sopx, sum(slxuat * dgxuat) ThanhTien
from ctpxuat
group by sopx
order by 2 desc
//11. Hien thi cac phieu xuat hang co tong thanh tien lon nhat
select top 1 sopx, sum(slxuat * dgxuat) ThanhTien
from ctpxuat
group by sopx
order by 2 desc
//12. Hien thi danh sach nha cung cap chua co don dat hang
select *
from nhacc
where manhacc not in (select manhacc from dondh)
//13. Hien thi cac don dat hang chua co phieu nhap
select *
from dondh
where sodh not in (select sodh from pnhap)
//14. Hien thi danh sach cac don dat hang so tong so lan nhap la lon nhat
SELECT TOP (1) SoDh AS SoDH, COUNT(SoPn) AS SoPn
FROM PNHAP
GROUP BY SoDh
ORDER BY SoPn DESC
//15. Hien thi danh sach cac nha cung cap chua co don dat hang
select *
from nhacc
where manhacc not in (select manhacc from dondh)
//16. Hien thi danh sach cach nha cung cap gom cac cot: ma nha cung cap, ten nha cung cap va tong so don dat hang
select nhacc.manhacc, tennhacc, count(sodh) 'So luong dat hang'
from nhacc inner join dondh on nhacc.manhacc=dondh.manhacc
group by nhacc.manhacc, tennhacc
//17. Hien thi danh sach cac nha cung cap gom cac cot: ma nha cung cap, ten nha cung cap, tong so don dat hang va tong so phieu nhap
select nhacc.manhacc, nhacc.tennhacc, count(dondh.sodh), count(sopn)
from nhacc inner join dondh on nhacc.manhacc=dondh.manhacc
inner join pnhap on dondh.sodh=pnhap.sodh
group by nhacc.manhacc, nhacc.tennhacc
//18.
select pnhap.sopn, NgayNhap, TenVtu, slnhap 'So luong nhap', '^-^' as 'So luong xuat'
from pnhap inner join ctpnhap on pnhap.sopn=ctpnhap.sopn
inner join vattu on ctpnhap.mavtu=vattu.mavtu
union
select pxuat.sopx, NgayXuat, TenVtu, '^-^', slxuat
from pxuat inner join ctpxuat on pxuat.sopx=ctpxuat.sopx
inner join vattu on ctpxuat.mavtu=vattu.mavtu
//19. Hien thi thong tin trong bang ctpxuat va bo sung them cot thanh tien, sao cho co dong thong ke thanh tien o tung nhom phieu xuat
select *, slxuat * dgxuat 'TongThanhTien'
from ctpxuat
group by sopx, mavtu, slxuat, dgxuat
order by sopx
compute sum(slxuat*dgxuat) by sopx
//20. Hien thi trong bang ctpnhap cac thong tin: ma vat tu, so nhap hang, so luong nhap, don gia nhap, va co dong tong thong ke so luong, gia thap nhat, gia cao nhat o tung vat tu.
select mavtu, sopn, slnhap, dgnhap
from ctpnhap
group by mavtu, sopn, slnhap, dgnhap
order by slnhap
compute sum(slnhap), max(dgnhap), min(dgnhap) by slnhap
//21. Tao bang VATTU_TAM co cau truc va du lieu duoc sao chep tu bang VATTU
select * into vattu_tam
from vattu
//22. Tang bang vattu_bo gom cac cot: ma vat tu, don vi tinh, phan tram va du lieu dc sao chep tu bang vattu co loc theo don vi la Bo
select mavtu, dvtinh, phantram into VATTU_BO
from vattu
where dvtinh like 'Bo'
