Sql Variables & 2nd Highest Salary Interview Question | Mysql, MariaDB & Postgres
Summary
TLDRIn this educational video, Bhesh from Master in Code teaches viewers about SQL variables, focusing on their practical applications. He explains how to declare and use variables within SQL queries, demonstrating with examples. The video dives into a real-world scenario, showing how to determine the rank of salaries, including finding employees with the second-highest salary. Bhesh walks through the logic and SQL implementation, step by step, enhancing viewers' understanding of SQL variables and their utility in database management.
Takeaways
- 😀 The video is a tutorial on using variables in SQL, focusing on their application in ranking employees by salary.
- 🔢 The video demonstrates how to declare and use variables directly in SQL queries using the colon (:) notation.
- 📊 It explains the practical use of variables for determining the rank of employees based on their salaries, including handling ties.
- 💼 The presenter introduces a real-world example involving a table of employee salaries to illustrate the concept of ranking.
- 👨💻 The tutorial walks through the process of implementing a ranking system using SQL variables step by step.
- 📝 The video script includes a live coding session where the presenter writes and explains SQL queries to calculate ranks.
- 🛠️ It highlights the importance of setting initial values for variables and updating them conditionally based on the data.
- 🔑 The video addresses a common interview question about finding the second-highest salary and extends it to include all employees with that salary.
- 🔍 The presenter uses a nested query to filter and display only the employees who have the second-highest salary.
- 🎓 The video concludes with a summary of the key learnings and a thank you note to the viewers.
Q & A
What is the main topic of the video?
-The main topic of the video is learning about variables in SQL, specifically focusing on how to use them to find the second-highest salary and rank employees accordingly.
What are the three main points covered in the video?
-The three main points covered in the video are: 1) Where to use variables in SQL, 2) How to use variables in SQL, and 3) An interview question on finding the second-highest salary using variables.
How does the video demonstrate the use of variables to rank employees by salary?
-The video demonstrates the use of variables by assigning ranks to employees based on their salaries. It uses a variable to track the previous salary and another to assign a rank, incrementing the rank when a new unique salary is encountered.
What is the purpose of the 'previous salary' variable in the SQL example?
-The 'previous salary' variable is used to store the salary of the previously processed row. It helps in determining whether the current salary is the same as the previous one, which is crucial for assigning correct ranks.
How does the video explain the concept of declaring variables in SQL?
-The video explains that variables can be declared directly in SQL using the 'SET' keyword followed by the variable name and the value it should hold, or within a SELECT query using a colon to denote declaration and an equal sign for assignment.
What is the initial value set for the 'rank' variable in the SQL example?
-The initial value set for the 'rank' variable in the SQL example is zero.
What is the initial value set for the 'previous salary' variable in the SQL example?
-The initial value set for the 'previous salary' variable in the SQL example is 'null'.
How does the video handle the case where multiple employees have the same salary?
-The video handles the case where multiple employees have the same salary by assigning them the same rank and only incrementing the rank when a new, unique salary is encountered.
What is the final output the video aims to achieve using the variables?
-The final output the video aims to achieve is a list of employees with their names, departments, salaries, and ranks, ordered by salary in descending order.
How does the video suggest filtering the result to only show employees with the second-highest salary?
-The video suggests using a nested query and a WHERE clause to filter the results, selecting only those rows where the rank is equal to two, which corresponds to the second-highest salary.
What is the significance of using variables in SQL for solving interview questions like this?
-Using variables in SQL for solving interview questions like finding the second-highest salary is significant because it allows for dynamic calculations and conditional logic that can be used to rank or order data based on specific criteria.
Outlines
💻 Introduction to SQL Variables and Application
In this segment, the host, Bhesh, welcomes viewers to the 'Master in Code' channel and introduces the topic of SQL variables. The video aims to teach viewers where and how to use SQL variables, with a focus on a practical example: finding the second-highest salary among employees. The host explains that the goal is not just to find the second-highest salary but to identify all employees who share this salary. The video also touches on how to use the 'WHERE' clause in conjunction with variables to print out employee details and salaries. Bhesh demonstrates how to declare variables in SQL, either directly by setting a value or within a SELECT query. The host then dives into the terminal to show a practical demonstration of these concepts using MySQL installed on Docker Desktop.
🔢 Implementing SQL Variable Logic for Salary Ranking
The second paragraph delves into the practical application of SQL variables to solve an interview question: determining the rank of employees based on their salaries. The host outlines the logic for this task, which involves comparing the current salary with a 'previous salary' variable to determine rank. The initial values for 'rank' and 'previous salary' are set, and the logic is implemented to increment the rank when the current and previous salaries differ. The host then attempts to implement this logic in SQL, facing a minor issue with the syntax but quickly correcting it. The video demonstrates how to use variables to calculate and display ranks alongside employee details, such as name and department. The host concludes by showing how to filter the results to find employees with the second-highest salary, emphasizing the utility of SQL variables in solving complex queries.
Mindmap
Keywords
💡Variables
💡SQL
💡Ranking
💡Salary
💡Docker Desktop
💡MySQL
💡Interview Question
💡SELECT Query
💡RANK
💡Nested Query
💡ORDER BY Clause
Highlights
Introduction to learning variables in SQL
Content outline: use cases for variables, how to use them, and an interview question on finding the second-highest salary
Explanation of ranking employees by salary, including handling ties
Demonstration of using the WHERE clause to filter results based on variables
Step-by-step guide to declaring a variable directly in SQL
Example of assigning a value to a variable using the SET statement
How to declare and use a variable within a SELECT query
Introduction to the interview question: finding all employees with the second-highest salary
Logic behind using variables to determine salary ranks
Initial setup of variables 'Rank' and 'Previous Salary' with null and zero values
Incrementing the rank based on the difference between previous and current salary
Implementation of logic in SQL to assign ranks to employees
Using variables to print employee details with their salary ranks
Fixing an issue with NULL values in the variable assignment
Final SQL query to display employee names, departments, salaries, and ranks
Filtering to find only employees with the second-highest salary rank
Conclusion and summary of the video's learnings
Transcripts
hello everyone my name is bhesh welcome
back to my Channel Master in code I hope
you are doing well today we are going to
learn variables in SQL so content of
this videos will be where do we use this
variables then second how can we use it
and at last we will cover one interview
question that is finding out the second
highest salary now this time we will not
find out only the second highest salary
but we will find out all those employees
who has second highest salary
and where can we use this now let me
show you one example
okay so as you can see here I have one
table Sam has the highest salary then
Alex has the second highest salary but
if you see Mike also has the second
highest salary so his rank also should
be two right then Lisa's rank should be
three then Bob's rank should be four and
if you see Jacob also has same salary as
Bob so his rank also should be four okay
and for Mary it should be five now once
we get this ranking then we can use the
wear Clause to print out the Y salary
okay along with the employee details so
that's something new when we are using
the variables okay now let's see how we
can use the variables in SQL let's go to
the terminal so this is my MySQL
terminal
I have installed in the docker desktop
so let's login in
that and
password
okay show databases will show me the all
databases then we will say use
study so now I in the study databas if I
say show tables it will show me list of
all the tables right
now how to declare the variable okay
first method is directly you use set the
variable name War one equal to some
value consider
Master right now if I want to print I
will just simply say
select
forward okay and when I was discussing
this to find out the ranks same ranks
this will be generated using the select
query so how do we use in select query
let's check that so to declare a direct
new variable in select query we can
simply say select War
to colon equal to 3 okay so here colon
is used for declaring the variable and
equal to is used for assigning the value
right
now you can see it has print the three
okay
now so now I hope you understood how to
declare the variables how to use it
right
now now we will move to our interview
question that is finding out the second
or highest salary using the variables
right so let's discuss the logic first
right so here
now what I will do I will have two
variables one is called Rank and second
will be the previous salary here
initially I will say previous salary
equal to
null and rank equal to zero so what my
logic will be if previous salary and
current salary are different then
increase my rank by one okay now for
this rank will be the one and assign
current salary to the previous salary
variable right now here what will
happen is this previous salary equal to
this salary no then increase the rank
okay by one so that becomes two here and
it becomes one here and we will assign
current salary to the previous salary
right now
previous salary and current salary are
same so what happens increase the rank
by zero or don't increase what will
happen rank is two okay right so Alex
and Mike has second highest salary
that's true and salary equal to previous
highest salary and so on so this logic
we will try to implement in our SQL
right now
uh let's try to implement our
logic what we want to print
name Department
salary
and order by
salary descending right let's go yeah
now let's declare those both variables
how we
declared select add direct War equal to
3 Let's copy
for Simplicity and then our another
variable so we don't have to put select
again we'll remove it first variable
will be
rank another variable will be previous
salary
right and let's try to print here Rank
and previous
salary alas every der table Yeah so this
is our der table and it should be having
the alas so we'll say
Wars
okay right so what we decided uh initial
values for rank we'll say zero and for
previous salary we will say null okay
zero and null right and then we
said every time for each row we will
change
salary previous salary to current salary
okay and we'll say here alas as previous
sell
okay now it is saying
null maybe this was the issue yeah this
was the issue excellent now let's work
on the rank okay so rank
as rank one and we'll say rank equal to
rank +
1
right so it also need the
colon so whenever we are updating the
value they are asking for to use colon
right so now it is increasing by plus
one but what was our logic
increase by zero if previous salary this
one previous salary and current salary
are same so how can we do
that let's instead of one we will say
if previous
salary equal
to current salary then increase by zero
else increase by 1
okay something is wrong
here yeah I missed one closing
bracket yes now for Sam rank is one for
Alex rank is two for my rank is
two and if you want to see employee who
has second highest salary only in that
case just do
select star from
add one nested query uh on the top of
this and
where rank
one equal to two right yeah so yes you
got the second highest salary yeah so
this is how we can use the variables to
calculate the rank in the table so yes
uh that's all about this video this was
my third and last video of calculating
the second highest salary I hope you
have learned something new today thank
you
Weitere ähnliche Videos ansehen
5.0 / 5 (0 votes)