Creating a table

- [Instructor] Tables are created using the CREATE TABLE statement, and for this exercise we're going to use the memory database, and this is a special SQLite thing, it uses an in memory database. It's very fast, it's also not persistent, which means it creates an entirely new database every time you load the page in SID. It's useful for experimenting with SQL without having to worry about deleting tables between sessions, but everything happens in dynamic memory. The entire database is stored in dynamic memory.

It's a special feature of SQLite which makes it particularly well suited for experimenting and prototyping. Now for this exercise I'm going to copy and paste from the exercise file, so this is the sql-chap03.txt file, in the exercise files, and I'm gonna copy and paste here, and paste this into SID and we'll take a look at the CREATE TABLE statement. So the CREATE TABLE statement starts with the CREATE TABLE keyword, all in caps there, and then the name of the table, in this case we're creating a table called test, and then a pair of parentheses, with the database schema inside the parentheses, and each column in the table is indicated by the name of the column and then the type of the column, and then a comma, and the last item in the list does not have a comma.

And then there's a semi-colon at the end of the statement. So this is the CREATE TABLE statement. And in this case, we're creating a very simple table. It has two columns, named A and B, and the A column is type integer, and the B column is type text. Create table statements are commonly formatted like this such that each column declaration is on a line by itself, and this makes it easier to read but does not affect the functionality. And so that we can see the table in action we're inserting some values into three rows, and then we do a SELECT statement.

And so I'm gonna go ahead and press go here. Now again this is an in memory database. So the database is created, the table is created, it's populated, and the SELECT statement is executed and then the whole thing goes away. So every time we reload the page in SID, that database is created and destroyed. So it's very, very temporary, so five queries, performed. The first one of course is the CREATE TABLE, and then there's three insert into statements, and a SELECT statement. Three rows affected, because we inserted data into three rows.

Three rows return by the query five, the SELECT statement, and elapsed time is very, very fast, because this all happens in memory. And here you can see, here's our table, with the three rows, in A values of one, two and three, and B values of A, B and C. Column A and column B. Very, very simple. So some things to understand here. Different data types are supported by different database engines. So it's important that you understand the types that are available on your system, and how you're going to use the data.

The syntax of the CREATE TABLE statement is actually very simple, but oftentimes you'll see very long, complex data types with all sorts of options and things, and we'll get into some of that later on in the course. Just for our purposes right now, this is very, very simple CREATE TABLE statement. The CREATE TABLE statement is used to create a table with a particular schema. You'll use this one designing and declaring your database tables.