subquery & correlated subquery & "is null" condition, Sql Server

subquery & correlated subquery & "is null" condition

--Sql Server 2014 Express Edition

---Enjoy Coding by alex ogrinja

/*
Table of contents
- create and insert into tables,
- select *, 
- Return only salesmen which brought up at least 3 incomes (point out subquery & correlated subquery),
- Return only salesmen which brought up no incomes (point out "is null" condition & correlated subquery).
*/

create table orders
(ord_no int
, purch_amt float
, ord_date date
, customer_id int
, salesman_id int);

insert into orders
values
(70001,      150.5  ,     '2012-10-05',  3005      ,   5002),
(70002  ,     65.26     ,  '2012-10-05' , 3002   ,      5001),
(70004   ,    110.5    ,   '2012-08-17'  ,3009  ,       5003),
(70007   ,    948.5   ,    '2012-09-10',  3005 ,        5002),
(70005    ,   2400.6 ,     '2012-07-27',  3007     ,    5001),
(70008     ,  5760       , '2012-09-10' , 3002      ,   5001),
(70010,       1983.43   ,  '2012-10-10',  3004    ,     5006),
(70003  ,     2480.4   ,   '2012-10-10' , 3009   ,      5003),
(70012   ,    250.45  ,    '2012-06-27' , 3008  ,       5002),
(70011    ,   75.29  ,     '2012-08-17' , 3003 ,        5007),
(70013     ,  3045.6,      '2012-04-25'  ,3002,         5001);

create table salesman 
(salesman_id int
, name varchar(100)
, city varchar(50)
, commission float);

insert into salesman 
values
(5001 ,        'James Hoog',  'New York' ,   0.15),
(5002  ,       'Nail Knite',  'Paris'    ,   0.13),
(5005   ,      'Pit Alex'  ,  'London'  ,    0.11),
(5006    ,     'Mc Lyon'   ,  'Paris'   ,    0.14),
(5003     ,    'Ogrinja Alexandru',  'Bucuresti' ,   0.12),
(5007      ,   'Paul Adam' ,  'Rome'    ,    0.13);

--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

select * from orders o
order by o.customer_id, o.salesman_id

select * from salesman s

--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

--Scenario A: Return only salesmen (S id, S name, O id, O purch_amt, O ord_date) which brought up at least 3 incomes.

-- Method subquery
select s.salesman_id, s.name, o.customer_id, format(o.purch_amt,'#,###.###') purch_amt, convert(date, o.ord_date) ord_date
from orders o join salesman s
on s.salesman_id= o.salesman_id
where o.salesman_id in 
(select o.salesman_id from orders o
group by o.salesman_id
having count(*)> 2)
order by o.salesman_id desc, o.customer_id desc;

-- Method correlated subquery
select s.salesman_id, s.name, o1.customer_id, format(o1.purch_amt,'#,###.###') purch_amt, o1.ord_date
from orders o1 join salesman s
on s.salesman_id= o1.salesman_id
where 2 < 
(select count(*) from orders o2 where o2.salesman_id=o1.salesman_id)
order by salesman_id desc, customer_id desc;

--->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- Scenario B: Return only salesmen (S name, O id, O purch_amt, O ord_date) which brought up no incomes.

-- Method is null condition
select s.name, o1.customer_id, format(o1.purch_amt,'#,###.###') purch_amt, o1.ord_date
from orders o1 right join salesman s
on s.salesman_id= o1.salesman_id
where o1.customer_id is null

-- Method correlated subquery
select s.name, o1.customer_id, format(o1.purch_amt,'#,###.###') purch_amt, o1.ord_date
from orders o1 full outer join salesman s
on s.salesman_id= o1.salesman_id
where 0 = 
(select count(*) from orders o2 where o2.salesman_id=o1.salesman_id);

---Enjoy Coding by alex ogrinja


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development