Bài giải_STRONGHOLD Database

--Câu 1:
Create database STRONGHOLD
--Câu 2:
create table Customer(
Ccode char(3) primary key,
CName char(50) not null,
Caddress varchar(100) not null default'',
Cphone char(15) default'')

create table OrderMaster(
OrderNo char(10) primary key,
OrderDate datetime not null,
CCode char(3) not null )

create table OrderDetails (
SrNo bigint identity primary key ,
OrderNO char(10) not null ,
ICode char(15) not null ,
Qty int not null default 0)

create table Item(
ICode char(15) primary key,
Iname char(50) not null,
Rate numeric(10,2) not null default 0 )

--Câu 3:
alter table OrderMaster
add constraint PK_OrderMaster_customer
foreign key(Ccode) references customer

alter table orderdetails
add constraint PK_OrderDetails_OrderMaster
foreign key(orderno) references ordermaster

alter table orderdetails
add constraint PK_OrderDetails_item
foreign key(icode) references item

--Câu 4:
-- INSERT VAO BANG CUSTOMER
insert into customer(ccode,cname,caddress,cphone)
values('GHL','Great Holidays Ltd','1,Lydia''s Avenue,Durham-41','115-72-43')
insert into customer(ccode,cname,caddress,cphone)
values('TLT','Travelite Ltd','22,Rodeo Drive,Manhattan-11','443-22-51')
insert into customer(ccode,cname,caddress,cphone)
values('ULS','United Lugage Services','14,Park Avenue,NY-27','123-56-34')
--INSERT VAO BANG ITEM
insert into item(icode,iname,rate)values('RKSK-B','Rucksacks-Brown',450)
insert into item(icode,iname,rate)values('RKSK-T','Rucksacks-Tan',500)
insert into item(icode,iname,rate)values('STCS-24-S-BD','Suitcase 24",Soft,Dark Brown',1575)
insert into item(icode,iname,rate)values('STCS-28-S-B','suitcase 28",Soft,Blue',1790)

-- INSERT VAO BANG ORDERMASTER
insert into ordermaster(orderno,orderdate,ccode) values('0704/99','10/15/1999','ULS')
insert into ordermaster(orderno,orderdate,ccode) values('0256/99','10/6/1999','ULS')
insert into ordermaster(orderno,orderdate,ccode) values('0856/99','10/9/1999','TLT')
insert into ordermaster(orderno,orderdate,ccode) values('0703/99','10/15/1999','TLT')
insert into ordermaster(orderno,orderdate,ccode) values('0083/98','12/30/1999','TLT')

--INSERT VAO BANG ORDERDETAILS
insert into orderdetails(orderno,srno,icode,qty) values('0083/98','1','RKSK-T',100)
insert into orderdetails(orderno,srno,icode,qty) values('0083/98','2','STCS-24-S-BD',100)
insert into orderdetails(orderno,srno,icode,qty) values('0256/99','3','STCS-24-S-BD',50)
insert into orderdetails(orderno,srno,icode,qty) values('0703/99','4','RKSK-T',70)
insert into orderdetails(orderno,srno,icode,qty) values('0703/99','5','STCS-24-S-BD',30)
insert into orderdetails(orderno,srno,icode,qty) values('0704/99','6','RKSK-T',20)
insert into orderdetails(orderno,srno,icode,qty) values('0856/99','7','RKSK-T',120)

--Câu 5:
--a
select icode as 'Item Code',iname as 'Item Name',(rate-rate*20/100) as 'Discounted Rate' from item
--b
select ordermaster.Orderno,ordermaster.Orderdate, orderdetails.ICode, item.Rate
from ordermaster join orderdetails on ordermaster.orderno=orderdetails.orderno
join item on orderdetails.icode=item.icode
--c
select Icode,Iname,Rate from item
where rate>(select rate from item where iname='Rucksacks-Tan')
--d
select customer.cname, sum(item.rate* orderdetails.qty) as ‘Tong so tien'
from customer join ordermaster on customer.ccode=ordermaster.ccode
join orderdetails on ordermaster.orderno=orderdetails.orderno
join item on orderdetails.icode=item.icode
group by cname
--e
update item set rate=400
where iname='Rucksacks-Brown'
--f
delete item where icode='rksk-B'

--Câu 6:
create view customer_view as
select customer.ccode, customer.cname, customer.cphone, ordermaster.orderdate, orderdetails.icode, orderdetails.qty
from customer join ordermaster on customer.ccode=ordermaster.ccode
join orderdetails on ordermaster.orderno=orderdetails.orderno

--Câu 7:
create procedure Dis_amount
@v_ccode char(3),
@v_Orderno char(10)
as
select sum(orderdetails.qty* item.rate) as 'Amount'
from ordermaster inner join orderdetails
on ordermaster.orderno=orderdetails.orderno
inner join item on orderdetails.icode=item.icode
where ccode=@v_ccode and orderdetails.orderno=@v_Orderno

--THUC THI PROCEDURE DIS_AMOUNT
exec Dis_amount 'TLT','0703/99'
--Câu 8:
create trigger Ins_trig on item
for insert
as
if((select rate from inserted)>500)
begin
print('rate does not exceed 500')
rollback transaction
end
--Kiem tra lai trigger
insert into item values('001', 'New Item', 900)