1. You've got the power
In this final chapter, we'll add to our knowledge by learning to create and update tables, and also how to insert and delete rows.
These are commonly described as CRUD operations, and we'll briefly cover what we mean by that acronym.
Then, we'll mainly focus on creating tables. In order to do that, we need to think about the type of data we want to store in each column.
2. CRUD operations
The acronym CRUD describes the 4 main type of operations you can carry out on a database.
First, we can CREATE - in a data sense, that could be tables or views, but in addition, database administrators can also create users, permissions and security groups.
Next, we perform READ operations. When we perform a SELECT query, we are reading from the database.
We can amend existing database records using UPDATE statements.
Finally, we can DELETE records, although this depends on having sufficient access permissions to carry this out.
Let's look at CREATE in more detail.
3. CREATE
We can create a table by using the keywords CREATE TABLE and then proving a table name. Each table name within a database should be unique.
Then, we open parenthesis and type the column name, a comma, then the data type for that column, and if appropriate, the size, length or format of that data type in brackets.
Each column is defined on a separate line, before closing the parenthesis.
For example, we can create a table named `test_table`.
This table has 3 columns, test_date, which is a date column, test_name which has the datatype varchar, and test_int which is an integer column.
Notice that test_name has the varchar datatype with the value 20 in brackets immediately after.
This value refers to the maximum number of characters that can be stored in this column.
So in this case, we can store anything up to 20 characters.
4. A few considerations when creating a table
Creating a table is straightforward, but we should spend some time planning it beforehand.
As well as table and column names, we'll need to think about the type of data each column will store, and where appropriate, the size or amount of data stored in the column.
5. Data types
Here are some of the more common data types used in SQL Server.
We can store dates, times or combined datetime values.
The default date format is YYYY-MM-DD.
Numeric values can be stored as integers, decimals, or bit, which stores either a 1 or 0, relating to TRUE or FALSE.
For strings, common types include char, varchar, and nvarchar.
I encourage you to research these and the other data types available, so you are aware of which best suits your needs.
6. Let's create some tables!
Let's create some tables!