Interview Question

Software Engineer Interview San Mateo, CA

Write a SQL command that returns users' order history and

  the total cost of each order including shipping. There are 3 tables: users table, orders table, order details table.
Tags:
technical, sql, database
Answer

Interview Answer

3 Answers

2

users = id, name
orders = id, user-ID, date, ship-adr, etc
order-details = order-ID, item-ID, item-quantity

select o.date, od.item-ID
 from users u, orders o, order-details od
 where o.user-ID = u.id and od.order-ID AND od.order-ID = o.id
 group by o.id

Martin on Sep 2, 2009
2

order - user_id, order_id
order_details - order_id,item_id,shipping_cost, cost
user - user_id, user_name

select u.user_name, od.order_id, sum(od.cost+od.shipping_cost) from order_details od
left join order o on o.order_id=od.order_id left join user u on u.user_id=o.user_id
where u.user_id = 1234 group by order_id

Arun on Dec 9, 2009
0

order - user_id, order_id
order_details - order_id,item_id,shipping_cost, cost
user - user_id, user_name

select u.user_name, od.order_id, sum(od.cost+od.shipping_cost) from order_details od
left join order o on o.order_id=od.order_id left join user u on u.user_id=o.user_id
where u.user_id = 1234 group by order_id

dr on Feb 13, 2012

Add Answers or Comments

To comment on this Question, Sign In with Facebook or Sign Up