## Interview Question: Count number of every week day in a year

Interview Question: With a single SELECT statement get the number of each week day in the current year.

Level: Intermediate

Expected Result:

```Day                                  Days in Year
------------------------------------ ------------
SUNDAY                                         52
MONDAY                                         52
TUESDAY                                        52
WEDNESDAY                                      52
THURSDAY                                       52
FRIDAY                                         53
SATURDAY                                       53```

Solution #1:

```WITH x AS (
SELECT LEVEL-1+TRUNC(SYSDATE, 'YYYY') AS dd
FROM dual
CONNECT BY TRUNC(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'YYYY')=TRUNC(SYSDATE, 'YYYY')
)
SELECT TO_CHAR(dd, 'DAY') "Day", COUNT(*) "Days in Year"
FROM x
GROUP BY TO_CHAR(dd, 'DAY'), TO_CHAR(dd, 'D')
ORDER BY TO_CHAR(dd, 'D');```

Explanation:

The WITH clause returns all days in the current year, this is a common trick used in majority of sql puzzle related to a calendar. The connect by query used in the WITH generated a date range which starts on TRUNC(SYSDATE, ‘YYYY’) – i.e. the 1st day of the year – and continues as long as the next day falls into the same year (see condition in the CONNECT BY clause). The main query groups by day name – TO_CHAR(dd, ‘DAY’) – and sorts by day number (in a week) – TO_CHAR(dd, ‘D’).

Solution #2:

```WITH x AS (
FROM dual
)
SELECT TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'DAY') "Day",
CASE WHEN MOD(days_in_year,52)>=LEVEL THEN 53
ELSE 52
END "Days in Year"
FROM x
CONNECT BY LEVEL<=7
ORDER BY TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'D');```

Explanation:

The idea behind this solution is totally different than in the 1st one. A year has 52 weeks and 1 or 2 days depending on whether it is a  leap year or not. So each day of the week happens 52 times a year + first one or two days of the year make corresponding week days have 53 days in that same year. If we know the number of days in a year (365 or 366) we can find out which days of the week will happen 53 times. For that matter we can take MOD(days_in_year, 52) expression that will return either 1 or 2. If the day order number within a year is 1 (or 2 for the leap year) we know that the corresponding week day will occur 53 times, otherwise – 52.

The WITH clause returns number of days in the current year. We get that by taking the 1st day of the current year: TRUNC(SYSDATE,’YYYY’), adding 12 months to it and subtract 1 day to get the last day of the current year. Taking TO_CHAR(…, ‘DDD’) – gives us the order number of that day in the year which is exactly the number of days in the current year.

The main query generates the date range from Jan-1 to Jan-7 in the current year, and assigns 52 or 53 to the 2nd column based on the logic described above.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

• Use Single SELECT statement only
• DECODE and CASE functions are not allowed
• An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
• The query should work in Oracle 11g.

### Expected Result:

```ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
JAMES                                   950
FORD                        3000
MILLER           1300```

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

### Expected Result:

```Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933
```

## Solutions

### #1: Using GROUP BY Over ROWNUM expression

```WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933```

### #2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

```WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
ORDER BY empno
MEASURES
A.empno firstemp,
LAST(empno) lastemp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B*)
DEFINE B AS empno = PREV(empno)+1
);```

### #3: Using LEAD Analytic function (credit to Krishna Jamal)

```WITH x AS
(
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

### Expected Result:

```Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933```

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

### Expected Result:

```DEPTNO Year 1   Year 2
------ -------- --------
10 1981     1982
20 1980     More (3)
30 1981

```

## Solutions

### #1: Using COUNT(DISTINCT ..)

```SELECT deptno, MIN(EXTRACT(YEAR FROM hiredate)) AS "Year 1",
CASE COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN ''
WHEN 2 THEN TO_CHAR(MAX(EXTRACT(YEAR FROM hiredate)))
ELSE 'More (' || COUNT(DISTINCT EXTRACT(YEAR FROM hiredate)) || ')'
END AS "Year 2"
FROM emp
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

### #2: Using DENSE_RANK Analytic Function

```WITH x AS (
SELECT deptno, EXTRACT(YEAR FROM hiredate) hire_year,
DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate)) rk
FROM emp
)
SELECT deptno, MIN(hire_year) "Year 1",
CASE MAX(rk) WHEN 1 THEN ''
WHEN 2 THEN CAST(MAX(hire_year) AS CHAR(4))
ELSE 'More (' || MAX(rk) || ')'
END AS "Year 2"
FROM x
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

### #3: Using PIVOT clause

```SELECT deptno, Y1 "Year 1",
CASE WHEN cnt>2 THEN 'More (' || cnt || ')'
ELSE TO_CHAR(Y2)
END "Year 2"
FROM
(
SELECT deptno, EXTRACT(YEAR FROM hiredate) yr,
CASE DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN 'Y1'
WHEN 2 THEN 'Y2'
END AS Y,
COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))OVER(PARTITION BY deptno) cnt
FROM emp
)
PIVOT
(
MAX(yr)
FOR y IN ('Y1' y1,'Y2' y2)
);

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Show Leading Spaces in Character String in SQL*PLus

SQL*Plus with its default settings ignores leading spaces when you attempt to output a character string variable using DBMS_OUTPUT.PUT_LINE procedure:

```SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345```

As you can see in the above example, two leading spaces are trimmed.

To make SQL*Plus showing the leading space characters, we need to change the following setting:

`SET SERVEROUTPUT ON FORMAT WRAPPED`

Now, the spaces will be preserved:

```SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345```

Alternatively, you can set the format to TRUNCATED:

```SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

PL/SQL procedure successfully completed.
```

The default setting is WORD WRAPPED:

```SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

PL/SQL procedure successfully completed.

SQL> SHOW SERVEROUTPUT
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED```

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Prevent Division by Zero with NULLIF function

If you want to avoid division by 0 issue, use NULLIF function to substitute zero in  denominator with NULL.

For example, the following query fails:

```SQL> SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
2 FROM dual;
SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
*
ERROR at line 1:
ORA-01476: divisor is equal to zero```

To fix it, use NULLIF and the result will be NULL instead of error:

```SELECT 100/NULLIF((SELECT COUNT(*)
FROM emp
WHERE deptno=40),0) expr
FROM dual;```

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## A few thoughts on parameterized cursors.

In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.

Let’s illustrate this point with a specific (though artificial) example.

Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:

```SQL> SELECT deptno, ename, sal
2  FROM emp e
3  WHERE sal>(SELECT AVG(sal)
4             FROM emp
5             WHERE deptno=e.deptno)
6  ORDER BY deptno, sal DESC, ename;

DEPTNO ENAME             SAL
---------- ---------- ----------
10 KING             5000
20 FORD             3000
20 SCOTT            3000
20 JONES            2975
30 BLAKE            2850
30 ALLEN            1600```

We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:

```SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
v_deptno  NUMBER;
v_avg_sal NUMBER;
CURSOR e IS
SELECT ename, sal
FROM emp
WHERE deptno=v_deptno
AND sal>v_avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
v_deptno:=v1.deptno;
v_avg_sal:=v1.avg_sal;
FOR v2 IN e LOOP
END LOOP;
END LOOP;
END;
/

deptno ename       sal
------ -------- ------
10 KING       5000
20 FORD       3000
20 SCOTT      3000
20 JONES      2975
30 BLAKE      2850
30 ALLEN      1600```

Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.

Alternatively, we can you a record variable:

```DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
v_dept d%ROWTYPE;
CURSOR e IS
SELECT ename, sal
FROM emp
WHERE deptno=v_dept.deptno
AND sal>v_dept.avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
v_dept:=v1;
FOR v2 IN e LOOP
END LOOP;
END LOOP;
END;```

A better way to pass variables to a cursor is to use cursor parameters:

```DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
SELECT ename, sal
FROM emp
WHERE deptno=c_deptno
AND sal>c_avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
END LOOP;
END LOOP;
END;
/

deptno ename       sal
------ -------- ------
10 KING       5000
20 FORD       3000
20 SCOTT      3000
20 JONES      2975
30 BLAKE      2850
30 ALLEN      1600```

As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.

The following example will optimize the last block by using a single cursor parameter:

```DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
CURSOR e(c_dept d%ROWTYPE) IS
SELECT ename, sal
FROM emp
WHERE deptno=c_dept.deptno
AND sal>c_dept.avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
FOR v2 IN e(v1) LOOP
END LOOP;
END LOOP;
END;
/```

So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?

• Parameterized cursors support default values for cursor parameters
• The cursors can be referenced more than once with different parameter values
• A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

### Expected Result:

```DEPTNO Year 1   Year 2
------ -------- --------
10 1981     1982
20 1980     More (3)
30 1981```

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

### Expected Result:

```--For N=100:

Biggest Prime Factor
--------------------
5

--For N=52:

Biggest Prime Factor
--------------------
13

--For N=21:

Biggest Prime Factor
--------------------
7
```

## Solutions

### #1: Using CTE (recursive WITH)

```WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
7

```

### #2: Using CONNECT BY clause , version 1

```WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;```

### #3: Using CONNECT BY clause, version 2

```WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;
```

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.