SQL CHALLENGE

-- My homework

 

-- 1. Question 1: Retrieve information about the products with color values except null, Red, silver/black, white and list price between £75 and £750.

-- Rename the column StandardCost to price. Also, sort the result in descending order by list price

 

SELECT [ProductID],[Name],[ListPrice],[Color],[StandardCost] Price FROM [Production].[Product]

WHERE ([Color] NOT IN ('Red','Silver','White','Black')) AND ([Color] IS NOT NULL) AND

([ListPrice] BETWEEN 75 AND 750)

ORDER BY ListPrice DESC;

 

-- 2.

--Find all the male employees born between 1962 to 1970 and with hire date greater than 2001 and female employees

-- born between 1972 and 1975 and hire date between 2001 and 2002.

 

SELECT BusinessEntityID,NationalIDNumber,Gender,YEAR(HireDate) AS HireDate, YEAR(BirthDate) AS Year_Born FROM [HumanResources].[Employee]

WHERE ((Gender = 'M') AND (YEAR(BirthDate) BETWEEN 1962 AND 1970) AND (YEAR(HireDate) > 2001)) OR ((Gender = 'F') AND (YEAR(BirthDate) BETWEEN 1972 AND 1975) AND (YEAR(HireDate) BETWEEN 2001 AND 2002));

 

--3. Create a list of 10 most expensive products that have a product number beginning with ‘BK’.

-- Include only the product ID, Name and colour.

 

SELECT TOP 10 [ProductID],[Name],[Color] FROM [Production].[Product]

WHERE ProductNumber LIKE 'BK%'

ORDER BY ListPrice DESC;

 

--4. Create a list of all contact persons, where the first 4 characters of the last name are the same as the first four characters of the email address.

--Also, for all contacts whose first name and the last name begin with the same characters, create a new column called full name combining first name and the last name only.

--Also provide the length of the new column full name.

 

SELECT FirstName, LastName, CONCAT(FirstName,' ', LastName) AS Full_Name, LEN(CONCAT(FirstName,' ', LastName)) AS len_full_name FROM [Person].[Person]

WHERE (SUBSTRING(FirstName,1,4) = SUBSTRING(LastName,1,4)) AND (SUBSTRING(FirstName,1,1) = SUBSTRING(LastName,1,1));

 

SELECT PP.BusinessEntityID, PP.FirstName,PP.LastName,PE.EmailAddress,CONCAT(PP.FirstName,' ', PP.LastName) AS Full_Name, LEN(CONCAT(PP.FirstName,' ', PP.LastName)) AS len_full_name

FROM [Person].[Person] AS PP

INNER JOIN [Person].[EmailAddress] AS PE

ON PP.BusinessEntityID = PE.BusinessEntityID

WHERE (SUBSTRING(PP.FirstName,1,4) = SUBSTRING(PE.EmailAddress,1,4)) AND (SUBSTRING(PP.FirstName,1,1) = SUBSTRING(PP.LastName,1,1));

 

--5 Question 5: Return all product subcategories that take an average of 3 days or longer to manufacture

SELECT PPS.ProductSubcategoryID, PPS.[Name], PP.DaysToManufacture

FROM [Production].[ProductSubcategory] AS PPS

LEFT JOIN [Production].[Product] AS PP

ON PPS.ProductSubcategoryID = PP.ProductSubcategoryID

WHERE PP.DaysToManufacture >= 3

;

 

--6. Create a list of product segmentation by defining criteria that places each item in a predefined segment as follows. If price gets less than £200 then low value. If price is between £201 and £750 then mid value.

-- If between £750 and £1250 then mid to high value else higher value.

-- Filter the results only for black, silver and red color products

 

SELECT [ProductID],[Name],ListPrice,Color,

CASE

WHEN ListPrice < 200

THEN 'LOW VALUE'

WHEN ListPrice BETWEEN 201 AND 750

THEN 'MID VALUE'

ELSE 'HIGH VALUE'

END AS product_segmentation

FROM [Production].[Product]

WHERE COLOR IN ('RED','BLACK','SILVER')

;