SQL Mock Interview | Intermediate | Part 1

This is a mock SQL Interview session for intermediate level SQL developers!

The feedback and answers will be posted in a separate video.

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Data Science Fundamentals with Python and SQL Specialization

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

Need a break from all the SQL talk?
Why dont' you follow us on our new travel photography instagram account here -

For enrolling and enquiries, please contact us at

[LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!

[designanddecor]

Primary key is for unique records. Make it an identity column.
Use rank function to find lowest salaries.
Add a condition to check null values to convert to inner join.
Check in start and end dates to find overlapping records.
Use Union All if you do not need distinct records for better performance👍

[madhusudhanreddyt]

to improve the performance.. first step is to select the specific columns from the tables rather than just specifying * which is going to get all the columns from the table..

[madhusudhanreddyt]

for overlapping dates, we can use the self join and check for the start date and end date conditions where class1 enddate should be less than class2 startdate...

[madhusudhanreddyt]

declare @var int
set @var = 99

select
case
when @var < 100 then 1
when @var > 90 then 2
end as Val1;

it is going to return 1, why because it is going to evaluate the first condition and if the first condition is satisfied it is going to give that value...

[madhusudhanreddyt]

-- find employee with second lowest salary in a department

;with cte_temp as
(
select
empid,
deptid,
salary,
DENSE_RANK() over (partition by deptid order by salary asc) as drno
from employee_sample
)
select empid, deptid, salary
from cte_temp where cte_temp.drno = 2;

[madhusudhanreddyt]

for the join question, how to make the left outer join work like inner join, we need to write a where condition where records not matching should be set to not null.
INNER JOIN:
select
emp.EmpID, emp.Name, emp.Gender, emp.country, emp.Salary, dept.DeptName, dept.DeptHead, dept.Location
from tbl_Employee as emp inner join tbl_Department as dept
on emp.DepartmentId = dept.DeptId

--
LEFT OUTER JOIN WORKING AS INNER JOIN
select
emp.EmpID, emp.Name, emp.Gender, emp.country, emp.Salary, dept.DeptName, dept.DeptHead, dept.Location
from tbl_Employee as emp left outer join tbl_Department as dept
on emp.DepartmentId = dept.DeptId
where dept.DeptId is not null

[kristyowens]

Use RANK function. Order by salary asc and pick the 2nd rank

[kristyowens]

Check for NULL value to convert to inner join. Union ALL combines all records. Union combines unique records.

[kristyowens]

Primary Key identifies unique records. It can not have NULL values.

[hv]

Primary Key identifies unique records. It does not take Null value. There can be more than one primary keys.

[hv]

Case will output 2 as it is the last condition satisfied.

[hv]

Aggregate functions like row number or rank for ranking

[hv]

Union all combines all records and gives better performance.

[hv]

Choose an integer field as primary key

[hv]

Join on integer columns to improve performance.

[yakkaluruvijaysankar]

Very useful information. Please do more videos like this.. thankyou

[abhimanyutiwari]

Thanks a lot mam for the mock Interview.