--DAY 6 --#15DAYSCHALLENGE --Second most recent activity ,if there is only one activity is there it should also reflect  --Letscode QUESTION create table UserActivity ( username      varchar(20) , activity      varchar(20), startDate     Date   , endDate      Date ); insert into UserActivity values  ('Alice','Travel','2020-02-12','2020-02-20') ,('Alice','Dancing','2020-02-21','2020-02-23') ,('Alice','Travel','2020-02-24','2020-02-28') ,('Bob','Travel','2020-02-11','2020-02-18'); ------------------------------------SOLUTION----------------------------------------------------- SELECT REPLICATE('*',150) AS 'INPUT_TABLE' SELECT * FROM UserActivity SELECT REPLICATE('*',150) AS 'SOLUTION START' SELECT A.username,A.activity,A.startDate,A.endDate  FROM  ( SELECT  U.*,DENSE_RANK () OVER (PARTITION BY U.username ORDER BY U.startDate ) as...

 --DAY 6 --#15DAYS CHALLENGE -- Converting rows into columns BY PIVOT  --Without using PIVOT ------------------------------SAMPLE TABLE CREATED--------------------------------------- SELECT REPLICATE('*',150) AS 'SAMPLE TABLE CREATED' create table players_location ( name varchar(20), city varchar(20) ); --delete from players_location; insert into players_location values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore'); SELECT REPLICATE('*',150) AS 'INPUT_TABLE' SELECT * FROM players_location ------------------------------------------------SOLUTION-------------------------------------------------------------- SELECT REPLICATE('*',150) AS 'SOLUTION START WITH PIVOT ' select * FROm  ( SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn FROM players_location ) A PIVOT( MAX(Name) FOR City...

 DAY 5 #15DAYSSQLCHALLENEGE ----EY INTERVIEW QUESTION---- --Write a SQL to find the employee who got a salary increase for consecutive 2 years  ----------------------------Table creation---------------- --create table EMP_EY ( emp_name varchar(10), dep_id int, salary int, YEARL DATETIME ); --DROP TABLE  EMP_EY; insert into EMP_EY values  ('Siva',1,30000,'2000'),('Siva',1,40000,'2001'),('Siva',1,50000,'2002'),('Arabinda',2,1000,'2010'), ('Arabinda',2,2000,'2011'),('Arabinda',2,3000,'2012'), ('Robert',3,100,'2012'),('Robert',3,102,'2015'),('Robert',3,105,'2017'), ('Sanvi',4,199,'2018'),('Sanvi',4,99,'1990'),('Sanvi',4,9,'1993') -------------------------------------------------Table creation --END ----------- SELECT * FROM EMP_EY SELECT E1.*  FROM EMP_EY E1 INNER JOIN EMP_EY E3 ON YEAR (E1.YEARL)=YEA...

---DAY 5 -- #15DAYSSQLCHALLENGE --DROP TABLE Employees Create Table Employees (  Id int primary key,  HIRE_DATE DATE,  Name nvarchar(50),  Salary int ) Go Insert Into Employees Values (1,'2022-12-21 12:53:27.120', 'Mark', 1000) Insert Into Employees Values (6, '2022-12-20 12:53:27.120' ,'ARABINDA', 1000) Insert Into Employees Values (6, '2022-12-2 12:53:27.120' ,'NAYAK', 1000) Insert Into Employees Values (2, '2022-12-8 12:53:27.120', 'John', 2000) Insert Into Employees Values (3, '2022-12-9 12:53:27.120', 'Pam', 3000) Insert Into Employees Values (4, '2022-12-11 12:53:27.120',  'Sara', 4000) Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000) Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000) Go --Now execute the following query. Notice that we get the running total as expected. -- 1. When no value is specified for...

 ---DAY 4 --#15DAYSSQLCHALLENGE --Difference between count(*) and count(column_name) in SQL --Count(*) will count the null value --count(column_name) will not count the null value  ------------------------------START ------------------- SELECT REPLICATE('*',200) AS 'SOLUTION'  SELECT * FROM [dbo].[emp_2020]   SELECT * FROM  [dbo].[emp_2021] SELECT REPLICATE('*',200) AS 'JOIN THE ABOVE TWO TABLE WITH FULL OUTER JOIN '   SELECT *   FROM [dbo].[emp_2020] E_20 FULL   JOIN   [dbo].[emp_2021] E_21  ON E_20.emp_id=E_21.emp_id  SELECT  COUNT (* ) 'Count the no of records in the table' ,REPLICATE('*',200) AS 'COUNT (*) --count the null value '   FROM [dbo].[emp_2020] E_20 FULL   JOIN   [dbo].[emp_2021] E_21  ON E_20.emp_id=E_21.emp_id   SELECT  COUNT (e_21.designation) 'Count of designation'   ,REPLICATE('*',200) AS ' COUNT (COLUMN NAME)--Here it will not count t...

 --#15day SQL CHALLENGE --DAY 4 --We are having a budget of 70000 ,have to hire senior & junior developers within 70000. --But first we have to hire senior & then to hire junior with the remaining budget(Amount is remain after hiring senior developer ) --So write a sql query to find the employee (junior & senior developer ) those got hired within the above condition. SELECT REPLICATE('*',400) as 'Sample_Table_Created' create table candidates ( emp_id int, experience varchar(20), salary int ); delete from candidates; insert into candidates values (1,'Junior',10000),(2,'Junior',15000),(3,'Junior',40000),(4,'Senior',16000),(5,'Senior',20000),(6,'Senior',50000); SELECT * FROM candidates ---------------------------------------START SOLUTION------------------------ SELECT REPLICATE('*',400) Solution WITH TOTAL_SALARY AS ( SELECT *,SUM (salary) OVER (PARTITION BY experience order by salary asc  ROWS BETWEEN UN...

  --#15day SQL CHALLENGE --DAY 4 ---Follow my blog to get the question & answer asked by a  company. ----Write a SQL Query to find out the EMP NAME, MANAGER,SENIOR MANAGER FROM EMPLOYEE TABLE SELECT REPLICATE('*',400) as 'Sample_Table_Created' create table emp_tbl( emp_id int, emp_name varchar(20), department_id int, salary int, manager_id int, emp_age int); insert into emp_tbl values (1, 'Ankit', 100,10000, 4, 39); insert into emp_tbl values (2, 'Mohit', 100, 15000, 5, 48); insert into emp_tbl values (3, 'Vikas', 100, 12000,4,37); insert into emp_tbl values (4, 'Rohit', 100, 14000, 2, 16); insert into emp_tbl values (5, 'Mudit', 200, 20000, 6,55); insert into emp_tbl values (6, 'Agam', 200, 12000,2, 14); insert into emp_tbl values (7, 'Sanjay', 200, 9000, 2,13); insert into emp_tbl values (8, 'Ashish', 200,5000,2,12); insert into emp_tbl values (9, 'Mukesh',300,6000,6,51); insert into emp_tbl v...