  |
Before submitting please remember below two points :- 1.In section 1 please enter your valid mail ID else you won't be able to submit the quiz and view the score of the quiz. 2.In section 2 please enter student details. If you enjoy giving this quiz and found it useful? If so, please share it with your friends. Note : If anyone is interested for demo interview then you can drop your CV to mail ID :etechdeeksha@gmail.com |
ODD MAN OUT : SET 1
Hands On SQL
Look, we all have limited time on our hands. And we’re getting closer to the interview date every single minute. That shouldn’t scare. That should motivate you!
Time is limited, that’s why we must do the things we want: Today with proper planning .
"Hands On SQL" book can guide you to achieve success in your next interview. This book covers SQL interview experiences, questions and answers for a range of SQL Developer. All of these questions have been collected from the people who attended interviews at various multinational companies across the world.
In Hands On Sql, you’ll learn:
-All interview questions are asked in various MNC
-Questions are categorized Chapterwise .
-In-depth explanations
-Covers real time questions and answers
-Lots of scenario based questions.
Useful as a reference guide for SQL Interview preparation.
Are you ready to start reading this book?
If so: Order Now and WIN your next SQL interview .
Find the number of unique days each employee worked ?
Problem Statement : Find the number of unique days each employee worked ?
Table Name : unq_days
Expected Output :
Table Name : unq_days
Expected Output :
Script to Populate the Above sample table Data :
create table unq_days(
emp_id number,
task_id number,
start_dy varchar2(20),
end_dy varchar2(20)
);
insert into unq_days values(2,1,'Tuesday','Wednesday');
insert into unq_days values(2,1,'Monday','Friday');
insert into unq_days values(1,3,'Friday','Friday');
insert into unq_days values(1,2,'Monday','Tuesday');
insert into unq_days values(1,1,'Monday','Wednesday');
Solution:
Step 1:- Create a table to find the days between and populate the data as per below script
create table days_table (
id number (2) ,
weekdays varchar2(20));
insert into days_table values (1,'Monday');
insert into days_table values (2,'Tuesday');
insert into days_table values (3,'Wednesday');
insert into days_table values (4,'Thursday');
insert into days_table values (5,'Friday');
insert into days_table values (6,'Saturday');
insert into days_table values (7,'Sunday');
Step 2:- Create a function to convert the days into a number value which we can use to find the range of between in step 3 .
CREATE OR REPLACE FUNCTION Fn_days_working(i_days varchar2)
RETURN number IS
v_id number :=0 ;
BEGIN
SELECT id into v_id
FROM days_table t where t.weekdays=i_days;
RETURN v_id ;
END;
Step 3:- Final query
SELECT Emp_id , count(1) from (
with tab as (
select t.emp_id , LISTAGG(t.working_days,',') wrdy from
(
select ud.emp_id ,
(select LISTAGG(distinct t.weekdays ,',')from days_table t
where t.id between Fn_days_working(ud.start_dy) and Fn_days_working(ud.end_dy)
) working_days
from unq_days ud
) t
group by t.emp_id
)
SELECT DISTINCT emp_id, trim(regexp_substr(wrdy, '[^,]+', 1, LEVEL)) str
FROM TAB
CONNECT BY LEVEL <= regexp_count(WRDY, ',')+1
)
group by emp_id ;
I am interested in hearing your feedback about this article , So that I can improve my lab series on sql for you.connect with us on facebook
Share
Did you enjoy reading this and found it useful? If so, please share it with your friends:
C INTERVIEW QUIZ SET:-2
| Before submitting please remember below two points :- 1.In section 1 please enter your valid mail ID else you won't be able to submit the quiz and view the score of the quiz. 2.In section 2 please enter student details. If you enjoy giving this quiz and found it useful? If so, please share it with your friends. Note : If anyone is interested for demo interview then you can drop your CV to mail ID :etechdeeksha@gmail.com |
Subscribe to:
Posts (Atom)