Manish_SQL_11_dec, Sql Server

Manish_SQL_11_dec

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     ,        ' ');

Select * from salesman;
Select * from Customer;
Select * from order1;
Select * from Salesman s 
Left join
    (Select c.customer_id, c.cust_name, c.salesman_id, o.ord_no from customer c 
    left join order1 o 
    on c.customer_id=o.customer_id and c.salesman_id = o.salesman_id)   a 
    
    On s.salesman_id = a.salesman_id;

Select s.salesman_Id, S.name, s.city, isnull(a.customer_ID, ' ') as 'Customer Id', isnull(a.cust_name, 'NoOrdByLinkdCust') as 'Cust Name', 
case isnull(a.Ord_no, 0)
when 0 then 'NoOrder'
End,
isnull(a.ord_no, 0) 'Order No', isnull(a.purch_amt,0) 'Purchase Amt'from Salesman s 
LEFT JOIN
(Select c.customer_id, c.cust_name, c.salesman_id, o.ord_no, o.purch_amt from customer c 
LEFT JOIN order1  o 
ON c.customer_id=o.customer_id and 
c.salesman_id = o.salesman_id ) a 
On s.salesman_id = a.salesman_id;

select * from customer;
select * from order1;

Insert into order1 values(70014,      150.5,       '2012-10-05',   '3060',  ' ');
select * from order1;

Insert into customer values('3011',         'Green',  'London    ',  300,         '5002');
Insert into order1 values(70015,      150.5,       '2012-10-05',   '3011',  '5006 ');

select * from customer;
select * from Order1;

Select c.customer_id, c.cust_name, c.salesman_id, o.ord_no, o.purch_amt from customer c 
LEFT JOIN order1  o 
ON c.customer_id=o.customer_id and 
c.salesman_id = o.salesman_id;

select customer.customer_id, customer.cust_name,customer.grade, order1.ord_no, order1.purch_amt from customer left outer join
order1 on customer.customer_id = order1.customer_id and customer.salesman_id = order1.salesman_id where
order1.purch_amt >2000 and customer.grade <> 0

select * from salesman left outer join 
(select customer.customer_id, customer.cust_name,customer.grade, order1.ord_no, order1.purch_amt, customer.salesman_id from customer left outer join
order1 on customer.customer_id = order1.customer_id and customer.salesman_id = order1.salesman_id where
order1.purch_amt >= 2000 and customer.grade <> 0) as a on salesman.salesman_id = a.salesman_id

select salesman.salesman_id, salesman.name, customer.customer_id, customer.cust_name,customer.grade, order1.ord_no, 
order1.purch_amt, order1.salesman_id from salesman 
left join order1
on salesman.salesman_id = order1.salesman_id
left join customer 
on customer.customer_id=order1.customer_id
Where  customer.grade<>0;

select * from salesman left outer join 
(select customer.customer_id, customer.cust_name,customer.grade, order1.ord_no, order1.purch_amt, order1.salesman_id 
 from customer left outer join
order1 on customer.customer_id = order1.customer_id ) as a on salesman.salesman_id = a.salesman_id;

select salesman.salesman_id, salesman.name, customer.customer_id, customer.cust_name,customer.grade, order1.ord_no, 
order1.purch_amt, order1.salesman_id from salesman 
left outer join order1 
on salesman.salesman_id = order1.salesman_id
left outer join customer 
on customer.customer_id=order1.customer_id
;


run \| edit \| history \| help	0