use master
go
Create database SQLChallenge
go
use SQLChallenge
CREATE TABLE Company
(
CompanyID int identity(100,1) primary key NOT NULL,
CompanyName varchar(30) NOT NULL,
CompanyAddress varchar(60) NOT NULL,
CompanyDomainName varchar(120) Null,
)
CREATE TABLE CompanyBranchinfo
(
ID uniqueidentifier primary key NOT NULL
DEFAULT newid(),
BranchId_No nvarchar(10) NOT NULL ,
BranchName nvarchar(130) NOT NULL,
CountryName varchar(30) NOT NULL,
ZipCode int not null,
ContactNo bigint null
)
CREATE TABLE CompanyEmpMappingTable
(
CompID int not null,
EmpID uniqueidentifier NULL
)
CREATE TABLE CompanyMappingTable
(
CID uniqueidentifier primary key NOT NULL
DEFAULT newid(),
CompanyID int not null ,
BranchID uniqueidentifier Not Null,
PartnerID uniqueidentifier Null,
)
CREATE TABLE Empinfo
(
ID uniqueidentifier primary key NOT NULL
DEFAULT newid(),
EmpID_No nvarchar(10) NOT NULL ,
Empname varchar(30) NOT NULL,
EmpDesignation varchar(60) NOT NULL,
EmpAge int Not Null,
EmpEmail varchar(200) Not Null,
EmpSex varchar(10) Not Null,
EmpSalary decimal(18,2) null,
EmpJoiningDate Date Not Null,
EmpReleavingDate Date Null,
Experience int null,
CreatedDate DateTime not null,
CreatedById int not null,
)
CREATE TABLE EmpStatusMappingTable(
EId int identity (1000,1)primary Key not null,
EmpID uniqueidentifier NOT NULL,
TeamLeadId uniqueidentifier NULL,
TaskId uniqueidentifier NULL
)
CREATE TABLE EmpWorkStatus
(
TID uniqueidentifier primary key NOT NULL
DEFAULT newid(),
TaskIDNo nvarchar(10) NOT NULL ,
TaskName nvarchar(400) Not Null,
TaskDescription nvarchar(600) Not Null,
TaskCreatedDateand DateTime NOT NULL,
TaskInProgress nvarchar(40) Not Null,
Taskcompleted nvarchar(40) Not Null,
TStatus nvarchar(13) Not Null,
Startdate Date Null,
EndDate Date Null,
DueDate Date Null,
)
CREATE TABLE partnerIdinfo
(
Pid uniqueidentifier primary key NOT NULL
DEFAULT newid(),
PartnerID nvarchar(10) NOT NULL,
partnername varchar(50) null,
partnerlocation varchar(50) null,
SponseredAmount decimal(18,2) null
)
CREATE TABLE TeamLeadinfo
(
ID uniqueidentifier primary key NOT NULL
DEFAULT newid(),
TeamLeadId nvarchar(10) NOT NULL ,
ManagerId uniqueidentifier NULL,
)
CREATE TABLE ManagerIdinfo
(
id uniqueidentifier primary key NOT NULL
DEFAULT newid(),
ManagerId nvarchar(10) NOT NULL ,
ownerID nvarchar(10) NOT NULL,
)
CREATE TABLE OwnerIdinfo
(
ownerID nvarchar(10) primary key NOT NULL,
ownername varchar(50) null
)
-- create a foriegn key Constraints
Alter table CompanyMappingTable add constraint tblCompanyMappingTable_CompanyID_tblCompany_CompanyID
Foreign key (CompanyID) references Company(CompanyID)
Alter table CompanyMappingTable add constraint tblCompanyMappingTable_BranchID_tblCompanyBranchinfo_ID
Foreign key (BranchID) references CompanyBranchinfo(ID)
Alter table CompanyMappingTable add constraint tblCompanyMappingTable_PartnerID_tblpartnerIdinfo_Pid
Foreign key (PartnerID) references partnerIdinfo(Pid)
Alter table CompanyEmpMappingTable add constraint tblCompanyEmpMappingTable_CompID_tblCompany_CompanyID
Foreign key (CompID) references Company(CompanyID)
Alter table CompanyEmpMappingTable add constraint tblCompanyEmpMappingTable_EmpID_tblEmpinfo_ID
Foreign key (EmpID) references Empinfo(ID)
Alter table EmpStatusMappingTable add constraint tblEmpStatusMappingTable_EmpID_tblEmpinfo_ID
Foreign key (EmpID) references Empinfo(ID)
Alter table EmpStatusMappingTable add constraint tblEmpStatusMappingTable_TeamLeadId_tblTeamLeadinfo_ID
Foreign key (TeamLeadId) references TeamLeadinfo(ID)
Alter table EmpStatusMappingTable add constraint tblEmpStatusMappingTable_TaskId_tEmpWorkStatus_Tid
Foreign key (TaskId) references EmpWorkStatus( Tid)
Alter table TeamLeadinfo add constraint tblTeamleadinfo_ManagerId_tblManagerIdinfo_id
Foreign key (ManagerId) references ManagerIdinfo(id)
Alter table ManagerIdinfo add constraint MangerIdinfo_ownerId_OwnerIdinfo_ownerID
Foreign key (ownerID) references OwnerIdinfo(ownerID)
SET IDENTITY_INSERT [dbo].[Company] ON
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (100, N'Syncfusion', N'North Street Mumbai', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (101, N'Syncfusion-Can', N'Syncfusion-canada 2891', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (102, N'Syncfusion-CH1', N'Syncfusion-Chennai', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (103, N'Syncfusion-TR1', N'Sync-Torono', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (104, N'Syncfusion-Cal', N'Syncfusion-Calgary', N'www.Syncfusion.ca')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (105, N'Syncfusion-Development ', N'Syncfusion-Development OMR chennai', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (106, N'Syncfusion-Development-BL ', N'Syncfusion-Bangalore ', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (107, N'Syncfusion-Development-MB ', N'Syncfusion-Mumbai ', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (108, N'Syncfusion-Ottawo ', N'Syncfusion-Ottawo ', N'www.Syncfusion.ca')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (109, N'Syncfusion-BLphase-1 ', N'Syncfusion-Bangalore Electronic City XpComplex', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (110, N'Syncfusion-Anna ', N'Syncfusion-Chennai Anna nagarx', N'www.Syncfusion.com')
INSERT [dbo].[Company] ([CompanyID], [CompanyName], [CompanyAddress], [CompanyDomainName]) VALUES (111, N'Syncfusion-CnPhase-2 ', N'Syncfusion-Chennai Annanagar XpComplex', N'www.Syncfusion.com')
SET IDENTITY_INSERT [dbo].[Company] OFF
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'00c63f79-0c35-4619-9c9a-132ddf1674de', N'Sy08', N'Syncfusion-canada 2891 Sunridge Way NE, Calgary, AB T1Y 7K7', N'Canada', 2891, 18888999720)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'c7b51712-a93b-4600-9242-238c44b11416', N'Sy02', N'Syncfusion-Chennai Anna nagar', N'India', 600040, 9000400090)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'98e88c35-7253-409b-95e2-38fc8c65c2c5', N'Sy07', N'Torono New Down Cambling Street', N'Canada', 2432, 403283389)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'50016229-0d74-4f87-851c-42307c78927f', N'Sy09', N'Syncfusion-Calgary DX 403-268-CITY', N'Canada', 2332, 4025955)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'eff348d6-5ae8-41ff-8ae6-6f40708c056e', N'Sy03', N'Syncfusion-Development OMR chennai', N'India', 610032, 9000400090)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'02763bb3-9b54-4bf2-9f83-7585447815a9', N'Sy04', N'Syncfusion-Bangalore Golden park', N'India', 62891, -11506)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'bff475d9-ad0f-4919-af89-7bfeefa53112', N'Sy06', N'Syncfusion-Mumbai ', N'India', 610032, 9000400271)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'da311901-e222-40fb-b62f-9193bd2884c7', N'Sy10', N'Syncfusion-Ottawo, Orleans, ON K4A 3T7', N'Canada', 2132, 847)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'bf3796c0-f02b-473f-8681-b28a76ff4adf', N'Sy05', N'Syncfusion-Bangalore Electronic City XpComplex', N'India', 765321, 9000400270)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'3580990a-76ce-44ed-aec5-dedf26a0cc14', N'Sy02', N'Syncfusion-Chennai Anna nagar', N'India', 600040, 9000400090)
INSERT [dbo].[CompanyBranchinfo] ([ID], [BranchId_No], [BranchName], [CountryName], [ZipCode], [ContactNo]) VALUES (N'158c5328-6010-452c-a302-e4377a96ff12', N'Sy01', N'Syncfusion-Chennai Annanagar XpComplex', N'India', 600039, 9000400080)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'c73d38c0-f58f-486a-a90e-201cd9b85baa', N'SyncD1908', N'Venkatesh', N'Software Developer-Level5 ', 26, N'Venkatesh@Syncfusion.com', N'Male', CAST(90000.00 AS Decimal(18, 2)), CAST(0x17340B00 AS Date), CAST(0x5B950A00 AS Date), 4, CAST(0x0000A19700000000 AS DateTime), 1006)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'fc9a6030-ed8f-467f-8b12-218816c661ce', N'SyncTX1003', N'Martin Kish', N'Ux Manager ', 28, N'kishmark@Syncfusion.com', N'Male', CAST(360000.00 AS Decimal(18, 2)), CAST(0x64310B00 AS Date), CAST(0x5B950A00 AS Date), 5, CAST(0x00009C0900000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'58807fc4-73e5-4740-b26b-286a3d0bb263', N'SyncTE1101', N'Andrew Mathew', N'Software Testing ', 27, N'Andrew Mathew@Syncfusion.com', N'Male', CAST(80000.00 AS Decimal(18, 2)), CAST(0x81350B00 AS Date), CAST(0x5B950A00 AS Date), 6, CAST(0x0000A19700000000 AS DateTime), 1007)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'6b371beb-124f-4edc-a674-3bb9dbb773ad', N'SyncD1906', N'NeelaKandan', N'Software Developer ', 24, N'Neelakandan@Syncfusion.com', N'Male', CAST(50000.00 AS Decimal(18, 2)), CAST(0xF2360B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1009)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'13d167da-7f54-4c4a-9db9-53748618869f', N'SyncMx109', N'Steve Jobs', N'Product Manager ', 31, N'jobs.steve@Syncfusion.com', N'Male', CAST(360000.00 AS Decimal(18, 2)), CAST(0x71270B00 AS Date), CAST(0x5B950A00 AS Date), 15, CAST(0x0000921600000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'3d5ca139-d707-42a7-9558-7c024bd5b548', N'SyncUX1907', N'Savitha v', N'Ux-Designer ', 23, N'Savitha@Syncfusion.com', N'Female', CAST(40000.00 AS Decimal(18, 2)), CAST(0x81350B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1007)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'b82698d7-4d6d-4e48-8f81-7c7c2b6db983', N'SyncTX1711', N'Grahami', N' Senior web Developer', 29, N'Graham@Syncfusion.com', N'female', CAST(920000.00 AS Decimal(18, 2)), CAST(0xAF2B0B00 AS Date), CAST(0x5B950A00 AS Date), 11, CAST(0x0000965400000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'633b43f4-fbdd-4521-929e-7f5e6cdef341', N'SyncUX1811', N'Sonia', N'Ux-designer-Level3 ', 24, N'Sonia@Syncfusion.com', N'Female', CAST(50000.00 AS Decimal(18, 2)), CAST(0xFF350B00 AS Date), CAST(0x5B950A00 AS Date), 3, CAST(0x0000A0A400000000 AS DateTime), 1009)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'd99f3d4f-19fd-4ae0-a906-842d0002f4b0', N'SyncUX1907', N'jhonsey', N'Ux-Designer ', 25, N'jhonsy@Syncfusion.com', N'Female', CAST(80000.00 AS Decimal(18, 2)), CAST(0xEC2E0B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1005)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'047c8a44-d77f-4b4c-b8e8-9ce1f597df16', N'SyncTX1709', N'Ferguson N', N'Project Manager ', 31, N'ferguson.n@Syncfusion.com', N'Male', CAST(260000.00 AS Decimal(18, 2)), CAST(0x4A2A0B00 AS Date), CAST(0x5B950A00 AS Date), 11, CAST(0x000094EF00000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'26d0dba4-8ebe-4379-9f39-a5643ba1e429', N'SyncTX1909', N'Henry Linn', N'Product Manager ', 27, N'henryLinn@Syncfusion.com', N'Male', CAST(230000.00 AS Decimal(18, 2)), CAST(0x6C310B00 AS Date), CAST(0x5B950A00 AS Date), 6, CAST(0x0000A19700000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'b778ce63-8925-434e-84ba-aadbc2b752a4', N'SyncTE1103', N'henry james', N'SoftwareTesting ', 27, N'henryJames@Syncfusion.com', N'Male', CAST(130000.00 AS Decimal(18, 2)), CAST(0x5B300B00 AS Date), CAST(0x5B950A00 AS Date), 6, CAST(0x0000A19700000000 AS DateTime), 1004)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'8a07ad4a-34f8-4f7c-84c9-ab8ae4a6d0a4', N'SyncD1888', N'RameshKannan', N'Software Developer-Level3 ', 24, N'rameshKannan@Syncfusion.com', N'Male', CAST(50000.00 AS Decimal(18, 2)), CAST(0xF2360B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1009)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'f03581f6-131d-4090-93da-c243fa9fccf3', N'SyncTX1711', N'Angelina', N' Project manager', 28, N'Angelina@Syncfusion.com', N'female', CAST(320000.00 AS Decimal(18, 2)), CAST(0xD4280B00 AS Date), CAST(0x5B950A00 AS Date), 12, CAST(0x00009C0900000000 AS DateTime), 1002)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'ed7e3e7d-4b11-48d7-8b57-c2ae989ccdc3', N'SyncD1608', N'Balachander', N'Software Developer ', 24, N'Balachander@Syncfusion.com', N'Male', CAST(50000.00 AS Decimal(18, 2)), CAST(0xF2360B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1009)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'230512d4-5358-4efb-b7a0-c6c1f2a03e26', N'SyncD1911', N'SaravananGM', N'Software Developer-Level1 ', 23, N'SaravananGM@Syncfusion.com', N'Male', CAST(40000.00 AS Decimal(18, 2)), CAST(0x8D370B00 AS Date), CAST(0x5B950A00 AS Date), 1, CAST(0x0000A19700000000 AS DateTime), 1009)
INSERT [dbo].[Empinfo] ([ID], [EmpID_No], [Empname], [EmpDesignation], [EmpAge], [EmpEmail], [EmpSex], [EmpSalary], [EmpJoiningDate], [EmpReleavingDate], [Experience], [CreatedDate], [CreatedById]) VALUES (N'ee19b236-fbbb-4640-8114-f9e9dfc67fe4', N'SyncD1909', N'Mohamed Sahir', N'Software Developer ', 24, N'MohamedSahir@Syncfusion.com', N'Male', CAST(50000.00 AS Decimal(18, 2)), CAST(0xF2360B00 AS Date), CAST(0x5B950A00 AS Date), 2, CAST(0x0000A19700000000 AS DateTime), 1009)
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (100, N'c73d38c0-f58f-486a-a90e-201cd9b85baa')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (101, N'fc9a6030-ed8f-467f-8b12-218816c661ce')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (102, N'58807fc4-73e5-4740-b26b-286a3d0bb263')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (103, N'6b371beb-124f-4edc-a674-3bb9dbb773ad')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (104, N'13d167da-7f54-4c4a-9db9-53748618869f')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (105, N'13d167da-7f54-4c4a-9db9-53748618869f')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (106, N'b82698d7-4d6d-4e48-8f81-7c7c2b6db983')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (107, N'633b43f4-fbdd-4521-929e-7f5e6cdef341')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (108, N'd99f3d4f-19fd-4ae0-a906-842d0002f4b0')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (109, N'047c8a44-d77f-4b4c-b8e8-9ce1f597df16')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (110, N'26d0dba4-8ebe-4379-9f39-a5643ba1e429')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (100, N'b778ce63-8925-434e-84ba-aadbc2b752a4')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (100, N'8a07ad4a-34f8-4f7c-84c9-ab8ae4a6d0a4')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (101, N'f03581f6-131d-4090-93da-c243fa9fccf3')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (102, N'ed7e3e7d-4b11-48d7-8b57-c2ae989ccdc3')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (104, N'230512d4-5358-4efb-b7a0-c6c1f2a03e26')
INSERT [dbo].[CompanyEmpMappingTable] ([CompID], [EmpID]) VALUES (102, N'ee19b236-fbbb-4640-8114-f9e9dfc67fe4')
INSERT [dbo].[OwnerIdinfo] ([ownerID], [ownername]) VALUES (N'OW1002', N'Satya Nadella')
INSERT [dbo].[OwnerIdinfo] ([ownerID], [ownername]) VALUES (N'OW1001', N'Bill Gates')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'40de65e3-1fc1-4890-b9eb-716d20696c44', N'SyncMx107', N'OW1002')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'2a89e8af-f2db-4540-b092-9dc034a9c456', N'SyncTX1711', N'OW1001')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'333f9c29-678b-47d8-a475-b018a5b87971', N'SyncTX1003', N'OW1002')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'c1c31fdb-0639-4e88-8dbb-be31a20f4979', N'SyncTX1909', N'OW1002')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'069cecf5-f767-49b5-be32-c3f5c5e9fb23', N'SyncMx109', N'OW1001')
INSERT [dbo].[ManagerIdinfo] ([id], [ManagerId], [ownerID]) VALUES (N'1c6ffee8-ad44-45f4-8ade-dce3039fe4d9', N'SyncTX1709', N'OW1001')
INSERT [dbo].[TeamLeadinfo] ([ID], [TeamLeadId], [ManagerId]) VALUES (N'6cda45dd-e113-4c2c-93a0-4f51faaf2687', N'SyncTX1711', N'069cecf5-f767-49b5-be32-c3f5c5e9fb23')
INSERT [dbo].[TeamLeadinfo] ([ID], [TeamLeadId], [ManagerId]) VALUES (N'70011685-393a-468c-ba89-63f5c345504f', N'SyncD1908', N'40de65e3-1fc1-4890-b9eb-716d20696c44')
INSERT [dbo].[TeamLeadinfo] ([ID], [TeamLeadId], [ManagerId]) VALUES (N'fde49496-e48d-46f7-bf22-ab2a3cc5d697', N'SyncD1888', N'c1c31fdb-0639-4e88-8dbb-be31a20f4979')
INSERT [dbo].[TeamLeadinfo] ([ID], [TeamLeadId], [ManagerId]) VALUES (N'3405ef53-7ae9-4980-a358-ba7fd6f03f0c', N'SyncD1911', N'1c6ffee8-ad44-45f4-8ade-dce3039fe4d9')
INSERT [dbo].[TeamLeadinfo] ([ID], [TeamLeadId], [ManagerId]) VALUES (N'30ed0406-7cc7-4ea3-82af-bae65358577f', N'SyncUX1811', N'333f9c29-678b-47d8-a475-b018a5b87971')
INSERT [dbo].[partnerIdinfo] ([Pid], [PartnerID], [partnername], [partnerlocation], [SponseredAmount]) VALUES (N'2363778e-0232-4947-a1f8-0a761cced7e7', N'Angular^7', N'Angular', N'France', CAST(30009070.00 AS Decimal(18, 2)))
INSERT [dbo].[partnerIdinfo] ([Pid], [PartnerID], [partnername], [partnerlocation], [SponseredAmount]) VALUES (N'fdc894eb-3d05-4a36-aced-1f00d2be2e99', N'MIC!', N'Microsoft', N'US', CAST(20000000.00 AS Decimal(18, 2)))
INSERT [dbo].[partnerIdinfo] ([Pid], [PartnerID], [partnername], [partnerlocation], [SponseredAmount]) VALUES (N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'IBM@3', N'IBM', N'Canada', CAST(100009070.00 AS Decimal(18, 2)))
INSERT [dbo].[partnerIdinfo] ([Pid], [PartnerID], [partnername], [partnerlocation], [SponseredAmount]) VALUES (N'e5c18d8f-0f0f-44a6-a919-eb7f14260cc4', N'Seimens!X', N'Microsoft', N'US', CAST(1000000.00 AS Decimal(18, 2)))
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (102, N'c7b51712-a93b-4600-9242-238c44b11416', N'fdc894eb-3d05-4a36-aced-1f00d2be2e99', N'd4b7a8c6-de3a-4e3d-b750-01fce3c63bee')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (101, N'00c63f79-0c35-4619-9c9a-132ddf1674de', N'fdc894eb-3d05-4a36-aced-1f00d2be2e99', N'a765d223-bb09-4888-b526-1c53ceb0d1f9')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (108, N'da311901-e222-40fb-b62f-9193bd2884c7', N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'f49a2fd8-9c7c-4b83-bf84-38cb8266c33b')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (107, N'bff475d9-ad0f-4919-af89-7bfeefa53112', N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'a16efe71-d39e-4960-8544-4d3e2ae5d1ce')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (109, N'bf3796c0-f02b-473f-8681-b28a76ff4adf', N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'a2899d44-cd99-42c6-a6f7-774c236ca304')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (100, N'bff475d9-ad0f-4919-af89-7bfeefa53112', N'2363778e-0232-4947-a1f8-0a761cced7e7', N'10591079-42d4-4039-b490-898243d30de5')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (103, N'98e88c35-7253-409b-95e2-38fc8c65c2c5', N'e5c18d8f-0f0f-44a6-a919-eb7f14260cc4', N'661377f2-bf94-4ab0-b319-8f8f103bf9a5')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (111, N'158c5328-6010-452c-a302-e4377a96ff12', N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'0f3d6a52-0f12-4b1c-9dc8-aa44770bd817')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (101, N'00c63f79-0c35-4619-9c9a-132ddf1674de', N'fdc894eb-3d05-4a36-aced-1f00d2be2e99', N'31217a5c-0a8b-41fd-ab0e-c73495d4bbd9')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (106, N'02763bb3-9b54-4bf2-9f83-7585447815a9', N'e5c18d8f-0f0f-44a6-a919-eb7f14260cc4', N'd2fd33ad-9919-4d60-bb1c-cedeabbd7869')
INSERT [dbo].[CompanyMappingTable] ([CompanyID], [BranchID], [PartnerID], [CID]) VALUES (110, N'3580990a-76ce-44ed-aec5-dedf26a0cc14', N'bfdc5672-6ff4-4956-8246-89602a622f7e', N'8dee9a6a-d179-4793-b9b3-d90703353728')
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'1a5571f2-147a-48cf-a1be-0ed9b2654fdc', N'Task101B13', N'Analyse about code fisrt approach', N'Dynamic database creation for when user login for multitenant app', CAST(0x0000A32800A5A677 AS DateTime), N'In Progress', N'null', N'started', CAST(0xC0380B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0xD5380B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'5b5993bc-2b97-4d16-b47a-0fbdb869e924', N'Task101A02', N'Bootstrap Design for Home Page', N'Create an pop window and Design a Bootstrap Design', CAST(0x0000A49800C60F57 AS DateTime), N'null', N'null', N'yetStarted', CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'61db0d77-beee-4588-a11b-1165daca518f', N'Task101A03', N'Analyse about Multitenant Application', N'Analyse about Multitenant Application shared Schema Approach ', CAST(0x0000A49800C7F2D8 AS DateTime), N'null', N'null', N'NotStarted', CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'150e8a41-bd0f-4476-b630-2455fd7f9a52', N'Task11TX33', N'Test a syncfusion Site', N'Test a syncfuison Site', CAST(0x0000A2CF00C60F57 AS DateTime), N'In Progress', N'notdone', N'notdone', CAST(0x34380B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x5B380B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'1682f50c-d76f-47bb-bfe1-26f7705678fc', N'Task102C13', N'Analyse about angular filter for Content page', N'Analyse about angular filter for Content page', CAST(0x0000A43C00C60F57 AS DateTime), N'In Progress', N'null', N'started', CAST(0xA9390B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0xC8390B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'7e4d0597-acbf-4090-a34c-2efb50b65d24', N'Task21UX23', N'Design a lyout for login page', N'Design a lyout for login page', CAST(0x0000A3FE00C60F57 AS DateTime), N'completed', N'completed', N'started', CAST(0x5F390B00 AS Date), CAST(0x62390B00 AS Date), CAST(0x65390B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'b9d42f2f-7a28-444b-9ea6-47544a3ae9ee', N'Task101A90', N'Analyse about Multitenant Application', N'Analyse about Multitenant Application seperate Schema Approach ', CAST(0x0000A49800C80F97 AS DateTime), N'null', N'null', N'NotStarted', CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'67623db1-0a49-4445-bf91-79c495e875b7', N'Task101B03', N'Analyze angular Directives Validation', N' Analyze angular Directives Validation', CAST(0x0000A1BF0073A997 AS DateTime), N'In Progress', N'null', N'started', CAST(0x87370B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0xE2370B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'35b4c699-0f69-4c00-b3cf-7c6fc018a538', N'Task101A01', N'Create a Lucene Search functionality in search', N'Lucene Search filter for syncfusion', CAST(0x0000A49800C218DF AS DateTime), N'null', N'null', N'yetStarted', CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x5B950A00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'ee67b8dc-e17f-4410-985d-ce3739481159', N'Task101A93', N'RavenDb insertion for bulk upload', N'Analyse RavenDb insertion for bulk upload', CAST(0x00009E8700423858 AS DateTime), N'completed', N'null', N'completed', CAST(0x6C350B00 AS Date), CAST(0x8B350B00 AS Date), CAST(0xF0350B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'74c57dba-c5eb-4a7b-9a93-d5d1fe5d14f1', N'Task101A23', N'Analyse Apache Solr', N'Analyse Apache solr', CAST(0x0000A0150073A997 AS DateTime), N'inprogress', N'pending', N'pending', CAST(0x3C380B00 AS Date), CAST(0x8B350B00 AS Date), CAST(0x95350B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'6cf7fc24-8060-4d8a-8070-e27e363f4f7c', N'Task101A13', N'analyse page Analytics', N'page analytics', CAST(0x0000A2CF00423858 AS DateTime), N'started', N'null', N'pending', CAST(0x3C380B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x6F380B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'5972828d-1ea4-4e55-8018-ee47345c4de8', N'Task11UX13', N'Create a layout for Search page in ps', N'Create a layout for Search page in ps for syncfusion products ', CAST(0x00009E8700C60750 AS DateTime), N'In Progress', N'In pending', N'started', CAST(0x0F3A0B00 AS Date), CAST(0x5B950A00 AS Date), CAST(0x233A0B00 AS Date))
INSERT [dbo].[EmpWorkStatus] ([TID], [TaskIDNo], [TaskName], [TaskDescription], [TaskCreatedDateand], [TaskInProgress], [Taskcompleted], [TStatus], [Startdate], [EndDate], [DueDate]) VALUES (N'77157e73-b84a-467b-98b0-fa9f8a69f02a', N'Task101A23', N'analyse Web Analytics', N'web analytics', CAST(0x00009E8700423858 AS DateTime), N'done', N'completed', N'completed', CAST(0x3C380B00 AS Date), CAST(0x6F380B00 AS Date), CAST(0x78380B00 AS Date))
SET IDENTITY_INSERT [dbo].[EmpStatusMappingTable] ON
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1010, N'6b371beb-124f-4edc-a674-3bb9dbb773ad', N'70011685-393a-468c-ba89-63f5c345504f', N'1a5571f2-147a-48cf-a1be-0ed9b2654fdc')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1011, N'ee19b236-fbbb-4640-8114-f9e9dfc67fe4', N'70011685-393a-468c-ba89-63f5c345504f', N'5b5993bc-2b97-4d16-b47a-0fbdb869e924')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1012, N'ed7e3e7d-4b11-48d7-8b57-c2ae989ccdc3', N'70011685-393a-468c-ba89-63f5c345504f', N'67623db1-0a49-4445-bf91-79c495e875b7')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1013, N'230512d4-5358-4efb-b7a0-c6c1f2a03e26', N'70011685-393a-468c-ba89-63f5c345504f', N'61db0d77-beee-4588-a11b-1165daca518f')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1014, N'8a07ad4a-34f8-4f7c-84c9-ab8ae4a6d0a4', N'70011685-393a-468c-ba89-63f5c345504f', N'1682f50c-d76f-47bb-bfe1-26f7705678fc')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1015, N'd99f3d4f-19fd-4ae0-a906-842d0002f4b0', N'30ed0406-7cc7-4ea3-82af-bae65358577f', N'5972828d-1ea4-4e55-8018-ee47345c4de8')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1016, N'3d5ca139-d707-42a7-9558-7c024bd5b548', N'30ed0406-7cc7-4ea3-82af-bae65358577f', N'7e4d0597-acbf-4090-a34c-2efb50b65d24')
INSERT [dbo].[EmpStatusMappingTable] ([EId], [EmpID], [TeamLeadId], [TaskId]) VALUES (1017, N'633b43f4-fbdd-4521-929e-7f5e6cdef341', N'30ed0406-7cc7-4ea3-82af-bae65358577f', N'5b5993bc-2b97-4d16-b47a-0fbdb869e924')
SET IDENTITY_INSERT [dbo].[EmpStatusMappingTable] OFF
-- SubQueries with Requirements
--How to Find a Total Sponsored amount for the company and group by according to the Branch-wise
--1) Here i have find Total Sponsored Company Amount
--2) Find By Branch-wise Sponsered amount
select IsNull(CompanyBranchinfo.BranchName,'TotalCompanySponsor Amount'), Sum (SponseredAmount) as [TotalSponseredAmount],Avg (SponseredAmount) as [AverageSponseredAmount],Max (SponseredAmount) as [HighestSponseredAmount] from Company
inner join CompanyMappingTable on Company.CompanyID = CompanyMappingTable.CompanyID
inner join partnerIdinfo on partnerIdinfo.Pid = CompanyMappingTable.PartnerID
inner join CompanyBranchinfo on CompanyBranchinfo.ID =CompanyMappingTable.BranchID
Group by CompanyBranchinfo.BranchName with Rollup
order by CompanyBranchinfo.BranchName Asc
--or
select
IsNull(BranchName,'TotalCompanySponsor Amount'),
Sum (SponseredAmount) as [TotalSponseredAmount],
Avg (SponseredAmount) as [AverageSponseredAmount],
Max (SponseredAmount) as [HighestSponseredAmount]
from Company As c
inner join CompanyMappingTable as CMap on CMap.CompanyID = c.CompanyID
inner join partnerIdinfo as partneridtable on partneridtable.Pid = CMap.PartnerID
inner join CompanyBranchinfo as CompanyBranchTable on CompanyBranchTable.ID = Cmap.BranchID
Group by BranchName with Rollup
having Avg(SponseredAmount)>1000000.000000
order by BranchName Asc
--How to Find a Total Sponsored amount for the company and group by according to the Branch-wise? Total = Wildcard Branch Records result1 + Wildcard Branch Records result1 ,etc
-- --1) Here i have find Total Sponsored amount by Branchwise ie..) By using wildcard Queries,get the Branch record and Sum up those branch Sponsered Amount as total Sponsered amount
select
IsNull(BranchName,'TotalCompanySponsor Amount'),
Sum (SponseredAmount) as [TotalSponseredAmount],
Avg (SponseredAmount) as [AverageSponseredAmount],
Max (SponseredAmount) as [HighestSponseredAmount]
from Company As c
inner join CompanyMappingTable as CMap on CMap.CompanyID = c.CompanyID
inner join partnerIdinfo as partneridtable on partneridtable.Pid = CMap.PartnerID
inner join CompanyBranchinfo as CompanyBranchTable on CompanyBranchTable.ID = Cmap.BranchID
Where BranchName like '%Ban%'
Group by BranchName with Rollup
order by BranchName Asc
-- select EmpName and Empsalary details
select Empname,EmpSalary from Empinfo order by EmpSalary desc
-- Get a salary Details According to the Employeesnames
select Max (distinct EmpSalary) as TotalExpense,count(*) as totalEmployees from Empinfo
-- Top3rd Highest Salary Employees
Select TOP 1 EmpSalary as '3rd Highest Salary'
from (SELECT DISTINCT TOP 3 EmpSalary from Empinfo ORDER BY EmpSalary DESC)
a ORDER BY EmpSalary ASC
-- Find a top 5 Highest Salary Employees Details
SELECT TOP 5 EmpSalary as '1st Highest Salary', Empname FROM Empinfo ORDER BY EmpSalary desc
--Who is getting a second highest salary in a company
select top 1 Empname,EmpSalary as 'Highestsalary' from (Select Top 2 Empname,EmpSalary from Empinfo order by EmpSalary desc) As Emp order by EmpSalary
-- How much amount is getting 4th highest salary in a company
select max (EmpSalary) as '4thEmpSalary' from Empinfo where EmpSalary Not in (select top 3 EmpSalary from Empinfo order by EmpSalary desc)
-- Here i have find an employees,in which Branch,compnay he is working ,what is there designation and whose salary above 50,000
select Company.CompanyName,
Empinfo.Empname,CompanyBranchinfo.BranchName,Empinfo.EmpDesignation,EmpSalary
from Company,CompanyEmpMappingTable,Empinfo,CompanyMappingTable,CompanyBranchinfo
where Company.CompanyID = CompanyEmpMappingTable.CompID
and CompanyEmpMappingTable.EmpID = Empinfo.ID
and Company.CompanyID = CompanyMappingTable.CompanyID
and CompanyMappingTable.BranchID = CompanyBranchinfo.ID
and EmpSalary > 50000
-- Here i have find an employees,in which Branch,compnay he is working ,what is there designation and whose salary below 50000 or equalto 50,000
select Company.CompanyName,
Empinfo.Empname,CompanyBranchinfo.BranchName,Empinfo.EmpDesignation,EmpSalary
from Company,CompanyEmpMappingTable,Empinfo,CompanyMappingTable,CompanyBranchinfo
where Company.CompanyID = CompanyEmpMappingTable.CompID
and CompanyEmpMappingTable.EmpID = Empinfo.ID
and Company.CompanyID = CompanyMappingTable.CompanyID
and CompanyMappingTable.BranchID = CompanyBranchinfo.ID
and EmpSalary <= 50000
-- Here i have to find an employee in which company ,Branch he is working who is Employee Team lead and who is teamlead Manager and Who is Owner of the company
select c.CompanyName,empinfo.EmpID_No,empinfo.Empname,empinfo.EmpDesignation,CBinfo.BranchName,TLinfo.TeamLeadId,Mgrinfo.ManagerId,Owinfo.ownername
from Company as c
left join CompanyEmpMappingTable as CempMap on CempMap.CompID = c.CompanyID
left join Empinfo as empinfo on empinfo.ID= CempMap.EmpID
left Join CompanyMappingTable as CMap on CMap.CompanyID = c.CompanyID
left join CompanyBranchinfo as CBinfo on CBinfo.ID = CMap.BranchID
left join EmpStatusMappingTable as EmpStatusMap on EmpStatusMap.EmpID = empinfo.ID
left join TeamLeadinfo as TLinfo on TLinfo.ID = EmpStatusMap.TeamLeadId
left join ManagerIdinfo as Mgrinfo on Mgrinfo.id = TLinfo.ManagerId
left join OwnerIdinfo as Owinfo on Owinfo.ownerID = Mgrinfo.ownerID
-- With Total Expense of the company for paid salary
select (Select sum(EmpSalary) from Empinfo) as totalpaidEmployeeSalary, c.CompanyName,empinfo.EmpID_No,empinfo.Empname,empinfo.EmpDesignation,CBinfo.BranchName,TLinfo.TeamLeadId,Mgrinfo.ManagerId,Owinfo.ownername
from Company as c
left join CompanyEmpMappingTable as CempMap on CempMap.CompID = c.CompanyID
left join Empinfo as empinfo on empinfo.ID= CempMap.EmpID
left Join CompanyMappingTable as CMap on CMap.CompanyID = c.CompanyID
left join CompanyBranchinfo as CBinfo on CBinfo.ID = CMap.BranchID
left join EmpStatusMappingTable as EmpStatusMap on EmpStatusMap.EmpID = empinfo.ID
left join TeamLeadinfo as TLinfo on TLinfo.ID = EmpStatusMap.TeamLeadId
left join ManagerIdinfo as Mgrinfo on Mgrinfo.id = TLinfo.ManagerId
left join OwnerIdinfo as Owinfo on Owinfo.ownerID = Mgrinfo.ownerID
-- Here i have find the company those dont have Branches but it have in primary Company Table
select * from Company
where CompanyID Not in (select distinct CompanyID from CompanyMappingTable)
-- Here i have listed a task between 2013 and 2014 years
SELECT *
FROM EmpWorkStatus
WHERE TaskCreatedDateand BETWEEN '2013-01-11 00:00:07.850' AND '2014-05-11 10:03:06.850'
-- here i am getting Top 5 distinct Experience Employee and in that getting minimum experience Employee
select * from Empinfo where Experience = (select min(Experience)from Empinfo Where Experience IN (
select distinct top 5 Experience from Empinfo order by Experience desc
))
-- Employees work in the company have 2 years less or equally experience
SELECT CompanyEmpMappingTable.CompID
FROM CompanyEmpMappingTable
WHERE EmpID IN (SELECT ID
FROM Empinfo where Experience <=2
);
-- Here i have find a company have Branch in Canada and descending the order
SELECT CompanyName, CompanyID,CompanyDomainName
FROM Company
WHERE CompanyID IN
(SELECT CompanyID
FROM CompanyMappingTable
WHERE BranchID IN
(SELECT ID
FROM CompanyBranchinfo
WHERE CountryName Like'%canada%'))order by CompanyName desc
-- Here i have find the Employees Employees be Male and Experience >3
select * from company
WHERE CompanyID IN
(SELECT CompID From CompanyEmpMappingTable where EmpID In(Select ID from Empinfo where EmpSex like 'Male%' and Experience >3))
select distinct Company.CompanyName,
Empinfo.Empname,BranchName,CountryName,Empinfo.EmpDesignation,EmpSalary, TStatus,TaskName,Taskcompleted
from Company,CompanyEmpMappingTable,Empinfo,CompanyMappingTable,CompanyBranchinfo,EmpWorkStatus,EmpStatusMappingTable
where Company.CompanyID = CompanyEmpMappingTable.CompID
and CompanyEmpMappingTable.EmpID = Empinfo.ID
and Company.CompanyID = CompanyMappingTable.CompanyID
and CompanyMappingTable.BranchID = CompanyBranchinfo.ID
and EmpWorkStatus.TID = EmpStatusMappingTable.TaskId
