WRITE THE SQL FOR THE FOLLOWING: 1.List the customers (customer id, last name, f

Question

WRITE THE SQL FOR THE FOLLOWING:

1.List the customers (customer id, last name, first name) who have more than 1 order.

2.List the customers (customer id, last name, first name) who have ordered at least one “computer” book.

3.List the publishing years (from BOOKS) and the number of books published in each year.

4.Calculate the total revenue from all ordered books.

Note: ORDERITEMS table holds data about each Order Item (book ISBN), quantity, and the price paid for each book.

5.How many different books have not been ordered?

Explanation / Answer

Ans 1) select customer_id, last_name, first_name, count(order) from customer_table a inner join orderitems b on a.customer_id=b.customer_id GROUP BY customer_id, last_name,first_name HAVING count(order)>1;

2) select customer_id,last_name,first_name,count(order) from customer_table a inner join books b on a.customer_id=b.customer_id where b.book ISBN='COMPUTER' GROUP BY customer_id,first_name,last_name HAVING count(order)>=1;

3) select publishing_year,count(book_ISBN) from books GROUP BY year;

4) select sum(price paid) from orderitems;

5) select * from books where book_ISBN NOT IN(select book_ISBN from orderitems);

Thanks :)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.