La Hoàng Nam

Lại ngồi cái máy cùi ở trường , nhưng hok sao
Hôm nay thi , cũng khá dễ , đề hok dài lắm , sau đây là 1 bài SQl tương tự như đề thi : ^^

CREATE DATABASE QLHH
GO
USE QLHH
go
CREATE TABLE HANGHOA
(
MAHH CHAR(5) PRIMARY KEY,
TENHH NVARCHAR(100),
DVT NVARCHAR(10),
SOLUONGTON FLOAT
)
GO
CREATE TABLE KHACHHANG
(
MAKH CHAR(5) PRIMARY KEY,
TENKH NVARCHAR(30),
DIACHI NVARCHAR(100),
DIENTHOAI VARCHAR(15),
EMAIL VARCHAR(30)
)
GO
CREATE TABLE HOADON
(
SOHD CHAR(5) PRIMARY KEY,
LOAIHD BIT,
NGAYHD DATETIME,
MAKH CHAR(5),
TRIGIA FLOAT,
FOREIGN KEY(MAKH) REFERENCES KHACHHANG(MAKH)
)
GO
CREATE TABLE LYDOCHI
(
MALDC INT IDENTITY(1,1) PRIMARY KEY,
NOIDUNG NVARCHAR(200)
)
GO
CREATE TABLE LYDOTHU
(
MALDT INT IDENTITY(1,1) PRIMARY KEY,
NOIDUNG NVARCHAR(200)
)
GO
CREATE TABLE CTHD
(
SOHD CHAR(5),
MAHH CHAR(5),
SOLUONG FLOAT,
DONGIA FLOAT,
PRIMARY KEY(SOHD,MAHH),
FOREIGN KEY(MAHH) REFERENCES HANGHOA(MAHH),
FOREIGN KEY(SOHD) REFERENCES HOADON(SOHD)
)
GO
CREATE TABLE PHIEUCHI
(
SOPC CHAR(5) PRIMARY KEY,
MAKH CHAR(5),
NGAYCHI DATETIME,
SOTIEN FLOAT,
MALDC INT,
FOREIGN KEY(MAKH) REFERENCES KHACHHANG(MAKH),
FOREIGN KEY(MALDC) REFERENCES LYDOCHI(MALDC)
)
GO
CREATE TABLE PHIEUTHU
(
SOPT CHAR(5) PRIMARY KEY,
MAKH CHAR(5),
NGAYTHU DATETIME,
SOTIEN FLOAT,
MALDT INT,
FOREIGN KEY(MAKH) REFERENCES KHACHHANG(MAKH),
FOREIGN KEY(MALDT) REFERENCES LYDOTHU(MALDT)
)
go
--Nhập dữ liệu bảng hàng hóa
insert into hanghoa values('HH001',N'Tivi SamSung 21 in','Cái',425)
insert into hanghoa values('HH002',N'Đầu VCD LG','Cái',140)
insert into hanghoa values('HH003',N'Đầu DVD Pionier','Cái',0)
insert into hanghoa values('HH004',N'Quạt trần Jamayiha','Cái',200)
insert into hanghoa values('HH005',N'Máy lạnh LG','Cái',0)
--nhập dữ liệu bảng khách hàng
insert into khachhang values('KH001',N'Trần Văn Mão',N'2 Cầu Giấy-Hà Nội','76653432','maotv@yahoo.com')
insert into khachhang values('KH002',N'Lê Đức Hải',N'12/5 Quan Hoa-Cầu Giấy-Hà Nội','98343422','haild@hotmail.com')
insert into khachhang values('KH003',N'Nguyễn Huy Hoàng',N'512-Thanh Xuân-Hà Nội','67855656','hoangnh@yahoo.com')
insert into khachhang values('KH004',N'Nguyễn Văn Huy',N'23-Đường Thanh Niên-Tây Hồ-Hà Nội','4564345','huynv@gmail.com')
insert into khachhang values('KH005',N'Hoàng Thu Thủy',N'5 Nguyễn An Ninh-Hoàng Mai-Hà Nội','66345566','thuyht@yahoo.com')
--nhập dữ liệu bảng hóa đơn
insert into hoadon values('B0001',1,'10/20/2007','KH004',327600000)
insert into hoadon values('M0001',0,'10/01/2007','KH001',897000000)
insert into hoadon values('M0002',0,'10/12/2007','KH001',240000000)
insert into hoadon values('M0003',0,'10/12/2007','KH003',740000000)
--Nhập dữ liệu bảng ly do chi
insert into lydochi(noidung) values(N'Chi tiền mua hàng')
insert into lydochi(noidung) values(N'Tiền còn nợ nhà cung cấp')
--Nhập dữ liệu bảng ly do thu
insert into lydothu(noidung) values(N'Thu tiền mua hàng của khách')
insert into lydothu(noidung) values(N'Thu tiền khách còn nợ')
--nhập dữ liệu bảng chi tiết hóa đơn
insert into cthd values('B0001','HH001',50,4680000)
insert into cthd values('B0001','HH002',60,1560000)
insert into cthd values('M0001','HH001',300,2990000)
insert into cthd values('M0002','HH002',200,1200000)
insert into cthd values('M0003','HH001',175,3600000)
insert into cthd values('M0003','HH004',200,550000)
--nhập dữ liệu bảng phiếu chi
insert into phieuchi values('PC001','KH001','10/25/2007',100000000,1)
insert into phieuchi values('PC002','KH001','10/30/2007',150000000,2)
insert into phieuchi values('PC003','KH003','10/20/2007',100000000,1)
--nhập dữ liệu bảng phiếu thu
insert into phieuthu values('PT001','KH004','10/05/2007',150000000,1)

--1.Thêm ràng buộc check cho cột số lượng với điều kiện số lượng > 0
alter table CTHD
add constraint Ck_CTHD_SoLuong check (soluong>0)
go

--2.Sửa lại cột quequan thành kiểu nvarchar(100)
alter table khachhang
add column diachi nvarchar(100)
go

--3.Thêm một cột ghi chú kiểu nvarchar(100) vào bảng đơn đặt hàng
alter table hoadon
add GhiChu nvarchar(100)
go

--4.Xoá cột ghi chú trong bảng đơn đặt hàng
alter table hoadon
drop column GhiChu
go

--5.Thêm ràng buộc khoá ngoại makh cho bảng hoá đơn tham chiếu đến bảng khách hàng
alter table HOADON
add constraint Fk_HOADON_MaKH foreign key (MaKH) references khachhang(MaKH)

go

--6.Lấy ra những khách hàng mà địa chỉ chứa cụm từ 'Hà nội'
select TENKH,DIACHI from KHACHHANG where DIACHI like N'%HàNội%'
go

--7.Lấy ra những khách hàng có số điện thoại mà 2 ký tự đầu là 04
select TENKH,DIENTHOAI from KHACHHANG where DIENTHOAI like N'[0][4]%'
go

--8.Đưa ra danh sách những hàng hoá có số lượng tồn =0
select TENHH,SOLUONGTON from HANGHOA where SOLUONGTON=0
go

--9.Đưa ra các hoá đơn mới lập trong ngày hôm nay
select * from HOADON where NgayHD=getdate()


go
--10.Đưa ra những hoá đơn lập trong tháng này
select * from HOADON where datepart(mm,getdate())=datepart(mm,ngayHD)

go

--11.Đưa ra những hoá đơn có trị giá >1000000
select * from HOADON where Trigia>1000000
go

--12.Tính tổng thành tiền của từng hoá đơn thông tin bao gồm (sohd,tongtien)
select Sohd,sum(soluong*dongia) as [TongTien] from CTHD group by SoHD
go

--13.Tính tổng số hàng của từng hoá đơn thông tin bao gồm (sohd,tongsohang)
select sohd,sum(soluong) as [tongsohang] from CTHD group by SoHD

go

--14.Tính tổng trị giá của các hoá đơn theo từng năm thông tin bao gồm (nam,tongtrigia)
select datepart(yy,ngayhd) as Nam,sum(trigia)as Tongtrigia from HoaDon group by datepart(yy,ngayhd)

go
--15.Giảm trị giá của các hoá đơn trong tháng này 10%
update HOADON
set Trigia=trigia-(trigia*10)/100 where datepart(mm,getdate())=datepart(mm,ngayhd)
go

--16.Xoá những hoá đơn cách đây 5 năm
delete hoadon
where datediff(yy,ngayhd,getdate())=5
go
--17.Đưa ra 5 hoá đơn gần đây nhất
select top (5) * from hoadon order by datediff (dd,ngayhd,getdate()) asc

go

--18.Đưa ra hoá đơn có trị giá cao nhất thông tin bao gồm (sohd,ngay(dd/mm/yyyy),trigia)
select top (1) with ties sohd,ngayhd,trigia from hoadon order by trigia asc

go

--19.Đưa ra những hoá đơn có tổng thành tiền>1000000
select * from CTHD where (select sum(soluong*dongia)from CTHD) >1000000