CSCE 520
Homework 4

Reading

Sections 3.1, 3.2, 3.3

To Submit

Undergrads and graduate students -- do Exercises 3.1.1, 3.2.1, 3.2.2(ii), 3.2.4, 3.2.10(a,c), 3.3.1(b,d), 3.3.4. Also do the following SQL exercise: Recall the database schema of Exercise 2.4.1. Write SQL queries for the following things:

  1. The number of pc's made by each maker
  2. The maximum screen size of a laptop made by E
  3. The average price of products made by each maker (but only if that average price is at least 200), in descending order of price
In each case, give your column headings reasonably descriptive names. Your queries must be correct in general, that is, they must work for any instance of the DB, not just the one presented in the book. Place your queries in a file hw4.sql and test them in MySQL.

The following applies to this and mutatis mutandis all future homeworks. To submit: From the shell prompt, login to mysql with the following shell command:

mysql -p sales

and enter your user password when prompted (this will work on unix/linux/Mac systems; for Windows, YMMV). Including the database name on the command line automatically loads it, so you do not need to issue a "USE sales" command inside mysql. Inside mysql, type

mysql> tee hw4.log
mysql> source hw4.sql
mysql> notee
mysql> quit

The first command tells mysql to append everything subsequently appearing in the session to the file hw4.log, until the notee command is given, which stops logging. The source command reads the hw4.sql file and runs its command in order as if you had typed them at the prompt.

You should make a .zip file hw4.zip containing both hw4.sql and hw4.log files. Upload your .zip file electronically using CSE Dropbox. If you are in Sections 001, H10, or J60, you should submit your assignment to Section 001 regardless of which of these sections you are in. If you are in Section 002, then submit to Section 002.

Graduate students only -- do Exercises 3.2.3(a,b), 3.2.10(b,d), 3.3.1(a,c).