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.
This week's worksheet will focus on:
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.
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).
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".
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.)
City
table?City
have?world
database?ORDER BY
)SELECT DISTINCT
)ORDER BY
)ASC
, DESC
)AVG
and write down your answer with exactly the same number of digits as this command returns.)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.