Sql Variables & 2nd Highest Salary Interview Question | Mysql, MariaDB & Postgres

Summary

In 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

00:00

💻 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.

05:00

🔢 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.

Keywords

💡Variables

In the context of the video, 'variables' refer to the storage locations paired with symbolic names, which are used to store values in SQL. Variables are essential for programming and data manipulation, allowing users to store intermediate results or specific values for use in queries. The video explains how to declare and use variables in SQL to calculate ranks of salaries, demonstrating the concept with a practical example where variables are used to determine the second-highest salary among employees.

💡SQL

SQL, or Structured Query Language, is a domain-specific language used in programming and designed for managing data held in a relational database management system. The video focuses on teaching viewers how to use SQL variables to perform operations such as finding the second-highest salary. SQL is central to the video's theme as it is the tool used to manipulate and retrieve data from databases.

💡Ranking

Ranking in the video refers to the process of assigning a unique rank to each row in a result set based on a specified order. The video demonstrates how to use SQL variables to rank employees by their salaries, which is a common operation in database queries when dealing with ordered data sets. The concept is illustrated through an example where employees are ranked according to their salary, and the focus is on identifying those with the second-highest salary.

💡Salary

Salary is a key term in the video as it represents the compensation paid to employees. The tutorial aims to find out the second-highest salary using SQL variables. It serves as the basis for the ranking system explained in the video, where the salaries of employees are compared to determine their rank within the company's payroll structure.

💡Docker Desktop

Docker Desktop is a development environment that allows users to build, debug, and share applications. In the video, the presenter mentions using Docker Desktop to install MySQL, which is a relational database management system. Docker Desktop is used here to provide a platform for running the SQL commands and demonstrating the concepts discussed in the tutorial.

💡MySQL

MySQL is an open-source relational database management system. The video script mentions logging into the MySQL terminal installed in Docker Desktop to demonstrate SQL queries. MySQL serves as the practical platform where the concepts of variables and ranking are applied to solve the interview question about finding the second-highest salary.

💡Interview Question

The video addresses a common interview question related to SQL, which is finding the second-highest salary among employees. This question tests the candidate's ability to manipulate data using SQL queries. The video provides a step-by-step approach to solving this problem, showcasing the practical application of SQL knowledge in a job interview scenario.

💡SELECT Query

The SELECT query is a fundamental operation in SQL used to retrieve data from a database. In the video, the presenter uses SELECT queries to declare variables and to perform operations like finding the second-highest salary. The SELECT query is integral to the video's demonstration as it is the primary method for interacting with the database and extracting the required information.

💡RANK

RANK in the video is a concept used to assign a unique sequence number to each row within a partition of a result set, based on the order by a specified column. The video explains how to calculate the rank of each employee's salary to identify those with the second-highest salary. The RANK concept is crucial for understanding how to order and partition data in SQL.

💡Nested Query

A nested query, also known as a subquery, is a query that is nested within another query. The video mentions using a nested query to filter out employees who have the second-highest salary. This technique is a common approach in SQL for performing complex data retrieval operations, and in the context of the video, it is used to solve the interview question effectively.

💡ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set in ascending or descending order. In the video, the presenter uses the ORDER BY clause to sort employees by their salaries in descending order, which is necessary for ranking and identifying the second-highest salary. This clause is a key component in the SQL queries demonstrated in the tutorial.

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