I wrote these questions thinking about what some basic t-sql statements that I would want to see if I were to interview a intern or junior level DBA. These cover some basics using both the adventure works transactional database and the adventure works data warehouse. I'm interested in seeing how others test people they interview. Let me know what suggestions you have for me to add to my questions.
Activities for Laptop
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.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.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 exists5. 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.
