Select & Update Statements.sql

USE QuanLyBanHang
-- CAU 1--
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX = 'Trung Quoc'
-- CAU 2--
SELECT MASP, TENSP
FROM SANPHAM
WHERE DVT IN('cay', 'quyen')
-- CAU 3--
SELECT MASP, TENSP
FROM SANPHAM
WHERE SUBSTRING(MASP,1,1)='B' AND SUBSTRING(MASP,3,2)='01' 
-- CAU 4--
SELECT MASP, TENSP
FROM SANPHAM
WHERE (NUOCSX='Trung Quoc') AND (GIA BETWEEN 30000 AND 40000)
-- CAU 5--
SELECT MASP, TENSP
FROM SANPHAM
WHERE (NUOCSX IN('Trung Quoc','Thai Lan')) AND (GIA BETWEEN 30000 AND 40000)
-- CAU 6--
SELECT SOHD, TRIGIA
FROM HOADON
WHERE MONTH(NGHD)=1 AND YEAR(NGHD)=2007 AND DAY(NGHD) BETWEEN 1 AND 2
-- CAU 7--
SELECT SOHD, TRIGIA, NGHD
FROM HOADON
WHERE MONTH(NGHD)=1 AND YEAR(NGHD)=2007
ORDER BY (NGHD) ASC, (TRIGIA) DESC
-- CAU 8--
SELECT KHACHHANG.MAKH, HOTEN
FROM KHACHHANG INNER JOIN HOADON ON KHACHHANG.MAKH=HOADON.MAKH
WHERE DAY(NGHD)=1 AND MONTH(NGHD)=1 AND YEAR(NGHD)=2007
-- CAU 9--
SELECT SOHD, TRIGIA
FROM NHANVIEN INNER JOIN HOADON ON NHANVIEN.MANV=HOADON.MANV
WHERE HOTEN='Nguyen Van B'AND DAY(NGHD)=28 AND MONTH(NGHD)=10 AND YEAR(NGHD)=2006
-- CAU 10 --
SELECT SANPHAM.MASP, TENSP
FROM CTHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD INNER JOIN KHACHHANG ON KHACHHANG.MAKH=HOADON.MAKH
WHERE KHACHHANG.HOTEN='Nguyen Van A' AND MONTH(HOADON.NGHD)=10 AND YEAR(HOADON.NGHD)=2006
-- CAU 11--
SELECT CTHD.SOHD
FROM CTHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD
WHERE CTHD.MASP IN('BB01','BB02')
-- CAU 12--
SELECT CTHD.SOHD
FROM CTHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD
WHERE CTHD.MASP IN('BB01','BB02') AND SL BETWEEN 10 AND 20
-- CAU 13--
(SELECT SOHD FROM CTHD WHERE MASP = 'BB01'AND SL >=
10 AND SL <= 20)
INTERSECT
(SELECT SOHD FROM CTHD WHERE MASP = 'BB02' AND SL >=
10 AND SL <= 20)
-- CAU 14--
(SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX='Trung Quoc')
UNION
(SELECT SANPHAM.MASP,TENSP
FROM CTHD INNER JOIN HOADON ON CTHD.SOHD=HOADON.SOHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP
WHERE DAY(NGHD)='1' AND MONTH(NGHD)='1' AND YEAR(NGHD)='2007')
-- CAU 15--
(SELECT MASP,TENSP
FROM SANPHAM)
EXCEPT
(SELECT CTHD.MASP,TENSP
FROM CTHD INNER JOIN SANPHAM ON CTHD.MASP=SANPHAM.MASP)
---------PHAN II------------
-- CAU 2--
SELECT*INTO SANPHAM1 FROM SANPHAM
SELECT*INTO KHACHHANG1 FROM KHACHHANG
-- CAU 3--
UPDATE SANPHAM1
SET GIA = GIA + 0.05*GIA
WHERE NUOCSX = 'Thai Lan'
-- CAU 4--
UPDATE SANPHAM1
SET GIA= GIA-0.05*GIA
WHERE NUOCSX='Trung Quoc' AND GIA<=10000
-- CAU 5--
UPDATE KHACHHANG1
SET LOAIKH='Vip'
WHERE ((NGDK<'1/1/2007' AND DOANHSO>=10000000) OR (NGDK>'1/1/2007' AND DOANHSO>=2000000))
---------PHAN III-------
USE QLGV
--CAU 1--
SELECT MAHV,HO,TEN,NGSINH,LOP.TRGLOP
FROM HOCVIEN INNER JOIN LOP ON HOCVIEN.MAHV=LOP.TRGLOP
-- CAU 2--
SELECT HOCVIEN.MAHV,TEN,HO,LANTHI,DIEM
FROM HOCVIEN INNER JOIN KETQUATHI ON HOCVIEN.MAHV=KETQUATHI.MAHV INNER JOIN MONHOC ON KETQUATHI.MAMH=MONHOC.MAMH
WHERE MONHOC.MAMH='CTRR' AND MALOP='K12'
-- CAU 3--
SELECT HOCVIEN.MAHV,HO,TEN,MAMH
FROM HOCVIEN INNER JOIN KETQUATHI ON HOCVIEN.MAHV=KETQUATHI.MAHV
WHERE LANTHI='1' AND KQUA='Dat'
-- CAU 4--
SELECT HOCVIEN.MAHV,HO,TEN
FROM HOCVIEN INNER JOIN KETQUATHI ON HOCVIEN.MAHV=KETQUATHI.MAHV INNER JOIN LOP ON HOCVIEN.MALOP=LOP.MALOP
WHERE LOP.MALOP='K11' AND KETQUATHI.MAMH='CTRR' AND KQUA='Khong Dat' AND LANTHI='1'
-- CAU 5--
SELECT HOCVIEN.MAHV,HO, TEN
FROM HOCVIEN INNER JOIN KETQUATHI ON HOCVIEN.MAHV=KETQUATHI.MAHV 
WHERE SUBSTRING (MALOP,1,1)='K' AND MAMH='CTRR' AND KQUA='Khong Dat'
-- CAU 6--
SELECT MAMH
FROM GIAOVIEN INNER JOIN  GIANGDAY ON GIAOVIEN.MAGV=GIANGDAY.MAGV
WHERE HOTEN='Tran Tam Thanh' AND HOCKY='1' AND NAM='2006'