Using SQL code to create tables

In relational databases, tables have to be created before you can add data to them.
The table definition that you create is referred to as the Schema of the table.
The schema can contain many different properties of the table, but in its simplest form you only need to specify a name for the table and a list of the column names and the data types for each of those columns.
we will create a couple of simple tables which we will use in a later lesson.
We will also show how the SN7577 and SN7577_Text tables were created and populated from csv files.
Lets create a couple of simple tables first.
The code creates a table called Animals with two columns
the first is called Id_A and is an Integer and the second is called Name and is of type Text.

Note:

As this table already exists in the database, it will fail if you try to run complaining that the table already exists.
Instead of running it as-is, change the table name slightly (perhaps include your initials as a prefix).

CREATE TABLE Animals_ro
(     Id_A Integer,
      Name Text
);

When you run this in the DB Browser application, no results are returned, but you should see the table Animals in the left pane under tables and in the messages pane at the bottom there should be a message saying the query was successful.

Exericse:

Make sure you ran the last code to create the Animals table:

CREATE TABLE Animals_ro
(     Id_A Integer,
      Name Text
);

Create another table called Animals_Eat with two columns, Id_E which is an integer and Eats which is text.

Solution:

The code is the same as above
The code for the second table only requires slight modifications

CREATE TABLE Animals_Eat
(   Id_E Integer,
    Eats
);

At this point we have two empty tables. The next step is to insert data into them.
To do this we can use the INSERT INTO command
The code below will insert seven rows into the Animals table.

INSERT INTO Animals (Id_A, Name)
 VALUES   (1, "Elephant"),
          (2, "Monkey"),
          (3, "Cat"),
          (4, "Dog"),
          (8, "Goat"),
          (10, "Pig"),
          (11, "Mouse")

Exercise:

Add the following rows of data to the Animal_Eats table

Solution:

In this solution, the column list has been omitted. This is permissible if the values being inserted are in the same order as they have been defined in the table. In general though it is recommended that the column names are explicitly listed.

INSERT INTO Animals_Eat 
 VALUES   (1, "Hay"),
          (3, "Fish"),
          (4, "Meat"),
          (6, "Goldfish Food"),
          (7, "Lettuse"),
          (8, "Flowers"),
          (10, "Anything")

For small tables defining them and populating them with data in this way may be acceptable.
But for larger tables this approach not only to defining the tables but adding potentially thousands of rows of data can be somewhat impractical.

Creating tables from other tables

Whenever you write a Select query and run it, the results are always in the form of a table.
In the results pane, you can see the column names and the rows of data in the results.
This provides a very easy way of creating new table based on the results of a query.
The following query selects a few of the columns from the SN7577 table

SELECT Q1,
       Q2,
       Q3,
       Sex,
       Age,
       Class
FROM SN7577;

If I want to make the results of this query into a new table,
I can do so by simply prefixing the Select with Create Table NewTablename AS like this:

CREATE Table SN7577_reduced AS
SELECT Q1,
       Q2,
       Q3,
       Sex,
       Age,
       Class
FROM SN7577;

Using the DB Browser application to create tables

So far we have created and populated tables from scratch or created tables from existing tables.
But initially your data is likely to be external to the relational database system in a set of simple files.
Typically in CSV or Tab delimited format.
All relational database systems will have some utitity which will allow you to import such files into tables in the database.
DB Browser application has a nice GUI (Graphical Use Interface) to allow you to do this.
Both the SN7577 and SN7577_Text tables that we have been using were created in the DB Browser application by importing a csv (comma seperated values) or a Tab delimited file containing the data.
For large datasets this is a very common approach

From the File menu select Import and then Table from CSV file. This will start the Import CSV file wizard and you will be asked to select the file of data you wish to import from a standard Windows file open dialog.
After you have selected the file, you will be shown the Import CSV file window which will allow you to set a name for the table (the default is taken from the filename). You will see the first few rows of the data and there are a few options which can be changed if needs be.

In our case all of the options are correctly set.
If your file was in Tab delimited format, you would need to change the Field separator option to Tab.
If youe file did not have a header row with column names you would un-check the appropriate box and DB Browser will allocate names for the columns.

When you click OK, a table will be created and the data loaded into the table.

Unfortunately this Import wizard in DB Browser does not do, or allow you to do everything that you might want when creating a table.
The most obvious problem is that we were not allowed to specify the data types to be associated with each of the columns in the table.
If you go to the table in the Database Structure tab and click the > you will see all of the fields and they are all listed as Text fields.
In order to put this right we will need to modify the table structure.
Select the newly created table in Database Structure tab and click the Modify Table button in the toolbar.
The Edit Table Definition dialog will appear.

In the picture above, the first column has been changed from Text to INTEGER. They all have to be changed to INTEGER apart from the last column which is of type REAL.
Notice that the bottom pane in the Window shows the SQL DDL statement that would create the table that you modifying.
When you change one of the columns from TEXT to INTEGER, this is immediately reflected in the Create Table statement.
It is slightly misleading because in fact we are modifying an existing table and in SQL-speak, this would be an Alter Table… statement. However it does illustrate quite well the fact that whatever you do in the GUI, it is essentially translated into an SQL statement and executed.
In addition to changing the data types there are several other options which can be set when you are creating of modifying a table.
For our tables we don’t need to make use of them but for completeness we will describe what they are:

PK - Primary Key, a unique identifier for the row. In the SN7577 table, there is no column which can act as a unique identifier for the row as a whole.

AI - Auto Increment. This isn’t really applicable to tables created in this way, i.e. the creation of the schema immediatly followed by loading data from a file. It is usally used to generate uniques values for a column which could then act as a primary key. If you have an autoinc column in a table, when you insert values you would not supply a value for the column as SQLite will automatically provide a value for each row added.

Not Null - If this is checked then it means that there must be a value for each row in this column. If it is not set and there is no value provided in the data then it will be set to NULL which means ‘I know nothing about what should be here’. (Not the string NULL but the NULL value)

In real datasets missing values are quite common and we have already looked at ways of dealing with them when they occur in tables. If you you were un-check this box and the data did have missing values for this column, the reord from the file would be rejected and the load of the file will fail.

U - Unique. This allows you to say that the contents of the columnn, which is not the primary key column has to have unique values in it. Like Allow Null this is another way of providing some data validation as the data is imported. Although it doesn’t really apply with the DB Browser import wizard as the data is imported before you are allowed to set this option

Default - This is used in conjunction with Not Null, if a value is not provided in the dataset, then if provided, the default value for that column will be used.

Check - This allows you to specify a constraint on the vlaues entered for the column. You could restrict the range of values or compare the value with other columns values.

These three options, Not Null, Unique and Default , need to be used with caution and certainly their use needs to be fully documented and explained.

Using SQL code to create views

In addition to tables all relational database systems have the concept of Views.
Views are based on tables. In the same way that we were able to create a table based on a Select query, we can create a View in the same way. You just replace Table with View.

CREATE VIEW SN7577_reduced AS
SELECT Q1,
       Q2,
       Q3,
       Sex,
       Age,
       Class
FROM SN7577;

Tables and Views are so closely related that if I try to run the code above, although I have changed Table to View I will get an error complaining that the Table already exists.

It is common practice when creating Views to indicate somewhere in the the name that it is in fact a View. e.g. vSN7577_reduced or SN7577_reduced_v.

Although tables and views can be used almost interchangeably in SELECT queries it is important to note that a view unlike a table contains no data.
It is simply the SQL statement needed to produce that data from the underlying data. This means that when you use a view there is the overhead of having to run this SQL first.
Although in practice the Database system will combine the SQL required by the View and the other SQL in your query so as to optimise how the SQL is executed.
The advantage of using Views is that it allows you to restrict how you see a table. In the example we used above it may be far easier to work with only the 6 columns that we need from the full SN7577 table rather than the full table with 202 columns.
A View isn’t restricted to simple SELECT statements it can be the result of aggregations and JOINS as well.
This can help reduce the complexity of queries based on the view and so aid readability.

key points

Database tables can be created using the DDL command ‘Create Table’
They can be populated using the ‘INSERT INTO ‘ command
The SQLite plugin allows you to create a table and import data into it in one step
There are many options availabele to the ‘CREATE TABLE command which allows greater control over how or what data can be loaded into thetable
A View can be treated just like a table in a query
A View does not contain data like a rtable does, only the instructions on how to get the data

LS0tCnRpdGxlOiAiQ3JlYXRpbmcgdGFibGVzIGFuZCB2aWV3cyIKb3V0cHV0OiBodG1sX25vdGVib29rCi0tLQoKIyBVc2luZyBTUUwgY29kZSB0byBjcmVhdGUgdGFibGVzCgoqIEluIHJlbGF0aW9uYWwgZGF0YWJhc2VzLCBgdGFibGVzYCBoYXZlIHRvIGJlIGNyZWF0ZWQgYmVmb3JlIHlvdSBjYW4gYWRkIGRhdGEgdG8gdGhlbS4KKiBUaGUgYHRhYmxlIGRlZmluaXRpb25gIHRoYXQgeW91IGNyZWF0ZSBpcyByZWZlcnJlZCB0byBhcyB0aGUgYFNjaGVtYWAgb2YgdGhlIHRhYmxlLgoKKiBUaGUgYHNjaGVtYWAgY2FuIGNvbnRhaW4gbWFueSBkaWZmZXJlbnQgcHJvcGVydGllcyBvZiB0aGUgdGFibGUsIGJ1dCBpbiBpdHMgc2ltcGxlc3QgZm9ybSB5b3Ugb25seSBuZWVkIHRvIHNwZWNpZnkgYSBgbmFtZSBmb3IgdGhlIHRhYmxlYCBhbmQgYSBgbGlzdCBvZiB0aGUgY29sdW1uIG5hbWVzYCBhbmQgdGhlIGBkYXRhIHR5cGVzYCBmb3IgZWFjaCBvZiB0aG9zZSBjb2x1bW5zLgoKKiB3ZSB3aWxsIGNyZWF0ZSBhIGNvdXBsZSBvZiBzaW1wbGUgdGFibGVzIHdoaWNoIHdlIHdpbGwgdXNlIGluIGEgbGF0ZXIgbGVzc29uLiAKKiBXZSB3aWxsIGFsc28gc2hvdyBob3cgdGhlIFNONzU3NyBhbmQgU043NTc3X1RleHQgdGFibGVzIHdlcmUgY3JlYXRlZCBhbmQgcG9wdWxhdGVkIGZyb20gY3N2IGZpbGVzLgoKKiBMZXRzIGNyZWF0ZSBhIGNvdXBsZSBvZiBzaW1wbGUgdGFibGVzIGZpcnN0LiAKCiogVGhlIGNvZGUgY3JlYXRlcyBhIHRhYmxlIGNhbGxlZCBgQW5pbWFsc2Agd2l0aCB0d28gY29sdW1ucwoKKiB0aGUgZmlyc3QgaXMgY2FsbGVkIGBJZF9BYCBhbmQgaXMgYW4gYEludGVnZXJgIGFuZCB0aGUgc2Vjb25kIGlzIGNhbGxlZCBgTmFtZWAgYW5kIGlzIG9mIHR5cGUgYFRleHRgLgoKIyMjIE5vdGU6CiogQXMgdGhpcyB0YWJsZSBhbHJlYWR5IGV4aXN0cyBpbiB0aGUgZGF0YWJhc2UsIGl0IHdpbGwgZmFpbCBpZiB5b3UgdHJ5IHRvIHJ1biBjb21wbGFpbmluZyB0aGF0IHRoZSB0YWJsZSBhbHJlYWR5IGV4aXN0cy4gCiogSW5zdGVhZCBvZiBydW5uaW5nIGl0IGFzLWlzLCBgY2hhbmdlIHRoZSB0YWJsZSBuYW1lYCBzbGlnaHRseSAocGVyaGFwcyBpbmNsdWRlIHlvdXIgaW5pdGlhbHMgYXMgYSBwcmVmaXgpLgoKYGBgc3FsCkNSRUFURSBUQUJMRSBBbmltYWxzX3JvCiggICAgIElkX0EgSW50ZWdlciwKICAgICAgTmFtZSBUZXh0Cik7CmBgYAoqIFdoZW4geW91IHJ1biB0aGlzIGluIHRoZSBEQiBCcm93c2VyIGFwcGxpY2F0aW9uLCBubyByZXN1bHRzIGFyZSByZXR1cm5lZCwgYnV0IHlvdSBzaG91bGQgc2VlIHRoZSB0YWJsZSBgQW5pbWFsc2AgaW4gdGhlIGxlZnQgcGFuZSB1bmRlciB0YWJsZXMgYW5kIGluIHRoZSBtZXNzYWdlcyBwYW5lIGF0IHRoZSBib3R0b20gdGhlcmUgc2hvdWxkIGJlIGEgbWVzc2FnZSBzYXlpbmcgdGhlIHF1ZXJ5IHdhcyBzdWNjZXNzZnVsLgoKIyMgRXhlcmljc2U6CjEuIE1ha2Ugc3VyZSB5b3UgcmFuIHRoZSBsYXN0IGNvZGUgdG8gY3JlYXRlIHRoZSBBbmltYWxzIHRhYmxlOgoKYGBgc3FsCkNSRUFURSBUQUJMRSBBbmltYWxzX3JvCiggICAgIElkX0EgSW50ZWdlciwKICAgICAgTmFtZSBUZXh0Cik7CmBgYAoyLiBDcmVhdGUgYW5vdGhlciB0YWJsZSBjYWxsZWQgYEFuaW1hbHNfRWF0YCB3aXRoIHR3byBjb2x1bW5zLCBgSWRfRWAgd2hpY2ggaXMgYW4gYGludGVnZXJgIGFuZCBgRWF0c2Agd2hpY2ggaXMgYHRleHRgLgoKIyMjIFNvbHV0aW9uOgoxLiBUaGUgY29kZSBpcyB0aGUgc2FtZSBhcyBhYm92ZQoyLiBUaGUgY29kZSBmb3IgdGhlIHNlY29uZCB0YWJsZSBvbmx5IHJlcXVpcmVzIHNsaWdodCBtb2RpZmljYXRpb25zCgpgYGBzcWwKQ1JFQVRFIFRBQkxFIEFuaW1hbHNfRWF0CiggICBJZF9FIEludGVnZXIsCiAgICBFYXRzCik7CgpgYGAKLS0tCgoqIEF0IHRoaXMgcG9pbnQgd2UgaGF2ZSB0d28gZW1wdHkgdGFibGVzLiBUaGUgbmV4dCBzdGVwIGlzIHRvIGluc2VydCBkYXRhIGludG8gdGhlbS4KCiogVG8gZG8gdGhpcyB3ZSBjYW4gdXNlIHRoZSBgSU5TRVJUIElOVE9gIGNvbW1hbmQKCiogVGhlIGNvZGUgYmVsb3cgd2lsbCBpbnNlcnQgc2V2ZW4gcm93cyBpbnRvIHRoZSBBbmltYWxzIHRhYmxlLgoKYGBgc3FsCklOU0VSVCBJTlRPIEFuaW1hbHMgKElkX0EsIE5hbWUpCiBWQUxVRVMgICAoMSwgIkVsZXBoYW50IiksCiAgICAgICAgICAoMiwgIk1vbmtleSIpLAogICAgICAgICAgKDMsICJDYXQiKSwKICAgICAgICAgICg0LCAiRG9nIiksCiAgICAgICAgICAoOCwgIkdvYXQiKSwKICAgICAgICAgICgxMCwgIlBpZyIpLAogICAgICAgICAgKDExLCAiTW91c2UiKQoKYGBgCgojIyBFeGVyY2lzZToKMS4gQWRkIHRoZSBmb2xsb3dpbmcgcm93cyBvZiBkYXRhIHRvIHRoZSBBbmltYWxfRWF0cyB0YWJsZQoKIVtpbWFnZV0oaW1hZ2VzL1NRTF8wN19BbmltYWxzX2VhdF9leC5wbmcpCgojIyMgU29sdXRpb246CkluIHRoaXMgc29sdXRpb24sIHRoZSBjb2x1bW4gbGlzdCBoYXMgYmVlbiBvbWl0dGVkLiBUaGlzIGlzIHBlcm1pc3NpYmxlIGlmIHRoZSB2YWx1ZXMgYmVpbmcgaW5zZXJ0ZWQgYXJlIGluIHRoZSBzYW1lIG9yZGVyIGFzIHRoZXkgaGF2ZSBiZWVuIGRlZmluZWQgaW4gdGhlIHRhYmxlLiBJbiBnZW5lcmFsIHRob3VnaCBpdCBpcyByZWNvbW1lbmRlZCB0aGF0IHRoZSBjb2x1bW4gbmFtZXMgYXJlIGV4cGxpY2l0bHkgbGlzdGVkLgoKYGBgc3FsCklOU0VSVCBJTlRPIEFuaW1hbHNfRWF0IAogVkFMVUVTICAgKDEsICJIYXkiKSwKICAgICAgICAgICgzLCAiRmlzaCIpLAogICAgICAgICAgKDQsICJNZWF0IiksCiAgICAgICAgICAoNiwgIkdvbGRmaXNoIEZvb2QiKSwKICAgICAgICAgICg3LCAiTGV0dHVzZSIpLAogICAgICAgICAgKDgsICJGbG93ZXJzIiksCiAgICAgICAgICAoMTAsICJBbnl0aGluZyIpCmBgYAoKKiBGb3Igc21hbGwgdGFibGVzIGRlZmluaW5nIHRoZW0gYW5kIHBvcHVsYXRpbmcgdGhlbSB3aXRoIGRhdGEgaW4gdGhpcyB3YXkgbWF5IGJlIGFjY2VwdGFibGUuIAoKKiBCdXQgZm9yIGxhcmdlciB0YWJsZXMgdGhpcyBhcHByb2FjaCBub3Qgb25seSB0byBkZWZpbmluZyB0aGUgdGFibGVzIGJ1dCBhZGRpbmcgcG90ZW50aWFsbHkgdGhvdXNhbmRzIG9mIHJvd3Mgb2YgZGF0YSBjYW4gYmUgc29tZXdoYXQgaW1wcmFjdGljYWwuCgoKIyMgQ3JlYXRpbmcgdGFibGVzIGZyb20gb3RoZXIgdGFibGVzCgoqIFdoZW5ldmVyIHlvdSB3cml0ZSBhIGBTZWxlY3RgIHF1ZXJ5IGFuZCBydW4gaXQsIHRoZSByZXN1bHRzIGFyZSBhbHdheXMgaW4gdGhlIGZvcm0gb2YgYSBgdGFibGVgLiAKCiogSW4gdGhlIHJlc3VsdHMgcGFuZSwgeW91IGNhbiBzZWUgdGhlIGNvbHVtbiBuYW1lcyBhbmQgdGhlIHJvd3Mgb2YgZGF0YSBpbiB0aGUgcmVzdWx0cy4KCiogVGhpcyBwcm92aWRlcyBhIHZlcnkgZWFzeSB3YXkgb2YgY3JlYXRpbmcgbmV3IHRhYmxlIGJhc2VkIG9uIHRoZSByZXN1bHRzIG9mIGEgcXVlcnkuCgoqIFRoZSBmb2xsb3dpbmcgcXVlcnkgc2VsZWN0cyBhIGZldyBvZiB0aGUgY29sdW1ucyBmcm9tIHRoZSBTTjc1NzcgdGFibGUKCmBgYHNxbApTRUxFQ1QgUTEsCiAgICAgICBRMiwKICAgICAgIFEzLAogICAgICAgU2V4LAogICAgICAgQWdlLAogICAgICAgQ2xhc3MKRlJPTSBTTjc1Nzc7CgpgYGAKCiogSWYgSSB3YW50IHRvIG1ha2UgdGhlIHJlc3VsdHMgb2YgdGhpcyBxdWVyeSBpbnRvIGEgbmV3IHRhYmxlLCAKKiBJIGNhbiBkbyBzbyBieSBzaW1wbHkgcHJlZml4aW5nIHRoZSBgU2VsZWN0YCB3aXRoIGBDcmVhdGUgVGFibGUgTmV3VGFibGVuYW1lIEFTYCBsaWtlIHRoaXM6CgpgYGBzcWwKQ1JFQVRFIFRhYmxlIFNONzU3N19yZWR1Y2VkIEFTClNFTEVDVCBRMSwKICAgICAgIFEyLAogICAgICAgUTMsCiAgICAgICBTZXgsCiAgICAgICBBZ2UsCiAgICAgICBDbGFzcwpGUk9NIFNONzU3NzsKCmBgYAojIyBVc2luZyB0aGUgREIgQnJvd3NlciBhcHBsaWNhdGlvbiB0byBjcmVhdGUgdGFibGVzCgoqIFNvIGZhciB3ZSBoYXZlIGNyZWF0ZWQgYW5kIHBvcHVsYXRlZCB0YWJsZXMgZnJvbSBzY3JhdGNoIG9yIGNyZWF0ZWQgdGFibGVzIGZyb20gZXhpc3RpbmcgdGFibGVzLiAKKiBCdXQgaW5pdGlhbGx5IHlvdXIgZGF0YSBpcyBsaWtlbHkgdG8gYmUgZXh0ZXJuYWwgdG8gdGhlIHJlbGF0aW9uYWwgZGF0YWJhc2Ugc3lzdGVtIGluIGEgc2V0IG9mIHNpbXBsZSBmaWxlcy4gCiogVHlwaWNhbGx5IGluIGBDU1ZgIG9yIGBUYWIgZGVsaW1pdGVkYCBmb3JtYXQuCgoqIEFsbCByZWxhdGlvbmFsIGRhdGFiYXNlIHN5c3RlbXMgd2lsbCBoYXZlIHNvbWUgdXRpdGl0eSB3aGljaCB3aWxsIGFsbG93IHlvdSB0byBpbXBvcnQgc3VjaCBmaWxlcyBpbnRvIHRhYmxlcyBpbiB0aGUgZGF0YWJhc2UuIAoqIERCIEJyb3dzZXIgYXBwbGljYXRpb24gaGFzIGEgbmljZSBHVUkgKEdyYXBoaWNhbCBVc2UgSW50ZXJmYWNlKSB0byBhbGxvdyB5b3UgdG8gZG8gdGhpcy4KCiogQm90aCB0aGUgYFNONzU3NyBhbmQgU043NTc3X1RleHQgdGFibGVzYCB0aGF0IHdlIGhhdmUgYmVlbiB1c2luZyB3ZXJlIGNyZWF0ZWQgaW4gdGhlIERCIEJyb3dzZXIgYXBwbGljYXRpb24gYnkgaW1wb3J0aW5nIGEgYGNzdmAgKGNvbW1hIHNlcGVyYXRlZCB2YWx1ZXMpIG9yIGEgYFRhYmAgZGVsaW1pdGVkIGZpbGUgY29udGFpbmluZyB0aGUgZGF0YS4KCiogRm9yIGxhcmdlIGRhdGFzZXRzIHRoaXMgaXMgYSB2ZXJ5IGNvbW1vbiBhcHByb2FjaAoKMS4gRnJvbSB0aGUgRmlsZSBtZW51IHNlbGVjdCBJbXBvcnQgYW5kIHRoZW4gYFRhYmxlIGZyb20gQ1NWIGZpbGVgLiAKVGhpcyB3aWxsIHN0YXJ0IHRoZSBgSW1wb3J0IENTViBmaWxlYCB3aXphcmQgYW5kIHlvdSB3aWxsIGJlIGFza2VkIHRvIHNlbGVjdCB0aGUgZmlsZSBvZiBkYXRhIHlvdSB3aXNoIHRvIGltcG9ydCBmcm9tIGEgc3RhbmRhcmQgV2luZG93cyBmaWxlIG9wZW4gZGlhbG9nLgoKMi4gQWZ0ZXIgeW91IGhhdmUgc2VsZWN0ZWQgdGhlIGZpbGUsIHlvdSB3aWxsIGJlIHNob3duIHRoZSBgSW1wb3J0IENTViBmaWxlYCB3aW5kb3cgd2hpY2ggd2lsbCBhbGxvdyB5b3UgdG8gc2V0IGEgbmFtZSBmb3IgdGhlIHRhYmxlICh0aGUgZGVmYXVsdCBpcyB0YWtlbiBmcm9tIHRoZSBmaWxlbmFtZSkuIFlvdSB3aWxsIHNlZSB0aGUgZmlyc3QgZmV3IHJvd3Mgb2YgdGhlIGRhdGEgYW5kIHRoZXJlIGFyZSBhIGZldyBvcHRpb25zIHdoaWNoIGNhbiBiZSBjaGFuZ2VkIGlmIG5lZWRzIGJlLgoKIVtpbWFnZV0oaW1hZ2VzL1NRTF8wN19JbXBvcnRfd2l6YXJkXzAxLnBuZykKCiogSW4gb3VyIGNhc2UgYWxsIG9mIHRoZSBvcHRpb25zIGFyZSBjb3JyZWN0bHkgc2V0LiAKKiBJZiB5b3VyIGZpbGUgd2FzIGluIFRhYiBkZWxpbWl0ZWQgZm9ybWF0LCB5b3Ugd291bGQgbmVlZCB0byBjaGFuZ2UgdGhlIGBGaWVsZCBzZXBhcmF0b3JgIG9wdGlvbiB0byBgVGFiYC4gCiogSWYgeW91ZSBmaWxlIGRpZCBub3QgaGF2ZSBhIGhlYWRlciByb3cgd2l0aCBjb2x1bW4gbmFtZXMgeW91IHdvdWxkIHVuLWNoZWNrIHRoZSBhcHByb3ByaWF0ZSBib3ggYW5kIERCIEJyb3dzZXIgd2lsbCBhbGxvY2F0ZSBuYW1lcyBmb3IgdGhlIGNvbHVtbnMuCgoxLiBXaGVuIHlvdSBjbGljayBPSywgYSB0YWJsZSB3aWxsIGJlIGNyZWF0ZWQgYW5kIHRoZSBkYXRhIGxvYWRlZCBpbnRvIHRoZSB0YWJsZS4KCiogVW5mb3J0dW5hdGVseSB0aGlzIEltcG9ydCB3aXphcmQgaW4gREIgQnJvd3NlciBkb2VzIG5vdCBkbywgb3IgYWxsb3cgeW91IHRvIGRvIGV2ZXJ5dGhpbmcgdGhhdCB5b3UgbWlnaHQgd2FudCB3aGVuIGNyZWF0aW5nIGEgdGFibGUuIAoqIFRoZSBtb3N0IG9idmlvdXMgcHJvYmxlbSBpcyB0aGF0IHdlIHdlcmUgbm90IGFsbG93ZWQgdG8gc3BlY2lmeSB0aGUgZGF0YSB0eXBlcyB0byBiZSBhc3NvY2lhdGVkIHdpdGggZWFjaCBvZiB0aGUgY29sdW1ucyBpbiB0aGUgdGFibGUuIAoqIElmIHlvdSBnbyB0byB0aGUgdGFibGUgaW4gdGhlIGBEYXRhYmFzZSBTdHJ1Y3R1cmVgIHRhYiBhbmQgY2xpY2sgdGhlIGA+YCB5b3Ugd2lsbCBzZWUgYWxsIG9mIHRoZSBmaWVsZHMgYW5kIHRoZXkgYXJlIGFsbCBsaXN0ZWQgYXMgVGV4dCBmaWVsZHMuIAoqIEluIG9yZGVyIHRvIHB1dCB0aGlzIHJpZ2h0IHdlIHdpbGwgbmVlZCB0byBtb2RpZnkgdGhlIHRhYmxlIHN0cnVjdHVyZS4KCiogU2VsZWN0IHRoZSBuZXdseSBjcmVhdGVkIHRhYmxlIGluIERhdGFiYXNlIFN0cnVjdHVyZSB0YWIgYW5kIGNsaWNrIHRoZSBgTW9kaWZ5IFRhYmxlYCBidXR0b24gaW4gdGhlIHRvb2xiYXIuIAoqIFRoZSBgRWRpdCBUYWJsZSBEZWZpbml0aW9uYCBkaWFsb2cgd2lsbCBhcHBlYXIuCgohW2ltYWdlXShpbWFnZXMvU1FMXzA3X0ltcG9ydF9XaXphcmRfMDIucG5nKQoKCiogSW4gdGhlIHBpY3R1cmUgYWJvdmUsIHRoZSBmaXJzdCBjb2x1bW4gaGFzIGJlZW4gY2hhbmdlZCBmcm9tIGBUZXh0YCB0byBgSU5URUdFUmAuIFRoZXkgYWxsIGhhdmUgdG8gYmUgY2hhbmdlZCB0byBgSU5URUdFUmAgYXBhcnQgZnJvbSB0aGUgbGFzdCBjb2x1bW4gd2hpY2ggaXMgb2YgdHlwZSBgUkVBTGAuCgoqIE5vdGljZSB0aGF0IHRoZSBib3R0b20gcGFuZSBpbiB0aGUgV2luZG93IHNob3dzIHRoZSBgU1FMIERETGAgc3RhdGVtZW50IHRoYXQgd291bGQgY3JlYXRlIHRoZSB0YWJsZSB0aGF0IHlvdSBtb2RpZnlpbmcuIAoqIFdoZW4geW91IGNoYW5nZSBvbmUgb2YgdGhlIGNvbHVtbnMgZnJvbSBgVEVYVGAgdG8gYElOVEVHRVJgLCB0aGlzIGlzIGltbWVkaWF0ZWx5IHJlZmxlY3RlZCBpbiB0aGUgQ3JlYXRlIFRhYmxlIHN0YXRlbWVudC4gCiogSXQgaXMgc2xpZ2h0bHkgbWlzbGVhZGluZyBiZWNhdXNlIGluIGZhY3Qgd2UgYXJlIG1vZGlmeWluZyBhbiBleGlzdGluZyB0YWJsZSBhbmQgaW4gU1FMLXNwZWFrLCB0aGlzIHdvdWxkIGJlIGFuIGBBbHRlciBUYWJsZWDigKYgc3RhdGVtZW50LiBIb3dldmVyIGl0IGRvZXMgaWxsdXN0cmF0ZSBxdWl0ZSB3ZWxsIHRoZSBmYWN0IHRoYXQgd2hhdGV2ZXIgeW91IGRvIGluIHRoZSBHVUksIGl0IGlzIGVzc2VudGlhbGx5IHRyYW5zbGF0ZWQgaW50byBhbiBTUUwgc3RhdGVtZW50IGFuZCBleGVjdXRlZC4KCiogSW4gYWRkaXRpb24gdG8gY2hhbmdpbmcgdGhlIGRhdGEgdHlwZXMgdGhlcmUgYXJlIHNldmVyYWwgb3RoZXIgb3B0aW9ucyB3aGljaCBjYW4gYmUgc2V0IHdoZW4geW91IGFyZSBjcmVhdGluZyBvZiBtb2RpZnlpbmcgYSB0YWJsZS4gCiogRm9yIG91ciB0YWJsZXMgd2UgZG9u4oCZdCBuZWVkIHRvIG1ha2UgdXNlIG9mIHRoZW0gYnV0IGZvciBjb21wbGV0ZW5lc3Mgd2Ugd2lsbCBkZXNjcmliZSB3aGF0IHRoZXkgYXJlOgoKCioqUEsqKiAtIGBQcmltYXJ5IEtleWAsIGEgdW5pcXVlIGlkZW50aWZpZXIgZm9yIHRoZSByb3cuIEluIHRoZSBTTjc1NzcgdGFibGUsIHRoZXJlIGlzIG5vIGNvbHVtbiB3aGljaCBjYW4gYWN0IGFzIGEgdW5pcXVlIGlkZW50aWZpZXIgZm9yIHRoZSByb3cgYXMgYSB3aG9sZS4KCioqQUkqKiAtIGBBdXRvIEluY3JlbWVudGAuIFRoaXMgaXNu4oCZdCByZWFsbHkgYXBwbGljYWJsZSB0byB0YWJsZXMgY3JlYXRlZCBpbiB0aGlzIHdheSwgaS5lLiB0aGUgY3JlYXRpb24gb2YgdGhlIHNjaGVtYSBpbW1lZGlhdGx5IGZvbGxvd2VkIGJ5IGxvYWRpbmcgZGF0YSBmcm9tIGEgZmlsZS4gSXQgaXMgdXNhbGx5IHVzZWQgdG8gZ2VuZXJhdGUgdW5pcXVlcyB2YWx1ZXMgZm9yIGEgY29sdW1uIHdoaWNoIGNvdWxkIHRoZW4gYWN0IGFzIGEgcHJpbWFyeSBrZXkuIElmIHlvdSBoYXZlIGFuIGF1dG9pbmMgY29sdW1uIGluIGEgdGFibGUsIHdoZW4geW91IGluc2VydCB2YWx1ZXMgeW91IHdvdWxkIG5vdCBzdXBwbHkgYSB2YWx1ZSBmb3IgdGhlIGNvbHVtbiBhcyBTUUxpdGUgd2lsbCBhdXRvbWF0aWNhbGx5IHByb3ZpZGUgYSB2YWx1ZSBmb3IgZWFjaCByb3cgYWRkZWQuCgoqKk5vdCBOdWxsKiogLSBJZiB0aGlzIGlzIGNoZWNrZWQgdGhlbiBpdCBtZWFucyB0aGF0IHRoZXJlIG11c3QgYmUgYSB2YWx1ZSBmb3IgZWFjaCByb3cgaW4gdGhpcyBjb2x1bW4uIElmIGl0IGlzIG5vdCBzZXQgYW5kIHRoZXJlIGlzIG5vIHZhbHVlIHByb3ZpZGVkIGluIHRoZSBkYXRhIHRoZW4gaXQgd2lsbCBiZSBzZXQgdG8gYE5VTExgIHdoaWNoIG1lYW5zIOKAmEkga25vdyBub3RoaW5nIGFib3V0IHdoYXQgc2hvdWxkIGJlIGhlcmXigJkuIChOb3QgdGhlIHN0cmluZyBgTlVMTGAgYnV0IHRoZSBgTlVMTCB2YWx1ZWApCgogICogSW4gcmVhbCBkYXRhc2V0cyBtaXNzaW5nIHZhbHVlcyBhcmUgcXVpdGUgY29tbW9uIGFuZCB3ZSBoYXZlIGFscmVhZHkgbG9va2VkIGF0IHdheXMgb2YgZGVhbGluZyB3aXRoIHRoZW0gd2hlbiB0aGV5IG9jY3VyIGluIHRhYmxlcy4gSWYgeW91IHlvdSB3ZXJlIHVuLWNoZWNrIHRoaXMgYm94IGFuZCB0aGUgZGF0YSBkaWQgaGF2ZSBtaXNzaW5nIHZhbHVlcyBmb3IgdGhpcyBjb2x1bW4sIHRoZSByZW9yZCBmcm9tIHRoZSBmaWxlIHdvdWxkIGJlIHJlamVjdGVkIGFuZCB0aGUgbG9hZCBvZiB0aGUgZmlsZSB3aWxsIGZhaWwuCgoqKlUqKiAtIGBVbmlxdWVgLiBUaGlzIGFsbG93cyB5b3UgdG8gc2F5IHRoYXQgdGhlIGNvbnRlbnRzIG9mIHRoZSBjb2x1bW5uLCB3aGljaCBpcyBub3QgdGhlIHByaW1hcnkga2V5IGNvbHVtbiBoYXMgdG8gaGF2ZSB1bmlxdWUgdmFsdWVzIGluIGl0LiBMaWtlIGBBbGxvdyBOdWxsYCB0aGlzIGlzIGFub3RoZXIgd2F5IG9mIHByb3ZpZGluZyBzb21lIGRhdGEgdmFsaWRhdGlvbiBhcyB0aGUgZGF0YSBpcyBpbXBvcnRlZC4gQWx0aG91Z2ggaXQgZG9lc27igJl0IHJlYWxseSBhcHBseSB3aXRoIHRoZSBEQiBCcm93c2VyIGltcG9ydCB3aXphcmQgYXMgdGhlIGRhdGEgaXMgaW1wb3J0ZWQgYmVmb3JlIHlvdSBhcmUgYWxsb3dlZCB0byBzZXQgdGhpcyBvcHRpb24KCioqRGVmYXVsdCoqIC0gVGhpcyBpcyB1c2VkIGluIGNvbmp1bmN0aW9uIHdpdGggYE5vdCBOdWxsYCwgaWYgYSB2YWx1ZSBpcyBub3QgcHJvdmlkZWQgaW4gdGhlIGRhdGFzZXQsIHRoZW4gaWYgcHJvdmlkZWQsIHRoZSBkZWZhdWx0IHZhbHVlIGZvciB0aGF0IGNvbHVtbiB3aWxsIGJlIHVzZWQuCgoqKkNoZWNrKiogLSBUaGlzIGFsbG93cyB5b3UgdG8gc3BlY2lmeSBhIGNvbnN0cmFpbnQgb24gdGhlIHZsYXVlcyBlbnRlcmVkIGZvciB0aGUgY29sdW1uLiBZb3UgY291bGQgcmVzdHJpY3QgdGhlIHJhbmdlIG9mIHZhbHVlcyBvciBjb21wYXJlIHRoZSB2YWx1ZSB3aXRoIG90aGVyIGNvbHVtbnMgdmFsdWVzLgoKVGhlc2UgdGhyZWUgb3B0aW9ucywgYE5vdCBOdWxsYCwgYFVuaXF1ZWAgYW5kIGBEZWZhdWx0YCAsIG5lZWQgdG8gYmUgdXNlZCB3aXRoIGNhdXRpb24gYW5kIGNlcnRhaW5seSB0aGVpciB1c2UgbmVlZHMgdG8gYmUgZnVsbHkgZG9jdW1lbnRlZCBhbmQgZXhwbGFpbmVkLgoKIyMgVXNpbmcgU1FMIGNvZGUgdG8gY3JlYXRlIHZpZXdzCgoqIEluIGFkZGl0aW9uIHRvIHRhYmxlcyBhbGwgcmVsYXRpb25hbCBkYXRhYmFzZSBzeXN0ZW1zIGhhdmUgdGhlIGNvbmNlcHQgb2YgYFZpZXdzYC4gCiogVmlld3MgYXJlIGJhc2VkIG9uIHRhYmxlcy4gSW4gdGhlIHNhbWUgd2F5IHRoYXQgd2Ugd2VyZSBhYmxlIHRvIGNyZWF0ZSBhIHRhYmxlIGJhc2VkIG9uIGEgYFNlbGVjdGAgcXVlcnksIHdlIGNhbiBjcmVhdGUgYSBgVmlld2AgaW4gdGhlIHNhbWUgd2F5LiBZb3UganVzdCByZXBsYWNlIGBUYWJsZWAgd2l0aCBgVmlld2AuCgpgYGBzcWwKQ1JFQVRFIFZJRVcgU043NTc3X3JlZHVjZWQgQVMKU0VMRUNUIFExLAogICAgICAgUTIsCiAgICAgICBRMywKICAgICAgIFNleCwKICAgICAgIEFnZSwKICAgICAgIENsYXNzCkZST00gU043NTc3OwpgYGAKCiogVGFibGVzIGFuZCBWaWV3cyBhcmUgc28gY2xvc2VseSByZWxhdGVkIHRoYXQgaWYgSSB0cnkgdG8gcnVuIHRoZSBjb2RlIGFib3ZlLCBhbHRob3VnaCBJIGhhdmUgY2hhbmdlZCBUYWJsZSB0byBWaWV3IEkgd2lsbCBnZXQgYW4gYGVycm9yYCBjb21wbGFpbmluZyB0aGF0IHRoZSBgVGFibGVgIGFscmVhZHkgZXhpc3RzLgoKSXQgaXMgY29tbW9uIHByYWN0aWNlIHdoZW4gY3JlYXRpbmcgYFZpZXdzYCB0byBpbmRpY2F0ZSBzb21ld2hlcmUgaW4gdGhlIHRoZSBuYW1lIHRoYXQgaXQgaXMgaW4gZmFjdCBhIFZpZXcuIGUuZy4gYHZTTjc1NzdfcmVkdWNlZCBvciBTTjc1NzdfcmVkdWNlZF92YC4KCiogQWx0aG91Z2ggdGFibGVzIGFuZCB2aWV3cyBjYW4gYmUgdXNlZCBhbG1vc3QgaW50ZXJjaGFuZ2VhYmx5IGluIFNFTEVDVCBxdWVyaWVzIGl0IGlzIGltcG9ydGFudCB0byBub3RlIHRoYXQgYSBgdmlld2AgdW5saWtlIGEgdGFibGUgYGNvbnRhaW5zIG5vIGRhdGFgLiAKKiBJdCBpcyBzaW1wbHkgdGhlIFNRTCBzdGF0ZW1lbnQgbmVlZGVkIHRvIHByb2R1Y2UgdGhhdCBkYXRhIGZyb20gdGhlIHVuZGVybHlpbmcgZGF0YS4gVGhpcyBtZWFucyB0aGF0IHdoZW4geW91IHVzZSBhIHZpZXcgdGhlcmUgaXMgdGhlIG92ZXJoZWFkIG9mIGhhdmluZyB0byBydW4gdGhpcyBTUUwgZmlyc3QuIAoqIEFsdGhvdWdoIGluIHByYWN0aWNlIHRoZSBEYXRhYmFzZSBzeXN0ZW0gd2lsbCBjb21iaW5lIHRoZSBTUUwgcmVxdWlyZWQgYnkgdGhlIFZpZXcgYW5kIHRoZSBvdGhlciBTUUwgaW4geW91ciBxdWVyeSBzbyBhcyB0byBvcHRpbWlzZSBob3cgdGhlIFNRTCBpcyBleGVjdXRlZC4KCiogVGhlIGFkdmFudGFnZSBvZiB1c2luZyBgVmlld3NgIGlzIHRoYXQgaXQgYWxsb3dzIHlvdSB0byByZXN0cmljdCBob3cgeW91IHNlZSBhIHRhYmxlLiBJbiB0aGUgZXhhbXBsZSB3ZSB1c2VkIGFib3ZlIGl0IG1heSBiZSBmYXIgZWFzaWVyIHRvIHdvcmsgd2l0aCBvbmx5IHRoZSA2IGNvbHVtbnMgdGhhdCB3ZSBuZWVkIGZyb20gdGhlIGZ1bGwgU043NTc3IHRhYmxlIHJhdGhlciB0aGFuIHRoZSBmdWxsIHRhYmxlIHdpdGggMjAyIGNvbHVtbnMuCgoqIEEgYFZpZXdgIGlzbuKAmXQgcmVzdHJpY3RlZCB0byBzaW1wbGUgU0VMRUNUIHN0YXRlbWVudHMgaXQgY2FuIGJlIHRoZSByZXN1bHQgb2YgYWdncmVnYXRpb25zIGFuZCBKT0lOUyBhcyB3ZWxsLiAKCiogVGhpcyBjYW4gaGVscCByZWR1Y2UgdGhlIGNvbXBsZXhpdHkgb2YgcXVlcmllcyBiYXNlZCBvbiB0aGUgdmlldyBhbmQgc28gYWlkIHJlYWRhYmlsaXR5LgoKIyMga2V5IHBvaW50cwoqIERhdGFiYXNlIHRhYmxlcyBjYW4gYmUgY3JlYXRlZCB1c2luZyB0aGUgRERMIGNvbW1hbmQg4oCYQ3JlYXRlIFRhYmxl4oCZCgoqIFRoZXkgY2FuIGJlIHBvcHVsYXRlZCB1c2luZyB0aGUg4oCYSU5TRVJUIElOVE8g4oCYIGNvbW1hbmQKCiogVGhlIFNRTGl0ZSBwbHVnaW4gYWxsb3dzIHlvdSB0byBjcmVhdGUgYSB0YWJsZSBhbmQgaW1wb3J0IGRhdGEgaW50byBpdCBpbiBvbmUgc3RlcAoKKiBUaGVyZSBhcmUgbWFueSBvcHRpb25zIGF2YWlsYWJlbGUgdG8gdGhlIOKAmENSRUFURSBUQUJMRSBjb21tYW5kIHdoaWNoIGFsbG93cyBncmVhdGVyIGNvbnRyb2wgb3ZlciBob3cgb3Igd2hhdCBkYXRhIGNhbiBiZSBsb2FkZWQgaW50byB0aGV0YWJsZQoKKiBBIFZpZXcgY2FuIGJlIHRyZWF0ZWQganVzdCBsaWtlIGEgdGFibGUgaW4gYSBxdWVyeQoKKiBBIFZpZXcgZG9lcyBub3QgY29udGFpbiBkYXRhIGxpa2UgYSBydGFibGUgZG9lcywgb25seSB0aGUgaW5zdHJ1Y3Rpb25zIG9uIGhvdyB0byBnZXQgdGhlIGRhdGEKCgoK

Creating tables and views

Using SQL code to create tables

Note:

Exericse:

Solution:

Exercise:

Solution:

Creating tables from other tables

Using the DB Browser application to create tables

Using SQL code to create views

key points