Manish_Sql_updated, Sql Server

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