SellingMgmt Script

create database SellingManagement
GO
use SellingManagement
GO
create table CUSTOMERS
(
CUST_ID CHAR(4) PRIMARY KEY,
NAME VARCHAR(40),
ADDRESS VARCHAR(50),
PHONE VARCHAR(20),
BIRTH_DATE SMALLDATETIME,
REG_DATE SMALLDATETIME,
TOTAL_ORDER MONEY
)
CREATE TABLE EMPLOYEES
(
EMP_ID CHAR(4) PRIMARY KEY,
EMP_NAME VARCHAR(40),
PHONE VARCHAR(20),
HIRE_DATE SMALLDATETIME
)
CREATE TABLE PRODUCTS
(
PRO_ID CHAR(4) PRIMARY KEY,
PRO_NAME VARCHAR(40),
UNIT VARCHAR(20),
COUNTRY VARCHAR(40),
PRICE MONEY
)
CREATE TABLE ORDERS
(
ORDER_ID INT PRIMARY KEY,
ORDER_DATE SMALLDATETIME,
CUST_ID CHAR(4),
EMP_ID CHAR(4),
TOTAL MONEY
)
Create table ORDER_ITEMS
(
ORDER_ID int foreign key references ORDERS(ORDER_ID),
PRO_ID char(4) foreign key references PRODUCTS(PRO_ID),
QUANTITY int,
constraint PK_ORDER_ITEMS primary key (ORDER_ID,PRO_ID)
)
ALTER TABLE ORDERS ADD CONSTRAINT FK_CUST_ID FOREIGN KEY(CUST_ID)
REFERENCES CUSTOMERS(CUST_ID)
ALTER TABLE ORDERS ADD CONSTRAINT FK_EMP_ID FOREIGN KEY(EMP_ID)
REFERENCES EMPLOYEES (EMP_ID)
set dateformat dmy

/*INSERT DU LIEU*/
insert into CUSTOMERS values('KH01','Nguyen Van A','731 Tran Hung Dao, Q5,
TpHCM','8823451','22/10/1960','22/07/2006',13060000)
insert into CUSTOMERS values('KH02','Tran Ngoc Han','23/5 Nguyen Trai, Q5,
TpHCM','908256478','03/04/1974','30/07/2006',280000)
insert into CUSTOMERS values('KH03','Tran Ngoc Linh','45 Nguyen Canh Chan, Q1,
TpHCM','938776266','12/06/1980','08/05/2006',3860000)
insert into CUSTOMERS values('KH04','Tran Minh Long','50/34 Le Dai Hanh, Q10,
TpHCM','917325476','09/03/1965','10/02/2006',250000)
insert into CUSTOMERS values('KH05','Le Nhat Minh','34 Truong Dinh, Q3,
TpHCM','8246108','10/03/1950','28/10/2006',21000)
insert into CUSTOMERS values('KH06','Le Hoai Thuong','227 Nguyen Van Cu, Q5,
TpHCM','8631738','31/12/1981','24/11/2006',915000)
insert into CUSTOMERS values('KH07','Nguyen Van Tam','32/3 Tran Binh Trong, Q5,
TpHCM','916783565','06/04/1971','12/01/2006',12500)
insert into CUSTOMERS values('KH08','Phan Thi Thanh','45/2 An Duong Vuong, Q5,
TpHCM','938435756','10/01/1971','13/12/2006',365000)
insert into CUSTOMERS values('KH09','Le Ha Vinh','873 Le Hong Phong, Q5,
TpHCM','8654763','03/09/1979','14/01/2007',70000)
insert into CUSTOMERS values('KH10','Ha Duy Lap','34/34B Nguyen Trai, Q1,
TpHCM','8768904','02/05/1983','16/01/2007',67500)
-------------------------------
-- NHANVIEN
insert into EMPLOYEES values('NV01','Nguyen Nhu Nhut','927345678','13/04/2006')
insert into EMPLOYEES values('NV02','Le Thi Phi Yen','987567390','21/04/2006')
insert into EMPLOYEES values('NV03','Nguyen Van B','997047382','27/04/2006')
insert into EMPLOYEES values('NV04','Ngo Thanh Tuan','913758498','24/06/2006')
insert into EMPLOYEES values('NV05','Nguyen Thi Truc Thanh','918590387','20/07/2006')
select*from CUSTOMERS
/*NHAP DU LIEU PRODUCTS*/
insert into PRODUCTS values('BC01','But chi','cay','Singapore',3000)
insert into PRODUCTS values('BC02','But chi','cay','Singapore',5000)
insert into PRODUCTS values('BC03','But chi','cay','Viet Nam',3500)
insert into PRODUCTS values('BC04','But chi','hop','Viet Nam',30000)
insert into PRODUCTS values('BB01','But bi','cay','Viet Nam',5000)
insert into PRODUCTS values('BB02','But bi','cay','Trung Quoc',7000)
insert into PRODUCTS values('BB03','But bi','hop','Thai Lan',100000)
insert into PRODUCTS values('TV01','Tap 100 mong','quyen','Trung Quoc',2500)
insert into PRODUCTS values('TV02','Tap 200 mong','quyen','Trung Quoc',4500)
insert into PRODUCTS values('TV03','Tap 100 tot','quyen','Viet Nam',3000)
insert into PRODUCTS values('TV04','Tap 200 tot','quyen','Viet Nam',5500)
insert into PRODUCTS values('TV05','Tap 100 trang','chuc','Viet Nam',23000)
insert into PRODUCTS values('TV06','Tap 200 trang','chuc','Viet Nam',53000)
insert into PRODUCTS values('TV07','Tap 100 trang','chuc','Trung Quoc',34000)
insert into PRODUCTS values('ST01','So tay 500 trang','quyen','Trung Quoc',40000)
insert into PRODUCTS values('ST02','So tay loai 1','quyen','Viet Nam',55000)
insert into PRODUCTS values('ST03','So tay loai 2','quyen','Viet Nam',51000)
insert into PRODUCTS values('ST04','So tay','quyen','Thai Lan',55000)
insert into PRODUCTS values('ST05','So tay mong','quyen','Thai Lan',20000)
insert into PRODUCTS values('ST06','Phan viet bang','hop','Viet Nam',5000)
insert into PRODUCTS values('ST07','Phan khong bui','hop','Viet Nam',7000)
insert into PRODUCTS values('ST08','Bong bang','cai','Viet Nam',1000)
insert into PRODUCTS values('ST09','But long','cay','Viet Nam',5000)
insert into PRODUCTS values('ST10','But long','cay','Trung Quoc',7000)
/*nhap du lieu SO HOA DON */

INSERT INTO ORDERS VALUES ('1001','20060723','KH01','NV01',320000)
INSERT INTO ORDERS VALUES ('1022','20070116',Null,'NV03',7000)
INSERT INTO ORDERS VALUES ('1023','20070117',Null,'NV01',330000)
select*from ORDERS
/*nhap du lieu CHI TIET HOA DON */
insert into ORDER_ITEMS values(1001,'TV02',10)

insert into ORDER_ITEMS values(1001,'ST01',5)
insert into ORDER_ITEMS values(1001,'BC01',5)
insert into ORDER_ITEMS values(1002,'BB01',20)
select * from ORDER_ITEMS

SellingMgmt Script

Uploaded by

Copyright:

Available Formats

SellingMgmt Script

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SellingMgmt Script

Uploaded by

Copyright:

Available Formats

create database SellingManagement

set dateformat dmy

/nhap du lieu SO HOA DON /

/nhap du lieu CHI TIET HOA DON /

insert into ORDER_ITEMS values(1001,'TV02',10)

select * from ORDER_ITEMS

You might also like