MySQL Sample Database

In the Notes on this site about using MySQL in the School, we use a sample database that contains a few small tables. The database is used in the examples in the Notes and is available for you to use from systems on the School's network.

The School has a MySQL database server for student use on an Enterprise Linux system, csmysql.cs.cf.ac.uk.

Look at the other Notes for information about accessing the School's server and your MySQL account and database.

The Sample Database

The sample database is called sample. It is installed on MySQL server csmysql.cs.cf.ac.uk. It is accessible from hosts with IP addresses within the cs.cf.ac.uk domain with username exampleuser and password passabc.

Tables in the Database

The tables in the database are:

systems
name oscode ownercode
blue 1 2
red 2 2
green 3 1
brown 2 3
people
pcode pname
1 John
2 Jane
3 Arthur
4 Sally
opsystem
oscode osname
1 Windows
2 Linux
3 MacOSX

Creating the Tables in your own Database

You can create copies of these tables in your own database. This can be useful if you want to try out MySQL from outside the School network. (You cannot log in as user exampleuser from outside).

Run the following SQL script in mysql, MySQL Workbench or phpMyAdmin.

drop table students;
drop table modules;
drop table marks;
create table students ( student_no varchar(10), surname varchar(20), forename varchar(20));
create table modules ( module_code varchar(8), module_name varchar(20));
create table marks ( student_no varchar(10), module_code varchar(8), mark integer);
insert into students values ('20060101','Dickens','Charles');
insert into students values ('20060102','ApGwilym','Dafydd');
insert into students values ('20060103','Zola','Emile');
insert into students values ('20060104','Mann','Thomas');
insert into students values ('20060105','Stevenson','Robert');
insert into modules values ('CM0001', 'Databases');
insert into modules values ('CM0002', 'Programming Languages');
insert into modules values ('CM0003', 'Operating Systems');
insert into modules values ('CM0004', 'Graphics');
insert into marks values ('20060101', 'CM0001', 80);
insert into marks values ('20060101', 'CM0002', 65);
insert into marks values ('20060101', 'CM0003', 50);
insert into marks values ('20060102', 'CM0001', 75);
insert into marks values ('20060102', 'CM0003', 45);
insert into marks values ('20060102', 'CM0004', 70);
insert into marks values ('20060103', 'CM0001', 60);
insert into marks values ('20060103', 'CM0002', 75);
insert into marks values ('20060103', 'CM0004', 60);
insert into marks values ('20060104', 'CM0001', 55);
insert into marks values ('20060104', 'CM0002', 40);
insert into marks values ('20060104', 'CM0003', 45);
insert into marks values ('20060105', 'CM0001', 55);
insert into marks values ('20060105', 'CM0002', 50);
insert into marks values ('20060105', 'CM0004', 65);
commit;