Here are your answers to the questions from last week You should get some variation of what I have given you here. If you think I've made a mistake somewhere let me know and we can discuss it. This will be a short series of blogs on interview questions to help you learn what to expect when entering your first interviews.
Activities for Laptop
SELECT *
FROM HumanResources.Employee
WHERE Maritalstatus = 'M' and hiredate between '1/1/2000' and getdate() and title like 'production%'
2. From the AdventureWorks database join the tables Production.Product and Production.TransactionHistory. Bring back columns ProductID, Quantity, and ActualCost from Production.TransactionHistory. Use Name and ProductNumber from the table Production.Product. Also, add a where clause that only returns the rows having an actual cost greater than zero.
SELECT h.productid, h.quantity, h.actualcost, p.name, p.productnumber
FROM Production.Product AS p INNER JOIN Production.TransactionHistory AS h
ON p.productid = h.productid
WHERE actualcost > 0
3. Using the AdventureWorksDW database show a join between Dim_Customer and Dim_Geography that brings back the columns LastName and FirstName aggregated separated by a comma, AddressLine1, and AddressLine2 from Dim_Customer. From Dim_Geography bring back the City, StateProvinceCode, and PostalCode columns. Then order by LastName.
SELECT c.lastname + ', ' + c.firstname AS fullname, c.addressline1, c.addressline2, g.city, g.stateprovincecode, g.postalcode
FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
ON c.geographykey = g.geographykey
ORDER BY lastname
4. This time using the same SQL statement from the last question replace the AddressLine1 and AddressLine2 columns with a case statement. The case statement should return only AddressLine1 if AddressLine2 is null and AddressLine1 and AddressLine2 separate by a comma if AddressLine2 does exists
SELECT c.lastname + ', ' + c.firstname AS Fullname, g.city, g.stateprovincecode, g.postalcode,
CASE
when c.addressline2 is null then c.addressline1
else c.addressline1 + ', ' + c.addressline2
end AS Address
FROM dbo.DimCustomer AS c INNER JOIN dbo.DimGeography AS g
ON c.geographykey = g.geographykey
where c.addressline2 is not null
ORDER BY lastname
5. Using a left join on FactResellerSales and Dim_Employee from the same database show EmployeeKey, LastName, and FirstName from Dim_Employee. From FactResellerSales show the SalesAmount column summed and then group by Dim_Employee columns. Also, use having statement to bring back only summed sales amount that have a total greater than 1 million. Lastly order by the summed sales amount in descending order. Hint: Should be only 15 rows.
SELECT e.employeekey, e.lastname, e.firstname, sum(s.salesamount) AS totalsales
FROM dbo.FactResellerSales AS s LEFT JOIN dbo.DimEmployee AS e
ON s.employeekey = e.employeekey
GROUP BY e.employeekey, e.lastname, e.firstname
HAVING sum(s.salesamount) > 1000000
ORDER BY sum(s.salesamount) desc
