Bài 1:

a. Tạo bảng:

use master

go

create database banhangrong

go

use banhangrong

go

create table khachhang

(

makhachhang char(6) not null primary key,

tencongty nvarchar(20) not null,

tengiaodich nvarchar(30) not null,

diachi nvarchar(30) not null,

email char(30) null,

dienthoai char(10) null,

fax char(10) null

)

go

create table dondathang

(

sohoadon char(6) not null primary key,

makhachhang char(6) not null,

manhanvien char(6) not null,

ngaydathang datetime not null,

ngaygiaohang datetime not null,

ngaychuyenhang datetime not null,

noigiaohang nvarchar(40) not null

)

go

create table nhanvien

(

manhanvien char(6) not null primary key,

ho nvarchar(10) not null,

ten nvarchar(20) not null,

ngaysinh datetime not null,

ngaylamviec datetime not null,

diachi nvarchar(40) not null,

dienthoai char(10) not null,

luongcoban char(10) not null,

phucap char(10) null

)

go

create table nhacungcap

(

macongty char(6) primary key,

tencongty nvarchar(30) not null,

tengiaodich nvarchar(30) not null,

diachi nvarchar(40) not null,

dienthoai char(10) not null,

fax char(10) null,

email varchar(30) null

)

go

create table chitietdathang

(

sohoadon char(6) not null,

mahang char(6) not null,

giaban char(10) not null,

soluong char(10) not null,

mucgiamgia char(10) null,

constraint pk_ctdh primary key(sohoadon, mahang)

)

go

create table mahang

(

mahang char(6) primary key,

tenhang nvarchar(20) not null,

macongty char(6) not null,

maloaihang char(6) not null,

soluong char(10) not null,

donvitinh nvarchar(20) not null,

giahang char(10) not null

)

go

create table loaihang

(

maloaihang char(6) primary key,

tenloaihang nvarchar(30) not null

)

go

alter table dondathang

add

constraint ddh_kh foreign key(makhachhang) references khachhang(makhachhang),

constraint ddh_nv foreign key(manhanvien) references nhanvien(manhanvien)

go

alter table chitietdathang

add

constraint ctdh_ddh foreign key(sohoadon) references dondathang(sohoadon),

constraint ctdh_mh foreign key(mahang) references mahang(mahang)

go

alter table mahang

add

constraint mh_ncc foreign key(macongty) references nhacungcap(macongty),

constraint mh_lh foreign key(maloaihang) references loaihang(maloaihang)

go

insert into khachhang values ('001','xuyen chi','xuyenchi','nguyen du,ha noi','[email protected]','047345678','')

insert into khachhang values ('002','hoa lan','hoa lan','nguyen du,ha noi','[email protected]','047345678','')

insert into khachhang values ('003','mai chi','mai chi','nguyen trai,sai gon','[email protected]','0883345678','')

insert into khachhang values ('004','hoang anh','hoang anh','tran phu,ha noi','[email protected]','048366678','')

insert into khachhang values ('005','a chau','a chau','nguyen tri thanh,ha noi','[email protected]','0473945678','')

go

insert into nhanvien values ('n01','tan','loan','10/10/1987','10/10/1987','me linh,ha noi','0975660333','500','300')

insert into nhanvien values ('n02','nguyen','hien','10/10/1987','10/10/1987','me linh,ha noi','0975660333','500','300')

insert into nhanvien values ('n03','trinh','dao','10/10/1987','10/10/1987','me linh,ha noi','0975660333','500','300')

insert into nhanvien values ('n04','le','huyen','10/10/1987','10/10/1987','me linh,ha noi','0975660333','500','300')

insert into nhanvien values ('n05','dao','thuan','10/10/1987','10/10/1987','me linh,ha noi','0975660333','500','300')

go

insert into dondathang values ('x01','001','n01','10/10/1987','10/10/1987','10/10/1987','may 10')

insert into dondathang values ('x02','002','n02','10/10/1987','10/10/1987','10/10/1987','dinh cong')

insert into dondathang values ('x03','003','n03','10/10/1987','10/10/1987','10/10/1987','det ha noi')

insert into dondathang values ('x04','004','n04','10/10/1987','10/10/1987','10/10/1987','bo cong an')

insert into dondathang values ('x05','005','n05','10/10/1987','10/10/1987','10/10/1987','cuc dang kiem')

go

insert into nhacungcap

values ('m01','gia long','gia long','tay son,ha noi','0987996262','8999978','[email protected]')

insert into nhacungcap

values ('m02','tran anh','tran anh','xuan thuy,ha noi','0990626262','998978','[email protected]')

insert into nhacungcap

values ('m03','viet tin','viet tin','long bien,ha noi','0988826262','888978','[email protected]')

insert into nhacungcap

values ('m04','good luck','good luck','thanh tong,ha noi','0987996262','8998978','[email protected]')

insert into nhacungcap

values ('m05','maybe','maybe','hang bong,ha noi','0987626299','9908978','[email protected]')

go

2. Lập các bản ghi:

insert into loaihang values ('00l','giay')

insert into loaihang values ('00g','do dung hoc tap')

insert into loaihang values ('002','hoc tap')

insert into loaihang values ('00n','do uong')

insert into loaihang values ('00b','but')

go

insert into mahang values ('h01','vo hong ha','m01','00l','23','cuon','200')

insert into mahang values ('h02','banh my','m02','00b','23','chiec','400')

insert into mahang values ('h03','nuoc loc','m03','00n','29','chai','800')

insert into mahang values ('h04','giay a4','m04','00g','34','tap','5000')

insert into mahang values ('h05','but thien long','m05','00b','77','chiec','150')

go

insert into chitietdathang values ('x01','h01','900','89','0')

insert into chitietdathang values ('x02','h02','400','22','3%')

insert into chitietdathang values ('x03','h03','600','2','20%')

insert into chitietdathang values ('x04','h04','800','53','50%')

insert into chitietdathang values ('x05','h05','200','60','0')

go

Bài 2:

use banhangrong

-- Danh sách các d?i tác cung c?p hàng cho công ty

select * from nhacungcap

go

-- Mã hàng, tên hàng và s? lu?ng c?a các m?t hàng hi?n có trong công ty

select mahang,tenhang,soluong from mahang

go

-- H? tên, d?a ch?, nam b?t d?u làm vi?c c?a các nhân viên trong công ty

select ho,ten,diachi,ngaylamviec from nhanvien

go

-- Ð?a ch?, di?n tho?i c?a 1 nhà cung c?p c? th? nào dó

select diachi,dienthoai from nhacungcap where (tencongty='Tran Anh')

go

-- Mã và tên c?a các m?t hàng có giá tr? l?n hon 300 và s? lu?ng ít hon 25

select mahang,tenhang from mahang where (giahang>'300') and (soluong<'25')

go

-- M?i m?t hàng trong côcng ty do ai cung c?p

select mahang.tenhang,nhacungcap.tencongty from mahang,nhacungcap where (mahang.macongty = nhacungcap.macongty)

go

-- Nh?ng m?t hàng mà 1 nhà cung c?p nào dó có th? cung c?p

select mahang.tenhang from mahang,nhacungcap where (mahang.macongty = nhacungcap.macongty) and ( nhacungcap.tencongty='tran anh')

go

-- Lo?i hàng d? u?ng do công ty nào cung c?p và d?a ch? c?a các công ty dó

select loaihang.tenloaihang, nhacungcap.tencongty, nhacungcap.diachi from loaihang,mahang,nhacungcap where (loaihang.maloaihang=mahang.maloaihang) and ( mahang.macongty=nhacungcap.macongty) and (tenloaihang='do uong')

go

-- Nh?ng khách hàng dã d?t mua m?t hàng nu?c l?c c?a công ty?

select khachhang.tengiaodich,mahang.tenhang,nhacungcap.tencongty

from khachhang,nhacungcap,dondathang,mahang,chitietdathang

where (khachhang.makhachhang=dondathang.makhachhang) and (dondathang.sohoadon=chitietdathang.sohoadon)and (mahang.mahang=chitietdathang.mahang) and (mahang.macongty=nhacungcap.macongty)

and(mahang.tenhang='banh my')

go

-- Ðon d?t hàng s? 1 do ai d?t và do nhân viên nào l?p, th?i gian và d?a di?m giao hàng

select khachhang.tengiaodich,nhanvien.ho,nhanvien.ten,dondathang.ngaygiaohang,dondathang.noigiaohang

from khachhang,dondathang,nhanvien

where (khachhang.makhachhang=dondathang.makhachhang) and( khachhang.makhachhang='001') and(dondathang.manhanvien=nhanvien.manhanvien)

go