sql basics experts plz

[kumarc]

Orders

RowNum orderdate custid   Salespeopleid Amt 10 8/2/96 4   2 540 20 1/30/99 4   10 1800 30 7/14/95 9   1 460 40 1/29/98 7   2 2400 50 2/3/98 6   7 600 60 3/2/98 6   7 720 70 5/6/98 9   7 150

salespeople

ID	Name	Age	Salary
1	Abel	61	100000
2	Borat	34	42000
5	Carl	34	30000
7	Dave	41	51000
10	Kent	57	114000
12	Jock	38	37000

Write a query that returns total and average sales by month for 1998:
Write a query that returns all salespeople that failed to get any orders for each year starting in 1997:
 

[tables]

 , group by clause ,not exists and left join ela work avutado chudu and then try writing queries. okalu rasina query chadivite you will never learn.

[Tesla]

1) select to_char(orderdate, 'MM') month, sum(amt) SUM, avg(amt) AVG from orders where to_char(orderdate, 'YYYY') = '1998' group by 
to_char(orderdate, 'MM');

 

I this this query explains itself.

 

 

 

select id, name, YEAR_NO_SALE from 
  (select * from salespeople a, (select distinct salespeopleid, to_char(orderdate, 'YYYY') year from orders where to_char(orderdate, 'YYYY') >= '1997') b -- this is to get all sales starting 1997, distinct used because, if the same person have more than one sale we just need one
where a.id = b.salespeopleid(+) order by id) aa, --  table aa give you all sales person and years they actually sold, if they did not sold in any year they will have atleast one entry
            (select '1997' YEAR_NO_SALE from dual union select '1998' YEAR_NO_SALE from dual  union select '1999' YEAR_NO_SALE from dual) bb -- table bb give you list of years form 1997 usually this is implemented by creating one table with the list of years, but for simplicity I have used union to get years
            where year is null or year <> year_no_sale  order by Id, YEAR_NO_SALE;
 

[Quickgun_murugan]

1 hour ago, Tesla said:

1) select to_char(orderdate, 'MM') month, sum(amt) SUM, avg(amt) AVG from orders where to_char(orderdate, 'YYYY') = '1998' group by 
to_char(orderdate, 'MM');

 

I this this query explains itself.

 

 

 

select id, name, YEAR_NO_SALE from 
  (select * from salespeople a, (select distinct salespeopleid, to_char(orderdate, 'YYYY') year from orders where to_char(orderdate, 'YYYY') >= '1997') b -- this is to get all sales starting 1997, distinct used because, if the same person have more than one sale we just need one
where a.id = b.salespeopleid(+) order by id) aa, --  table aa give you all sales person and years they actually sold, if they did not sold in any year they will have atleast one entry
            (select '1997' YEAR_NO_SALE from dual union select '1998' YEAR_NO_SALE from dual  union select '1999' YEAR_NO_SALE from dual) bb -- table bb give you list of years form 1997 usually this is implemented by creating one table with the list of years, but for simplicity I have used union to get years
            where year is null or year <> year_no_sale  order by Id, YEAR_NO_SALE;
 

Inner join vadochu kada  using Salesppl ID with not equal to clause

a.salespplID != b.ID