Subscribe to this blog
Briefcase
Print
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 LaptopFROM 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.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.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 existsCASE
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.
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
Posted by Matthew Geske on 20 December 2010
4 will never execute this:
when c.addressline2 is null then c.addressline1
because your where excludes records that have addressLine2 of null...no?
Posted by mgtleviste on 7 March 2013
I think this questions are for SQL Developer.