Bài Tập Hàm và Thủ Tục | Bài tập Cơ sở dữ liệu

lOMoAR cPSD| 45474828
TRƯỜNG ĐẠI HỌC SƯ PHẠM KỸ THUẬT-ĐẠI HỌC ĐÀ NẴNG KHOA CÔNG NGHỆ SỐ
BÀI TẬP HÀM VÀ THỦ TỤC
Họ và tên: Phan Huỳnh Viết Thiện
Mã sinh viên:23115141122121
Lớp học phần:124CSDL201
Giảng viên: Nguyễn Thị Hà Quyên lOMoAR cPSD| 45474828
1. Hiển thị thông tin đầy đủ về tất sản phẩm, và cột được đặt tên là “Tình
trạng nhận một trong các giá trị:
i)Hết nếu soLuongCanDuoi=0 ii)Gần
hết nếu soLuongCanDuoi < 10 iii)Còn
vô tư nếu soLuongCanDuoi>=10
CREATE PROCEDURE dbo.HienThiThongTinSanPham AS BEGIN SELECT maSP, tenSP, donGiaBan, soLuongHienCon, soLuongCanDuoi, CASE
WHEN soLuongCanDuoi = 0 THEN N'Hế&t'
WHEN soLuongCanDuoi < 10 THEN N'Gầ*n hế&t' ELSE N'Còn vô tư' END AS TinhTrang FROM SanPham; END; lOMoAR cPSD| 45474828 2.
Thống kê (Tìm kiếm) những sản phẩm có tên chứa @ten và giá bán ra <
@gia (với @ten và &gia là tham số vào)
CREATE PROCEDURE dbo.TimKiemSanPham @ten NVARCHAR(100), @gia DECIMAL(18, 2) AS BEGIN SELECT maSP, tenSP, donGiaBan, soLuongHienCon, soLuongCanDuoi FROM SanPham WHERE tenSP LIKE '%' + @ten + '%' AND donGiaBan < @gia; END; lOMoAR cPSD| 45474828
3. Thống kê những giá đã từng bán ra của một sản phẩm (với @maSP là tham số vào)
CREATE PROCEDURE dbo.ThongKeGiaBanSanPham @maSP NVARCHAR(50) AS BEGIN SELECT maSP, ngayBan, giaBan, soLuongBan FROM LichSuBanHang WHERE maSP = @maSP ORDER BY ngayBan ASC; END; lOMoAR cPSD| 45474828
4. Thống kê những sản phẩm có giá bán trong đoạn [@min,@max] với
@min và @max là 2 tham số vào (sắp xếp giảm dần theo giá, tăng dần theo mã sản phẩm)
CREATE PROCEDURE dbo.ThongKeSanPhamTheoGia @min DECIMAL(18, 2), @max DECIMAL(18, 2) AS BEGIN SELECT maSP, tenSP, donGiaBan, soLuongHienCon, soLuongCanDuoi FROM SanPham WHERE
donGiaBan BETWEEN @min AND @max ORDER BY donGiaBan DESC, maSP ASC; END; lOMoAR cPSD| 45474828 CREATE
5. Thống kê các sản phẩm bán chạy (có tham số vào là số các sản phẩm cần thống kê)
PROCEDURE dbo.ThongKeSanPhamBanChay @top INT AS BEGIN SELECT TOP (@top) sp.maSP, sp.tenSP, sp.donGiaBan, sp.soLuongHienCon, sp.soLuongCanDuoi,
SUM(ct.soLuongDat) AS TongSoLuongBan FROM SanPham sp JOIN
ChiTietDonHang ct ON sp.maSP = ct.maSP GROUP BY lOMoAR cPSD| 45474828 CREATE
sp.maSP, sp.tenSP, sp.donGiaBan, sp.soLuongHienCon, sp.soLuongCanDuoi ORDER BY SUM(ct.soLuongDat) DESC; END; lOMoAR cPSD| 45474828 CREATE
6. Thống kê những tháng có doanh thu trên một giá trị định mức nào
đó (định mức là tham số vào)
PROCEDURE dbo.ThongKeThangCoDoanhThuCao @dinhMuc DECIMAL(18, 2) AS BEGIN SELECT YEAR(ngayTaoHD) AS Nam, MONTH(ngayTaoHD) AS Thang,
SUM(tongGiaTri) AS TongDoanhThu FROM DonDatHang_HoaDon GROUP BY
YEAR(ngayTaoHD), MONTH(ngayTaoHD) HAVING
SUM(tongGiaTri) > @dinhMuc; END; lOMoAR cPSD| 45474828 CREATE
7. Thống kê giá trung bình, giá max, giá min ở các phiếu nhập hàng cho mỗi sản phẩm
PROCEDURE dbo.ThongKeGiaNhapHang AS BEGIN SELECT sp.maSP, sp.tenSP,
AVG(ct.giaNhap) AS GiaTrungBinh,
MAX(ct.giaNhap) AS GiaMax, MIN(ct.giaNhap) AS GiaMin FROM SanPham sp JOIN
ChiTietPhieuNhap ct ON sp.maSP = ct.maSP GROUP BY sp.maSP, sp.tenSP; END; lOMoAR cPSD| 45474828
8. Thống kê số lần khách hàng mua hàng (sắp xếp giảm dần)
CREATE PROCEDURE dbo.ThongKeSoLanMuaHangCuaKhachHang AS BEGIN SELECT kh.maKH, kh.tenKH,
COUNT(dh.maDH) AS SoLanMuaHang FROM KhachHang kh JOIN
DonDatHang_HoaDon dh ON kh.maKH = dh.maKH GROUP BY kh.maKH, kh.tenKH ORDER BY COUNT(dh.maDH) DESC; END; lOMoAR cPSD| 45474828
9. Thống kê số lượng khách mua hàng theo từng tỉnh/thành phố (sắp xếp
giảm dần theo số lượng, tăng dần theo mã khách hàng)
CREATE PROCEDURE dbo.ThongKeSoLuongKhachHangTheoTinh AS BEGIN SELECT tp.maTTP, tp.tenTTP,
COUNT(kh.maKH) AS SoLuongKhachHang FROM TinhTP tp JOIN
KhachHang kh ON tp.maTTP = kh.maTTP GROUP BY tp.maTTP, tp.tenTTP ORDER BY
SoLuongKhachHang DESC, tp.maTTP ASC; END; lOMoAR cPSD| 45474828
10.Tính tổng tiền khi biết maDH
CREATE PROCEDURE dbo.TinhTongTienDonHang @maDH NVARCHAR(50) AS BEGIN
DECLARE @TongTien DECIMAL(18, 2);
SELECT @TongTien = SUM(soLuongDat * donGia) FROM ChiTietDonHang WHERE maDH = @maDH; UPDATE DonDatHang_HoaDon SET tongGiaTri = @TongTien WHERE maDH = @maDH; SELECT
maDH, ngayTaoHD, tongGiaTri AS TongTien FROM DonDatHang_HoaDon WHERE maDH = @maDH; END; lOMoAR cPSD| 45474828
11.Tính thành tiền khi biết đơn giá và số lượng
CREATE PROCEDURE dbo.TinhThanhTien
@donGia DECIMAL(18, 2), @soLuong INT AS BEGIN
DECLARE @thanhTien DECIMAL(18, 2);
SET @thanhTien = @donGia * @soLuong; SELECT @donGia AS DonGia, @soLuong AS SoLuong, @thanhTien AS ThanhTien; END; lOMoAR cPSD| 45474828
12.Tăng tự động các column ID cho tất cả các table
IF OBJECT_ID('dbo.LayDanhSachBangCoKhoaChinh', 'IF') IS NOT NULL
DROP FUNCTION dbo.LayDanhSachBangCoKhoaChinh; GO
CREATE FUNCTION dbo.LayDanhSachBangCoKhoaChinh()
RETURNS @DanhSachBang TABLE (TenBang NVARCHAR(MAX)) AS BEGIN
INSERT INTO @DanhSachBang (TenBang) SELECT DISTINCT t.name FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.is_primary_key = 1; RETURN; END; GO
IF OBJECT_ID('dbo.CaiDatIDTuTangChoThucTheManh', 'P') IS NOT NULL
DROP PROCEDURE dbo.CaiDatIDTuTangChoThucTheManh; GO
CREATE PROCEDURE dbo.CaiDatIDTuTangChoThucTheManh AS BEGIN SET NOCOUNT ON;
DECLARE @TenBang NVARCHAR(MAX);
DECLARE @LenhSQL NVARCHAR(MAX);
DECLARE ConTroBang CURSOR FOR
SELECT TenBang FROM dbo.LayDanhSachBangCoKhoaChinh(); OPEN ConTroBang;
FETCH NEXT FROM ConTroBang INTO @TenBang; WHILE @@FETCH_STATUS = 0 BEGIN SET @LenhSQL = ' IF NOT EXISTS ( SELECT 1 FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = ''' + @TenBang + ''' AND
c.name = ''ID'' AND c.is_identity = 1 ) BEGIN
ALTER TABLE ' + QUOTENAME(@TenBang) + ' ADD ID INT IDENTITY(1,1); END '; BEGIN TRY EXEC sp_executesql @LenhSQL; END TRY lOMoAR cPSD| 45474828 BEGIN CATCH
PRINT N'LôLi khi chỉNnh sưNa baNng: ' + @TenBang; END CATCH;
FETCH NEXT FROM ConTroBang INTO @TenBang; END; CLOSE ConTroBang; DEALLOCATE ConTroBang; END; GO