For this assignment make sure that your submission includes your SQL queries and

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.");

Explanation / Answer

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))

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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