ElaineBrown**, C# - rextester

ElaineBrown**

Create procedure abc(
@a varchar(10) = 'Hello',
@b varchar(10)
)
AS
BEGIN
Select a+b
End

Exec abc 7,' starts'
 number(5) references orders10(orderid),orderqty number(4),itemno number(5) references 
item10(itemno) ON DELETE SET NULL);

Table 5
======
create table warehouse10(warehouseno number(5) primary key,wcity varchar(12));

Table 6
======
create table shipment10(orderid number(5) references orders10(orderid),warehouseno number(5) references warehouse10(warehouseno),
shipdate date);

Insert Queries:
===========
insert into customer10 values(2001,'hari','mysore');
insert into customer10 values(2002,'harika','bangalore');
insert into customer10 values(2003,'helen','chennai');
insert into customer10 values(2004,'Bishal','goa');
insert into customer10 values(2005,'Sarkar','cochin');

insert into orders10 values(101,'10-jan-2021',2001,2400);
insert into orders10 values(102,'20-jan-2021',2002,500);
insert into orders10 values(103,'16-jan-2021',2003,1400);
insert into orders10 values(104,'21-jan-2021',2002,1100);
insert into orders10 values(105,'17-jan-2021',2003,2200);

insert into item10 values(31,10);
insert into item10 values(32,5);
insert into item10 values(33,15);
insert into item10 values(34,12);
insert into item10 values(35,14);

insert into orderitem10 values(101,200,31);
insert into orderitem10 values(102,100,32);
insert into orderitem10 values(103,50,33);
insert into orderitem10 values(104,54,34);
insert into orderitem10 values(105,500,35);

insert into warehouse10 values(01,'bangalore');
insert into warehouse10 values(02,'mysore');
insert into warehouse10 values(03,'delhi');
insert into warehouse10 values(04,'chennai');
insert into warehouse10 values(05,'cochin');

insert into shipment10 values(101,01,'12-jan-2021');
insert into shipment10 values(102,03,'22-jan-2021');
insert into shipment10 values(103,02,'18-jan-2021');
insert into shipment10 values(104,02,'22-jan-2021');
insert into shipment10 values(105,02,'20-jan-2021');

QUERIES:
========
   1) Produce a listing(name, no of orders, average order amount).

select c.cname,count(o.orderid),avg(o.orderamt) from customer10 c,orders10 o 
       where c.custid=o.custid group by c.cname;

CNAME        COUNT(O.ORDERID)   AVG(O.ORDERAMT)
	------------         ----------------                     ---------------
	harika                     2            		 800
	hari                         1            		2400
	helen                       2            		1800

2) List the order number for orders that were shipped from all warehouse the company has in a specific city.

select orderid,warehouseno from shipment10 
       where warehouseno in(select warehouseno from warehouse10
       where wcity='mysore');

ORDERID WAREHOUSENO
	---------- 	  -----------
       	103          	   2
       	104               2
       	105           	   2

3) Demonstrate how to delete item no 10 from the item table and make that field NULL in the order item table.

delete from item10 where itemno=33;
      
       1 row deleted
      
        SQL> select * from item10;

ITEMNO  UNITPRICE
                  ----------       ----------
        	 31         	10
       	 32          	5
        	 34         	12
        	 35         	14


run \| edit \| history \| help	0