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