COMP 111: Foundations of Computing HW #3 Standard Query Language Due Wed, 2/13/08
Remember to show all of your work in neat, legible print. Copy your work onto a new sheet of paper after working it out on scratch paper if necessary. Note that for this homework, you may want to test your SQL against the real database. A MySQL client for accessing the database server for this assignment is installed on all of the Computer Science lab computers. You also have the option of installing MySQL on your own computer; see the course website for links and instructions. The installer is very user-friendly, so there is no reason not to put it on your own machine as long as you are on the Wheaton network.. How to Access MySQL for this assignment MySQL is a “command line” interface, so you will need to open up a command window. Note that the icon installed by default on your desktop under Windows is configured to log into your own computer – not the server in my office on which the class database resides – so you can‟t use that icon for this assignment. To get a command window: On Windows: start menu > All Programs > Accessories > Command Prompt On Mac OSX: In the Applications folder, open “Utilities” and then “Terminal” The command you will type will do the following: Start the MySQL program Indicate that you want to log in as the user “comp106” Indicate that you want to connect to my office computer as the host (named after me) Type in your password
The way you do this is to type the following at the command window prompt: mysql –u comp106 –h lisamichaud -p Enter password: universal Once you are in MySQL, you tell it you want to look at the carsdata database by typing: mysql> use carsdata When you are ready to quit, you can just type: mysql> exit
Homework #3 Page 1 of 4
...and close the window. The database for this homework contains information about, unsurprisingly, cars, collected in 1978-79 by Consumer Reports. It has the following fields in a table labeled „cars‟ : id: an identifier number country: the country that manufactures the car type: the make and the model as a single string, e.g. “Toyota Camry” mpg: the miles per gallon for this model weight drive_ratio horsepower displacement: in cubic inches cylinders: how many the car has
Take a moment to look over the information in the table. One way to see the entire table is: SELECT * FROM cars; This is the most general query you can run, and will show you everything in the database table. (The table is wider than the standard window, and may wrap funny; it will be easier when there are fewer fields in the return set.) It is recommended that you try out your queries before putting them down as your answers. THE ACTUAL ASSIGNMENT: Specify the SQL command that would retrieve the following information (5 points point each). Assume that the most salient way to identify a car is through its „type‟ field (its make and model), but that other data that is relevant to the response should be included as well (as specified in #2, for example). Note also that there is no guarantee that the resulting set is anything but the empty set if no cars qualify. Finally, note that I am interested in the SQL command, not the answer to the question. However, you are encouraged to enter your SQL commands into a MySQL window in order to confirm that the answers are what you expect them to be, and that the syntax is correct. 1. What are all of the cars that have 6 cylinders? 2. List all of the Buicks and sort them by their gas mileage (display that, too). 3. What is the maximum horsepower of any American car? 4. If Fred wants an American car and he hates 4 cylinder cars, what are his options?
Homework #3 Page 2 of 4
5. Fred‟s spouse wants to widen their search to include non-American cars if they get at least 25 miles per gallon (maintaining the restriction that they not be 4 cylinders). Now what is the total set of possible cars they might look at (including the ones Fred likes)? 6. Fred‟s friend Diana is also car shopping. She wants to buy American, but has a thing against Fords. Actually, she might be persuaded to buy a non-American car because she really likes Volkswagons (VWs). What is the set of cars she would be interested in? 7. How many Japanese cars are in this database? 8. How many different countries are represented in this database? 9. How many European cars are in this database? (No, there is no field in these records indicating that a car is European, other than the different values in the „country‟ field that indicate European countries.) 10. What is the maximum horsepower produced by each country? Hint: The result should look like:
+---------+-----------------+ | country | max(horsepower) | +---------+-----------------+ | France | 133 | | Germany | 110 | | Italy | 69 | | Japan | 97 | | Sweden | 125 | | U.S. | 155 | +---------+-----------------+
11. What are the gas guzzlers that get fewer than 20 miles per gallon, and what country makes each of them? 12. Summarize the above results by just listing the countries that make gas guzzlers and how many each makes. Hint: The result should look like:
+---------+------------------------+ | country | number of gas guzzlers | +---------+------------------------+ | France | 1 | | Sweden | 1 | | U.S. | 10 | +---------+------------------------+
13. List any cars that have 8 cylinders and get more than 20 miles per gallon (not bloody likely, eh?) 14. How do American 4-cylinder cars stack up against Japanese 4-cylinder cars for mileage? 15. What 8-cylinder cars are out there that are not station wagons? (Assuming that „wagon‟ in the type is the indicator here.)
Homework #3 Page 3 of 4
Look at the SQL below and write the question this query would answer (in the format of those listed above). Merely translating the SQL into English is insufficient; what is the motivation of getting these data? (5 points each) 16. SELECT type,MPG FROM cars WHERE country=”Japan”; 17. SELECT type,weight,country FROM cars ORDER BY weight; 18. SELECT AVG( horsepower ) FROM cars WHERE type LIKE “Ford%”; 19. SELECT cylinders,AVG( horsepower ) AS „Average power‟, AVG( mpg ) AS „Average mileage‟ FROM cars GROUP BY cylinders; 20. SELECT COUNT( * ) AS „gas efficient‟ FROM cars WHERE mpg>30 AND country=”U.S.”;
Include in your submission the Wheaton Honor Code Pledge: I have abided by the Wheaton College Honor Code in this work.
Homework #3 Page 4 of 4