Manish_Sql_updated
create table salesman (salesman_id char(6), name char(12), city char(20), commission decimal(2,2));
Insert into salesman values('5001', 'James Hoog', 'New York', 0.15)
Insert into salesman values('5002 ', 'Nail Knite', 'Paris', 0.13);
Insert into salesman values('5005 ', 'Pit Alex', 'london', 0.11);
Insert into salesman values('5003 ', 'Lauson Hen',' ', 0.12);
Insert into salesman values('5006 ', 'Mc Lyon ','Paris ', 0.14);
Insert into salesman values('5007 ', 'Paul Adam ','Romes ', 0.13);
Insert into salesman values('9007 ', 'Paul Adam ','Romes ', 0.13);
create table customer (customer_id char(6), cust_name char(12), city char(20), grade decimal(6), salesman_id char(6));
Insert into customer values('3002', 'Nick Rimando', 'New York', 100, '5001');
Insert into customer values('3005', 'Graham Zusi ', 'California', 200, '5002');
Insert into customer values('3001', 'Brad Guzan', 'London ', 0 , '5005');
Insert into customer values('3004', 'Fabian Johns', 'Paris ', 300, '5006');
Insert into customer values('3001', 'Brad Guzan', 'London ', 0 , '5005');
Insert into customer values('3001', 'Brad Guzan', 'London ', 0 , '5005');
Insert into customer values('3009', 'Geoff Camero', 'Berlin ', 100, '5003');
Insert into customer values('3008', 'Julian Green', 'London ', 300, '5002');
Insert into customer values('3003', 'Jozy Altidor', 'Moscow ', 200, '5007');
Delete from customer where cust_name = ' Brad Guzan ';
Delete from customer where cust_name = 'Brad Guzan';
Insert into customer values('3001', 'Brad Guzan', 'London ', 0 , '5005');
Insert into customer values('3007', 'Brad Davis ', 'New York ', 200, '5001');
create table order1 (ord_no decimal(7,0), purch_amt decimal(6,2), ord_date date, customer_id char(6), salesman_id char(6));
Insert into order1 values(70001, 150.5, '2012-10-05', '3005', '5002');
Insert into order1 values(70009, 270.65, '2012-09-10', '3001 ', '5005');
Insert into order1 values(70002, 65.26, '2012-10-05', '3002 ', '5001');
Insert into order1 values(70007, 948.5, '2012-09-10', '3005 ', '5002');
Insert into order1 values(70004, 110.5, '2012-08-17', '3009 ', '5003');
Insert into order1 values(70005, 2400.6, '2012-07-27', '3007 ', '5001');
Insert into order1 values(70008, 5760, '2012-09-10', '3002 ', '5001');
Insert into order1 values(70010, 1983.43, '2012-10-10', '3004 ', '5006');
Insert into order1 values(70003, 2480.4, '2012-10-10', '3009 ', '5003');
Insert into order1 values(70012, 250.45, '2012-06-27', '3008 ', '5002');
Insert into order1 values(70011, 75.29 , '2012-08-17', '3003 ', '5007');
Insert into order1 values(70013, 3045.6, '2012-04-25', '3002 ', '5001');
Insert into customer values('3050', 'BradGuzanNEW', 'London ', 350 , '5005');
select customer.customer_id,customer.cust_name,isnull(cnt,0) as cnt1
from customer left outer join (select customer_id ,count(*) cnt from
order1 group by customer_id) a on
customer.customer_id = a.customer_id;
select customer_id, cust_name, (select count(*) from order1
where order1.customer_id = customer.customer_id) as
num_ord from customer;
select customer.cust_name, customer.customer_id,count(order1.ord_no)
from customer left outer join order1 on customer.customer_id =
order1.customer_id group by customer.cust_name,customer.customer_id;
select customer.cust_name,customer.city,order1.ord_no,order1.ord_date,
order1.purch_amt, salesman.name, salesman.commission
from customer left outer join order1 on customer.customer_id = order1.customer_id
left outer join salesman on customer.salesman_id = salesman.salesman_id;
Insert into customer values('3060', 'TestGuzanNEW', 'London ',
350 , ' ');
|
run
| edit
| history
| help
|
0
|
|
|