Solutions on order processing Database - Request to all the user to suggest for the corrections and optimization

create database ord_proc
use ord_proc
CREATE TABLE CUSTOMER (
custid int,
cname char(15) not null,
city varchar(30),
primary key (custid)
)


insert into CUSTOMER values (111,'John Smith', 'Karkala')
insert into CUSTOMER values (112,'Ramesh N', 'Nitte')
insert into CUSTOMER values (113,'Franklin', 'Karkala')
insert into CUSTOMER values (114,'Alica', 'mangalore')
insert into CUSTOMER values (115,'Raju', 'Udupi')

drop table customer
drop table c_order
drop table item
drop TABLE ORDER_ITEM
drop table shipment
drop table warehouse

CREATE TABLE C_ORDER (
orderid int,
odate datetime,
custid int,
ordamt int,
primary key (orderid)  ,
foreign key(custid) references CUSTOMER(custid)on delete cascade on update cascade,
)


insert into C_ORDER values (201,'2001-08-03', 111,null)
insert into C_ORDER values (202,'2002-08-03', 111,null)
insert into C_ORDER values (203,'2001-08-04', 112,null)
insert into C_ORDER values (204,'2004-02-01', 113,null)
insert into C_ORDER values (205,'2001-04-02', 114,null)
insert into C_ORDER values (206,'2005-02-01', 115,null)



update C_ORDER set ordamt = (select sum(O.qty * T.price) from ORDER_ITEM O, ITEM T
                              where O.itemid = T.itemid and O.orderid = 206)

where orderid = 206

select * from C_ORDER

CREATE TABLE ITEM (
itemid  int,
price int,
primary key (itemid)
 )

insert into ITEM values (301,2000)
insert into ITEM values (302,2000)
insert into ITEM values (303,1000)
insert into ITEM values (304,5000)
insert into ITEM values (305,4000)


CREATE TABLE ORDER_ITEM (
orderid int,
itemid int,
qty int,
primary key (orderid,itemid),
foreign key(orderid) references C_ORDER(orderid) on delete cascade on update cascade,
foreign key(itemid) references ITEM(itemid) on delete cascade on update cascade,
)

insert into ORDER_ITEM values (201,302,4)
insert into ORDER_ITEM values (201,303,4)
insert into ORDER_ITEM values (201,304,4)
insert into ORDER_ITEM values (202,303,2)
insert into ORDER_ITEM values (202,305,4)
insert into ORDER_ITEM values (203,302,1)
insert into ORDER_ITEM values (204,305,2)




CREATE TABLE WAREHOUSE (
warehouseid int,
city varchar(20)not null,
primary key (warehouseid)
  )


insert into WAREHOUSE values (1,'MAGALORE')
insert into WAREHOUSE values (2,'MAGALORE')
insert into WAREHOUSE values (3,'MAGALORE')
insert into WAREHOUSE values (4,'UDUPI')
insert into WAREHOUSE values (5,'UDUPI')
insert into WAREHOUSE values (6,'KARKALA')


select * from WAREHOUSE

CREATE TABLE SHIPMENT (
orderid int,
warehouseid int,
ship_dt datetime,
primary key (orderid,warehouseid)  ,
foreign key(orderid) references C_ORDER(orderid) on delete cascade on update cascade,
foreign key(warehouseid) references WAREHOUSE(warehouseid) on delete cascade on update cascade
  )



SELECT * FROM C_ORDER

insert into SHIPMENT values (201,1,'2001-04-02')
insert into SHIPMENT values (201,2,'2001-04-04')
insert into SHIPMENT values (202,1,'2001-05-02')

insert into SHIPMENT values (202,2,'2002-05-12')
insert into SHIPMENT values (202,3,'2003-06-01')
insert into SHIPMENT values (202,4,'2003-06-01')
insert into SHIPMENT values (203,1,'2004-02-01')
insert into SHIPMENT values (203,2,'2004-02-01')
insert into SHIPMENT values (203,3,'2004-02-01')
insert into SHIPMENT values (204,4,'2004-06-02')



SELECT * FROM WAREHOUSE
SELECT * FROM SHIPMENT

select date(ship_dt) from SHIPMENT

SELECT DATEPART(ship_dt,date ) from SHIPMENT

SELECT  left(10,ship_dt ) from SHIPMENT

select ship_dt from  SHIPMENT

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

SELECT GETDATE()

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

SELECT CONVERT(VARCHAR(10),ship_dt,111) as shipdate from SHIPMENT

select datepart(year,month)




1. Produce a listing: CUSTNAME, #oforders, AVG_ORDER_AMT, where the middle column is the total
numbers of orders by the customer and the last column is the average order amount for that customer.

select C.cname , count(O.orderid) as NO_OF_ORDR, avg(O.ordamt) as AVG_ORD_AMT
from CUSTOMER C, C_ORDER O
where C.custid = O.custid group  by C.cname


2.List the order# for orders that were shipped from all the warehouses that the company has in a specific city.

using not in
------------
select O.orderid from C_ORDER O
where not exists (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid)
                 )



select O.orderid from C_ORDER O
where not exists (
                        (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid))
                                         union

(select warehouseid from SHIPMENT  where orderid = O.orderid and   warehouseid not in
(select warehouseid from WAREHOUSE where city = 'MAGALORE'))
                       
                 )




select O.orderid from C_ORDER O
where not exists (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid)
                           
                 )


using count
-----------

select A.orderid from shipment A,warehouse B
where A.warehouseid = B.warehouseid and B.city='MAGALORE' group by A.orderid
having count(*) = (select count(*) from warehouse where city='MAGALORE')


using left outer join
----------------------

select O.orderid from C_ORDER O
where not exists (select orderid  from (
                    (select warehouseid from WAREHOUSE where city = 'MAGALORE') as R1
                        left outer join
                     (select warehouseid, orderid  from SHIPMENT  where orderid = O.orderid) as R2 on  R1.warehouseid = R2.warehouseid)
                    where orderid is null
                 )






3. Demonstrate the deletion of an item from the ITEM table and
demonstrate a method of handling the rows in the
ORDER_ITEM table that contain this particular item.