SQL Statement containing the errors -
Select Emp.FullName , Dep.DeptName ,
Salary / 12 as MonthlySal,
Replace(BaseRate,'$','') + Replace(PremiumRate,'$','') as TotalRate
Case WHEN Salary > 50000 Then 1
WHEN Salary > 50000 AND HireDate > '2026-01-01' Then 2
Else 9 End as SalFlag,
FROM Employees Emp
LEFT JOIN Dept Dep
ON Emp.DeptID = Dep.DeptID
WHERE Emp.DeptID in (1,2,3) AND ManagerID <> 3
Database Scripts -
USE [Training]
GO
/****** Object: Table [dbo].[Dept] Script Date: 2024-09-24 12:58:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dept](
[DeptID] [int] NULL,
[DeptName] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Employees] Script Date: 2024-09-24 12:58:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] NOT NULL,
[FullName] [nvarchar](250) NOT NULL,
[DeptID] [int] NULL,
[Salary] [int] NULL,
[HireDate] [date] NULL,
[ManagerID] [int] NULL,
[BaseRate] [varchar](10) NULL,
[PremiumRate] [varchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (1, 'HR')
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (2, 'Sales')
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (3, 'Marketing')
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (4, 'Finance')
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (5, NULL)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (1, 'Owens, Kristy', 1, 35000, CAST('2018-01-22' AS Date), 3, '$45', '$10')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (2, 'Adams, Jennifer', 1, 55000, CAST('2017-10-25' AS Date), 5, '$45', '$0')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (3, 'Smith, Brad', 1, 110000, CAST('2015-02-02' AS Date), 7, '$40', '$20')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (4, 'Ford, Julia', 4, 75000, CAST('2019-08-30' AS Date), 5, '$50', '$30')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (5, 'Lee, Tom', 2, 110000, CAST('2018-10-11' AS Date), 7, '$30', '$0')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (6, 'Jones, David', 3, 85000, CAST('2012-03-15' AS Date), 5, '$30', '$5')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (7, 'Miller, Bruce', 1, 100000, CAST('2014-11-08' AS Date), NULL, '$80', '$40')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (9, 'Peters, Joe', 3, 11000, CAST('2020-03-09' AS Date), 5, '$20', '$0')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (10, 'Joe, Ala', 4, 11500, CAST('2020-03-09' AS Date), 5, '$30', '$0')
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID], [BaseRate], [PremiumRate]) VALUES (11, 'Clark, Kelly', 2, 11500, CAST('2020-03-09' AS Date), 5, '$40', '$10')
GO