Tuesday, May 14, 2013

Database Developer Interview Homework

     A friend of mine was recently interviewing for a sql developer job here in town and received some "homework" from a prospective employer. Seems like a pretty good idea, and the questions were pretty good. He let me take a look at it, and after going over the worksheet, I decided to take a crack at them...here are my answers:

A)
ORDER
OrderID
Item1Qty
Item2Qty
Item1Name
Item2Name
Item1Price
Item2Price
CustomerName
CustomerPhone
OrderTotal

1. How would you normalize the above table?  What are the typical relationships and constraints that you would have to consider?  Would you add any indexes?  Why?
How would you normalize the above table?

Figure 1. Normalized Tables
  What are the typical relationships and constraints that you would have to consider?
            You would have to consider the 1:M relationship between customer and order, and the M:M relationship between Order and Item. Foreign key constraints would have to be enacted between customer and order as well as foreign key constraints to the junction table(Order Item) from Order and Item.
 Would you add any indexes?  Why?
            I would add indexes to the primary keys since they are used in any join. I would also include commonly used fields in a where clause to a covering index to speed up queries.
2. How would you implement a price change to an item?
       You would update the Price field in the Item table 
3. How would you keep a history of how much each item cost over time?
You can implement this in a number of ways.
a.      You can add the Price field to the Order Item table to see what the price was at the time of the transaction
b.      You can Implement a slowly changing dimension(from warehousing) in the Item table where we would use a surrogate key for the join to Order Item , keep the ITEM_ID as a natural key and insert a new record every time an item's price changes with validity dates.
Figure 2. Item table with validity dates
c.      You can store this in a separate table that "snowflakes" off of Item and make the primary key the ITEM_ID and VALID_FROM_DATE
Figure 3. Item Price Hist Table Usage


4. Given a customer’s name, write a query of how much he/she has spent.

Let’s say the customer’s name is Acme and were using the original schema from question 1:

SELECT
      CUST.CUSTOMER_NAME,
      SUM(ORD_ITEM.QTY)*SUM(ITEM.PRICE) AS TOTAL
FROM
      CUSTOMER CUST
      INNER JOIN ORDER ORD ON CUST.CUSTOMER_ID =ORD.CUSTOMER_ID
      INNER JOIN ORDER_ITEM ORD_ITEM ON ORD.ORDER_ID = ORD_ITEM.ORDER_ID
      INNER JOIN ITEM ON ORD_ITEM.ITEM_ID = ITEM.ITEM_ID
WHERE
      CUST.CUSTOMER_NAME='Acme'
GROUP BY
      CUST.CUSTOMER_NAME
5. How would you change the query so that it also reports by month and year?

SELECT
      CUST.CUSTOMER_NAME,
      SUM(ORD_ITEM.QTY)*SUM(ITEM.PRICE) AS TOTAL,
      DATEPART(YEAR,ORDER_DATE) AS ORDER_YEAR,
      DATEPART(MONTH, ORDER_DATE) AS ORDER_MONTH
FROM
      CUSTOMER CUST
      INNER JOIN ORDER ORD ON CUST.CUSTOMER_ID =ORD.CUSTOMER_ID
      INNER JOIN ORDER_ITEM ORD_ITEM ON ORD.ORDER_ID = ORD_ITEM.ORDER_ID
      INNER JOIN ITEM ON ORD_ITEM.ITEM_ID = ITEM.ITEM_ID
WHERE
      CUST.CUSTOMER_NAME='Acme'

GROUP BY
CUST.CUSTOMER_NAME, DATEPART(YEAR,ORDER_DATE),DATEPART(MONTH, ORDER_DATE)
B)


Employee
EmpID
EmpName
1
Joy
2
Ira
3
Chris

Project           
ProjectID
ProjectName
1
GAE
2
CVue
3
1098T
  
ProjectAssignment
ProjectID
EmpID
1
1
1
2
2
2
2
3
3
1

Using tables above, write a query that will return the following result:

Total_Projects
Joy_Project_Count
Ira_Project_Count
Chris_Project_Count
3
2
2
1

-->Create Tables

create table Employee
(EmpID int, EmpName char(10))

insert into Employee (EmpID, EmpName) values (1, 'Joy'), (2, 'Ira'), (3, 'Chris')
go

create table Project
(ProjectID int, ProjectName char(10))

insert into Project (ProjectID, ProjectName) values (1, 'GAE'),(2, 'CVue'),(3,'1098T')
go

create table ProjectAssignment
(ProjectID int, EmpID int)

insert into ProjectAssignment (ProjectID, EmpID) values (1,1), (1,2), (2,2), (2,3),(3,1)
go


-->Execute Query

SELECT 
       count(distinct ProjectID) as Total_Projects,
       SUM([Joy]) as Joy_Project_Count,
       SUM(Ira) as Ira_Project_Count,
       SUM([Chris]) as Chris_Project_Count
     
FROM    (

SELECT
      COUNT(PROJECTASSIGNMENT.PROJECTID) AS PROJECT_COUNT,
      PROJECTASSIGNMENT.PROJECTID,
      EMPLOYEE.EMPNAME
FROM
      EMPLOYEE
      INNER JOIN PROJECTASSIGNMENT ON EMPLOYEE.EMPID=PROJECTASSIGNMENT.EMPID
      INNER JOIN PROJECT ON EMPLOYEE.EMPID=EMPLOYEE.EMPID
GROUP BY
      PROJECTASSIGNMENT.PROJECTID,
      EMPLOYEE.EMPNAME
  ) P PIVOT ( COUNT(PROJECT_COUNT)
                    FOR EMPNAME  IN ([JOY],[IRA],[CHRIS])
                  ) AS PVT

Figure 4. Project Counts


1 comment:

  1. Nice, Worth reading!
    Can you please post more T-SQL questions, kind of brain warm up before interview.

    Thanks,
    Swat

    ReplyDelete