SQLTeam.com | Weblogs | Forums

EASY SQL problems I can't solve!


#1

Hi guys!

I have an important test today on a few basic SQL problem. I studied it in the past but I don't feel 100% confident with these 3 questions:

1 Write a query showing for every author that sold any books how many titles (book_id) and how many units (quantity ordered) they sold;
2 Write a query showing the authors that did not sell any books;
3 Write a query that shows all pairs of customers who bought the same book and the book they bought;

The tables are:
Author (autor_id, book_id) and Customer (customer_id, book_id, quantity_ordered)

Thanks in advance!


#2

Try to write them then post your work. The community here will be able to help you find and correct any problems.

If, OTOH, we just post solutions, and they turn out to be wrong, you'll get a bad score and the community will get a bad rep. Unhappy for all of us!


#3

Sure!

For the first I was thinking

SELECT author.autor_id, author.book_id, customer.quantity_ordered
FROM author, customer
WHERE autor.book_id=customer.book_id;

For the second

SELECT author.autor_id FROM author
WHERE customer.book_id IS NULL

For the third I don't know what function to use...

SELECT customer.customer_id, customer.book_id FROM customer
WHERE ...


#4

The first query looks close, but since the question is "how many" you might want to SUM the quantities. Also you don't need to select book_id (as I read the question).

The second query is missing a join to customer (probably a left join)

The third query is asking for a Catesian product. So, think about how you would query all the books bought by all customers, then do that twice and join on book id

something like:

select c1.customer_id, c2.customer_id, b.book_id
from ( query to get all books by all customers) c1
join (same query) c2
on c1.book_id = c2.book_id
-- probably also
where c1.cust_id <> c2.cust_id


#5

Thanks gbritton!

I guess the first one, then, is to be edited in

SELECT author.autor_id, author.book_id, customer.quantity_ordered
FROM author, customer
WHERE autor.book_id=customer.book_id
SUM (quantity_ordered) AS "Total";

For the second I guess

SELECT author.autor_id FROM author
LEFT JOIN customers ON author.book_id !=customer.book_id

Working on the third!


#6

Those are great queries to see in action. Make sure you study the code below until you understand it!

SELECT a.author_id, COUNT(c.book_id) AS how_many_titles, SUM(c.quantity_ordered) AS how_many_units
FROM Author a
INNER JOIN Customer c ON c.book_id = a.book_id
GROUP BY a.author_id

SELECT a.author_id
FROM Author a
LEFT OUTER JOIN Customer c ON c.book_id = a.book_id
GROUP BY a.author_id
HAVING COUNT(c.book_id) = 0

SELECT c1.customer_id, c2.customer_id, c1.book_id
FROM Customer c1
INNER JOIN Customer c2 ON c2.book_id = c1.book_id AND c2.customer_id > c1.customer_id