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;
/

differentiate between float and double. : Sql dba

1046

---

What is an implicit commit?

1048

---

What is difference between my sql and sql?

1058

---

Does sql support programming?

1063

---

Explain the the update statement in sql?

1027

---

Mention what is the use of function "module procedure" in pl/sql?

1044

---

What is a function in oracle pl sql?

1028

---

Define implicit and explicit cursors.

1135

---

Explain what is rdbms?

1026

---

Table 1: col1 Timestamp ---------------- 01-mar-2012 11:12:46 Table 2: col2 Timestamp -------------------- 01-mar-2012 11:12:10 01-mar-2012 11:11:23 Write a query to display a row with table2 col2 value less than tabl1 col1 value. Maximum timestamp value previous to table1 col1 value. Display a result as: Col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10

2107

---

Why indexing is needed?

980

---

what is union, minus and interact commands? : Sql dba

1179

---

What is meant by temporal data?

961

---

what is the difference between mysql_fetch_array and mysql_fetch_object? : Sql dba

1052

---

what are integrity rules?

1020

---