---

Activity Objectives

1. Students will learn about databases. They will review spreadsheets as a metaphor for data tables and relational databases.
2. Students will create a database using a simple tool such as SQLite and learn how to perform queries.

Activity

We will use MySQL to create a database. You can use the Tutorialspoint MySQL online terminal to enter and run the code. Replace the existing text in the editor with the text below. You may need to begin your code with “BEGIN TRANSACTION;”.

1. Create the database. This is essentially a shell structure for everything else. We will call the database historyinfo. Remember to include the semicolon at the end of the line and hit return.

CREATE DATABASE historyinfo;

You should receive a message such as “Query OK, 1 row affected (0.01 sec)”. If this line produces an error, try omitting it and starting with the next line.

2. Select database. Even though it is obvious to you which database you want to use, perhaps you will be working with several different databases in the future.

USE historyinfo;

You should receive a confirmation message similar to: “Database changed”, even though you didn’t really change anything.

3. Finally we will get to something more interesting. Create a table in the database, but at the same time, let’s add some fields (columns). Each line starts with the field name and then states the field type.  Each field line ends with a comma, except for the last.

CREATE TABLE historyevents (
EventYear INT,
Event VARCHAR(255),
Description VARCHAR(255)
);

You should get some like like “Query OK, 0 rows affected” for a confirmation. Common field types include INT for whole numbers (-5,0,33, 2019), DECIMAL, VARCHAR( ) for text, where the number in parentheses is the allowed number of characters), DATE and TIME.

4. Enter some information into the database. We tell the database which table to use (even though we only have one), then you provide a list of fields, then a list of data (both lists should be in the same order).

INSERT INTO historyevents (EventYear, Event, Description)
VALUES ('1215', 'Magna Carta', 'Rights document signed by monarch in England');

You should receive something like “Query OK, 1 row affected” for confirmation. Yes, finally you have affected a row (record)!

5. Let’s perform a query to see the result of all of this effort. Retrieve some information from the database.

SELECT * FROM historyevents;

A nice little table should appear in the output.

+-----------+-------------+----------------------------------------------+                                         
| EventYear | Event       | Description                                  |                                         
+-----------+-------------+----------------------------------------------+                                         
|      1215 | Magna Carta | Rights document signed by monarch in England |                                         
+-----------+-------------+----------------------------------------------+                                         
1 row in set (0.00 sec)

Here, “*” tells the query to select all records from the historyevents table, but we only have one record, so that is all it shows.. There are many ways to make the query more specific. We can constrain the query to display only certain fields, or only show records with certain values in the field. There are many more options, and trying to figure out how to use them to get exactly which data you require can be challenging.

6. Next, enter two new records at once. You only have to type VALUES once. Remember to separate each record with a comma.

INSERT INTO historyevents (EventYear, Event, Description) VALUES ('960', 'Song Dynasty', 'Start of Song Dynasty in China'), ('1066', 'Norman Invasion', 'Normans Conquer of England');

Now let’s try a more specific query:

SELECT EventYear, Event
FROM historyevents
WHERE EventYear < 1100;

You should see results similar to:

+-----------+-----------------+                                                                                     
| EventYear | Event           |                                                                                     
+-----------+-----------------+                                                                                     
|       960 | Song Dynasty    |                                                                                     
|      1066 | Norman Invasion |                                                                                     
+-----------+-----------------+                                                                                     
2 rows in set (0.00 sec)                                                                                            

Here are some useful commands to get information about your database:

• SELECT DATABASE();
• SHOW TABLES;
• DESCRIBE tablename;

---

« Databases | COURSE | Using Image Processing to Gain Superhero Vision »