Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, chances are that you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that table is the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.
Views can be considered as virtual tables. Generally speaking, a table has a set of definition that sets the structure of the table, and physically stores the data. A view also has a set of definitions, which is built on top of the table(s) or another view (s), and it does not physically store the data.
The syntax for creating a view is as follows:
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
“SQL Statement” can be any of the SQL statements we have discussed in this tutorial.
Indexes help us retrieve data from tables quicker. Let’s use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.
Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. Typical cases include the following:
Add a column
Drop a column
Change a column name
Change the data type for a column
Please note that the above is not an exhaustive list. There are other instances where ALTER TABLE is used to change the table structure, such as changing the primary key specification.
A primary key is used to uniquely identify each row on a table. It can either be part of the actual record itself, or it can be an artificial field (something that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, it is called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).