Help creating the necessary TSQL for this basic requirement.

Question

I've gotten really rusty with TSQL I'm ashamed to say. Using Entity Framework has made me forget what little TSQL I even knew. And I haven't exactly made very complex queries with Entity Framework either, just never had the need to sad to say.

This is one exercise our professor gave us this Friday:

"Monica buys a gallon of milk from the supermarket "Peoples" every Sunday.

Create a database schema that would accomodate this requirement and also create a query that returns, a list of products bought by Monica ordered by product Category."

The database schema I have no problem creating, it's the query that has me for a loop.

I realize that without providing the schema it's just sharing the TSQL from your imaginary schema will teach me a lot about TSQL.

Thanks for your time.

Answer 1

There are so many ways to do this and depends heavily on your schema. This is one way. Obviously, this is not a complete schema.

ProductCategory(Id, CategoryName)

Product(Id, ProductCategoryId, ProductName)

Store(Id, StoreName)

Customer(Id, FirstName, LastName)

Order(Id, StoreId, CustomerId, Date)

OrderDetail(Id, OrderId, ProductId)

SELECT Product.ProductName, ProductCategory.CategoryName
FROM Product INNER JOIN ProductCategory ON Product.CategoryId = ProductCategoryId 
INNER JOIN OrderDetail ON OrderDetail.ProductId = Product.Id 
INNER JOIN Order ON Order.Id = OrderDetail.OrderId
INNER JOIN Customer ON Order.CustomerId = Customer.Id
WHERE Customer.FirstName = 'Monica'
ORDER BY ProductCategory.CategoryName