Applying SQL to a retail business: Single table queries

SELECT ALL
(ProductID)
FROM CustomerTab. CustomerOrdersTabl

This question is easy and retrieves each ProductID from the CustomerOrdersTabl desk, together with repeated values. It’s helpful as a result of a whole record of all merchandise ordered is required, together with what number of instances every product has been ordered, for subsequent evaluation or reporting.

--a)
SELECT ProductID, ProductDescription, Price
FROM  CustomerTab.CustomerProductTabl 
WHERE Price < 1;
--b)
SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price BETWEEN 2 AND 5;
--c)
SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price > 6;

a. This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise the place the fee is lower than 1.

b. This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk, filtering to incorporate solely merchandise whose price is between 2 and 5, inclusive.

c. This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters to incorporate solely merchandise with a price better than 6.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE ProductID IN (2, 567, 89);

This question retrieves the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose ProductID matches one of many specified values: 2, 567, or 89.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl
WHERE ProductDescription = 'Instantaneous Digicam' OR ProductDescription = 'Cellphone Stand';

This question is especially helpful when seeking to extract details about particular merchandise by their descriptions. The question retrieves the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose ProductDescription is both ‘Instantaneous Digicam’ or ‘Cellphone Stand’.

SELECT OrderID, CustomerID, ProductID, OrderQuantity, OrderDate
FROM CustomerTab. CustomerOrdersTabl
WHERE NOT OrderQuantity = 5;

This question retrieves the OrderID, CustomerID, ProductID, OrderQuantity, and OrderDate columns from the CustomerOrdersTabl desk. It filters the outcomes to exclude orders the place the OrderQuantity is precisely 5.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE ProductDescription LIKE '%Set%';

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose ProductDescription incorporates the phrase “Set” anyplace throughout the description.

SELECT JobTitle, EmployeeID
FROM Worker.Pay
WHERE Wage IS NULL;

This question selects the JobTitle and EmployeeID columns from the Pay desk. It filters the outcomes to incorporate solely these workers whose Wage is at the moment NULL, which means their wage info is lacking or has not been recorded.

SELECT JobTitle, EmployeeID
FROM Worker.Pay
WHERE Wage IS NOT NULL;

This question selects the JobTitle and EmployeeID columns from the Pay desk. It filters the outcomes to incorporate solely these workers whose Wage isn’t NULL, which means their wage info is accessible and has been recorded.

SELECT Price
FROM CustomerTab.CustomerProductTabl
WHERE EXISTS (SELECT Price
             FROM CustomerTab.CustomerProductTabl
WHERE Price > 6);

This question selects the Price column from the CustomerProductTabl desk. It makes use of a subquery throughout the EXISTS clause to test if there are any information in the identical desk the place the Price is larger than 6. If such information exist, the outer question will return the Price for all rows within the CustomerProductTabl desk.

SELECT *
FROM CustomerTab.CustomerProductTabl
WHERE Price > ALL (SELECT Price
            FROM CustomerTab.CustomerProductTabl
            WHERE Price < 4)

This question retrieves all columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these rows the place the Price is larger than all the prices returned by a subquery. The subquery returns the prices of merchandise which have a price lower than 4.

SELECT *
FROM CustomerTab.CustomerProductTabl
WHERE 4 < ANY (SELECT Price
              FROM CustomerTab.CustomerProductTabl);

It is a relatively broad question, the place the situation is met so long as a single product in all the desk prices greater than 4, whatever the particular price of different merchandise. It retrieves all columns from the CustomerProductTabl desk. It then makes use of a subquery to filter the outcomes and returns solely these rows the place the quantity 4 is lower than any of the prices discovered within the desk. In different phrases, the question checks if there may be no less than one product within the desk with a price better than 4. If such a product exists, it returns all rows from the desk.

SELECT EmployeeID, JobTitle, (Wage + Bonus) AS TotalIncome
FROM Worker.Pay
WHERE (Wage + Bonus) > 3000

This question selects the EmployeeID, JobTitle, and a calculated subject (Wage + Bonus) labeled as TotalIncome from the Pay desk. It filters the outcomes to incorporate solely these workers whose mixed Wage and Bonus exceed 3000.

SELECT EmployeeID, JobTitle, HourlyRate, (HourlyRate * 100) AS Threshold
FROMEmployee.Pay
WHERE (HourlyRate * 100) > 1500;

This question selects the EmployeeID, JobTitle, HourlyRate, and a calculated subject (HourlyRate * 100) labeled as Threshold from the Pay desk. It filters the outcomes to incorporate solely these workers whose calculated Threshold worth, primarily based on multiplying their hourly fee by 100, exceeds 1500.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
ORDER BY Price ASC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It then orders the outcomes by the Price column in ascending order, which means from the bottom price to the very best.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
ORDER BY Price DESC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It then orders the outcomes by the Price column in descending order, which means from the very best price to the bottom.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price < 6
ORDER BY Price ASC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose Price is lower than 6. After filtering, the outcomes are sorted by the Price column in ascending order, which means from the bottom price to the very best.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price > 3
ORDER BY Price DESC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose Price is larger than 3. After filtering, the outcomes are sorted by the Price column in descending order, which means from the very best price to the bottom.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price < 5
ORDER BY ProductDescription ASC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose Price is lower than 5. After filtering, the outcomes are sorted alphabetically by the ProductDescription column in ascending order.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
WHERE Price < 5
ORDER BY ProductDescription DESC;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It filters the outcomes to incorporate solely these merchandise whose Price is lower than 5. After filtering, the outcomes are sorted by the ProductDescription column in descending order, which means the descriptions will likely be listed from Z to A.

SELECT EmployeeID, EmployeeID, JobTitle, Wage
FROM Worker.Pay
ORDER BY JobTitle ASC, Wage DESC;

This question selects the EmployeeID, JobTitle, and Wage columns from the Pay desk. The outcomes are ordered first by the JobTitle in ascending alphabetical order after which, inside every job title group, by Wage in descending order.

SELECT EmployeePayID, JobTitle, Hiredate, Wage
FROM Worker.Pay
WHERE JobTitle IN ('Salesman', 'Shipper')
ORDER BY JobTitle, Wage;

This question selects the EmployeePayID, JobTitle, Hiredate, and Wage columns from the Pay desk. It filters the outcomes to incorporate solely these workers whose job title is both ‘Salesman’ or ‘Shipper’. The outcomes are then ordered first by JobTitle after which by Wage in ascending order.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl 
ORDER BY 2, 3;

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk throughout the CustomerTab schema. It orders the outcomes primarily based on the second and third columns of the choice record, which correspond to ProductDescription and Price, respectively.

SELECT ProductID, ProductDescription, Price
FROM CustomerTab.CustomerProductTabl

This question selects the ProductID, ProductDescription, and Price columns from the CustomerProductTabl desk. It retrieves all of the information within the desk with out making use of any filtering or sorting.

--a)
SELECT COUNT(EmployeeID) AS Worker
FROM Worker.Pay;
--b)
SELECT COUNT (DISTINCT JobTitle)
FROM Worker.Pay;
--c)
SELECT COUNT(Wage) AS TotalSalaries
FROM Worker.Pay;
--d)
SELECT COUNT (*) AS TotalRows
FROM Worker.Pay;

a. This question counts the variety of non-null EmployeeID entries within the Pay desk. The result’s labeled as Worker.

b. This question counts the variety of distinctive job titles within the Pay desk.

c. This question counts the variety of non-null Wage entries within the Pay desk. The result’s labeled as TotalSalaries.

d. This question counts the overall variety of rows within the Pay desk. The result’s labeled as TotalRows.

SELECT SUM(Wage) AS TotalSalaries
FROM Worker.Pay;

This question calculates the overall sum of all Wage values within the Pay desk. The result’s labeled as TotalSalaries.

SELECT AVG(Wage) AS TotalSalaries
FROM Worker.Pay;

This question calculates the common (imply) of all Wage values within the Pay desk. The result’s labeled as TotalSalaries.

--a)
SELECT MIN(Wage) AS MinimumSalary
FROM Worker.Pay;
--b)
SELECT MAX(Wage) AS MaximumSalary
FROM Worker.Pay;

a. This question retrieves the minimal Wage worth from the Pay desk. The result’s labeled as MinimumSalary.

b. This question retrieves the utmost Wage worth from the Pay desk. The result’s labeled as MaximumSalary.

-- Calculate whole compensation for every worker (Wage + Bonus)
SELECT EmployeeID, JobTitle, Wage, Bonus, (Wage + Bonus) AS TotalCompensation
FROM Worker.Pay;
-- Discover the common whole compensation for all workers
SELECT AVG(Wage + Bonus) AS AverageTotalCompensation
FROM Worker.Pay;

This question selects the EmployeeID, JobTitle, Wage, and Bonus columns from the Pay desk. It additionally calculates the overall compensation for every worker by summing their Wage and Bonus, labeling the consequence as TotalCompensation.

SELECT EmployeeID, Metropolis 
FROM Worker.Worker 
GROUP BY EmployeeID, Metropolis;

This question selects the EmployeeID and Metropolis columns from the Worker desk. It teams the outcomes by each EmployeeID and Metropolis, which is required for utilizing the GROUP BY clause in SQL when deciding on columns that aren’t being aggregated.

SELECT EmployeeID, SUM(Wage) AS TotalSalary
FROM Worker.Pay
GROUP BY Wage, EmployeeID;

This question selects the EmployeeID and calculates the sum of Wage values grouped by Wage and EmployeeID from the Pay desk. The results of the sum is labeled as TotalSalary.

SELECT Metropolis, COUNT(*) AS EmployeeCount
FROM Worker.Worker
GROUP BY Metropolis;

This question selects the Metropolis column from the Worker desk and counts the variety of workers in every metropolis. The rely of workers is labeled as EmployeeCount.

SELECT Metropolis, COUNT (*) AS EmployeeCount
FROM Worker.Worker
GROUP BY Metropolis
ORDER BY EmployeeCount DESC;

This question selects the Metropolis column from the Worker desk and counts the variety of workers in every metropolis. It teams the outcomes by metropolis and orders the cities by the rely of workers in descending order, so cities with the very best variety of workers seem first. The rely of workers is labeled as EmployeeCount.

SELECT Metropolis, COUNT(*) AS EmployeeCount
FROM Worker.Worker
GROUP BY CityORDER BY 1, 2;

This question selects the Metropolis column from the Worker desk and counts the variety of workers in every metropolis. It teams the outcomes by metropolis and orders the outcomes first by town identify in ascending alphabetical order after which by the worker rely in ascending order. The rely of workers is labeled as EmployeeCount.

SELECT EmployeeID, SUM (Wage + Bonus) AS TotalCompensation
FROM Worker.Pay
GROUP BY EmployeeID
HAVING SUM (Wage + Bonus) <= 3000;

This question selects the EmployeeID from the Pay desk and calculates the overall compensation for every worker by summing their Wage and Bonus. The outcomes are grouped by EmployeeID, and solely these workers whose whole compensation is lower than or equal to 3000 are included within the consequence set. The whole compensation is labeled as TotalCompensation.

SELECT Metropolis, COUNT (*) AS EmployeeCount
FROM Worker.Worker
GROUP BY Metropolis
HAVING COUNT (*) <= 3
ORDER BY EmployeeCount DESC;

This question selects the Metropolis column from the Worker desk and counts the variety of workers in every metropolis. It teams the outcomes by metropolis and filters to incorporate solely these cities which have 3 or fewer workers. The cities are then ordered by the worker rely in descending order, so cities with the very best counts (as much as 3) seem first. The rely of workers is labeled as EmployeeCount.

SELECT Metropolis, COUNT (*) AS EmployeeCount
FROM Worker.Worker
GROUP BY Metropolis
HAVING COUNT (*) <= 3
ORDER BY 2,1;

This question selects the Metropolis column from the Worker desk and counts the variety of workers in every metropolis. It teams the outcomes by metropolis and filters to incorporate solely these cities which have 3 or fewer workers. The outcomes are then ordered first by the worker rely after which by town identify. The rely of workers is labeled as EmployeeCount.

SELECT EmployeeID, CONCAT (FirstName, ' ', LastName) AS FullName
FROM Worker.Worker

This question selects the EmployeeID and creates a full identify for every worker by concatenating their FirstName and LastName with an area in between. The result’s labeled as FullName. The information is retrieved from the Worker desk throughout the Worker schema.

SELECT EmployeeID, LastName, FirstName,
TRANSLATE (Cellphone, '0123456789', 'xxxxxxxxxx') AS ProtectedPhone
FROM Worker.Worker;

This question selects the EmployeeID, LastName, FirstName, and a modified model of the Cellphone column from the Worker desk throughout the Worker schema. The TRANSLATE() operate is used to switch every digit within the telephone quantity with an ‘x’, successfully masking the telephone quantity for privateness. The masked telephone quantity is labeled as ProtectedPhone.

SELECT EmployeeID, UPPER(LastName) AS UppercaseLastName,
UPPER(FirstName) AS UppercaseFirstName,
EmployeeAddress, Metropolis, PostCode, Cellphone
FROM Worker.Worker

This question selects the EmployeeID, converts the LastName and FirstName to uppercase, and in addition retrieves EmployeeAddress, Metropolis, PostCode, and Cellphone from the Worker desk. The uppercase conversions are labeled as UppercaseLastName and UppercaseFirstName for readability.

SELECT EmployeeID, LastName, FirstName,
SUBSTRING (Cellphone, 6, 4) AS ExtractedPhoneSubstring
FROM Worker.Worker;

This question selects the EmployeeID, LastName, FirstName, and a selected substring of the Cellphone quantity from the Worker desk. The question extracts a portion of the telephone quantity beginning on the sixth character and persevering with for 4 characters. This extracted substring is labeled as ExtractedPhoneSubstring.

SELECT EmployeeID, LastName, FirstName, LTRIM(RTRIM(EmployeeAddress)) AS CleanedAddress,Metropolis,
PostCode, Cellphone
FROM Worker.Worker;

This question selects the EmployeeID, LastName, FirstName, Metropolis, PostCode, and Cellphone columns from the Worker desk. It additionally cleans up the EmployeeAddress by eradicating any main and trailing areas, utilizing the LTRIM() and RTRIM() features, and labels the cleaned deal with as CleanedAddress.

SELECT EmployeeID, LEN(LastName) AS LastNameLength,
LEN(FirstName) AS FirstNameLength
FROM Worker.Worker;

This question selects the EmployeeID from the Worker desk and calculates the size of the LastName and FirstName for every worker. The lengths of the final and first names are labeled as LastNameLength and FirstNameLength, respectively.

SELECT EmployeeID, JobTitle, HireDate,
COALESCE (HourlyRate, 0) + COALESCE (Wage, 0) + COALESCE(Bonus, 0) AS
TotalCompensation
FROM Worker.Pay;

This question selects the EmployeeID, JobTitle, and HireDate from the Pay desk. It calculates the TotalCompensation for every worker by summing the HourlyRate, Wage, and Bonus fields. The COALESCE() operate is used to switch any NULL values in these fields with 0 to make sure correct calculations.

SELECT EmployeeID, LastName, FirstName,
EmployeeAddress, Metropolis, PostCode,
RIGHT(Cellphone + REPLICATE('-', 15), 15) AS FormattedPhone
FROM Worker.Worker;

This question selects the EmployeeID, LastName, FirstName, EmployeeAddress, Metropolis, and PostCode from the Worker desk. It additionally codecs the Cellphone quantity to make sure it has a set size of 15 characters by padding with hyphens (-) if essential. The formatted telephone quantity is labeled as FormattedPhone.

SELECT ProductID, LEFT(REPLICATE('.', 30) + ProductDescription, 30) AS
FormattedProductDescription, Price
FROM CustomerTab.CustomerProductTabl;

This question selects the ProductID, a formatted model of the ProductDescription, and Price from the CustomerProductTabl desk. The ProductDescription is formatted to make sure it’s 30 characters lengthy, utilizing dots (.) for padding if essential. The formatted product description is labeled as FormattedProductDescription.

SELECT ProductID, ProductDescription,
CAST (Price AS DECIMAL (10, 2)) AS ConvertedPrice
FROM CustomerTab.CustomerProductTabl;

This question selects the ProductID and ProductDescription columns from the CustomerProductTabl desk. It converts the Price column right into a decimal quantity with two decimal locations, offering a extra exact illustration of the product value. The transformed price is labeled as ConvertedPrice.

SELECT EmployeeID, CONCAT (FirstName, ' ', LastName) AS FullName, CONCAT (SUBSTRING (Cellphone, 1, 3), '-', SUBSTRING (Cellphone, 4, 3), '-', SUBSTRING (Cellphone, 7, 4)) AS FormattedPhone
FROM Worker.Worker;

This question selects the EmployeeID from the Worker desk. It constructs a full identify for every worker by concatenating their FirstName and LastName with an area in between, and codecs the Cellphone quantity into an ordinary format (XXX-XXX-XXXX). The complete identify is labeled as FullName, and the formatted telephone quantity is labeled as FormattedPhone.

SELECT EmployeeID,
JobTitle,
HireDate,
LastDate,
DATEDIFF (YEAR, HireDate, LastDate) AS YearsOfService
FROM Worker.Pay
WHERE DATEDIFF (YEAR, HireDate, LastDate) >= 7AND
YEAR(HireDate) BETWEEN 2001 AND 2009
AND YEAR(LastDate) BETWEEN 2011 AND 2012.

This question selects the EmployeeID, JobTitle, HireDate, and LastDate from the Pay desk. It calculates the variety of years of service every worker has, utilizing the distinction between HireDate and LastDate. The calculation is labeled as YearsOfService. The question then filters the outcomes to incorporate solely these workers who’ve no less than 7 years of service and had been employed between the years 2001 and 2009, with a final working date between 2011 and 2012.

SELECT EmployeeID,
JobTitle,
HireDate,
LastDate,
Wage AS CurrentSalary,Bonus,
CASE
WHEN DATEDIFF (YEAR, HireDate, GETDATE ()) >= 2AND
YEAR(LastDate) BETWEEN 2011 AND 2012 THEN
Wage * 1.05 -- Apply 5% wage enhance.
ELSE Wage -- No wage will increase.
END AS FutureSalary
FROM Worker.Pay

This question selects the EmployeeID, JobTitle, HireDate, LastDate, Wage (labeled as CurrentSalary), and Bonus from the Pay desk. It calculates a FutureSalary primarily based on sure situations: if an worker has been employed for no less than 2 years and their final working date is between 2011 and 2012, they obtain a 5% wage enhance. In any other case, their wage stays unchanged.

SELECT EmployeeID,
JobTitle,
Wage AS CurrentSalary, Bonus AS CurrentBonus,
CASE
WHEN JobTitle="Supervisor" THEN Wage * 0.10
ELSE Wage * 0.05
END AS PerformanceBonus
FROM Worker.Pay
WHERE YEAR(LastDate) BETWEEN 2011 AND 2012

This question selects the EmployeeID, JobTitle, Wage (labeled as CurrentSalary), and Bonus (labeled as CurrentBonus) from the Pay desk. It calculates a PerformanceBonus primarily based on the worker’s job title: Managers obtain a ten% bonus of their wage, whereas different workers obtain a 5% bonus. The question filters the outcomes to incorporate solely these workers whose LastDate is between the years 2011 and 2012.

SELECT EmployeeID,
JobTitle,
Wage AS CurrentSalary,
Bonus AS CurrentBonus,
Wage *
(CASE
WHEN MONTH (GETDATE ()) = 1 AND JobTitle="Supervisor" THEN 0.10
WHEN MONTH (GETDATE ()) = 7 AND JobTitle <> 'Supervisor' THEN 0.07
ELSE 0.05
END) AS PerformanceBonus
FROM Worker.Pay

This question selects the EmployeeID, JobTitle, Wage (labeled as CurrentSalary), and Bonus (labeled as CurrentBonus) from the Pay desk. It calculates a PerformanceBonus primarily based on the present month and the worker’s job title:

• Managers obtain a ten% bonus if the present month is January.
• Non-managers obtain a 7% bonus if the present month is July.
• In all different instances, a 5% bonus is utilized.

SELECT EmployeeID,
JobTitle,
HireDate,
Wage AS OriginalSalary,
CASE
WHEN JobTitle="Salesman" AND DATEDIFF (YEAR, HireDate, GETDATE ()) > 5 THEN
Wage * 1.10
WHEN JobTitle="Shipper" AND DATEDIFF (YEAR, HireDate, GETDATE ()) > 3 THEN Wage
* 1.05
ELSE Wage
END AS AdjustedSalary
FROM Worker.Pay

This question selects the EmployeeID, JobTitle, HireDate, and Wage (labeled as OriginalSalary) from the Pay desk. It calculates an AdjustedSalary primarily based on the worker’s job title and their size of service:

• Salesmen who’ve been employed for greater than 5 years obtain a ten% wage enhance.
• Shippers who’ve been employed for greater than 3 years obtain a 5% wage enhance.
• All different workers have their salaries stay unchanged.

SELECT EmployeeID,
JobTitle,
HireDate,
HourlyRate,
LastDate,
CASE
WHEN HourlyRate IS NOT NULL AND DATEDIFF (WEEK, HireDate, GETDATE ()) > 0 THEN
(40 * HourlyRate) + ((CASE WHEN HourlyRate > 0 THEN (HourlyRate * 1.5) ELSE 0 END)
* (DATEDIFF (WEEK, HireDate, GETDATE ()) - 1) * 40)
ELSE
Wage
END AS CalculatedSalary
FROM Worker.Pay;

This question selects the EmployeeID, JobTitle, HireDate, HourlyRate, and LastDate from the Pay desk. It calculates a CalculatedSalary primarily based on hourly fee and weeks labored. If an worker is paid hourly and has been employed for a couple of week, their wage is calculated utilizing each common and additional time charges. In any other case, the common wage is used.

SELECT EmployeeID,
JobTitle,
HireDate,
DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsOfService,
CASE
WHEN DATEDIFF(YEAR, HireDate, GETDATE()) < 5 THEN 10
ELSE 15
END AS VacationDaysAccrued
FROM Worker.Pay

This question selects the EmployeeID, JobTitle, and HireDate from the Pay desk. It calculates the variety of YearsOfService for every worker primarily based on their rent date. Relying on the variety of years of service, it determines the variety of VacationDaysAccrued:

• Workers with lower than 5 years of service accrue 10 trip days.
• Workers with 5 or extra years of service accrue 15 trip days.

SELECT EmployeeID,
JobTitle,
HireDate,
DATEDIFF (YEAR, HireDate, GETDATE ()) AS YearsOfService,
CASE
WHEN DATEDIFF (YEAR, HireDate, GETDATE ()) >= 20 THEN 'Eligible for
Retirement'
ELSE 'Not Eligible for Retirement'
END AS RetirementStatus
FROM Worker.Pay

This question selects the EmployeeID, JobTitle, and HireDate from the Pay desk. It calculates the variety of YearsOfService for every worker primarily based on their rent date. Relying on the variety of years of service, it assigns a RetirementStatus:

• Workers with 20 or extra years of service are marked as ‘Eligible for Retirement’.
• Workers with lower than 20 years of service are marked as ‘Not Eligible for Retirement’.

SELECT EmployeeID,
JobTitle,
CONVERT (NVARCHAR (10), HireDate, 120) AS HireDateFormatted,
CONVERT (NVARCHAR (10), LastDate, 120) AS LastDateFormatted
FROM Worker.Pay

This question selects the EmployeeID and JobTitle from the Pay desk. It converts the HireDate and LastDate columns into a selected string format (yyyy-mm-dd) and labels them as HireDateFormatted and LastDateFormatted, respectively. The CONVERT() operate is used for this formatting.

SELECT JobTitle,
CONCAT ('Congratulations, "', JobTitle, '", in your ', DATEDIFF (YEAR,
HireDate, GETDATE ()), '-year work anniversary with our firm!') AS
AnniversaryMessage
FROM Worker.Pay

This question selects the JobTitle from the Pay desk. It generates a personalised work anniversary message for every worker by concatenating their job title and the variety of years they’ve been with the corporate. The message is labeled as AnniversaryMessage.