1. How to work with tables
Welcome to this chapter about working with tables and relational and hierarchical data models. We will see how data can be structured hierarchically, why it is different from a standard table definition, and how to work with tables.
2. General SQL statements
More specifically, we will learn how to create a table with the definition of fields and their data types. We will insert data to a table, update the fields in a table, and see how to drop the complete table and how to delete the content of a table without deleting the table structure. Finally, we will change the structure of a table by adding a new column to the table or dropping a column in the table.
3. Creating a table
In the first SQL statement, we are creating a new table. To create a table, we start with the statement CREATE TABLE and the name of a table such as Person. In the definition of the table, the fields and their data types are defined. In this example, we define a field ID of the data type integer and a field Name of the data type character. Char 32 means to create a string with a maximum of 32 characters. NOT Null means, that the field needs to have any value and cannot be NULL. In general, the definition consists of the name of the field followed by the data type.
Common databases provide many different data types. This could be for example INT representing an integer or CHAR representing a string.
4. Insert and update a table
On the previous slide, we created a table. Now, we will learn how to insert data into this table and to update it. To insert data to a table we have to use the syntax INSERT INTO tableName VALUES, with the values for the fields in parentheses. An example could be to insert data to the table Person with value one and Smith.
Next, we are updating a field. This is done by using the statement UPDATE tableName SET field to a value where a condition is met. With this syntax we could update the name field of the row with ID equals one to the name Anderson.
5. Delete and drop a table
The next thing is to learn how to drop a table and how to delete the contents of a table. To delete, we have to use the syntax DELETE FROM tableName with a condition. With the example DELETE FROM person WHERE ID equals 1, we will delete the row for id equals 1. If the table has other rows, the delete statement will not affect them. If we want to drop the entire table, both the contents and the definition, we have to use DROP TABLE tableName. To drop the table Person we created before, we have to use DROP TABLE Person.
6. Change a table structure
The last statements we'll learn, can be used to change the structure of a table. There are many possibilities to modify a table, but in this lesson we are concentrating on the statements add and drop. To add a column we have to use ALTER TABLE tableName ADD columnName. This could be for example, ALTER TABLE Person ADD new, to add the column new to the table Person. To delete a column we have to use ALTER TABLE tableName DROP COLUMN columnName. As an example, we could delete the column old of the table Person with ALTER TABLE Person DROP COLUMN old.
7. Let's practice!
We have seen many different techniques that can be used to organize and to manipulate data tables. Now it's your turn.