create database quanlibanhang create table chitietdathang (sohoadon char(10)foreign key (sohoadon) references dondathang(sohoadon), mahang char(10)foreign key (mahang) references mathang(mahang), giaban int, soluong int, mucgiamgia int) create table dondathang (sohoadon char(10) primary key, makhachhang char(10)foreign key (makhachhang) references khachhang(makhachhang), manhanvien char(10)foreign key (manhanvien) references nhanvien(manhanvien), ngaydathang datetime, ngaygiaohang datetime, ngaychuyenhang datetime, noigiaohang char(10)) create table khachhang (makhachhang char(10) primary key, tencongty char(10), tengiaodich char(10), diachi char(10), email char(10), dienthoai int, fax int) create table loaihang (maloaihang char(10) primary key, tenloaihang char(10)) create table mathang (mahang char(10)primary key, tenhang char(10), macongty char(10) foreign key (macongty) references nhacungcap(macongty), maloaihang char(10) foreign key (maloaihang) references loaihang(maloaihang), soluong int, donvitinh char(10), giahang int) create table nhacungcap (macongty char(10) primary key, tencongty char(10), tengiaodich char(10), diachi char(10), dienthoai int, fax int, email char(10)) create table nhanvien (manhanvien char(10) primary key, ho char(10), ten char(10), ngaysinh datetime, ngaylamviec datetime, diachi char(10), dienthoai int, luongcoban int, phucap int) insert into nhacungcap(macongty,tencongty,tengiaodich,diachi) values (01,A1,000,HANOI) insert into nhacungcap(macongty,tencongty,tengiaodich,diachi) values (02,A2,111,HUE) insert into nhacungcap(macongty,tencongty,tengiaodich,diachi) values (03,A3,222,HCM) select * from nhacungcap insert into mathang(mahang,tenhang,soluong) values (01,Duong,1000) insert into mathang(mahang,tenhang,soluong) values (02,Sua,2000) insert into mathang(mahang,tenhang,soluong) values (03,Banh,3000) select * from mathang insert into nhanvien(manhanvien,ten,ngaysinh,ngaylamviec,luongcoban,phucap) values (01,ANH,12/02/1989,01/01/2011,300000000,300) insert into nhanvien(manhanvien,ten,ngaysinh,ngaylamviec,luongcoban,phucap) values (02,DUC,12/02/1990,02/02/2011,350000000,350) insert into nhanvien(manhanvien,ten,ngaysinh,ngaylamviec,luongcoban,phucap) values (03,ANH,12/02/1991,03/03/2011,200000000,300) select * from nhanvien insert into khachhang(makhachhang,tencongty,tengiaodich,diachi) values (01,A1,000,HANOI) insert into khachhang(makhachhang,tencongty,tengiaodich,diachi) values (02,A2,111,HUE) insert into khachhang(makhachhang,tencongty,tengiaodich,diachi) values (03,A3,222,HCM) select * from khachhang insert into dondathang values (01,01,01,5/12/2000,5/15/2000,5/13/2000,HUE) insert into dondathang values (02,02,01,6/12/2000,6/15/2000,6/13/2000,HCM) insert into dondathang values (03,01,02,4/12/2000,4/15/2000,4/13/2000,HANOI) insert into dondathang values (04,03,02,5/8/2000,5/12/2000,5/10/2000,HUE) insert into dondathang values (05,01,03,7/12/2000,7/15/2000,7/13/2000,DANANG) select * from dondathang insert into chitietdathang values (01,01,10000,1000,10) insert into chitietdathang values (02,03,15000,3000,12) insert into chitietdathang values (03,02,12000,6000,10) insert into chitietdathang values (04,01,10000,1000,10) insert into chitietdathang values (05,03,15000,4000,12) select * from chitietdathang create procedure quanlibanhang @mahang varchar(10), @tenhang varchar(10), @loaihang varchar(10), @giahang int, @macongty varchar(10) as if(@macongty in(select macongty from nhacungcap)) insert into mathang(mahang,tenhang,giahang,macongty) values (@mahang,@tenhang,@giahang,@macongty) else print(0) create procedure quanlibanhang @mahang char(10), @ngay1 datetime, @ngay2 datetime, @sl int output as select sl= SUM(soluong)from chitietdathang,dondathang where @mahang=mahang and @ngay1>=ngaygiaohang and @ngay2
Posted on: Sat, 02 Nov 2013 10:36:47 +0000
Trending Topics
Recently Viewed Topics
© 2015