SQL exercises on retail shopping: Single table queries

SELECT ALL
(ProductID)
FROM CustomerTab. CustomerOrdersTabl

This query is straightforward and retrieves every ProductID from the CustomerOrdersTabl table, including repeated values. It is useful because a complete list of all products ordered is needed, including how many times each product has been ordered, for subsequent analysis or reporting.

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

a. This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products where the cost is less than 1.

b. This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table, filtering to include only products whose cost is between 2 and 5, inclusive.

c. This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters to include only products with a cost greater than 6.

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

This query retrieves the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose ProductID matches one of the specified values: 2, 567, or 89.

SELECT ProductID, ProductDescription, Cost
FROM CustomerTab.CustomerProductTabl
WHERE ProductDescription = 'Instant Camera' OR ProductDescription = 'Phone Stand';

This query is particularly useful when looking to extract information about specific products by their descriptions. The query retrieves the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose ProductDescription is either ‘Instant Camera’ or ‘Phone Stand’.

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

This query retrieves the OrderID, CustomerID, ProductID, OrderQuantity, and OrderDate columns from the CustomerOrdersTabl table. It filters the results to exclude orders where the OrderQuantity is exactly 5.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose ProductDescription contains the word “Set” anywhere within the description.

SELECT JobTitle, EmployeeID
FROM Employee.Pay
WHERE Salary IS NULL;

This query selects the JobTitle and EmployeeID columns from the Pay table. It filters the results to include only those employees whose Salary is currently NULL, meaning their salary information is missing or has not been recorded.

SELECT JobTitle, EmployeeID
FROM Employee.Pay
WHERE Salary IS NOT NULL;

This query selects the JobTitle and EmployeeID columns from the Pay table. It filters the results to include only those employees whose Salary is not NULL, meaning their salary information is available and has been recorded.

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

This query selects the Cost column from the CustomerProductTabl table. It uses a subquery within the EXISTS clause to check if there are any records in the same table where the Cost is greater than 6. If such records exist, the outer query will return the Cost for all rows in the CustomerProductTabl table.

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

This query retrieves all columns from the CustomerProductTabl table. It filters the results to include only those rows where the Cost is greater than all the costs returned by a subquery. The subquery returns the costs of products that have a cost less than 4.

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

This is a rather broad query, where the condition is met as long as a single product in the entire table costs more than 4, regardless of the specific cost of other products. It retrieves all columns from the CustomerProductTabl table. It then uses a subquery to filter the results and returns only those rows where the number 4 is less than any of the costs found in the table. In other words, the query checks if there is at least one product in the table with a cost greater than 4. If such a product exists, it returns all rows from the table.

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

This query selects the EmployeeID, JobTitle, and a calculated field (Salary + Bonus) labeled as TotalIncome from the Pay table. It filters the results to include only those employees whose combined Salary and Bonus exceed 3000.

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

This query selects the EmployeeID, JobTitle, HourlyRate, and a calculated field (HourlyRate * 100) labeled as Threshold from the Pay table. It filters the results to include only those employees whose calculated Threshold value, based on multiplying their hourly rate by 100, exceeds 1500.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It then orders the results by the Cost column in ascending order, meaning from the lowest cost to the highest.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It then orders the results by the Cost column in descending order, meaning from the highest cost to the lowest.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose Cost is less than 6. After filtering, the results are sorted by the Cost column in ascending order, meaning from the lowest cost to the highest.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose Cost is greater than 3. After filtering, the results are sorted by the Cost column in descending order, meaning from the highest cost to the lowest.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose Cost is less than 5. After filtering, the results are sorted alphabetically by the ProductDescription column in ascending order.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It filters the results to include only those products whose Cost is less than 5. After filtering, the results are sorted by the ProductDescription column in descending order, meaning the descriptions will be listed from Z to A.

SELECT EmployeeID, EmployeeID, JobTitle, Salary
FROM Employee.Pay
ORDER BY JobTitle ASC, Salary DESC;

This query selects the EmployeeID, JobTitle, and Salary columns from the Pay table. The results are ordered first by the JobTitle in ascending alphabetical order and then, within each job title group, by Salary in descending order.

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

This query selects the EmployeePayID, JobTitle, Hiredate, and Salary columns from the Pay table. It filters the results to include only those employees whose job title is either ‘Salesman’ or ‘Shipper’. The results are then ordered first by JobTitle and then by Salary in ascending order.

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

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table within the CustomerTab schema. It orders the results based on the second and third columns of the selection list, which correspond to ProductDescription and Cost, respectively.

SELECT ProductID, ProductDescription, Cost
FROM CustomerTab.CustomerProductTabl

This query selects the ProductID, ProductDescription, and Cost columns from the CustomerProductTabl table. It retrieves all the records in the table without applying any filtering or sorting.

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

a. This query counts the number of non-null EmployeeID entries in the Pay table. The result is labeled as Employee.

b. This query counts the number of unique job titles in the Pay table.

c. This query counts the number of non-null Salary entries in the Pay table. The result is labeled as TotalSalaries.

d. This query counts the total number of rows in the Pay table. The result is labeled as TotalRows.

SELECT SUM(Salary) AS TotalSalaries
FROM Employee.Pay;

This query calculates the total sum of all Salary values in the Pay table. The result is labeled as TotalSalaries.

SELECT AVG(Salary) AS TotalSalaries
FROM Employee.Pay;

This query calculates the average (mean) of all Salary values in the Pay table. The result is labeled as TotalSalaries.

--a)
SELECT MIN(Salary) AS MinimumSalary
FROM Employee.Pay;
--b)
SELECT MAX(Salary) AS MaximumSalary
FROM Employee.Pay;

a. This query retrieves the minimum Salary value from the Pay table. The result is labeled as MinimumSalary.

b. This query retrieves the maximum Salary value from the Pay table. The result is labeled as MaximumSalary.

-- Calculate total compensation for each employee (Salary + Bonus)
SELECT EmployeeID, JobTitle, Salary, Bonus, (Salary + Bonus) AS TotalCompensation
FROM Employee.Pay;
-- Find the average total compensation for all employees
SELECT AVG(Salary + Bonus) AS AverageTotalCompensation
FROM Employee.Pay;

This query selects the EmployeeID, JobTitle, Salary, and Bonus columns from the Pay table. It also calculates the total compensation for each employee by summing their Salary and Bonus, labeling the result as TotalCompensation.

SELECT EmployeeID, City 
FROM Employee.Employee 
GROUP BY EmployeeID, City;

This query selects the EmployeeID and City columns from the Employee table. It groups the results by both EmployeeID and City, which is required for using the GROUP BY clause in SQL when selecting columns that are not being aggregated.

SELECT EmployeeID, SUM(Salary) AS TotalSalary
FROM Employee.Pay
GROUP BY Salary, EmployeeID;

This query selects the EmployeeID and calculates the sum of Salary values grouped by Salary and EmployeeID from the Pay table. The result of the sum is labeled as TotalSalary.

SELECT City, COUNT(*) AS EmployeeCount
FROM Employee.Employee
GROUP BY City;

This query selects the City column from the Employee table and counts the number of employees in each city. The count of employees is labeled as EmployeeCount.

SELECT City, COUNT (*) AS EmployeeCount
FROM Employee.Employee
GROUP BY City
ORDER BY EmployeeCount DESC;

This query selects the City column from the Employee table and counts the number of employees in each city. It groups the results by city and orders the cities by the count of employees in descending order, so cities with the highest number of employees appear first. The count of employees is labeled as EmployeeCount.

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

This query selects the City column from the Employee table and counts the number of employees in each city. It groups the results by city and orders the results first by the city name in ascending alphabetical order and then by the employee count in ascending order. The count of employees is labeled as EmployeeCount.

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

This query selects the EmployeeID from the Pay table and calculates the total compensation for each employee by summing their Salary and Bonus. The results are grouped by EmployeeID, and only those employees whose total compensation is less than or equal to 3000 are included in the result set. The total compensation is labeled as TotalCompensation.

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

This query selects the City column from the Employee table and counts the number of employees in each city. It groups the results by city and filters to include only those cities that have 3 or fewer employees. The cities are then ordered by the employee count in descending order, so cities with the highest counts (up to 3) appear first. The count of employees is labeled as EmployeeCount.

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

This query selects the City column from the Employee table and counts the number of employees in each city. It groups the results by city and filters to include only those cities that have 3 or fewer employees. The results are then ordered first by the employee count and then by the city name. The count of employees is labeled as EmployeeCount.

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

This query selects the EmployeeID and creates a full name for each employee by concatenating their FirstName and LastName with a space in between. The result is labeled as FullName. The data is retrieved from the Employee table within the Employee schema.

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

This query selects the EmployeeID, LastName, FirstName, and a modified version of the Phone column from the Employee table within the Employee schema. The TRANSLATE() function is used to replace each digit in the phone number with an ‘x’, effectively masking the phone number for privacy. The masked phone number is labeled as ProtectedPhone.

SELECT EmployeeID, UPPER(LastName) AS UppercaseLastName,
UPPER(FirstName) AS UppercaseFirstName,
EmployeeAddress, City, PostCode, Phone
FROM Employee.Employee

This query selects the EmployeeID, converts the LastName and FirstName to uppercase, and also retrieves EmployeeAddress, City, PostCode, and Phone from the Employee table. The uppercase conversions are labeled as UppercaseLastName and UppercaseFirstName for clarity.

SELECT EmployeeID, LastName, FirstName,
SUBSTRING (Phone, 6, 4) AS ExtractedPhoneSubstring
FROM Employee.Employee;

This query selects the EmployeeID, LastName, FirstName, and a specific substring of the Phone number from the Employee table. The query extracts a portion of the phone number starting at the 6th character and continuing for 4 characters. This extracted substring is labeled as ExtractedPhoneSubstring.

SELECT EmployeeID, LastName, FirstName, LTRIM(RTRIM(EmployeeAddress)) AS CleanedAddress,City,
PostCode, Phone
FROM Employee.Employee;

This query selects the EmployeeID, LastName, FirstName, City, PostCode, and Phone columns from the Employee table. It also cleans up the EmployeeAddress by removing any leading and trailing spaces, using the LTRIM() and RTRIM() functions, and labels the cleaned address as CleanedAddress.

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

This query selects the EmployeeID from the Employee table and calculates the length of the LastName and FirstName for each employee. The lengths of the last and first names are labeled as LastNameLength and FirstNameLength, respectively.

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

This query selects the EmployeeID, JobTitle, and HireDate from the Pay table. It calculates the TotalCompensation for each employee by summing the HourlyRate, Salary, and Bonus fields. The COALESCE() function is used to replace any NULL values in these fields with 0 to ensure accurate calculations.

SELECT EmployeeID, LastName, FirstName,
EmployeeAddress, City, PostCode,
RIGHT(Phone + REPLICATE('-', 15), 15) AS FormattedPhone
FROM Employee.Employee;

This query selects the EmployeeID, LastName, FirstName, EmployeeAddress, City, and PostCode from the Employee table. It also formats the Phone number to ensure it has a fixed length of 15 characters by padding with hyphens (-) if necessary. The formatted phone number is labeled as FormattedPhone.

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

This query selects the ProductID, a formatted version of the ProductDescription, and Cost from the CustomerProductTabl table. The ProductDescription is formatted to ensure it is 30 characters long, using dots (.) for padding if necessary. The formatted product description is labeled as FormattedProductDescription.

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

This query selects the ProductID and ProductDescription columns from the CustomerProductTabl table. It converts the Cost column into a decimal number with two decimal places, providing a more precise representation of the product price. The converted cost is labeled as ConvertedPrice.

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

This query selects the EmployeeID from the Employee table. It constructs a full name for each employee by concatenating their FirstName and LastName with a space in between, and formats the Phone number into a standard format (XXX-XXX-XXXX). The full name is labeled as FullName, and the formatted phone number is labeled as FormattedPhone.

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

This query selects the EmployeeID, JobTitle, HireDate, and LastDate from the Pay table. It calculates the number of years of service each employee has, using the difference between HireDate and LastDate. The calculation is labeled as YearsOfService. The query then filters the results to include only those employees who have at least 7 years of service and were hired between the years 2001 and 2009, with a last working date between 2011 and 2012.

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

This query selects the EmployeeID, JobTitle, HireDate, LastDate, Salary (labeled as CurrentSalary), and Bonus from the Pay table. It calculates a FutureSalary based on certain conditions: if an employee has been employed for at least 2 years and their last working date is between 2011 and 2012, they receive a 5% salary increase. Otherwise, their salary remains unchanged.

SELECT EmployeeID,
JobTitle,
Salary AS CurrentSalary, Bonus AS CurrentBonus,
CASE
WHEN JobTitle = 'Manager' THEN Salary * 0.10
ELSE Salary * 0.05
END AS PerformanceBonus
FROM Employee.Pay
WHERE YEAR(LastDate) BETWEEN 2011 AND 2012

This query selects the EmployeeID, JobTitle, Salary (labeled as CurrentSalary), and Bonus (labeled as CurrentBonus) from the Pay table. It calculates a PerformanceBonus based on the employee’s job title: Managers receive a 10% bonus of their salary, while other employees receive a 5% bonus. The query filters the results to include only those employees whose LastDate is between the years 2011 and 2012.

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

This query selects the EmployeeID, JobTitle, Salary (labeled as CurrentSalary), and Bonus (labeled as CurrentBonus) from the Pay table. It calculates a PerformanceBonus based on the current month and the employee’s job title:

• Managers receive a 10% bonus if the current month is January.
• Non-managers receive a 7% bonus if the current month is July.
• In all other cases, a 5% bonus is applied.

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

This query selects the EmployeeID, JobTitle, HireDate, and Salary (labeled as OriginalSalary) from the Pay table. It calculates an AdjustedSalary based on the employee’s job title and their length of service:

• Salesmen who have been employed for more than 5 years receive a 10% salary increase.
• Shippers who have been employed for more than 3 years receive a 5% salary increase.
• All other employees have their salaries remain 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
Salary
END AS CalculatedSalary
FROM Employee.Pay;

This query selects the EmployeeID, JobTitle, HireDate, HourlyRate, and LastDate from the Pay table. It calculates a CalculatedSalary based on hourly rate and weeks worked. If an employee is paid hourly and has been employed for more than one week, their salary is calculated using both regular and overtime rates. Otherwise, the regular salary 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 Employee.Pay

This query selects the EmployeeID, JobTitle, and HireDate from the Pay table. It calculates the number of YearsOfService for each employee based on their hire date. Depending on the number of years of service, it determines the number of VacationDaysAccrued:

• Employees with less than 5 years of service accrue 10 vacation days.
• Employees with 5 or more years of service accrue 15 vacation 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 Employee.Pay

This query selects the EmployeeID, JobTitle, and HireDate from the Pay table. It calculates the number of YearsOfService for each employee based on their hire date. Depending on the number of years of service, it assigns a RetirementStatus:

• Employees with 20 or more years of service are marked as ‘Eligible for Retirement’.
• Employees with less 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 Employee.Pay

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

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

This query selects the JobTitle from the Pay table. It generates a personalised work anniversary message for each employee by concatenating their job title and the number of years they have been with the company. The message is labeled as AnniversaryMessage.