CREATE DATABASE QLCH;
USE QLCH;

CREATE TABLE KHACH(
ma_khach nvarchar(6) NOT NULL PRIMARY KEY,
Ten_kh nvarchar(30),
Diachi_kh nvarchar(30)
)

CREATE TABLE DONHANG (
so_don nvarchar(6) NOT NULL PRIMARY KEY,
Ma_khach nvarchar(6),
Ngay_don DATE,
FOREIGN KEY (Ma_khach) REFERENCES khach(ma_khach)
)

CREATE TABLE phieugiao(
so_phieu nvarchar(6) NOT NULL PRIMARY KEY,
ma_khach nvarchar(6),
ngay_giao DATE,
noi_giao nvarchar(30),
tong_tien FLOAT(9),
FOREIGN KEY (ma_khach) REFERENCES khach(ma_khach)
)

CREATE TABLE dongphieu(
so_phieu nvarchar(6),
ma_hang nvarchar(6),
don_gia FLOAT(6),
so_luonggi FLOAT(7),
FOREIGN KEY (so_phieu) REFERENCES phieugiao(so_phieu),
FOREIGN KEY (ma_hang) REFERENCES  hang(ma_hang)
)

CREATE TABLE hang(
ma_hang nvarchar(6) NOT NULL PRIMARY KEY,
ten_hang nvarchar(15),
mota_hang nvarchar(30),
don_vi nvarchar(10)
)

CREATE TABLE dongdon(
so_don nvarchar(6),
ma_hang nvarchar(6),
so_luongd INT,
FOREIGN KEY (so_don) REFERENCES donhang(so_don),
FOREIGN KEY (ma_hang) REFERENCES hang(ma_hang)
)

-- 2
ALTER TABLE khach
ADD sothich text;

SELECT * FROM khach;
-- thêm 2 cột noicungcap và noisanxuat kiểu text
ALTER TABLE hang
ADD NoiCungCap text, NoiSanXuat text;

--2 sửa kiểu dữ liệu sothich từ text sang memo(nvarchar)
ALTER TABLE khach
ALTER COLUMN SoThich nvarchar;

-- sửa kiểu dữ liệu NoiCungCap sang số
ALTER TABLE hang
ALTER COLUMN noicungcap NUMERIC;

-- xoá cột NoiCungCap, NoiSanXuat trong bảng hang
ALTER TABLE hang
DROP COLUMN noicungcap, noisanxuat;

-- xoá cột sothich trong bảng khach
ALTER TABLE khach
DROP COLUMN SoThiCh;

--II
SELECT * FROM khach;
INSERT INTO khach
VALUES ('K001', N'Đào Minh Thư', N'Đại học Quốc gia'),
    ('K002', N'Nguyễn Liên Dung', N'BNC'),
    ('K003', N'Phạm Hoàng Nhung', N'Đại học Thuỷ Lợi'),
    ('K004', N'Trịnh Hồng Cường', N''),
    ('K005', N'Nguyễn Văn Hải', N'Đại học Thuỷ Lợi');
INSERT INTO donhang
VALUES ('DH001', 'K001', '3/22/2002'),
    ('DH002', 'K001', '7/13/2003'),
    ('DH003', 'K002', '12/24/2002');
INSERT INTO hang
VALUES ('H001', N'Hoa loa kèn', N'Hoa trắng, to', 'Bông'),
        ('H002', N'Hoa hồng', N'Nhiều mau', 'Bông'),
        ('H003', N'Hoa lan', N'', 'Cành');

SELECT * FROM khach;
SELECT * FROM donhang;
SELECT * FROM dongdon;
SELECT * FROM hang;
SELECT * FROM dongphieu;
SELECT * FROM phieugiao;
INSERT INTO dongdon
VALUES ('DH001','H001', 100),
        ('DH001','H002', 30),
        ('DH002','H003', 1);
INSERT INTO phieugiao
VALUES ('PG001', 'K001', '05/12/2017', N'Cồn nổi', 500),
        ('PG002', 'K002', '03/22/2018', N'Hà nội', 500),
        ('PG003', 'K002', '01/01/2018', N'Thanh hoá', 500);
-- them phieu giao
INSERT INTO phieugiao
VALUES ('PG004', 'K002', '10/28/2017', N'Hà nội', 350000),
        ('PG005', 'K004', '12/19/2018', N'Thanh hoá', 490000),
        ('PG006', 'K001', '02/23/2019', N'Hà nội', 1000000);
INSERT INTO dongphieu
VALUES ('PG001','H001', 30000 , 20),
        ('PG002','H003', 15000 , 2),
        ('PG003','H002', 5000 , 130);

DELETE FROM dongdon WHERE ma_hang = 'H001';
DELETE FROM dongphieu WHERE ma_hang = 'H001';
DELETE FROM hang WHERE ma_hang = 'H001';

DELETE FROM khach WHERE Diachi_kh = N'Đại học Thuỷ Lợi';
DELETE FROM khach WHERE Ten_kh = N'Trịnh Hồng Cường';
SELECT * FROM khach;

--Sửa đổi địa chỉ của khách hàng thành địa chỉ mới là Đại học Quốc Gia với ma_khach là K002.
UPDATE khach
SET Diachi_kh = N'Đại học Quốc Gia'
WHERE ma_khach = 'K002';
SELECT * FROM khach;

--Sửa đổi tất cả các tên hàng là “Hoa lan” thành “Hoa phong lan” và mô tả hàng là “Hàng nhập khẩu từ Đà Lạt”
UPDATE hang
SET ten_hang = N'Hoa phong lan', mota_hang = N'Hàng nhập khẩu từ Đà Lạt'
WHERE ten_hang = N'Hoa lan';
SELECT * FROM hang;
--III
-- 1. Xem toàn bộ nội dung của bảng KHACH
SELECT * FROM khach;

--2. Xem toàn bộ nội dung của bảng HANG.
SELECT * FROM hang;

-- 3. Xem toàn bộ nội dung của bảng DONHANG.
SELECT * FROM donhang;

-- 4. Đưa ra Tên và Địa chỉ của tất cả các Khách hàng trong bảng KHACH.
SELECT Ten_kh, Diachi_kh FROM khach;

-- 5. Đưa ra Tên hàng, Mô tả hàng và Đơn vị tính của tất cả các mặt hàng trong bảng HANG.
SELECT ten_hang, mota_hang, don_vi
FROM hang;

--6. Đưa ra tất cả các đơn vị tính dùng để tính hàng hóa.
SELECT don_vi FROM hang;

--7. Đưa ra tất cả các tên hàng trong bảng HANG.
SELECT ten_hang FROM hang;

-- 8. Đưa ra tất cả các địa chỉ của khách hàng.
SELECT diachi_kh FROM KHACH;

-- 9. Đưa ra so_phieu, ma_hang, don_gia, soluonggi và Thành tiền (don_gia*so_luong) của tất cả các hàng trong bảng DONGPHIEU.
SELECT so_phieu, ma_hang, don_gia, so_luonggi, don_gia*so_luonggi AS ThanhTien
FROM dongphieu;

-- 10. Đưa ra so_phieu, ma_hang, don_gia, soluonggi và Thành tiền (don_gia*so_luong),sử dụng bí danh la thanh_tien của tất cả các hàng trong bảng DONGPHIEU
SELECT so_phieu, ma_hang, don_gia, so_luonggi, don_gia*so_luonggi AS ThanhTien
FROM dongphieu AS thanhtien;

--11. Đưa ra tất cả giá trị của bảng HANG theo thứ tự giảm dần của ma_hang.
SELECT * FROM hang
ORDER BY ma_hang DESC;

--12. Đưa ra tất cả các giá trị của bảng PHIEUGIAO theo thứ tự tăng dần (giảm dần) của tổng tiền (tong_tien).
-- tăng dần
SELECT * FROM phieugiao
ORDER BY tong_tien;

-- giảm dần
SELECT * FROM phieugiao
ORDER BY tong_tien DESC;

--13. Đưa ra tất cả các khách hàng có địa chỉ là Đại học Quốc Gia trong bảng KHACH.
SELECT * FROM KHACH
WHERE Diachi_kh = N'Đại học Quốc Gia';

--14. Đưa ra So_phieu, Ma_khach trong bảng PHIEUGIAO với điều kiện Tong_tien>=100.000, và sắp xếp theo điều kiện giảm dần của Tong_tien.
SELECT so_phieu, ma_khach
FROM phieugiao
WHERE tong_tien >= 100000
ORDER BY tong_tien DESC;

--15. Đưa ra ten_hang, mota_hang của những hàng hóa có don_vi được tính theo bông trong bảng HANG.
SELECT ten_hang, mota_hang
FROM hang
WHERE don_vi = N'Bông';

--16. Đưa ra danh sách các bản ghi bao gồm so_phieu, ma_khach, ngay_giao trong bảng PHIEUGIAO với điều kiện Noi_giao là Hà Nội và Tong_tien> 50.000.
SELECT so_phieu, ma_khach, ngay_giao
FROM phieugiao
WHERE noi_giao = N'Hà Nội' AND tong_tien > 50000;

-- 17. Đưa ra danh sách các bản ghi bao gồm Ma_khach, Ten_khach trong bảng KHACH với điều kiện địa chỉ của khách ở Đại học Quốc Gia hoặc Đại học Thủy Lợi.
SELECT ma_khach, Ten_kh
FROM khach
WHERE Diachi_kh IN (N'Đại học Quốc gia', N'Đại học Thuỷ Lợi');

-- 18. Đưa ra những mặt hàng trong bảng HANG có đơn vị tính là Bông, Cành hoặc Bó.
SELECT * FROM hang
WHERE don_vi IN (N'Bông', N'Cành', N'Bó');

--19. Đưa ra danh sách những khách hàng (Ma_khach, Tong_tien) nằm trong bảng PHIEUGIAO có Noi_giao nằm trong số các địa điểm sau: Hà Nội, Hồ Chí Minh, Hải Phòng.
SELECT ma_khach, tong_tien FROM phieugiao
WHERE noi_giao IN (N'Hà Nội', N'Hồ Chí Minh', N'Hải Phòng');

-- 20. Đưa ra tất cả những thông tin trong bảng PHIEUGIAO với điều kiện tong_tien nằm trong khoảng từ 100.000 đến 500.000.
SELECT * FROM phieugiao
WHERE tong_tien BETWEEN 100000 AND 500000;

--21. Đưa ra ma_kh, ten_kh trong bảng KHACH của những khách hàng có họ là Nguyễn.
SELECT ma_khach, ten_kh FROM khach
WHERE Ten_kh LIKE N'Nguyễn%';

--22. Đếm số lần mua hàng của khách hàng có ma_kh là K001 trong bảng PHIEUGIAO. Thuộc tính mới này được đặt tên là SoLanMua.
SELECT COUNT(ma_khach) AS SoLanMua
FROM phieugiao
WHERE ma_khach = 'K001';
 -- c2
SELECT  ma_khach, COUNT(ma_khach) AS SoLanMua
FROM phieugiao
GROUP BY ma_khach
HAVING  ma_khach = 'K001';

-- 23. Tính tổng tiền trung bình của mỗi PHIEUGIAO. Thuộc tính mới này được đặt tên là TrungBinhPG.
SELECT  ma_khach, avg(tong_tien) AS TrungBinhPG
FROM phieugiao
GROUP BY ma_khach;

SELECT * FROM phieugiao;
--24. Tính tổng tiền trong bảng PHIEUGIAO của những khách hàng có ma_khach = ‘K002’.
SELECT SUM(tong_tien) AS TrungBinhPG
FROM phieugiao
WHERE ma_khach = 'K002';

-- 25. Tính tổng số các khách hàng trong bảng KHACH có địa chỉ (diachi_KH) là Đại học Thủy Lợi.
INSERT INTO khach
VALUES  ('K004', N'Trịnh Đình Quang', N'Đại học Thuỷ Lợi'),
    ('K005', N'Nguyễn Văn Hải', N'Đại học Thuỷ Lợi');

SELECT COUNT(ten_kh) AS TongSoKhachHang
FROM khach
WHERE Diachi_kh = N'Đại học Thuỷ Lợi';


--26.  Đưa ra ma_khach, ngay_giao, noi_giao trong bảng PHIEUGIAO có tong_tien thanh toán lớn nhất.
SELECT ma_khach, ngay_giao, noi_giao
FROM phieugiao
WHERE tong_tien = (SELECT MAX(tong_tien) FROM phieugiao);

--27.  Đưa ra ma_khach, ngay_giao, noi_giao trong bảng PHIEUGIAO có tong_tien thanh toán nhỏ nhất.
SELECT ma_khach, ngay_giao, noi_giao
FROM phieugiao
WHERE tong_tien = (SELECT MIN(tong_tien) FROM phieugiao);