Few queries of Oracle SQL(WIP)

1. Get count of weekdays or weekends in a month. I have used sysdate here to get the count for current month, you can replace sysdate with any other date as per your requirement.


-- For weekends in current month
SELECT COUNT(1)
FROM
  (SELECT TO_CHAR(to_date(level
    ||TO_CHAR(sysdate,'Mon-yyyy'),'dd-Mon-yyyy'),'DAY') AS m_date
  FROM dual
    CONNECT BY level <= TO_CHAR(last_day(sysdate),'dd')
  )
WHERE trim(m_date) IN ('SATURDAY','SUNDAY');


-- For weekdays in current month
SELECT COUNT(1)
FROM
  (SELECT TO_CHAR(to_date(level
    ||TO_CHAR(sysdate,'Mon-yyyy'),'dd-Mon-yyyy'),'DAY') AS m_date
  FROM dual
    CONNECT BY level <= TO_CHAR(last_day(sysdate),'dd')
  )
WHERE trim(m_date) NOT IN ('SATURDAY','SUNDAY');

2. Get 1 to 100 number using a SQL query

SELECT level  FROM dual CONNECT BY level <= 100