Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Answers To DBA Interview Questions

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

Comments

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.

Leave a Comment

Please register or log in to leave a comment.