2007W


Instructor
TA




MAT259 Visualizing Information(4 units)
George Legrady
Charlie Roberts

Lecture: Tuesday 10:00-12:00, Lab: Thursday 10:00-12:00 - e-studio/Arts2220
Lab 5

I have created a database called 'lab5' that everybody should be able to read from, but not write to. In it there are three tables: books, customers and orders. These tables were used to create the queries below, so if you try them out on the lab5 database you should get results. Make sure you execute the 'use lab5;' command before trying these.

In SELECT queries, everything before the 'from' determines -what- you want to select, everything after the 'from' determines where you want to get it from.

Below are sample MySQL queries and a description of what they accomplish. It would probably help to login to the lab5 database and take a look at the tables and the data they contain before trying to understand these.

select price from books;
The above query returns the data held in the price field of each record in the table books. By default it will also return the key of each record.

select price from books where price > 15;
The above query returns the data held in the price field of each record in the table books is the price if greater than 15.

select lab5.orders.customer_id, lab5.books.* from lab5.orders, lab5.books where orders.book_ISBN = books.ISBN;
This query joins the orders table with the books table. It finds the customer_id of each order, and then displays all information associated with the book that the customer order (as stored in the books table).

select books.title from lab5.books,lab5.orders where orders.customer_id = 7 and orders.book_ISBN = books.ISBN;
This query selects the titles of books ordered by the customer with the customer_id of 7. Since the titles of the books is not found in the 'orders' table, we have to use the ISBN field in order to find the associated title inside the 'books' table.

Below is a link to commented, example Processing code used to display MySQL queries:

mysql_a.pde

Here's a link to the MySQL library for Processing, make sure to read the (brief) documentation:

http://www.bezier.de/mysql/

Finally, here's link to a better MySQL GUI editor called Navicat... there is a free 30 day trial period for it. You can also use this program to easily import Excel data into MySQL, which MySQL Query Browser doesn't allow you to do (well, at least not easily.)

http://www.navicat.com/

Assignment 5 due 2/15/07
 

Execute the following queries on the 'lab5' database and display the results in Processing using the println method. Email me the .pde file directly; DO NOT SEND IT TO THE LIST.

1. Show me the dates for every purchase of the book with ISBN # 0-652-11112-9.

2. Show me the name of the customer who ordered the book with the ISBN # 0-244-12233-5. You must find this using code.

3. Show the total cost of all purchases by Fritzy Fritztopholeus. Do this with code (Hint: look at the SUM function of MySQL... or you could sum these using code in Processing)