Question

For this assignment make sure that your submission includes your SQL queries and the results of the queries. You can use phpmyadmin, your own machine, the mysql> prompt.....whichever you wish!

Using the books database that we reviewed in class, give SQL queries to list the following:

1. customer names and addresses sorted by names ascending

2. customer names and order numbers and order amounts sorted by amounts descending

3. all authors that wrote a book that have the word 'computer' in the title

4. all customers that ordered a book that has a review

EXTRA CREDIT: 5. customer with the most orders (our best customer)

use books;

insert into customers values

(3, "Julie Smith", "25 Oak Street", "Airport West"),

(4, "Alan Wong", "1/47 Haines Avenue", "Box Hill"),

(5, "Michelle Arthur", "357 North Road", "Yarraville");

8 insert into orders values

(NULL, 3, 69.98, "2007-04-02"),

(NULL, 1, 49.99, "2007-04-15"),

(NULL, 2, 74.98, "2007-04-19"),

(NULL, 3, 24.99, "2007-05-01");

insert into books values

("0-672-31697-8", "Michael Morgan", "Java 2 for Professional Developers", 34.99),

("0-672-31745-1", "Thomas Down", "Installing Debian GNU/Linux", 24.99),

("0-672-31509-2", "Pruitt, et al.", "Teach Yourself GIMP in 24 Hours", 24.99),

("0-672-31769-9", "Thomas Schenk", "Caldera OpenLinux System Administration Unleashed", 49.99);

insert into order_items values

(1, "0-672-31697-8", 2),

(2, "0-672-31769-9", 1),

(3, "0-672-31769-9", 1),

(3, "0-672-31509-2", 1),

(4, "0-672-31745-1", 3);

insert into book_reviews values

("0-672-31697-8", "Morgan's book is clearly written and goes well beyond most of the basic Java books out there.");

Answer 1

1.

Select Name,Address from Customers order by Name;

2.

Select c.Name,o.OrderID,o.Amount from Customers c inner join Orders o on c.CustomerID = o.CustomerID order by o.Amount;

3.

Select Author from Books where title like '%computer%' ;

4.

Select c.Name from Customers c inner join Orders o on c.CustomerID = o.CustomerID inner join Order_Items ot on o.OrderID = ot.OrderID inner join Book_Reviews br on ot.ISBN = br.ISBN ;

EXTRA CREDIT: 5.

select c.Name from Orders o, Customers c where o.CustomerID = c.CustomerID and o.CustomerID IN (Select CustomerID from Orders group by CustomerID having count(*) = (Select count(*) from Orders o group by o.CustomerID order by count(*) desc limit 1))