SQL Server | Optum SQL Interview Questions | SQL Interview Preparation | Part 42

For SQL Quiz-

Find Us On FaceBook-

---Query--

IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE NAME='OPTUM_P3_EMP')
BEGIN
DROP TABLE OPTUM_P3_EMP
END
CREATE TABLE OPTUM_P3_EMP
(
EMP_ID INT,
DEPT_ID INT
)
IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE NAME='OPTUM_P3_DEPT')
BEGIN
DROP TABLE OPTUM_P3_DEPT
END

CREATE TABLE OPTUM_P3_DEPT
(
DEPT_ID INT,
DEPT_NAME VARCHAR(50)
)

INSERT INTO OPTUM_P3_EMP VALUES(1,10),(2,11),(3,13),(4,22)
INSERT INTO OPTUM_P3_DEPT VALUES(10,'IT'),(11,'ACC'),(12,'FINANCE'),(13,'ADMIN')

SELECT * FROM OPTUM_P3_EMP
SELECT * FROM OPTUM_P3_DEPT

SELECT E.EMP_ID,D.DEPT_NAME
FROM OPTUM_P3_EMP AS E
LEFT JOIN OPTUM_P3_DEPT AS D
ON E.DEPT_ID=D.DEPT_ID
WHERE D.DEPT_ID IS NULL

[daveshkashyap]

bro pls share the second question code

[ravindrasingh]

Nice questions. ..really very helpful. .
Thanks for sharing

[abhigyapandey]

Nice video Sir, if possible then please let me know on which topics MCQ's questions are based in Online Assesment round for Optum.

[Sfamily-s]

Very informative! best part is in your channel as you teach with practical examples which is superb. Thank you

[serendipitous]

In landline/mobile example, you've used over partition by method to divide into group and produce row count.
As per your example, the M is typed first, L is typed second so we will get RW=1 and 2 respectively. Hence it works.
But what if L is typed first and then M second. Then RW =1 for Landline and RW=2 for Mobile. So it will return Landline number even though Mobile number is there. It doesnt work right?
Sometimes data entry will get mixed up, we wont get them in order.

[shubhamkanojia]

We could have achieved the output for Landline/Mobile without join...by just selecting from the derived table and using where rw =1.
Do you think join was necessary?