SQL-Lesson 14. Creating tables (CREATE TABLE)

The SQL language is used not only for processing information, but also for performing all operations with databases and tables, including creating and working with tables.

There are two ways to create tables using:

Visual interface - most DBMSs have a visual interface for interactive table creation and management;

SQL statements - tables can be manipulated using SQL statements directly.

It is worth noting that when you use the DBMS interactive toolkit, in fact, all the work is done by SQL statements, that is, the interface itself creates these commands imperceptibly for the user (this is similar to recording a macro in Excel, when the macro recorder records your actions and turns them into VBA commands).

1. Creation of tables

To create tables programmatically, use the operator CREATE TABLE. For this, you need to specify the following data:

the name of the table, which is specified after the keyword CREATE TABLE;

names and definitions of table columns separated by commas;

in some DBMS it is also required that the location of the table be specified.

Let's create a new table and name it Customers:

CREATE TABLE Customers (
ID             CHAR(10) NOT NULL Primary key,
Custom_name    CHAR(25) NOT NULL,
Custom_address CHAR(25) NULL,
Custom_city    CHAR(25) NULL,
Custom_Country CHAR(25) NULL,
ArcDate        CHAR(25) NOT NULL, DEFAULT NOWO)

Yes, we first specify the name of the new table, then in brackets we list the columns that we will create, and their names cannot be repeated within the same table. After the column names, the data type for each field is specified (CHAR(10)), then we specify whether the field can contain empty values (NULL or NOT NULL), and the field that will be the primary key (Primary key) must also be specified.

The SQL language also allows you to define a default value for each field, that is, if the user does not specify a value for a certain field, it will be automatically set by the DBMS. The default value is defined by the DEFAULT keyword when defining columns with the CREATE TABLE operator.

2. Updating tables

In order to change the table in SQL, the operator ALTER TABLE is used. When using this operator, you should enter the following information:

the name of the table we want to change;

a list of changes we want to make.

For example, let's add a new column to the table Sellers, in which we will specify the seller's phone:

ALTER TABLE Sellers  
ADD Phone CHAR (20)

Besides adding columns, we can also remove them. Let's now delete the Phone field. To do this, we will write the following query:

ALTER TABLE Sellers 
DROP COLUMN Phone

3. Deleting tables

Tables are deleted using the DROP TABLE operator. To delete the Sellers_new table, we can write the following query:

DROP TABLE Sellers_new

Many DBMSs have rules that prevent the deletion of tables that are already linked to other tables. If these rules apply and you delete such a table, the DBMS blocks the deletion operation until the relationship is deleted. Such measures prevent accidental deletion of the required tables.