create database quanlybanhang_giaovien;

go

use quanlybanhang_giaovien;

go

--create table

drop table ITem_Details;

go

create table Item_Details(

Item_No varchar(5) constraint PK_Item_Details

primary key,

Item_Name varchar(max) not null,

Item_Description varchar(50),

QOH int not null,

Amount int not null default 0,

ROS int not null,

constraint CK_Amount check (Amount>=0)

);

go

create table Customer_Details(

Cust_ID varchar(5) constraint PK_Cust primary key,

Cust_Name varchar(max) not null,

Cust_Address varchar(50),

Cust_ph_no varchar(15)

);

go

create table Transaction_Master(

Tran_No int identity constraint PK_trans primary key,

Cust_ID varchar(5) not null constraint FK_Cust references Customer_Details,

Tran_Date datetime default getdate(),

Tran_Type varchar(15),

Model varchar(9)

);

go

create table Transaction_Details(

Tran_No int not null constraint FK_Tran references Transaction_Master,

Item_No varchar(5) not null constraint FK_Item references Item_Details,

QuantityOrdered int constraint CK_Qty check (QuantityOrdered>0),

UnitPrice money constraint CK_Unit check (UnitPrice>0),

TotalAmount money,

constraint PK_Tran_Details primary key (Tran_No, Item_No)

);

--chen du lieu

insert into Item_Details values ('B005','Red Bull','Drink',255,6000,20);

insert into Item_Details values ('D001','Coca Cola','Drink',795,1000,50);

insert into Item_Details values ('D004','Saigon Special','Drink beer',488,14000,50);

insert into Item_Details values ('E002','Number One','Drink',386,1,1);

insert into Item_Details values ('E003','Heineken','Drink beer',123,3,4);

go

insert into Customer_Details values ('F001','AS','Hanoi','0903287952');

insert into Customer_Details values ('F002','B','Hungyen','0903287952');

insert into Customer_Details values ('F003','C','Namdinh','0903287952');

insert into Customer_Details values ('F004','D','Bacninh','0903287952');

insert into Customer_Details values ('F005','E','Hatay','0903287952');

go

insert into Transaction_Master values ('F002','12/10/2008','Cash','Inhouse');

insert into Transaction_Master values ('F001','12/11/2008','Credit Card','Take-away');

insert into Transaction_Master values ('F001','12/15/2008','Cash','Inhouse');

insert into Transaction_Master values ('F002','12/14/2008','Credit Card','Take-away');

insert into Transaction_Master values ('F003','12/18/2008','Cash','Take-away');

go

insert into Transaction_Details values (1,'B005',100,30,3000);

insert into Transaction_Details values (1,'D001',20,100,2000);

insert into Transaction_Details values (1,'D004',10,100,1000);

insert into Transaction_Details values (2,'B005',50,50,2500);

insert into Transaction_Details values (2,'E002',40,50,2000);

--lay du lieu

go

select * from Item_Details;

select * from Customer_Details;

select * from Transaction_Master;

select * from Transaction_Details;

go

--cap nhat du lieu

update Item_Details set Amount=Amount+100

where Item_Name like 'Coca Cola';

go

select * from Item_Details;

--phan 3.2

update Transaction_Details set

Unitprice=UnitPrice*0.9

where QuantityOrdered>=50

go

select * from Transaction_Details;

--phan 3.3:

go

update Customer_Details set Cust_ph_no='no phone'

where Cust_ph_no is null;

go

insert into Customer_Details values ('A001','abc','1234567',Null);

go

select * from Customer_Details;

--phan 3.4

update item_details set item_name .write('alcohol-free',0,0)

where item_Description like 'Drink beer';

go

select * from item_details;

--phan 3.5

update Customer_Details set

Cust_Name .write('Care',len(Cust_Name),0)

where Cust_ID not in (select a.Cust_ID from

Customer_Details a, Transaction_Master b, Transaction_Details c

where a.Cust_ID = b.Cust_ID and b.Tran_No = c.Tran_No)

go

select * from Customer_Details;

--PHAN 4.1:

go

select * from Customer_Details where Cust_Name like 'c%'

go

select top 25 percent * from Transaction_Master;

--thu lam voi view

create view abc

as

select * from Customer_Details;

go

select * from abc;

go

create view abc1

as

select count(*) [songuoi] from Customer_Details;

go

select * from abc1;

--tao view de xem khach nay mua hang gi

go

create view abc2

as

select a.Cust_ID, a.Cust_Name, d.item_name, c.*

from Customer_Details a,

Transaction_Master b,

Transaction_Details c,

Item_Details d

where a.Cust_ID= b.Cust_ID and

b.Tran_NO= c.Tran_No and

c.Item_No = d.Item_No

go

select * from abc2;

select Cust_Name, sum(TotalAmount) [Tong]

from abc2

group by Cust_Name;

--tao mot view dua ra danh sach khach hang sap xep theo ten giam dan

create view abc3

as

select top 100 percent * from Customer_Details

order by Cust_Name desc;

go

select * from Customer_Details;

select * from abc3;

go

alter view abc3

as

select top 99.99 percent * from Customer_Details

order by Cust_Name desc;

go

select a.*, b.* from Transaction_Master a,

Transaction_Details b

where a.Tran_No= b.Tran_No;

select a.Tran_No, a.Cust_Id, a.Tran_Date,

sum(b.totalAmount) [TotalAmount]

from transaction_Master a,Transaction_Details b

where a.Tran_No = b.Tran_No

group by all a.Tran_No, a.Cust_ID, a.Tran_Date;

go

select a.Cust_Name [Customer Name],

b.Tran_Date [Transaction_Date]

from Customer_Details a, Transaction_Master b

where a.Cust_ID = b.Cust_ID

and a.Cust_ID='F001';

select top 1 with ties * from Item_Details

order by QOH desc;

select * from Item_Details where

QOH=(select max(QOH) from Item_Details);

--cau tiep theo

--tao 1 trigger de neu sua Cust_ID thi cac cho lien quan phai sua theo

create trigger trg_update

on Customer_Details

instead of update

as

--disabled constraint FK_Cust

alter table Transaction_Master nocheck constraint FK_Cust;

--update 2 bang

update Customer_Details set Cust_ID = (select Cust_ID from Inserted)

where Cust_ID= (select Cust_ID from Deleted);

update Transaction_Master set Cust_ID = (select Cust_ID from Inserted)

where Cust_ID= (select Cust_ID from Deleted);

--phuc hoi lai constraint

alter table Transaction_Master check constraint FK_Cust;

GO

update customer_Details set Cust_ID='F0001' where

Cust_ID = 'F001';

--them 1 cot status varchar(30) vao bang ITem_Details

alter table Item_Details add status varchar(30);

--tao sp update cho cot status theo yeu cau sau (chi dung 1 lenh update)

--QOH<300: 'chat luong kem'

--QOH>=300 QOH<=500: 'chat luong vua vua'

--con lai bao tot

/*create proc update_item

as

update Item_Details set

status = case

when QOH<300 then

'chat luong kem'

when QOH>=300 and QOH<500 then

'chat luong vua vua'

else

'chat luong tot'

end

*/

go

exec update_item;

select * from Item_Details;