Answer Posted / lokesh

-- Table which maintains the Holidays list.
CREATE TABLE HOLIDAYS_TABLE
(
HOLIDAY_NAME VARCHAR2(20),
HOLIDAY_DATE DATE
);


-- procedure to Calculate the Number of Working days in a
period
CREATE OR REPLACE FUNCTION CALC_WORKING_DAYS(START_DATE IN
DATE, END_DATE IN DATE)

RETURN NUMBER

IS

v_stdate DATE := TO_DATE(START_DATE);
v_eddate DATE := TO_DATE(END_DATE);

v_k NUMBER := 0;
v_cnt NUMBER := 0;
v_bk_hldys NUMBER := 0;


BEGIN


-- To Find the total number of days in the period
v_k := v_eddate - v_stdate ;

-- To Exclude Weekends from the total number of days in
the period
FOR i IN 1..v_k LOOP

IF to_char(v_stdate,'Dy') NOT IN ('Sat','Sun') THEN
v_cnt := v_cnt +1;
END IF;
v_stdate := v_stdate + 1;

END LOOP;

-- To find the Public Holidays which are not on Weekends in
the given Period
SELECT COUNT(DISTINCT(HOLIDAY_DATE)) INTO v_bk_hldys FROM
HOLIDAYS_TABLE where HOLIDAY_DATE BETWEEN TO_DATE
(START_DATE) AND TO_DATE(END_DATE) AND to_char
(HOLIDAY_DATE,'Dy') NOT IN ('Sat','Sun');

-- To Subtract the public holidays which are not on
Weekends
v_cnt := v_cnt - v_bk_hldys;

RETURN v_cnt;
END;
/

Explain dml and ddl?

1004

---

What is vector point function?

1057

---

what is the stuff function and how does it differ from the replace function? : Sql dba

1062

---

What is the purpose of normalization?

1049

---

how to calculate expressions with sql statements? : Sql dba

1040

---

What are the operators in sql?

1035

---

Explain the components of sql?

1068

---

What are the different types of triggers?

1139

---

How do you update a table in sql?

993

---

Which join is like an inner join?

1033

---

what are date and time data types? : Sql dba

991

---

What is varchar data type in sql?

1088

---

Does postgresql run on the cloud?

1154

---

how to include numeric values in sql statements? : Sql dba

1104

---

What are the different type of joins in sql?

957

---