Introduction
This is an interview question. It seems very simple, but there are some tricks to it. Analyzing it can review some important concepts of SQL Query.
Question
Given a table with four columns,
- Employees - table with columns,
Request
Get the Manager's Name, the employee amount per manager, and the total salaries for each team.
A. Set up Table and initial Data
Set up the Table by the question,
USE [TestDB]
GO
/****** Object: Table [dbo].[Employees] Script Date: 5/28/2021 7:42:51 PM ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]
.
[Employees](
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Salary] [int] NOT NULL,
[ManagerId] [int] NOT NULL,
CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED (
[Id] ASC )WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Insert initial Data
insert dbo.Employees
Values ('Greg', 100000, 1),
('George', 150000, 1),
('Helen', 130000, 1),
('Tom', 120000, 2),
('Kevin', 110000, 2),
('David', 120000, 3),
('Geek', 110000,3),
('Tesla', 120000,3)
The result will be,
B. Self JOIN in SQL
This is a self-JOIN issue. A self-join is a regular join, but the table is joined with itself. We review the JOIN types below; find a detailed article about Joins in SQL here: Joins in SQL
Different Types of SQL JOINs
Here are the different types of JOINs in SQL,
- (INNER)JOIN. Returns records that have matching values in both tables
- LEFT(OUTER)JOIN. Returns all records from the left table and the matched records from the right table
- RIGHT(OUTER)JOIN. Returns all records from the right table, and the matched records from the left table
- FULL(OUTER)JOIN. Returns all records when there is a match in the either the left or right table
We make a self-JOIN,
We got the result,
Where the Manager's Name is what we need, the employee ID and Salary were SQL SELECT from the employee table (left) which we need to count and sum.
Cross Join
It returns the Cartesian product of rows from tables in the join. In other words, it will produce rows combining each row from the first table with each from the second table.
Example. I have two tables, Students and employees. The Cross Join result will be 45(5*9)
C. Group by in SQL
To get count and sum, we need to use Group by Clause,
The SQL GROUP BY Statement
The GROUP BY statement groups rows with the same values into summary rows, like "find the number of customers in each country."
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result set by one or more columns.
SELECT m.Name [Manager Name], count(m.Id) [team amount], sum(e.salary) [Total Salary]
FROM dbo.Employees e JOIN dbo.Employees m
ON e.ManagerId = m.Id
group by m.name
We groupby m.name and use aggregate functions Count() and Sum().
Note
When we select m.name, we have to group by m.nam; otherwise, suppose we group by m.id (that is correct),
SELECT m.Name [Manager Name], count(m.Id) [team amount], sum(e.salary) [Total Salary]
FROM dbo.Employees e JOIN dbo.Employees m
ON e.ManagerId = m.Id
group by m.id
Then we got an error message for the selected m.name,
Msg 8120, Level 16, State 1, Line 1,
Column 'dbo.Employees.Name' is invalid in the select list because it is not contained in an aggregate function or the GROUP BY clause.
Summary
This article discussed the major features of the SQL query - JOI and Group By.
Reference