SQL_Week_1

SQL_Week_1.sql

-- Init DB
CREATE DATABASE QuanLySieuThi;
USE QuanLySieuThi;

DROP TABLE KHACHHANG;
CREATE TABLE KHACHHANG (
	MaKH char(4) PRIMARY KEY NOT NULL,
	HoTen varchar(40) NOT NULL,
	DChi varchar(50),
	SoDt varchar(20),
	NgSinh smalldatetime,
	DoanhSo money,
	NgDK smalldatetime 
);

DROP TABLE NHANVIEN;
CREATE TABLE NHANVIEN (
	MaNV char(4) PRIMARY KEY NOT NULL,
	HoTen varchar(40) NOT NULL,
	SoDT varchar(20),
	NgVL smalldatetime,
);

DROP TABLE SANPHAM;
CREATE TABLE SANPHAM (
	MaSP char(4) PRIMARY KEY NOT NULL,
	TenSP varchar(40) NOT NULL,
	DVT varchar(20),
	NuocSX varchar(40),
	Gia money
);

DROP TABLE HOADON;
CREATE TABLE HOADON (
	SoHD int PRIMARY KEY NOT NULL,
	NgHD smalldatetime,
	MaKH char(4) FOREIGN KEY REFERENCES KHACHHANG(MaKH),
	MaNV char(4) FOREIGN KEY REFERENCES NHANVIEN(MaNV),
	TriGia money
);

-- Cach 1
DROP TABLE CTHD;
CREATE TABLE CTHD (
	SoHD int NOT NULL,
	MaSP char(4) NOT NULL,
	SL int,
	CONSTRAINT PK_CTHD PRIMARY KEY (SoHD, MaSP)
);

-- Cach 2
CREATE TABLE CTHD (
	SoHD int NOT NULL,
	MaSP char(4) NOT NULL,
	SL int,
);
ALTER TABLE CTHD ADD CONSTRAINT Pk_CTHD PRIMARY KEY (SoHD, MaSP);

------------ Khoa ngoai -------------
-- Cach 1
CREATE TABLE CTHD (
	SoHD int FOREIGN KEY REFERENCES HOADON(SoHD),
	MaSP char(4) FOREIGN KEY REFERENCES SANPHAM(MaSP),
	SL int,
);

-- Cach 2
CREATE TABLE CTHD (
	SoHD int NOT NULL,
	MaSP char(4) NOT NULL,
	SL int,
);
ALTER TABLE CTHD ADD CONSTRAINT Pk_CTHD_HD FOREIGN KEY (SoHD) REFERENCES HOADON(SoHD);
ALTER TABLE CTHD ADD CONSTRAINT Pk_CTHD_NV FOREIGN KEY (MaSP) REFERENCES SANPHAM(MaSP);




ALTER TABLE KHACHHANG ADD CONSTRAINT Pk_KhachHang PRIMARY KEY (MaKH); 
ALTER TABLE NHANVIEN ADD CONSTRAINT Pk_NhanVien PRIMARY KEY (MaNV);
ALTER TABLE SANPHAM ADD CONSTRAINT Pk_SanPham PRIMARY KEY (MaSP);
ALTER TABLE HOADON ADD CONSTRAINT Pk_HoaDon PRIMARY KEY (SoHD);
-- ALTER TABLE CTHD ADD CONSTRAINT Pk_CTHD FOREIGN KEY (SoHD) REFERENCES HOADON(MaHD);



-- 2. Them thuoc tinh ghi chu ------
ALTER TABLE SANPHAM ADD GhiChu varchar(20);

-- 3. Them thuoc tinh LoaiKH -------
ALTER TABLE KHACHHANG ADD LoaiKH tinyint;

-- 4. Sua kieu du lieu cua thuoc tinh GHICHU trong quan he SANPHAM thanh varchar(100)
ALTER TABLE SANPHAM ALTER COLUMN GhiChu	varchar(100)

-- 5. Xoa thuoc tinh ghi chu trong quan he san pham
ALTER TABLE SANPHAM DROP COLUMN GhiChu

-- 6. Thuoc tinh LoaiKH trong quan he KHACHHANG co the luu cac gia tri "Vang lai", "Thuong xuyen", ...
ALTER TABLE KHACHHANG ALTER COLUMN LoaiKH varchar(50)

-- 7. Don vi tinh cua san pham chi co the la ("cay", "hop", "cai", "quyen", "chuc")
ALTER TABLE SANPHAM ALTER COLUMN DVT 

-- 8. Gia ban cua san pham tu 500d tro len
ALTER TABLE