Creating tables. Statement CREATE TABLE. Examples
Before studying this topic, it is recommended to familiarize yourself with the topic:
- 1. Statement CREATE TABLE. Creating a table. General form
- 2. Examples of creating tables
- Related topics
Search other resources:
1. Statement CREATE TABLE. Creating a table. General form
The table is the main element of any relational database. All the necessary information that must be stored in the database is placed in tables. Tables can be linked to each other. The number of tables in the database is not limited and depends on the complexity of the task being solved.
To create a table in SQL, use the CREATE TABLE statement. In the simplest case, the general form of the CREATE TABLE statement is as follows
CREATE TABLE Table_Name ( Field_Name_1 Type_1, Field_Name_2 Type_2, ... Field_Name_N Type_N )
- Table_Name – database table name. If there is a table with the same name in the database, then an error will occur;
- Field_Name_1, Field_Name_2, Field_Name_N – names of fields (columns) of the database table. The name of each field must be unique. Field names may be the same in different tables;
- Type_1, Type_2, Type_N – respectively, field types Field_Name_1, Field_Name_2, Field_Name_N such as INTEGER, DECIMAL, DATE and others.
Field_Name_1, Field_Name_2, Field_Name_N fields may be subject to restrictions. Each constraint is listed after the field name. In this case, the general form of the CREATE TABLE statement looks something like this:
CREATE TABLE Table_Name ( Field_Name_1 Type_1 Attribute_1, Field_Name_2 Type_2 Attribute_2, ... Field_Name_N Type_N Attribute_N )
- Attribute_1, Attribute_2, Attribute_N – restrictions on fields Field_Name_1, Field_Name_2, Field_Name_N. Restrictions are set by one of the possible words: NULL, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY and others.
Consideration of existing attribute fields in SQL is not the subject of this topic.
2. Examples of creating tables
2.1. An example of creating a simple table for accounting for goods in a store
- Using the SQL language tools, create a table named Product, which will display the following information about the accounting of goods in the store
|Purchase price, USD||Quantity, pieces||Date of receiving||Note|
- The table must contain a primary key and ensure the uniqueness of records.
- To ensure the uniqueness of records in the table, you need to create an additional field, which will be a counter (auto-increment). The name of the field is ID_Product. This field is the primary key. Also, this field has a NOT NULL constraint (non-empty field).
- The next step in the solution is assigning names to the fields of the table. Due to the fact that not all database management systems (DBMS) support Cyrillic characters, table field names will be given in Latin characters. In our case, the names and data types formed in the following table are formed:
|The name of the field in the task condition||Field name in SQL language||Field type||Explanation|
|ID_Product||ID_Product||INTEGER||Primary key, counter, NOT NULL|
|Product name||[Name]||VARCHAR(100)||String type of variable length up to a maximum of 100 characters|
|Purchase cost||Price||DECIMAL(15, 2)||Precision is 15 digits, scale is 2 decimal places|
|Quantity pieces||[Count]||INTEGER||Integer number|
|Date of receiving||[Date]||DATE|
- Writing code in SQL language, taking into account the features of the previous steps. The text of the table creation program in SQL is as follows
CREATE TABLE [Product] ( [ID_Product] Integer Not Null Primary Key, [Name] VarChar(100) , [Price] Decimal(15, 2), [Count] Integer, [Date] Date, [Note] VarChar(200) )
Microsoft SQL Server allows you to specify field names without their frame in square brackets .That is, the previous command can be rewritten as follows
CREATE TABLE Product ( ID_Product Integer Not Null Primary Key, Name VarChar(100) , Price Decimal(15, 2), Count Integer, Date Date, Note VarChar(200) )
In the code above, the ID_Product field is set to Not Null and Primary Key constraints. As a result of running the SQL program, the following table will be created
Figure 1 shows the fields of the created table in Microsoft SQL Server Management Studio.
Figure 1. Stages of creating a Product table in Microsoft SQL Server Management Studio 18: 1 – creating a file; 2 – set of SQL query; 3 – launching a request for execution; 4 – resulting table
Of course, it is possible to create a table that does not have an ID_Product field and does not have a primary key. Such a table will not ensure the uniqueness of records, since it is possible that the data in two records may match. In this case, the SQL code of the program looks like
CREATE TABLE Product ( Name VarChar(100) , Price Decimal(15, 2), Count Integer, Date Date, Note VarChar(200) )
2.2. An example of creating a subscriber telephone accounting table
Using the tools of the SQL language (T-SQL), create a table for registering subscribers’ phones.
|Name||Address||Phone Number 1||Phone Number 2||Phone Number 3|
|Ivanov I.I.||New York||123456||067-1234567||–|
Ensure uniqueness and correct saving of table records.
To ensure the uniqueness of records, you need to create an additional field – a counter. This field will increase its value by 1 each time a new record is added. If the entry is deleted, the current maximum value of the counter will not decrease. Thus, all numeric values of this field will differ from each other (will be unique). In our case, the ID_Subscriber field is added. This field does not allow NULL values.
For the Name and [Phone Number 1] fields, it is advisable to set the restriction (attribute) NOT NULL. This means that you must enter a value in these fields. This is logical, since the subscriber in the database must have at least a name and at least one phone number.
After the changes made, the table fields will have the following properties
|Field name||Data type||Explanation|
|ID_Subscriber||INTEGER||Primary Key, counter, NOT NULL|
|Name||VARCHAR(50)||Surname and name of the subscriber|
|[Phone Number 1]||VARCHAR(20)||NOT NULL|
|[Phone Number 2]||VARCHAR(20)|
|[Phone Number 3]||VARCHAR(20)|
Given the above, the CREATE TABLE command in Transact-SQL (T-SQL) would look like this:
Create Table Subscriber ( ID_Subscriber Int Not Null Primary Key, [Name] VarChar(50) Not Null, [Address] VarChar(100), [Phone Number 1] VarChar(20) Not Null, [Phone Number 2] VarChar(20), [Phone Number 3] VarChar(20) )
Field names in the query
[Phone Number 1] [Phone Number 2] [Phone Number 3]
must be enclosed in square brackets , since the names consist of several words (there is a space character between the words).
Figure 2 shows the steps for creating a table in Microsoft SQL Server Management Studio.
Figure 2. Microsoft SQL Server Management Studio window. Stages of query formation: 1 – creation of the “SQL Query 2.sql” file; 2 – SQL query set; 3 – execution; 4 – resulting table
2.3. An example of creating a table for accounting for wages and deductions in an organization
Using the SQL language, create an Account table that records accrued wages in a certain organization. A sample table is as follows
|Name||Position||Accrued salary||Date of employment||Gender|
Implement the table in such a way that records are unique.
To ensure the uniqueness of records, an additional counter field ID_Account of the Int type is created. It is advisable to select this field as the primary key if you need to use the data of this table in other related tables.
After modification, the table fields will have the following properties.
|Field name (attribute)||Data type||Additional explanations|
|ID_Account||Int||Auto increment (counter), Primary Key, Not Null value, ensures the uniqueness of records|
|[Name]||VARCHAR(50)||Last name and first name, Not Null field|
|Position||VARCHAR(100)||Position, Not null field|
|Salary||DECIMAL||Type designed to store monetary values|
|[Employment Date]||DATE||Date, Not Null value|
|Gender||CHAR(1)||Gender, Not Null value|
In the modified table, the Salary field allows null values. Such a case may be, for example, when a person is hired, but his wages have not yet been accrued. In this situation, the Null value is temporarily set. All other fields are required to be filled in.
The SQL query that creates the above table looks like
/* Create the Account table */ Create Table Account ( ID_Account Int Not Null Primary Key, [Name] VarChar(50) Not Null, [Position] VarChar(100) Not Null, Salary Decimal Null, [Employment Date] Date Not Null, Gender Char(1) )
The result of executing the SQL query is shown in Figure 3
Figure 3. The result of query execution in Transact-SQL (T-SQL)
- Microsoft SQL Server Management Studio 2018. Example of creating a simple query
- Table modification. The ALTER TABLE Statement