1 .Generate
numbers/sequence using dual with a specific interval.
1
2
3
|
SELECT *
FROM (SELECT CASE
WHEN MOD(LEVEL,4)=1
THEN LEVEL END
seq FROM dual
CONNECT BY LEVEL <=100)
WHERE seq IS NOT
NULL
|
This is generate numbers
with interval of 4.
1
5
9
13
…
1
2
3
|
SELECT *
FROM (SELECT CASE
WHEN MOD(LEVEL,5)=1
THEN LEVEL END
seq FROM dual
CONNECT BY LEVEL <=100)
WHERE seq IS NOT
NULL
|
This is generate numbers
with interval of 5.
1
6
11
16
…
To generate multiples of a number using dual in ORACLE.
1
2
|
SELECT LEVEL*3
FROM dual
CONNECT BY LEVEL <=20
|
This will generate
multiples of 3..
SEQ
3
6
9
12
…..
****************************************************
2. The next question is
on triggers. There are 4 columns in a table, marks1, marks2,marks3 and total.
The trigger should calculate the total and update the total when
we insert marks1, marks2 and marks3.
To create the table:
1
2
|
create table test_marks(subject
varchar2(10),
marks1 number,
marks2 number, marks3 number, total number)
|
On insert, the trigger
should automatically calculate the sum of marks1, marks2 and marks3 and assign
it to total column:
1
2
|
INSERT INTO test_marks(subject,marks1,marks2,marks3)
VALUES ('A1',12,13,14);
|
Answer :
trigger
to update the value of total as sum of marks1, marks2, marks3
1
2
3
4
5
|
CREATE OR REPLACE TRIGGER find_total
BEFORE INSERT ON test_marks
FOR EACH ROW
BEGIN
:new.total :=
:new.marks1+:new.marks2+:new.marks3;
END;
|
;
Note: AFTER INSERT will
not work in this case.
****************************************************
3. How I understood this
query which orders by based on the ‘number part’ .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create table ting(tno
varchar2(4));
insert into ting
values('1');
insert into ting
values('2');
insert into ting
values('3');
insert into ting
values('10');
insert into ting
values('11');
insert into ting
values('1a');
insert into ting
values('20');
insert into ting
values('2a');
insert into ting
values('3a');
insert into ting
values('10a');
insert into ting
values('10b');
insert into ting
values('31b');
insert into ting
values('31');
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT *
FROM ting;
TNO
----
1
2
3
10
11
1a
20
2a
3a
10a
10b
31b
31
13 rows selected
|
The final solution is
provided at the end. You can skip the following sections and directly go for
the final solution. The following steps are a breakdown of the final solution.
This is how I understood the final query.
Step 1. Replace all the digit
with 0 using translate.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT translate(tno,'0123456789','000000000')
FROM ting;
TRANSLATE(TNO,'0123456789','00
------------------------------
0
0
0
00
00
0a
00
0a
0a
00a
00b
00b
00
13 rows selected
|
Step 2. Now replace the 0 with
null.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT translate(tno,'0123456789','000000000')
q,REPLACE(translate(tno,'0123456789','000000000'),'0','') FROM ting;
Q
REPLACE(TRANSLATE(TNO,'0123456
----
------------------------------
0
0
0
00
00
0a a
00
0a a
0a a
00a a
00b b
00b b
00
13 rows selected
|
Step 3. Next we find the length
of the values which are not null(with alphabets). Gives 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT translate(tno,'0123456789','000000000')
q,length(REPLACE(translate(tno,'0123456789','000000000'),'0','')) FROM ting;
Q
LENGTH(REPLACE(TRANSLATE(TNO,'
----
------------------------------
0
0
0
00
00
0a
1
00
0a
1
0a
1
00a
1
00b
1
00b
1
00
13 rows selected
|
Step 4. Find the total length
(length(tno))of the string and using NVL replace the null values in the above
query with 0.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT tno,length(tno),nvl(length(REPLACE(translate(tno,'0123456789','000000000'),'0','')),0)
FROM ting;
TNO
LENGTH(TNO) NVL(LENGTH(REPLACE(TRANSLATE(T
---- -----------
------------------------------
1
1
0
2
1
0
3
1
0
10
2
0
11
2
0
1a
2
1
20
2
0
2a
2
1
3a
2
1
10a
3
1
10b
3
1
31b
3
1
31
2
0
13 rows selected
|
Step 5. Use substr to extract
only the digits from tno using 1 as start index and the difference between
length(tno)
-nvl(length(REPLACE(translate(tno,’0123456789′,’000000000′),’0′,”)),0) as the
end index. This gives the first column in the final query (A)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> SELECT substr(tno,1,length(tno)-nvl(length(REPLACE(translate(tno,'0123456789','000000000'),'0','')),0))
a FROM ting;
A
----
1
2
3
10
11
1
20
2
3
10
10
31
31
13 rows selected
|
Step 6. Use substr to display
only the alphabets. Add 1 to the end_index in the above query and take it as
the start index. (I have added rownum here to show the rows with null values.)
This is the second row in the final query (B).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> SELECT rownum,
substr(tno,length(tno)-nvl(length(REPLACE(translate(tno,'0123456789','000000000'),'0','')),0)+1)
b FROM ting;
ROWNUM
B
---------- ----
1
2
3
4
5
6
a
7
8
a
9
a
10
a
11
b
12
b
13
13 rows selected
|
Step 7. Here I am just
displaying the two columns we created earlier. Column A in step 5 and column B
in step 6 along with the original column tno.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> SELECT substr(tno,1,
length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0))
a,
2
substr(tno,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)
+1 ) b,
3
tno
4
FROM ting;
A
B TNO
---- ---- ----
1
1
2
2
3
3
10
10
11
11
1
a 1a
20
20
2
a 2a
3
a 3a
10
a 10a
10
b 10b
31
b 31b
31
31
13 rows selected
|
Step 8. The final solution . Try
running the query without using NULLS FIRST and you will know the reason it has
been kept there.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL> SELECT substr(tno,1,
length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0))
a,
2
substr(tno,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)
+1 ) b,
3
tno
4
FROM ting
5
ORDER BY to_number(a),b NULLS FIRST
6
;
A
B TNO
---- ---- ----
1
1
1
a 1a
2
2
2
a 2a
3
3
3
a 3a
10
10
10
a 10a
10
b 10b
11
11
20
20
31
31
31
b 31b
13 rows selected
SQL>
|
****************************************************
4. How will you insert
multiple rows of dummy data into a date column or a varchar2 column?
This query can be used to “fill” an empty table with date datatype column.
1
2
3
|
SELECT sysdate,level-1
lv,7*(LEVEL-1) s,
trunc(SYSDATE-(7*(LEVEL-1)))
t FROM dual
CONNECT BY LEVEL <
10;
|
This query uses the
LEVEL pseudo-column to get dates with an interval of 7 days.
1
2
3
4
5
6
7
8
9
10
|
SYSDATE
LV S T
7/5/2011 5:22:14 PM
0 0 7/5/2011
7/5/2011 5:22:14 PM
1 7 6/28/2011
7/5/2011 5:22:14 PM
2 14 6/21/2011
7/5/2011 5:22:14 PM
3 21 6/14/2011
7/5/2011 5:22:14 PM
4 28 6/7/2011
7/5/2011 5:22:14 PM
5 35 5/31/2011
7/5/2011 5:22:14 PM
6 42 5/24/2011
7/5/2011 5:22:14 PM
7 49 5/17/2011
7/5/2011 5:22:14 PM
8 56 5/10/2011
|
Another example to get
dates with an interval of 3 days.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT trunc(SYSDATE-(3*(LEVEL-1)))
dt FROM dual
CONNECT BY LEVEL <10 span="">
dt
----------
7/5/2011
7/2/2011
6/29/2011
6/26/2011
6/23/2011
6/20/2011
6/17/2011
6/14/2011
6/11/2011
|
To populate an empty
table with dummy varchar2 values, we can use DBMS_RANDOM.STRING
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select DBMS_RANDOM.STRING('A',5)
ran from dual connect
by level<
10;
ran
---------
zodaY
baktb
EUvWO
WAcca
rdRuw
brQiw
kcksX
ddhmo
EyoQl
|
****************************************************
5. If you want to fill an
empty table with groups of data to work on analytic functions or group by
functions, then you can generate “groups” of numbers by manipulating the ROWNUM
column using the following query.
1
2
3
4
5
6
|
SQL> SELECT ROWNUM
r, ROWNUM/2-0.1 a, trunc(ROWNUM/2-0.1) two,
2 ROWNUM/3-0.1
b, trunc(ROWNUM/3-0.1) three,
3
ROWNUM/4-0.1 c, trunc(ROWNUM/4-0.1) four
4
FROM dual
5
CONNECT BY LEVEL <=20
6 ;
|
The output [see the
columns marked TWO THREE and FOUR]:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
R
A
TWO
B THREE
C FOUR
----------
---------- ---------- ---------- ---------- ---------- ----------
1
0.4 0
0.23333333
0
0.15 0
2
0.9 0
0.56666666
0
0.4 0
3
1.4
1
0.9
0
0.65 0
4
1.9 1
1.23333333
1
0.9 0
5
2.4 2
1.56666666 1
1.15 1
6
2.9
2
1.9
1
1.4 1
7
3.4 3
2.23333333
2
1.65 1
8
3.9 3
2.56666666
2
1.9 1
9
4.4
4
2.9 2
2.15 2
10
4.9 4
3.23333333
3
2.4 2
11
5.4 5
3.56666666
3
2.65 2
12
5.9
5
3.9
3
2.9 2
13
6.4 6
4.23333333
4
3.15 3
14
6.9 6
4.56666666
4
3.4 3
15
7.4
7
4.9 4
3.65 3
16
7.9 7
5.23333333
5
3.9 3
17
8.4 8
5.56666666
5
4.15 4
18
8.9
8
5.9 5
4.4 4
19
9.4 9
6.23333333
6 4.65
4
20
9.9 9
6.56666666
6
4.9 4
20 rows selected
|
****************************************************
6.
In this query, we need to update col2 in to_update table with the value in col4
of update_using table. If the value in col4 is already present in col2, then
col2 should not be updated. In this case since 11 is already present in
to_update table, it should not be updated for 1 in to_update table.
See
the output to get a better understanding of the requirement.
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE to_update(col1
NUMBER,col2 NUMBER);
CREATE TABLE update_using(col3
NUMBER,col4 NUMBER);
INSERT INTO to_update
VALUES(1,NULL);
INSERT INTO to_update
VALUES(2,NULL);
INSERT INTO to_update
VALUES(3,NULL);
INSERT INTO to_update
VALUES(4,11);
INSERT INTO to_update
VALUES(5,NULL);
INSERT INTO to_update
VALUES(6,NULL);
INSERT INTO update_using
VALUES(1,11);
INSERT INTO update_using
VALUES(2,12);
INSERT INTO update_using
VALUES(5,13);
COMMIT;
|
First solution using correlated subquery :
1
2
3
4
|
UPDATE to_update
t SET t.col2=(SELECT
col4 FROM update_using
WHERE t.col1=col3
AND col4 NOT IN
(SELECT col2
FROM to_update WHERE
col2 IS NOT NULL))
WHERE col2
IS NULL
|
But the above query will
fail if you add another row :
1
|
INSERT INTO update_using
VALUES(2,13);
|
as it will not know
which value of col4 should be used to update col2. Here 2 has two corresponding
values 12 and 13.
The next solution uses
the concept of “update join” which introduces us to the concept of “key preserved table”. I will not get into the details.
The query given below will not work. For this to work, we need to have a
primary key on the table from which we are updating(update_using).
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
update(SELECT a.col1,a.col2,b.col3,b.col4 FROM to_update
a,update_using b
2
WHERE a.col1 =b.col3 )
3
SET col2 =col4
4
WHERE col4 NOT IN
(SELECT col2
FROM to_update WHERE
col2 IS NOT NULL)
5
;
update(SELECT a.col1,a.col2,b.col3,b.col4
FROM to_update a,update_using b
WHERE a.col1
=b.col3 )
SET col2
=col4
WHERE col4
NOT IN (SELECT col2 FROM to_update
WHERE col2 IS NOT
NULL)
ORA-01779: cannot
modify a column which maps to a
non key-preserved table
|
To make this query work,
create a primary key on update_using :
1
2
|
ALTER TABLE update_using
ADD CONSTRAINT up_us_pk
PRIMARY KEY(col3)
|
Now when we run the
query :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
update(SELECT a.col1,a.col2,b.col3,b.col4 FROM to_update
a,update_using b
2
WHERE a.col1 =b.col3 )
3
SET col2 =col4
4
WHERE col4 NOT IN
(SELECT col2
FROM to_update WHERE
col2 IS NOT NULL);
2 rows updated
SQL> select *
from to_update
2
;
COL1 COL2
---- ----
1
2
12
3
4
11
5
13
6
6 rows selected
SQL>
|
****************************************************
7. From a table
containing numbers, display only the digits after the decimal point. If a
number is 1.980 then the output should be 0.980
1
2
3
4
5
6
7
|
CREATE TABLE test_decimal(num
NUMBER);
INSERT INTO test_decimal
VALUES(1.2345);
INSERT INTO test_decimal
VALUES(20.0990);
INSERT INTO test_decimal
VALUES(1.00);
INSERT INTO test_decimal
VALUES(1.12357);
INSERT INTO test_decimal
VALUES(100);
COMMIT;
|
The table contains :
1
2
3
4
5
6
7
|
NUM
---------
1.2345
20.099
1
1.12357
100
|
Answer:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> select trunc(num)
from test_decimal;
TRUNC(NUM)
----------
1
20
1
1
100
SQL> select num-trunc(num)
from test_decimal;
NUM-TRUNC(NUM)
--------------
0.2345
0.099
0
0.12357
0
SQL>
|
Alternative solutions:
1
2
|
select mod(num,1)
from test_decimal;
select num-cast(num
as integer) from
test_decimal;
|
****************************************************
8. A table contains team
name and result. The result can have 3 values- WIN, LOSS or DRAW. For each team
find the total number of matches, number of wins, number of losses and the
number of drawn matches.
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TABLE match(team
VARCHAR2(5),result VARCHAR2(4));
INSERT INTO match
VALUES('A','WIN');
INSERT INTO match
VALUES('A','LOSS');
INSERT INTO match
VALUES('A','WIN');
INSERT INTO match
VALUES('B','WIN');
INSERT INTO match
VALUES('B','LOSS');
INSERT INTO match
VALUES('C','WIN');
INSERT INTO match
VALUES('C','DRAW');
INSERT INTO match
VALUES('D','LOSS');
INSERT INTO match
VALUES('E','DRAW');
COMMIT;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> select *
from match;
TEAM RESULT
----- ------
A
WIN
A
LOSS
A
WIN
B
WIN
B
LOSS
C
WIN
D
LOSS
E
DRAW
C
DRAW
9 rows selected
|
Answer :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
--------------------
SQL> SELECT team,win,lost,draw,win+lost+draw
total FROM
2
(SELECT team,SUM(CASE
WHEN result
='WIN' THEN 1 ELSE 0 END) win,
3
SUM(CASE WHEN result ='LOSS'
THEN 1
ELSE 0 END) lost,
4
SUM(CASE WHEN result ='DRAW'
THEN 1
ELSE 0 END) draw FROM
match
5
GROUP BY team)
6
;
TEAM
WIN
LOST DRAW
TOTAL
----- ----------
---------- ---------- ----------
A
2
1
0 3
B
1
1
0 2
C
1
0
1 2
D
0
1
0 1
E
0
0
1 1
SQL>
|
****************************************************
9. A question similar to
the one solved above. In this case the table contains three columns. The first
column has the name of the first team, the second column has the name of the
second team and the third column contains the winner of the match. If the match
is a drawn match, then a ‘-’ is inserted into the winners column.
We need to write a query to find the total number of matches
played by each team, the number of drawn matches, the number of losses and
wins.
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TABLE vs(team1
VARCHAR2(2),team2 VARCHAR2(2), winner VARCHAR2(2));
INSERT INTO vs
VALUES('A','B','A');
INSERT INTO vs
VALUES('A','B','B');
INSERT INTO vs
VALUES('A','C','C');
INSERT INTO vs
VALUES('A','D','A');
INSERT INTO vs
VALUES('B','C','B');
INSERT INTO vs
VALUES('B','D','B');
INSERT INTO vs
VALUES('C','D','D');
INSERT INTO vs
VALUES('A','D','-');
INSERT INTO vs
VALUES('A','D','-');
COMMIT;
|
Our Table :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> select *
from vs;
TEAM1 TEAM2 WINNER
----- ----- ------
A
B A
A
C C
A
D A
B
C B
B
D B
C
D C
A
B B
A
C -
A
D -
9 rows selected
SQL>
|
Answer :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> select Team,sum(WIN)
Win,sum(c)-sum(WIN)-sum(DRAW) Loss ,sum(DRAW) Draw,sum(c) TOTAL from (
2
select team1 Team ,
3
sum(case when team1=winner then
1 else 0
end) WIN,
4
sum(case when winner like
'-' then 1
else 0 end) DRAW,
5
count(team1) c from vs group by
team1
6
union all
7
select team2 Team ,
8
sum(case when team2=winner then
1 else 0
end) WIN,
9
sum(case when winner like
'-' then 1
else 0 end) DRAW,
10
count(team2) c from vs group by
team2)
11 group by Team
12 ;
TEAM
WIN
LOSS DRAW
TOTAL
---- ----------
---------- ---------- ----------
A
2
2 2
6
B
3
1
0 4
C
2
1
1 4
D
0
3
1 4
SQL>
|
****************************************************
10. There are two tables-
‘has_sequence’ which contains a list of numbers and table ‘sequence_range’
which contains the start number and end number for each range. Write a query to
find those numbers from ‘has_sequence’ table which does not fall in any of the
range specified in the ‘sequence_range’ table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
CREATE TABLE has_sequence
(seq NUMBER);
INSERT INTO has_sequence
VALUES (3);
INSERT INTO has_sequence
VALUES (4);
INSERT INTO has_sequence
VALUES (5);
INSERT INTO has_sequence
VALUES (6);
INSERT INTO has_sequence
VALUES (7);
INSERT INTO has_sequence
VALUES (20);
INSERT INTO has_sequence
VALUES (21);
INSERT INTO has_sequence
VALUES (22);
INSERT INTO has_sequence
VALUES (23);
INSERT INTO has_sequence
VALUES (24);
INSERT INTO has_sequence
VALUES (31);
INSERT INTO has_sequence
VALUES (32);
INSERT INTO has_sequence
VALUES (33);
INSERT INTO has_sequence
VALUES (34);
INSERT INTO has_sequence
VALUES (35);
INSERT INTO has_sequence
VALUES (36);
CREATE TABLE sequence_range(start_num
NUMBER, end_num NUMBER);
INSERT INTO sequence_range
VALUES (4,6);
INSERT INTO sequence_range
VALUES (20,23);
INSERT INTO sequence_range
VALUES (30,34);
COMMIT;
|
The tables:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL> SELECT *
FROM has_sequence;
SEQ
---
3
4
5
6
7
20
21
22
23
24
31
32
33
34
35
36
16 rows selected
SQL> SELECT *
FROM sequence_range;
START_NUM END_NUM
--------- -------
4
6
20
23
30
34
SQL>
|
We can see that the numbers 3,7,24,35 and 36 does not fall under
any of the ranges in ‘sequence_range’ table.
Answer:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> SELECT *
FROM has_sequence h
2
WHERE NOT
EXISTS
3
(SELECT 1 FROM sequence_range s WHERE h.seq
BETWEEN s.start_num AND
s.end_num)
4
;
SEQ
---
3
7
24
35
36
SQL>
|
****************************************************
11. Another variation of
the above question is to update the ‘group_id’ field in ‘has_sequence’ field
with the ‘group_id’ field from ‘sequence_range’ table by matching the number
from ‘has_sequence’ and the corresponding sequence it belongs to, in the
‘sequence_range’ table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SQL> SELECT *
FROM has_sequence;
SEQ GROUPID
--- -------
3
4
5
6
7
20
21
22
23
24
31
32
33
34
35
36
16 rows selected
SQL> SELECT *
FROM sequence_range ;
START_NUM END_NUM
GROUPID
--------- -------
-------
4
6 A
20
23 B
30
34 C
|
Answer :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL> UPDATE has_sequence
t SET t.groupid = (SELECT
s.groupid FROM sequence_range
s
2
WHERE t.seq BETWEEN
s.start_num AND s.end_num);
16 rows updated
SQL> SELECT *
FROM has_sequence;
SEQ GROUPID
--- -------
3
4 A
5 A
6 A
7
20 B
21 B
22 B
23 B
24
31 C
32 C
33 C
34 C
35
36
16 rows selected
SQL>
|
****************************************************
12. Useful in removing
multiple commas or spaces from a text.
Answer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
SQL> SELECT 'how,,,to,,replace,,,,multiple,,,commas' FROM dual;
'HOW,,,TO,,REPLACE,,,,MULTIPLE
--------------------------------------
how,,,to,,replace,,,,multiple,,,commas
SQL> SELECT
replace(replace(REPLACE('how,,,to,,replace,,,,multiple,,,commas',',',' @'),'@
',''),' @',', ')
FROM dual;
REPLACE(REPLACE(REPLACE('HOW,,
----------------------------------
how, to, replace,
multiple, commas
SQL> SELECT replace(replace(REPLACE('how,,,to,,replace,,,,multiple,,,commas',',','
@'),'@ ',''),' @',' ') FROM dual;
REPLACE(REPLACE(REPLACE('HOW,,
------------------------------
how to replace multiple
commas
SQL>
|
****************************************************
13. 1. Write a query to
find the date on which the first ‘FRIDAY’ of a month falls.
2. Write a query to find the date on which the last ‘FRIDAY’ of
a month falls.
3. Write a query to find the date on which the second ‘FRIDAY’
of a month falls.
Answer :
First ‘FRIDAY’ of the month:
1
2
|
SELECT next_day(trunc(SYSDATE,'MM')-1,'FRIDAY')
first_of_month FROM dual;
|
The ‘-1′ is required as the first ‘FRIDAY’ might fall on the 1st
of the month.
For eg. if 1-JULY-2011 falls on a FRIDAY, then without the ‘-1′ we
would get the next Friday, 8-JULY-2011.
Last ‘FRIDAY’ of the month:
1
2
|
SELECT next_day(last_day(trunc(SYSDATE))-7,'FRIDAY')
last_of_month FROM dual
|
Second ‘FRIDAY’ of the month:
1
|
SELECT next_day((trunc(SYSDATE,'mm')-1)+7,'FRIDAY')
FROM dual
|
****************************************************
14. Some more queries on
dates.
1. Query to find the dates of all the ‘FRIDAY’ (or any other day
of the week) of a month.
1
2
3
4
5
6
7
|
SELECT d
FROM
(SELECT decode(trim(to_char(trunc(SYSDATE,'MM')-1+LEVEL,'DAY')),
'FRIDAY',trunc(SYSDATE,'MM')-1+LEVEL)
d
FROM dual
CONNECT BY LEVEL
<=
last_day(trunc(SYSDATE))+1-trunc(SYSDATE,'MM')-1)
WHERE d
IS NOT NULL
|
This returns all the
dates which falls on a ‘FRIDAY’ for the current month.
2. Query to check if a given year is leap year:
There are two easy ways
to check if a year is a leap year or not.
In the first query,
using trunc with the format
mask ‘YYYY’ we go to the first day
of the year (1st Jan) and then add ’59′ days to it. If the year is a leap year,
then the 59th day will be the 29th of Feb. If it is not a leap year, then the
59th day will be the 1st of March. We check this by extracting the Month using
(MON). If MON returns ‘FEB’ then it is a leap year (TRUE) else it is not a leap
year.
1
2
3
|
SELECT DECODE(TO_CHAR
(TRUNC(TO_DATE('3/3/2004','DD/MM/YYYY'),'YYYY')+59,
'MON'),'FEB','TRUE','FALSE')
leap_year FROM dual
|
In the second query, we
go to the last day of the year (31st dec).
In a leap year, the 31st of Dec is the 366th day of the year. We check if the
31st of Dec is the 366th day (for a leap year) or the 365th day ( for non leap
years).
1
2
3
|
SELECT DECODE(TO_CHAR
(TO_DATE('31/12/'||'2011','DD/MM/YYYY'),'DDD'),
'366','TRUE',
'FALSE') leap_year FROM dual
|
****************************************************
