In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover analytical functions list aggregate, date functions,  union all, pivot & prime numbers. Breaking single string to multiple rows using dual, rownum or level providing the separator.


Scenario 1. In below example remove the duplicate. Example: Records 1 & 2 are identical.

1Src
CREATE TABLE "DISTANCE"
(
"SOURCE_NAME" VARCHAR2(20 BYTE),
"DEST_NAME"   VARCHAR2(20 BYTE),
"DISTANCE"    NUMBER
);

Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','JAIPUR',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('JAIPUR','DELHI',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','PUNE',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('PUNE','DELHI',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','SPITI',731);

COMMIT;

Solution:  You can use the LEAST/GREATEST function to find out the dupes.

SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME),
GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance )AS RNUM
FROM DISTANCE d;
2

From this data set you can filter the rows using the RNUM column to fetch distinct rows.

WITH data_set AS
(SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance ) AS RNUM
FROM DISTANCE d
)
SELECT * FROM data_set WHERE RNUM =1;
3

Scenario 2. In below example customer data is present for product with dates. Fetch the customer who came in all months of quarter at least once. Like Jan, Feb, Mar or Apr, May, June etc. Notice in below data-set C1 & C3 came in all months of quarter Q1 & Q2 respectively.

11

Solution: You can use the date functions to find out the months & quarter.

SELECT CUST_ID,
listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
ORDER BY BILL_DATE) months_name,
TO_CHAR(BILL_DATE,'q')
FROM SALES
GROUP BY CUST_ID,
TO_CHAR(BILL_DATE,'YYYY'),
TO_CHAR(BILL_DATE,'q');
111
WITH dataset AS
(SELECT CUST_ID,
listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
ORDER BY BILL_DATE) months_name,
TO_CHAR(BILL_DATE,'YYYY') year_,
TO_CHAR(BILL_DATE,'q') quarter_
FROM SALES
GROUP BY CUST_ID,
TO_CHAR(BILL_DATE,'YYYY'),
TO_CHAR(BILL_DATE,'q')
)
SELECT *
FROM dataset
WHERE months_name IN ('01||02||03','04||05||06','07||02||09','10||11||12') ;
12

Scenario 3.  Output of this SQL: Select count(1), SUM(1) from dual where 1=2;

Select count(1), SUM(1) from dual where 1=2;
333

Scenario 4.  Print 1st day of Month, Last Day, Mid, Week, Quarter for given date.

WITH dataset AS
(SELECT to_date('10-JAN-2019','dd-mon-yyyy') date_ FROM dual
union
SELECT to_date('20-FEB-2019','dd-mon-yyyy') date_ FROM dual
union
SELECT to_date('05-JUN-2019','dd-mon-yyyy') date_ FROM dual
)
SELECT to_char(date_,'DD-MM-YYYY'), to_char(date_,'Q') Quarter ,to_char(date_,'W') week,
trunc((date_),'MM') FIRST_DATE_OF_MONTH, last_day(date_) LAST_DATE_OF_MONTH
FROM dataset;
4444

Print Day of the date:

day

Scenario 5.  In below example you have to print date twice. See sample date.

Input:  555  Output Required: 6666

Solution:

WITH data_set AS
( SELECT id_ FROM dataset
UNION ALL
SELECT id_ FROM dataset
)
SELECT * FROM data_set ORDER BY 1;
555

Scenario 6.  In below example you have ~ tilde separated date in column VAL. You have to break the string using SQL like below. Refer required output screenshot.

Input: 7777 Required Output: 7777

CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));

INSERT INTO  DATASET VALUES (1, 'A~B~C~D');
INSERT INTO  DATASET VALUES (2, 'X~Y~Z');
INSERT INTO  DATASET VALUES (5, 'K');
COMMIT;

Solution: You can use the level also instead of rownum to generate numbers.

7777

Scenario 7.  Similar to Scenario 6, In below example you have data in VAL column & you have bring it in one row separated by double pipe ||.

Input : 7777 Required Output: 7777

DROP TABLE DATASET;
CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'A');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'B');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'C');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'D');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Y');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Z');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'X');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('5', 'K');
COMMIT;

Solution: 

SELECT ID_,
listagg (VAL, '||') within GROUP (
ORDER BY VAL) NEW_VAL
FROM dataset
GROUP BY ID_;
7777

Scenario 8. Rotating rows to column, see below screenshot.

Input: 7777 Output: 77

Solution : Using Pivot Clause

SELECT *
FROM
(SELECT prod_id, month_, sales FROM sales_data
) pivot( MAX(sales) FOR month_ IN ('Jan' AS JAN, 'Feb' AS FEB, 'Mar' AS MAR, 'Apr' AS APR) );
7777

Scenario 9 : Print prime numbers using SQL

WITH a AS
( SELECT rownum rn FROM dual CONNECT BY rownum <=20
),
b AS
(SELECT rownum rn FROM dual CONNECT BY rownum =b.rn
),
prime_num AS
(SELECT rn FROM div WHERE mod_=0 GROUP BY rn HAVING COUNT(rn1)=2 ORDER BY 1
)
SELECT * FROM prime_num;
prime

Visit below blogs for more scenario questions.


Thanks!

Happy Learning! Your feedback would be appreciated!

5 thoughts on “Oracle SQL Scenario Questions – Part 2

  1. Scenario 6 : optimized query would be :

    select id_, regexp_substr(val,'[^~]+’, 1, level)
    from dataset
    connect by regexp_substr(val, ‘[^~]+’, 1, level) is not null;

    Kind regards,
    Priyanka

    Liked by 1 person

    1. Thanks for your input.
      I have tried your query, it was giving duplicates records in output.

      On top of this query we can go with group by or distinct (thought distinct is not recommended for huge records) to pick unique records. Or may be analytical query to fetch 1 record per group.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s