vastdp.blogg.se - Interview questions for oracle sql developer

Scenario 9 : Print prime numbers using SQL ) pivot( MAX(sales) FOR month_ IN ('Jan' AS JAN, 'Feb' AS FEB, 'Mar' AS MAR, 'Apr' AS APR) ) (SELECT prod_id, month_, sales FROM sales_data Rotating rows to column, see below screenshot. INSERT INTO "DATASET" (ID_, VAL) VALUES ('5', 'K') INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'X') INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Z') INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Y') INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'D') INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'C') INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'B') INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'A') Similar to Scenario 6, In below example you have data in VAL column & you have bring it in one row separated by double pipe ||. SELECT DISTINCT ID_,regexp_substr(VAL,'+',1,level) VALįROM TAB CONNECT BY LEVEL<=cnt ORDER BY 1 (SELECT ID_,VAL,regexp_count(VAL,'~')+1 cnt FROM DATASET) Solution: You can use the level also instead of rownum to generate numbers. INSERT INTO DATASET VALUES (1, 'A~B~C~D') Refer required output screenshot.ĬREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100)) You have to break the string using SQL like below. In below example you have ~ tilde separated date in column VAL. In below example you have to print date twice. Trunc((date_),'MM') FIRST_DATE_OF_MONTH, last_day(date_) LAST_DATE_OF_MONTH SELECT to_char(date_,'DD-MM-YYYY'), to_char(date_,'Q') Quarter ,to_char(date_,'W') week, SELECT to_date('0','dd-mon-yyyy') date_ FROM dual SELECT to_date('2','dd-mon-yyyy') date_ FROM dual (SELECT to_date('1','dd-mon-yyyy') date_ FROM dual

Print 1st day of Month, Last Day, Mid, Week, Quarter for given date. Select count(1), SUM(1) from dual where 1=2 Output of this SQL: Select count(1), SUM(1) from dual where 1=2 Listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP ( Solution: You can use the date functions to find out the months & quarter. Notice in below data-set C1 & C3 came in all months of quarter Q1 & Q2 respectively. Like Jan, Feb, Mar or Apr, May, June etc. Fetch the customer who came in all months of quarter at least once. In below example customer data is present for product with dates. Row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance ) AS RNUM GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance )AS RNUMįrom this data set you can filter the rows using the RNUM column to fetch distinct rows. Row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), Solution: You can use the LEAST/GREATEST function to find out the dupes. Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','SPITI',731) Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('PUNE','DELHI',1427) Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','PUNE',1427) Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('JAIPUR','DELHI',281) Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','JAIPUR',281)

Part 4 Blog Link: Oracle SQL – Scenario Questions Part 4 Topic: Unique Index, Non-Unique Index & Functional Index.

Part 3 Blog Link: Oracle SQL – Scenario Questions – Part 3 : Topics : RPAD, PLAD, Null Function, Instr, Substr, regex_substr, translate.

Part 1 Blog Link: Oracle SQL Scenario Questions Part 1 Topics: regex, pivot, binary tree and other Oops related PL/SQL questions.

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

Container & Pluggable Database | Oracle 12c.

Customer-Orders Sample Schema | Oracle 12c.

PL/SQL Cursor Variable – Ref Cursors (Strong & Weak Type).Oracle Cloud Infrastructure Foundations.Oracle Apex | Create Workspace & Developer Users.Building REST APIs using SQL Developer & ORDS | GET.Building REST APIs | SQL Developer | POST & DELETE.Calling Oracle Database REST APIs using Python.Securing Oracle Database REST APIs using BasicAuth.Manage Python Virtual Environments & Packages using Pipenv.Scala – Different way of code execution.