The University of Western Australia
School of Computer Science and Software Engineering
 
 

School of Computer Science and Software Engineering

CITS1402 Relational Database Management Systems


Worksheet One

Each week's worksheet will introduce (or reinforce) the concepts presented in lectures in the previous week (or weeks).

Students should submit their work in the format specified by the worksheet using the cssubmit program.

As this process is mainly to encourage good work habits, the submissions will not usually be marked, but just quickly checked to ensure that they seem sensible.

However at any stage we may mark them more thoroughly, or run any code that is submitted. In addition, the submissions are intended to be primarily your own work, and we may use software to identify unusual similarities between submissions that may warrant further examination.

Content

This week's worksheet will focus on:

  1. Connecting to a MySQL server using a client program
  2. Practicing basic SQL involving just one table

The error messages given by MySQL are so basic that they are almost entirely useless, so you may wish to familiarize yourself with the MySQL documentation as you will almost certainly need it at some stage.

Starting to use MySQL in the Labs

These instructions assume that you are using Windows but MySQL works on other operating systems in a similar way. The instructions are for a UWA computer lab user but the exercise can be worked through on your own computer if you install MySQL. On a Mac, for example, the way of specifying paths is different.

Step 1. Browse to the H: drive, navigate down to ecm_data > MySQL, then run Start_MySQL_Server.cmd. This starts the mysqld process. If the server starts successfully, a DOS shell window should be open on the desktop showing some messages about the server. Do not close this window while you are using MySQL. Closing this window terminates the MySQL server process.

Note that if you were late enrolling in CITS1402, or if you have only just enrolled, then the MySQL folder may not have yet been installed on your H drive (this happens automatically every so often for the units that you are enrolled in). In that case you can often find the material under the CITS folder, CITS1402 on your T drive. Just copy across the MySQL folder to the ecm_data folder. Otherwise, please get IT help.

Step 2. Run the MySQL 5.6 Command Line Client. This opens a second DOS shell and should display a MySQL message asking for a password. There is no password, just type the return key.

You should now be at a "mysql>" prompt. (start using your SQL commands from here).

Installing an example database

Make a subdirectory called "Databases" in your H: drive (home directory) in the "My Documents" folder. Make a subdirectory of that called Lab01. Note that you should always save your work somewhere on your H drive so that you can find it again if you log on later on another computer.

For our first worksheet, we will use a database called "world" that is supplied freely by MySQL. It can be downloaded from http://dev.mysql.com/doc/index-other.html. Select the version marked "world database (InnoDB version, used in MySQL certifications and training", download and unzip.

I have put an unzipped text file version of this here.

You want to save it as a text file in the Databases\Lab01 subdirectory of your home directory. Call it "world_innodb.sql". (If later you cannot find this file, double check that it has not been saved with an invisible .txt extension.)

Now start a MySQL client window if you have not already got one running.

At the mysql> prompt type
CREATE DATABASE world;
USE world;
This creates a new empty database called world and says that you will be working with that from now on.

At the mysql> prompt type
SOURCE H:\My Documents\Databases\Lab01\world_innodb.sql
This uses the SQL instructions (and there are a lot of them) inside the world_innodb.sql text file to load up a database full of facts about countries and cities. That makes the world database.

If MySQL can not find the file and you get an error please check that there is not an invisible extension on the file name (eg, .txt) and check the path is correct. You can use "Folder Properties" under Windows to change .txt files to .sql ones.

To test the loading, type
SHOW TABLES;
You should see a list with three tables.

If you are using your own computer or a different set up, general instructions for initialising this example database, courtesy of Oracle are here. Basically, download, uncompress, load into MySQL using the command "source".

Questions

Before you start, ensure that you have installed the world database -- for this week's exercises we will just be using the tables City and Country one at a time (later we will be combining information from multiple tables). Start by typing

SELECT * FROM City;

and examine the output. In SQL (and in many other computing contexts), the * character is used as shorthand for "all" or "everything" so this statement should be viewed as saying "Select everything from City". If a statement produces too much output, you can limit how much you see by saying

SELECT * FROM City LIMIT 10;

Use SQL to determine the answers to the following questions (submit the answers, not the SQL statements). You may need to use the SQL functions MAX, MIN, AVG and you may have to look up the documentation on String functions to do things like finding out how many characters are contained in a string.

You do not necessarily have to write a single SQL command that returns the precise answer to the question, just enough commands that allow you to "read off" the answer from the screen or even from the diagnostics supplied by the client program.

(Warning: The table contains some accented characters that may not display correctly on different systems or client programs or combinations of the two. The questions should not encounter any of these.)

  1. How many rows are in the City table?
  2. How many attributes does a City have?
  3. How many tables are in the world database?
  4. How many cities have a population between 500000 and 1000000 (inclusive)?
  5. What is the name of the most populous city in the table? (Hint: ORDER BY)
  6. How many different country codes are used in the table? (Hint SELECT DISTINCT)
  7. What is the country code that is earliest in the alphabet? (Hint: ORDER BY)
  8. What is the country code that is last in the alphabet? (Hint: ASC, DESC)
  9. What is the name of the country that has the alphabetically earliest country code?
  10. What is the country code for the country containing the English city London?
  11. What is the largest city in the country that contains the other city called London?
  12. What is the average city population for the cities in Germany? (Use the MySQL command AVG and write down your answer with exactly the same number of digits as this command returns.)
  13. What is the standard deviation of the populations of the German cities?
  14. How many Chinese cities have populations of strictly more than 1000000?
  15. What is the total population of all the listed Chinese cities?

Write each of your answers down, one per line, in a plain text file (not a Word document, not a PDF etc) and submit this file via cssubmit.

Your file should contain exactly fifteen lines, each line containing a number or a word.

Written with StackEdit.


School of Computer Science and Software Engineering

This Page

Last updated:
Thu Dec 8 18:10:09 2011

Website Feedback:
[email protected]

http://undergraduate.csse.uwa.edu.au/units/CITS1401/