Create database tables, introduction to SQL

Database servers store information in database tables. In order to save information provided by the sensors of your robot into a table, first you need to be able to create a table. In this lecture you will learn about database table creation. You will learn how you can define columns with different data types. You will also learn how to list the already created tables and how to modify a table definition or how to delete a table. You will also learn about the SQL insert and the SQL select commands.

Figure 1 - Check how you can create a table, insert data into it and search in the database

Requirements

Ozeki 10 installed: Installed Ozeki 10 Robot OS
Lego connection installed: Connect the Lego robot to Ozeki 10
MySQL database server installed: http://www.ozeki.hu/index.php?owpn=6150

Task #1:

Open SQL Console in your Ozeki 10's database connection!

The way that you are going to modify your database is to run SQL commands on it. Luckily you run them through Ozeki 10. For it you just need to open the database connection that you created in the previous lecture and here you have to select the SQL tabpage (Figure 2). The main feature that you are going to use is the text field, where you can type the SQL commands. Above it you can see a drop-down menu, where your previously executed commands are saved, so they can be reused. If you have selected your command, you can run it with the 'Execute' button. The answer returned from the SQL server will show all results in a table that will appear below the 'Execute' button.

Figure 2 - SQL Console of the database connection

Task #2:

Create a table to store your robot details in this SQL Console with CREATE TABLE SQL command!

The first table that you are going to create is a table that contains some details about different type of robots. This table will store the name of the robot, the number of motors and number of sensors that the robot has The SQL command for creating a table is: CREATE TABLE. You have to start the command with this and after that you have to type the name of the table that you want to create. In this case the table is called 'Robots' (Figure 3).

Figure 3 - Creating the following table: 'Robots'

Then you need to type the names of the different columns between round brackets and select the information type that you will be able to store in it. The first column is called 'Name', which will store the name of the robot, and it stores 'VARCHAR(255)' types, which means that in this column you can save texts with the maximum length of 255 letters. For the next column give the name 'Number_of_motors' with 'INT(10)' type. This type ensures the storage of a number with ten digits. The last column that you need to add will store the number of sensors on the robot. So, please type 'Number_of_sensors' and 'INT(10)' should be the type. All column creation is separated by commas and in the names you should use underline character instead of spaces because of the syntax you can see in Figure 4. When you have finished with all of these, just click on the 'Execute' button and know how to successfully create the first table of your database.

Figure 4 - A full CREATE TABLE command

Task #3:

Display the description of your table with DESC SQL Command!

When you created the table in the previous task, you gave all the columns some attribute. In this case they have a name and type. But there are more attributes that are added as table columns. You can check these attributes of a table with the 'DESC' command (Figure 5). In the SQL Console you only need type the name of the table that you want to see, so let's see the details of the 'Robots' table, so please type 'Robots' and click 'Execute' the command and watch to results.

When you run this command, the description of the table will show up for you with all the columns that you created with all of its properties (Figure 5). The first two may be familiar for you, the 'Field' and 'Type' because you specified these when you created the table. The next attribute is the 'NULL', which means that the a record can store NULL attributes or not. By default it is 'YES', but you can modify it by adding 'NOT NULL' constraints when creating a table. The next one is 'Key', which means that the values of this column must be different in each record. You can set a column to be a key by adding the 'PRIMARY KEY(column_name)' when you create the table. The 'Default' attribute means that you can set a default value to a column by adding 'DEFAULT' constraint.

Figure 5 - Using the DESC command in the SQL Console

Task #4:

Use INSERT INTO SQL command to place some robot details!

Now you have got the table and you are aware of all of its properties. But your table is still empty, so it's time to fill some data in it. You will be able to do that with the 'INSERT INTO' SQL command. So first type it into the SQL Console. Then you have to type the table name, where you want to insert the data to. In this case it is called 'Robots'. After that you have to specify which columns you would like to put in the data. So in round brackets list the name of the columns, like in (Figure 6).

Define the data next, which you want to insert into the 'Robots' table. First you have to type the column names in a bracket (Figure 6) then after the 'VALUES' keyword, please type the data that you would like to add. It is very important to use the same order here as you entered the column names because with that you can avoid data collision and of course the data will be placed in the proper column. If using text data, please type it between double quotation marks (Figure 6). Insert a few robots: ("Chopper",2,0), ("Sniffer",2,0), ("Dozer",3,4) and ("Legoshnikov",1,1).

Figure 6 - An INSERT INTO command containing a few attributes

Task #5:

Query all data from the table with SELECT * SQL command!

Because your table is now filled with some data, now you have got the chance to display them in Ozeki 10. In SQL try the 'SELECT' command which selects and displays them. There are so many ways you can produce a query with the 'SELECT' command and this lecture will show you some really simple examples of it. First try the simplest one, which is a query that reaches all table data. For that type 'SELECT * FROM Robots'. The '*' character means that it will show all columns of the table in the result table. 'FROM Robots' means that you ask for data from the Robots table. If you click on 'Execute' and you've correctly done everything, see the same results like on Figure 7.

Figure 7 - Query all data from your table

Task #6:

Query data from a few selected columns with the SELECT 'tablename' SQL command!

But let's say you don't care about all information and you only want to see the name of each robot and how many motors are equipped on them. With the 'SELECT' command you can do it very easily. Just type 'SELECT Name, Number_of_motors FROM Robots' in the SQL Console. As you can see on Figure 8, just type the column names instead of the '*' character, so now you will be able to see only the data of these columns and in the same order you requested. If you click on 'Execute' you should see the same two columns like you can see on the figure below.

Figure 8 - Query a few selected columns

Task #7:

Query data from the table that meets a criteria by using the SELECT FROM WHERE SQL statement!

With the previous queries you didn't do any selection on the data, you displayed them. But using the 'WHERE' after 'SELECT' (Figure 9), you can display only some data that meets a criteria. In this example you will query the robots that have got at least one sensor equipped on them. So this please type 'SELECT * FROM Robots WHERE Number_of_sensors > 0'. This means that the results table will only contain robots where the value of 'Number_of_sensors' is bigger than zero. In case you click on 'Execute' the result table will show up on your computer.

Figure 9 - Query all robots with sensors