Databases. SQL. Creating tables. Statement CREATE TABLE. Examples

Creating tables. Statement CREATE TABLE. Examples

Before studying this topic, it is recommended to familiarize yourself with the topic:


Contents


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 
)

here

  • 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
)

here

  • 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

Task.

  1. Using the SQL language tools, create a table named Product, which will display the following information about the accounting of goods in the store

Product Name

Purchase price, USD Quantity, pieces Date of receiving Note
  1. The table must contain a primary key and ensure the uniqueness of records.

 

Solution.

  1. 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).
  1. 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
Note Note VARCHAR(200)
  1. 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

ID_Product Name Price Count Date Note

Figure 1 shows the fields of the created table in Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio 18. Stages of creating a table

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

Task.

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
Johnson J. Kiev 789012 033-7777778 102
Petrenko P.P. Warshaw 044-2521412

Ensure uniqueness and correct saving of table records.

Solution.

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
Address VARCHAR(100) Address
[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.

Microsoft SQL Server Management Studio window. Stages of query formation

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

Task.

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
Johnson J. Manager 3200.00 01.02.2128 M
Petrova M.P. Clerk 2857.35 02.03.2125 F
Williams J. Secretary 3525.77 01.08.2127 F
Wilson K. Recruiter 1200.63 22.07.2125 F

Implement the table in such a way that records are unique.

 

Solution.

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

Microsoft SQL Server Management Studio. The result of query execution in Transact-SQL (T-SQL). Command CREATE TABLE

Figure 3. The result of query execution in Transact-SQL (T-SQL)

 


Related topics