Assignment1.sql

USE JOB_PORTAL_DB;
GO

--1. Identify the users who try to logon before 2017 but never try to logon during 2017. Eliminate duplicate lines from your output.
--Output Colums: User Login, User Name, User Phone
--Order by: User Login

SELECT DISTINCT SL.Login AS  [User Login], SL.Full_Name AS  [User Name], SL.Phone_Number AS  [User Phone] FROM [JOB_PORTAL_DB].[dbo].[Security_Logins] AS  SL 
JOIN [JOB_PORTAL_DB].[dbo].[Security_Logins_Log] AS SLL ON SL.Id = SLL.Login WHERE SLL.Logon_Date < '2017-01-01' 
AND SL.Id NOT IN (SELECT Login FROM [dbo].[Security_Logins_Log] WHERE Logon_Date BETWEEN '2017-01-01'and '2017-12-31')
ORDER BY 'User Login';

--2. Identify the companies where applicants applied for the job 10 or more times. Eliminate duplicate lines from your output.
--Output Colums : Company Name (English only)
--Order by: Company Name

SELECT DISTINCT CD.Company_Name FROM [JOB_PORTAL_DB].[dbo].[Applicant_Job_Applications]  AS  AJA 
JOIN [JOB_PORTAL_DB].[dbo].[Company_Jobs] AS CJ ON CJ.Id = AJA.Job 
JOIN [JOB_PORTAL_DB].[dbo].[Company_Descriptions] AS CD ON CJ.Company = CD.Company
WHERE CD.LanguageId = 'EN' 
GROUP BY CD.Company_Name Having Count(*) >=10  ORDER BY CD.Company_Name ;

--3. Identify the Applicants with highest current salary for each Currency. 
--Output Colums : Applicant Name, Current Salary, Currency

SELECT SL.Full_Name AS [Applicant Name], AP.Current_Salary AS [Current Salary], AP.Currency FROM [JOB_PORTAL_DB].[dbo].[Applicant_Profiles]  AS  AP
JOIN [JOB_PORTAL_DB].[dbo].[Security_Logins] AS SL ON AP.Login = SL.Id
WHERE AP.Current_Salary IN ( SELECT MAX(Current_Salary) FROM  [JOB_PORTAL_DB].[dbo].[Applicant_Profiles] WHERE Currency = AP.Currency GROUP BY Currency)
ORDER BY AP.Currency;

--4. For each company, determine the number of jobs posted. If a company doesn't have posted jobs, show 0 for that company. 
--Output Colums : Company Name (English Only), #Jobs Posted (show 0 if none)
--Order by: #Jobs Posted

SELECT CD.Company_Name AS [Company Name], COUNT(CJ.Company) AS [#Jobs Posted] FROM [JOB_PORTAL_DB].[dbo].[Company_Profiles] AS CP
JOIN [JOB_PORTAL_DB].[dbo].[Company_Descriptions] CD ON CP.Id = CD.Company AND CD.LanguageID = 'EN'
LEFT JOIN [JOB_PORTAL_DB].[dbo].[Company_Jobs] CJ ON CP.Id = CJ.Company
GROUP BY CD.Company_Name  
ORDER BY [#Jobs Posted];

--5. Determine the total number of companies that have posted jobs and the total number of companies that have never posted jobs in one data set with 2 rows like the one below:
-- Clients with Posted Jobs:
-- Clients without Posted Jobs:


SELECT 'Clients with Posted Jobs:' AS [Title], COUNT(DISTINCT CP.Id) AS [NNN] FROM [JOB_PORTAL_DB].[dbo].[Company_Profiles] AS CP
JOIN [JOB_PORTAL_DB].[dbo].[Company_Jobs] AS CJ ON CP.Id = CJ.Company
UNION
SELECT 'Clients without Posted Jobs:' AS [Title], COUNT(DISTINCT CP.Id) AS [NNN] FROM [JOB_PORTAL_DB].[dbo].[Company_Profiles] AS CP
LEFT JOIN [JOB_PORTAL_DB].[dbo].[Company_Jobs] AS CJ ON CP.Id = CJ.Company

WHERE CJ.Company IS NULL;