Write a SQL to print Cricket Dashboard

---

CREATE TABLE cricket (
    match_no   NUMBER,
    team_a     CHAR(30),
    team_b     CHAR(30),
    winner     CHAR(30)
);

insert into cricket values(01,'WESTINDIES','SRILANKA','WESTINDIES');
insert into cricket values(02,'INDIA','SRILANKA','INDIA');
insert into cricket values(03,'AUSTRALIA','SRILANKA','AUSTRALIA');
insert into cricket values(04,'WESTINDIES','SRILANKA','SRILANKA');
insert into cricket values(05,'AUSTRALIA','INDIA','AUSTRALIA');
insert into cricket values(06,'WESTINDIES','SRILANKA','WESTINDIES');
insert into cricket values(07,'INDIA','WESTINDIES','WESTINDIES');
insert into cricket values(08,'WESTINDIES','AUSTRALIA','AUSTRALIA');
insert into cricket values(09,'WESTINDIES','INDIA','INDIA');
insert into cricket values(10,'AUSTRALIA','WESTINDIES','WESTINDIES');
insert into cricket values(11,'WESTINDIES','SRILANKA','WESTINDIES');
insert into cricket values(12,'INDIA','AUSTRALIA','INDIA');
insert into cricket values(13,'SRILANKA','NEWZEALAND','SRILANKA');
insert into cricket values(14,'NEWZEALAND','INDIA','INDIA');

---

Query 1:

with matches_played as (select team_name, sum(cnt) as no_of_matches_played from(
   select TEAM_A team_name,count(*) cnt 
  from cricket group by TEAM_A
   union all
   select TEAM_b,count(*) from cricket group by TEAM_b)
   group by team_name), 
matches_won as (select winner team_name,count(*) no_of_matches_won
                from cricket
                group by winner)
select matches_played.team_name, no_of_matches_played,nvl(no_of_matches_won,0) matches_won,no_of_matches_played-nvl(no_of_matches_won,0) matches_lost
from matches_played full outer join matches_won
on matches_played.team_name = matches_won.team_name;

---

Query 2:

select matches_played.team_name, no_of_matches_played,nvl(no_of_matches_won,0) matches_won,no_of_matches_played-nvl(no_of_matches_won,0) matches_lost
from (select team_name, sum(cnt) as no_of_matches_played from(
    select TEAM_A team_name,count(*) cnt 
    from cricket group by TEAM_A
    union all
    select TEAM_b,count(*) from cricket group by TEAM_b)
    group by team_name) matches_played, 
    (select winner team_name,count(*) no_of_matches_won
    from cricket
    group by winner)matches_won
where matches_played.team_name = matches_won.team_name(+);

---

Query 3: 

with d as (select team_a a,
       team_b b,
       case when team_a = winner then 1 else 0 end a_w,
       case when team_b = winner then 1 else 0 end b_w
from cricket)
select c,count(*) total,sum(w) won,count(*) -sum(w) lost from (
select decode(r,1,a,b) c,decode(r,1,a_w,b_w) w
from d,(select rownum r from dual connect by level <=2))
group by c;

All about Oracle Date - Part 1

Query to print all the dates of a month

with d as (
select to_date('01/01/2019','DD/MM/YYYY') as dt from dual)
select c_date,trim(to_char(c_date,'DY')) day,
       to_char(c_date,'W') week_of_month,
       to_char(c_date,'WW') week_of_year,
       to_char(c_date,'IW') iso_week_of_year
from(
select trunc(d.dt,'MONTH')+level-1 c_date
from d
connect by level <=trunc(last_day(d.dt)) -trunc(d.dt,'MONTH')+1)
order by c_date;

SQL to Find number of saturday and sunday in the current month

SQL to Find number of saturday and sunday in the current month

---

SELECT
    TO_CHAR(c_date,'DAY') day,
    COUNT(*)
FROM
    (
        SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) c_date
        FROM dual
        CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1
    )
WHERE
    TO_CHAR(c_date,'DY') IN ('SAT','SUN')
GROUP BY
    TO_CHAR(c_date,'DAY');

---

WITH c_date as (SELECT trunc(SYSDATE,'MONTH') + ( level - 1 ) d
        FROM dual
        CONNECT BY level <= last_day(trunc(SYSDATE) ) - trunc(SYSDATE,'MONTH') + 1)
SELECT TO_CHAR(c_date.d,'DAY') day,COUNT(*)
FROM c_date
WHERE
    TO_CHAR(c_date.d,'DY') IN ('SAT','SUN')
GROUP BY

    TO_CHAR(c_date.d,'DAY');

---

-- Query to find the number of saturday and sunday between two given dates

SELECT

    TO_CHAR(c_date,'DAY') day,

    COUNT(*)

FROM

    (SELECT trunc(to_date('01/01/2019','DD/MM/YYYY')) + ( level - 1 ) c_date

        FROM dual

        CONNECT BY level <= trunc(to_date('31/12/2019','DD/MM/YYYY')) - trunc(to_date('01/01/2019','DD/MM/YYYY')) + 1

    )

WHERE TO_CHAR(c_date,'DY') IN ('SAT','SUN')

GROUP BY

    TO_CHAR(c_date,'DAY');