Anyone here with SQL experience?

[Blitz24]

Having a bit of an issue trying to do these questions:

1. List the customers who ordered computers
2. List the other products bought by customers who bought computers
3. List the customers who did not place any order since January 1 2014
4. List the products bought by at least one customer of Newark
5. List the products bought by all the customers of Newark
6. List the products ordered only by the customers of Newark
7. List the products never ordered by customers of New Jersey


I have done questions 1, 3, 4, and 7 but I am not sure how to do question 2, 5 or 6. 2 uses a nested query but I have no idea how to make it operate properly.

 

[doctorrg]

Having a bit of an issue trying to do these questions:

1. List the customers who ordered computers
2. List the other products bought by customers who bought computers
3. List the customers who did not place any order since January 1 2014
4. List the products bought by at least one customer of Newark
5. List the products bought by all the customers of Newark
6. List the products ordered only by the customers of Newark
7. List the products never ordered by customers of New Jersey


I have done questions 1, 3, 4, and 7 but I am not sure how to do question 2, 5 or 6. 2 uses a nested query but I have no idea how to make it operate properly.

Hard to say without clear knowledge of the table structure, but for 2 you'll just have to restrict customers to those who bought computers, and then you need to get the list of products bought by these customers.

 

[Blitz24]

Hard to say without clear knowledge of the table structure, but for 2 you'll just have to restrict customers to those who bought computers, and then you need to get the list of products bought by these customers.

That I did. The issue is that this is the table structure:

Customer(CID, Name, City, State),

Order(OID, CID, Date), and

Product(PID, ProductName, Price)

LineItem(LID, OID, PID, Number, TotalPrice),

As a result, I have 12 lineitem orders for 10 customers (to even attempt question 2, as the customers need multiple orders).
But I don't know what query to run to check the LID that would have everything else equal.
OR
How to have multiple entries in the PID field for LineItem so that I could identify it in one LID.

 

[doctorrg]

Just do the easy way and do a sub-query. Divide and conquer.

Sub query would check for all customers who bought computers.

select DISTINCT c.CID FROM Customer c, Order o, LineItem l, Product p
where c.CID=o.CID and o.OID=l.OID and l.PID=p.PID and p.ProductName="Computer"

That would give you all customer ids of the customers who bought computers.

Now you need all products bought by these customers.

select DISTINCT p.ProductName from Product p, LineItem l, Order o
where p.PID=l.PID and l.OID=o.OID and o.CID in (<sub-query>)

in case you need to exclude computers themselves, you'll just add:

and p.ProductName!="Computer"

If there are multiple products that are computers the ProductName constraint will simply be a little more complex to cover all such products.

Shouldn't that do it?

 

[Blitz24]

Sorry, doesn't yield an error. It actually yields all of the products available that aren't computers, not what the other products are that the people who ordered computers also ordered.

 

[doctorrg]

Just to clarify, the following would be the full query:

select DISTINCT p.ProductName from Product p, LineItem l, Order o
where p.PID=l.PID and l.OID=o.OID and o.CID in (
select DISTINCT c.CID FROM Customer c, Order o, LineItem l, Product p
where c.CID=o.CID and o.OID=l.OID and l.PID=p.PID and p.ProductName="Computer"
) and p.ProductName!="Computer"

 

[Blitz24]

Yes it is.

For Questions 5 and 6, would it be similar to 2 or would it be a different query?

 

[doctorrg]

Similar principle. If you understand 2, you should be able to do 5 and 6.