CareerCup

I am making below assumptions

1)Customer( CustomeID,Fname,Lname)
2)Order(OrderID,CustomerId)

Final query is "Select * from order where CustomerId in ( Select CustomerId from Customer where Lname like '<input_from_user>' )"

I think it is better to re-write the above subquery to a standard outer join.

Select * from order, customer where order.customerid = customer.customerid and customer.Lname like '<input>'

how would you track order ? we need order table as well.

Aslo need to have inventory table. It is needed to check if the goods is avaialbe or not.

1) Customer(CustId, FName, LName)
2) Inventory(InvId, Item)
3) Orders(OrderId, CustId)
4) Order_Items(OrderId, InvId)

SELECT * 
FROM customer c JOIN orders o ON c.custId=o.custId
JOIN order_items oi ON oi.OrderId=o.OrderId
JOIN inventory i ON oi.invId=i.invId
WHERE c.LName=:lname;

**NB: Breaking Order_items into a separate table to prevent a many-to-many relationship. For interview, this is probably not necessary

Create Table Customer
( Customer ID
Customer First Name
customer last Name
Customer Address
)

Create Table Inventory
(
ProductID
ProductName
ProductQuantity
)

Create Table Order
(
OrderID
ProductID
ProductName
QuantitySold
Date
CustomerID
)


select * from Customer C , Order O where C.CustomerID=O.CustomerID and C.CustomerLastName ='Das'