One of my favorite interview questions that tips even seasoned SQL guys (maybe because it's too simple) is around querying data that involves a self join.
What is a self join?
A self join is a join in which a table is joined with itself, specially when the table has a foreign key which references its own primary key.
Question: Given an Employee table which has 3 fields - Id (Primary key), Salary and Manager Id, where manager id is the id of the employee that manages the current employee, find all employees that make more than their manager in terms of salary. Bonus: Write the table creation script.
Let's start by creating our table:
CREATE TABLE Employee (
[Id] [int] NOT NULL,
[ManagerId] [int] NOT NULL,
[Salary] [money] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
This will create our table with Id as primary key. Now let's modify this table to add a foreign key betweek ManagerId and Id.
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ManagerId])
REFERENCES [dbo].[Employee] ([Id])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employee]
GO
Now we come to the meat of the question. How do you find all employees that make more than their manager. There are 2 ways to do this - one, use a self join; and two; use a sub-query.
Self-join solution:
select e.*, m.Salary as "Manager Salary"
from Employee e
join Employee m on e.ManagerId = m.Id
where e.Salary > m.Salary
Here you are "joining" Employee table to itself on the FK relation of ManagerId and then querying on salary.
Sub-query solution:
select *
from Employee e
where e.Salary >
(select m.Salary from Employee m
where e.ManagerId = m.Id)
This is most common solution I get from candidates. Which is fine, but knowing alternate approaches to finding a solution is always very helpful.
Happy coding till next time!
In the table creation script, I wouldn't do it like this:
ReplyDelete[ManagerId] [int] NOT NULL
When you get to the top manager, they don't have a manager themselves so you need to let the field be null for that one person at least.
Both options are fine. Since the top guy will never make the select clause (his salary cannot be more than his salary) either of the assumptions work. But yes, from an interview perspective, I like it if the candidate can think of such questions.
ReplyDeletehow can i find the managers of managers?
ReplyDeleteYou will need to use WITH RECURSIVE
ReplyDeletehow to know one employee working more than one department in sql ?
ReplyDeletewow...nice blog, very helpful information. Thanks for sharing.
ReplyDeleteNO.1 API DEVELOPMENT SERVICES | MASSIL TECHNOLOGIES
Hello There,
ReplyDeleteHip Hip Hooray! I was always told that slightly slow in the head, a slow learner. Not anymore! It’s like you have my back. I can’t tell you how much I’ve learnt here and how easily! Thank you for blessing me with this effortlessly ingestible digestible content.
I don't often ask questions here, but this one (especially being a regex thing) has me a bit stumped.
I'm trying to piece together a regex that allows characters, digits and a couple of other characters.
Essentially:
^[a-zA-Z0-9:,';\/]+$
Also it can't start or end with a slash...so the above becomes:
^[^\/][a-zA-Z0-9:,';\/]+[^\/]$
So this gives a match:
abcD,xy/z
and this doesn't
/abcD,xy/z
Anyways great write up, your efforts are much appreciated.
Thanks & Regards,
Morgan
Hello,
ReplyDeleteI love all the posts, I really enjoyed.
I would like more information about this, because it is very nice., Thanks for sharing.
i reviewed few articles on improving group by performance by adding indexes for columns in group by clause for single table.
Please advise how to improve performance in following scenario where i have to join multiple tables.
Should i add the group by columns to the clustered index or create a new nonclustered index on all the group by columns for each table
table?
I am so grateful for your blog. Really looking
forward to read more.
Thanks and Regards
Hi Mate,
ReplyDeleteGasping at your brilliance! Thanks a tonne for sharing all that content. Can’t stop reading. Honestly!
I'm trying to do modest HTML formatting on a PDF Grid. But I have a problem. My code works ok on my dev machine, but when I upload it to Azure, it throws exception "A generic error occurred in GDI+." ASP .Net
By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
Please keep providing such valuable information.
Thank you,
Radhey
Excellent informative blog, keep for sharing.
ReplyDeleteBest System Integration services | Massil Technologies
SELECT a.employee, a.salary
ReplyDeleteFROM employee AS a, employee AS b
JOIN b ON a.Id = b.Id
WHERE a.salary>(SELECT salary FROM employee WHERE manager_Id = <>);
You create sense out of the foremost complex topics.
ReplyDeleteux firm
Great! We will be connecting to this enormous post on our site. Continue the good writing.
ReplyDeleteweb site designs
Plumbing & HVAC Services San Diego
ReplyDeleteAir Star Heating guarantees reliability and quality for all equipment and services
Air Star Heating is specializing in providing top-quality heating, ventilating, air conditioning, and plumbing services to our customers and clients.
Our company is leading the market right now. By using our seamless and huge array of services. Our customers can now have the privilege of taking benefit from our services very easily and swiftly. To cope up with the desires and needs of our clients we have built an excellent reputation. We are already having a huge list of satisfied customers that seem to be very pleased with our services.
Plumbing & HVAC Services in San Diego. Call now (858) 900-9977 ✓Licensed & Insured ✓Certified Experts ✓Same Day Appointment ✓Original Parts Only ✓Warranty On Every Job.
Visit:- https://airstarheating.com
They’re an accountable team that’s easy to work with, and they can be trusted to keep projects on track.
ReplyDeletebest website designers
Hmm!! This blog is really cool, I’m so lucky that I have reached here and got this awesome information.
ReplyDeleteUX firms