SQL Interview Questions and answers Part 35 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 35 | SQL Scenario based Interview Question and Answer

Problem Statement :- Employees Table has five columns namely Employee_no, Birth_date , first_name , last_name and Joining_date

(1.)As a convention the values in first_name and last_name should always be in uppercase. But due to data entry issues some records may not adhere to this convention. Write a query to find all such records where first_name is not in upper case.
(2.) For some records the first_name column has full name and last_name is blank. Write a SQL query to update it correctly,
(3.) Calculate tenure of employees as of 30th Jun 2017. Prepare following above sample report :
(4.) List all the employees whose work anniversary is same as their birthday date.
(5.) Find the Youngest (minimum age) employee with tenure more than 5 years as of 30th June 2017.

------------------------------------------------------------------------------------------------------------------
Follow me on Social Networking Sites :
------------------------------------------------------------------------------------------------------------------
*Twitter:
*Instagram :

--------------------------------------------------------------------------------------------------------------
Table and Insert SQL Script :
--------------------------------------------------------------------------------------------------------------
Create Table Employees
(
Employee_no BigInt,
Birth_date Date,
First_name Varchar(50),
Last_name Varchar(50),
Joining_date Date
)

INSERT INTO Employees Values(1001,CAST('1988-08-15' AS Date),'ADAM','WAUGH', CAST('2013-04-12' AS Date))
INSERT INTO Employees Values(1002,CAST('1990-05-10' AS Date),'Mark','Jennifer', CAST('2010-06-25' AS Date))
INSERT INTO Employees Values(1003,CAST('1992-02-07' AS Date),'JOHN','Waugh', CAST('2016-02-07' AS Date))
INSERT INTO Employees Values(1004,CAST('1985-06-12' AS Date),'SOPHIA TRUMP','', CAST('2016-02-15' AS Date))
INSERT INTO Employees Values(1005,CAST('1995-03-25' AS Date),'Maria','Gracia', CAST('2011-04-09' AS Date))
INSERT INTO Employees Values(1006,CAST('1994-06-23' AS Date),'ROBERT','PATRICA', CAST('2015-06-23' AS Date))
INSERT INTO Employees Values(1007,CAST('1993-04-05' AS Date),'MIKE JOHNSON','', CAST('2014-03-09' AS Date))
INSERT INTO Employees Values(1008,CAST('1989-04-05' AS Date),'JAMES','OLIVER', CAST('2017-01-15' AS Date))
---------------------------------------------------------------------------------------------------------------
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting #ITJUNCTION4ALL

[sonurawat]

#1
select * from employees_3
where first_name not like upper(first_name);

[LiveWithDebasmita]

For Qno -> 2 I have done something like this.


with cte as
(
select e.*, value from Employees as e cross apply string_split(First_name, ' ')
),
nextCte as
(
select *, row_number() over(partition by Employee_No order by Employee_No) as seq from cte
)
select a.Employee_no, a.Birth_date, b.value as First_name, c.value as Last_name, a.Joining_date from nextCte as a
inner join nextCte as b on a.Employee_no = b.Employee_no and a.seq = b.seq
inner join nextCte as c on a.Employee_no = c.Employee_no and a.seq+1 = c.seq
union all
select b.Employee_no, b.Birth_date, b.First_name, b.Last_name, b.Joining_date from
(select Employee_No from nextCte group by Employee_No having count(Employee_no) = 1) as a
inner join nextcte as b on a.employee_no = b.employee_no order by Employee_no;

[shakiraafshan]

1) with cte as (select *, substring(first_name, 2, 1) as x from employees)
select * from cte where ascii(x) not between ascii('A') and ascii('Z')

3) select * from employees
where month(birth_date) = month(joining_date)
and day(birth_date) = day(joining_date)

[venkatc]

Grt, keep going on.Pls take use cases also

[SahilKhuranaMusic]

My solution to part 3 and 5 :
3)
select Tenure_in_years, count(*) from
(select *, case
when Years < 1 then '< 1 year'
when (1 < Years and 3 > Years) then '1-3 year'
when (3 < Years and 5 > Years) then '3-5 year'
when 5 < Years then '5+ year' end as 'Tenure_in_years'
from
(select employee_no, cast(datediff(month, Joining_date, '2017-06-30')
as float)/12 as 'Years' from employees) a ) b group by Tenure_in_years


5)

select cast(datediff(year, birth_date, '2017-06-30') as float) as minimum_age
from (
select *, rank() over (order by birth_date desc) as rnk
from Employees where
cast(datediff(month, Joining_date, '2017-06-30') as float)/12 > 5 ) a
where rnk = 1

[mayanktripathi]

alternative solution for outout 5:
select datediff(year, max(Birth_date), '2017-06-30') from Employees
where
datediff(year, Joining_date, '2017-06-30')>5;

[im_everim_ever]

great work. Please upload videos on user defined functions and stored procedures.

[somasreebiswas]

Your videos are awesome, great work. keep going..

[sonurawat]

part #4:
select * from employees_3
where to_char(birth_date, 'dd-mm')=to_char(joining_date, 'dd-mm');

[mohdtanveer]

Thanks for sharing this. Can you make videos on date time?

[bvshreyas]

great work. Could please make videos on lead and lag scenarios

[uma]

Are you going to add more videos in this SQL Scenario type?

[chroya]

Thanks for your sharing! I want to know for question 2, how to do it with MySQL

[francoco]

there's something weird regarding the joining_date column, which is affecting the results on C and E if you work with the original table. At the 14:38min, you'd see that the records 1001, 1005, 1008 change to a complete different joining date suddenly. it took me some time to find out why I got different results.

[LiveWithDebasmita]

Hi for qno -> 1, if we don't use collate, is there any other way to do it? I've come up with something but I believe there is a better way to it. Can you please share if there is any?

1) declare @max_value int;
set @max_value = (select max(len_first_name) from (select *, len(First_Name) as len_first_name from Employees) as a);

with cte as
(
select 1 as d
union all
select d+1 as d from cte
where d < @max_value
),
nextcte as (
select b.*, a.d , ascii(SUBSTRING(First_Name, d, 1)) as Ascii_value from cte as a
inner join (select *, len(First_Name) as len_first_name from Employees) as b on a.d <= b.len_first_name
)
select distinct a.Employee_no, a.Birth_date, a.First_name, a.Last_name, a.Joining_date from
(select *, case when Ascii_value between 65 and 90 or Ascii_value = 32 then 1 else 0 end as status from nextcte ) as a
where status = 0;

[bishwarup]

select * from Employees;
/* Detect upper case */
select
*
from
Employees
where CAST(First_Name as binary) <> upper(First_Name);

select
*
, case
when trim(last_name) = '' then SUBSTRING_INDEX(First_Name, ' ', 1) else first_name end as first_name
, case
when trim(last_name) = '' then SUBSTRING_INDEX(First_Name, ' ', -1) else last_name end as last_name
, abs(datediff(joining_date,'2017-06-30')/365) as tenure
, abs(datediff(birth_date,'2017-06-30')/365) as age
from
Employees
;

select
*
from
employees
where month(Birth_date) = month(joining_date)
and day(Birth_date) = day(Birth_date);


with cte as
(select
*
, case
when trim(last_name) = '' then SUBSTRING_INDEX(First_Name, ' ', 1) else first_name end as first_names
, case
when trim(last_name) = '' then SUBSTRING_INDEX(First_Name, ' ', -1) else last_name end as last_names
, abs(datediff(joining_date,'2017-06-30')/365) as tenure
, abs(datediff(birth_date,'2017-06-30')/365) as age
from
Employees
)
select * from cte
where tenure >5
order by age
limit 1;