-----------------------------------------------------------------------------

--LAY TEN SAN PHAM ,TEN NHASX, TEN NHOM,GIA VA SAP XEP THEO CHIEU GIAMR DAN CUA GIA SP --

SELECT TENSANPHAM,TENNSX,TENNHOM,GIA ,ANH

FROM SANPHAM,NHASANXUAT,NHOMSANPHAM

WHERE SANPHAM.NHOM=NHOMSANPHAM.MANHOM AND SANPHAM.NSX=NHASANXUAT.MANSX ORDER BY GIA DESC

--------------------------------------------------------

--LAY NHUNG SAN PHAM MA NHA SAN XUAT ='SAMSUNG'SAP XEP THEO CHIEU GIAM DAN CUA 'GIA'

SELECT TENSANPHAM,TENNSX,GIA

FROM SANPHAM SP,NHASANXUAT NSX

WHERE SP.NSX=NSX.MANSX AND TENNSX ='SAMSUNG' ORDER BY GIA ASC

---------------------------------------------------------------------

--LAY NHUNG SAN PHAM CO 'GIA' LON HON 25

SELECT MASANPHAM,TENSANPHAM,GIA ,NAMSX

FROM SANPHAM

WHERE GIA >= '200'

----------------------------------------------------------

---DEM SO LUONG SANPHAM CUA TUNG NHA SAN XUAT ----

--select count(nsx='01') as canon,count(nsx='02')as panasonic,count(nsx='03')as sony,count(nsx='04')astoshiba,count(nsx='05')as hp

--,count(nsx='06')as dell,count(nsx='07')as sansung,count(nsx='08')as khac

--from sanpham,nhasanxuat

--where sanpham.nsx = nhasanxuat.mansx

--------------------

-- lay top 10 san pham co gia cao nhat

select top 10 masanpham,tensanpham,tennsx,gia,namsx

from sanpham ,nhasanxuat

where sanpham.nsx=nhasanxuat.mansx order by gia desc

----------------------------

--10 samn pham co gia thap nhat

select top 10 masanpham,tensanpham,tennsx,gia,namsx

from sanpham ,nhasanxuat

where sanpham.nsx=nhasanxuat.mansx order by gia asc

------------------------------------

--TAO MOI MOT CSDL---

create database nhansu

use nhansu

create table nhanvien

(

ma nvarchar(10) primary key,

hoten nvarchar(50) NOT NULL,

gioitinh nvarchar(3) null,

diachi nvarchar (100) null,

DONVI NVARCHAR (10)NOT NULL FOREIGN KEY(DONVI) REFERENCES DONVI(MA)

nghenghiep nvarchar(100) null,

mucluong money null

)

------------LENH INSERT--

--CHEN THEM COT 'DONVI' VAO BANG 'NHANVIEN'

ALTER TABLE NHANVIEN ADD DONVI NVARCHAR(10) null --FOREIGN KEY(DONVI) REFERENCES DONVI(MA)

-----------------

create table donvi

(

ma nvarchar(10),tendonvi nvarchar(100)

)

-------------------------

--TAO RANG BUOC 2 BANG VOI NHAU = KHOA NGOAI

-------------------------

--CAU LENH DISTINCT---------

SELECT DISTINCT TENNSX ,TENSANPHAM,GIA

FROM SANPHAM,NHASANXUAT

WHERE SANPHAM.NSX=NHASANXUAT.MANSX

------------------