Answers To DBA Interview Questions

2008/05/19

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

1. Using the AdventureWorks database and bring back all the columns from the HumanResources.Employee table where the title starts with Production, marital status is M and the hire date is between 1/1/2000 and getdate(). Hint: Should get 12 rows back.

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