Project Assignment 10

Answering Questions

The CEO of Neo Record Corp knocks on your office door.

"Hey there! Well, I decided that the Omnitunes client application definitely needs to be able to answer certain questions for the user. You know, so that our customers can actually use the system. I've been reading about usability. I realize we didn't mention this earlier, but the Internet was off and I couldn't send you my ideas sooner. Anyway, you're agile right? Doesn't that mean you can put up with my fickle demands?"

"Umm... not exactly," you reply. "Agile refers to a methodology that embraces change and --"

"Ok great! I'll have my secretary type up the list of questions as soon as she prints my email," he says, and quickly leaves your office.

Using your music_app database, create SQL statements that answer the following questions.

1. What are the song names, album names, and artist names of every song in the system?
2. What are all the genre names in the system?
3. What are the ten most recent transfers that occurred? (include the song name, the node where it came from, and the price paid)
4. What is the average price that was paid for all transfers?
5. What nodes are available for a song, in order from best to worst?*
6. How many albums are there for each artist in the system?
7. What is the average number of albums per artist in the system?
8. How many songs per genre are there in the system?
9. How much money has been made from outbound song transfers?

* Assume that records exist in the node table for a particular song (identified by a song_id), and that each node has some quality score (ping_time or speed... arbitrarily your choice).

Hints

It may help to have some sample data in your database. Because everyone's schema is different, you'll have to create your own data insertions. I suggest either recording all your INSERT statements in an external file, or dumping your database after there's some data in it. That way, if you need to rebuild the database you can just import the dumpfile / execute the infile.

You may find that you need to alter your database schema in order to answer the questions. (Especially if altering your database design results in simpler queries!)

Specific Tasks

Record the answers to the above queries in your documentation.

To be completed by Monday, March 3