Creating databases and tables in SQL

SQL (Structured Query Language) is a language for managing data in relational databases. SQL allows us to perform various operations on the data, such as querying, inserting, updating, deleting, and manipulating.

One of the most basic and essential operations in SQL is creating databases and tables. A database is a collection of related data that is organized into tables. A table is a structure that consists of rows and columns, where each row represents a record and each column represents a field.

In this article, we will learn how to create databases and tables using SQL, and how to specify their properties and constraints.

How to create a database

To create a new database in SQL, we use the CREATE DATABASE statement. The syntax of the CREATE DATABASE statement is:

                        
CREATE DATABASE database_name;
                        
                    

Where database_name is the name of the database that we want to create. The name of the database should follow the naming rules of the database management system (DBMS) that we are using, such as SQL Server, MySQL, Oracle, etc.

For example, to create a database called School, we can use the following SQL statement:

                        
CREATE DATABASE School;
                        
                    

This statement will create a new database called School in the DBMS that we are using. The new database will be empty, and we need to create tables and other objects in it.

To create a database with some additional options, such as the character set, collation, or storage engine, we can use the following syntax:

                        
CREATE DATABASE database_name

[CHARACTER SET charset_name]

[COLLATE collation_name]

[ENGINE engine_name];

                        
                    

Character set

Where charset_name is the name of the character set that we want to use for the database, such as utf8, latin1, etc. The character set defines the set of characters that can be stored in the database.

Collate

collation_name is the name of the collation that we want to use for the database, such as utf8_general_ci, latin1_swedish_ci, etc. The collation defines the rules for comparing and sorting the characters in the database.

Engine

engine_name is the name of the storage engine that we want to use for the database, such as InnoDB, MyISAM, etc. The storage engine defines how the data is stored and accessed in the database.

For example, to create a database called School with the character set utf8, the collation utf8_general_ci, and the storage engine InnoDB, we can use the following SQL statement:

                        
CREATE DATABASE School

CHARACTER SET utf8

COLLATE utf8_general_ci

ENGINE InnoDB;

                        
                    

This statement will create a new database called School with the specified options in the DBMS that we are using.

Creating tables

To create a new table in a database, we use the CREATE TABLE statement. The syntax of the CREATE TABLE statement is:

                        
CREATE TABLE table_name (

  column1 datatype [constraints],

  column2 datatype [constraints],

  ...

);

                        
                    

Table name

Where table_name is the name of the table that we want to create. The name of the table should follow the naming rules of the DBMS that we are using.

Columns

column1, column2, etc. are the names of the columns that we want to create in the table. The names of the columns should follow the naming rules of the DBMS that we are using.

Datatype

datatype is the type of data that we want to store in the column, such as int, varchar, date, etc. The datatype defines the range and format of the values that can be stored in the column.

Constraints

constraints define the restrictions and validations that the column values must follow. They are the optional rules that we want to apply to the column, such as:

• PRIMARY KEY: Uniquely identifies each row within a table, preventing duplicate entries. It acts as the table's mandatory identification marker.
• UNIQUE: Ensures no duplicate values reside within a specified column, excluding the primary key value. This constraint maintains the uniqueness of specific data points.
• NOT NULL: Mandates that values must be present in the designated column, preventing null entries and ensuring completeness of data.
• FOREIGN KEY: Establishes a relationship between two tables by linking a column in one table (child) to the primary key of another table (parent). This constraint guarantees data consistency and referential integrity across tables.
• DEFAULT: Assigns a predefined value to a column when no explicit value is provided during data insertion, streamlining data entry and reducing null values.
• CHECK: Defines a specific condition that values in a column must adhere to, enforcing data validity and accuracy based on predetermined criteria.

For example, to create a table called Students with four columns: StudentID, LastName, FirstName, and BirthDate, we can use the following SQL statement:

                        
CREATE TABLE Students (

  StudentID int PRIMARY KEY,

  LastName varchar(50) NOT NULL,

  FirstName varchar(50) NOT NULL,

  BirthDate date

);

                        
                    

This statement will create a new table called Students in the database that we are using. The table will have four columns:

• StudentID: An integer column that will store the unique identifier of each student. This column will be the primary key of the table, which means that it will uniquely identify each row in the table and cannot contain null or duplicate values.
• LastName: A varchar column that will store the last name of each student. This column cannot contain null values, which means that it is mandatory to provide a value for this column when inserting or updating a row in the table.
• FirstName: A varchar column that will store the first name of each student. This column cannot contain null values, which means that it is mandatory to provide a value for this column when inserting or updating a row in the table.
• BirthDate: A date column that will store the birth date of each student. This column can contain null values, which means that it is optional to provide a value for this column when inserting or updating a row in the table.

To create a table with some additional options, such as the character set, collation, or storage engine, we can use the following syntax:

                        
CREATE TABLE table_name (

  column1 datatype [constraints],

  column2 datatype [constraints],

  ...

)

[CHARACTER SET charset_name]

[COLLATE collation_name]

[ENGINE engine_name];

                        
                    

Where charset_name, collation_name, and engine_name are the same as in the CREATE DATABASE statement.

For example, to create a table called Students with the same columns as before, but with the character set utf8, the collation utf8_general_ci, and the storage engine InnoDB, we can use the following SQL statement:

                        
CREATE TABLE Students (

  StudentID int PRIMARY KEY,

  LastName varchar(50) NOT NULL,

  FirstName varchar(50) NOT NULL,

  BirthDate date

)

CHARACTER SET utf8

COLLATE utf8_general_ci

ENGINE InnoDB;

                        
                    

This statement will create a new table called Students with the specified options in the database that we are using.

Common Mistakes and Best Practices:

• Avoid naming conflicts: Ensure database, table, and column names are unique and descriptive.
• Plan your schema: Define your data structure carefully before creating tables to avoid future modifications.
• Document your work: Use comments to explain the purpose of tables, columns, and constraints.
• Secure your data: Implement access control and encryption measures to protect sensitive information.
• Backup regularly: Create regular backups of your databases to prevent data loss.

Conclusion

In this article, we learned how to create databases and tables using SQL, and how to specify their properties and constraints. We saw that the CREATE DATABASE statement is used to create a new database, and the CREATE TABLE statement is used to create a new table in a database. We also learned how to use the different options and parameters of these statements, such as the character set, collation, storage engine, data type, and constraints. We hope that this article helped you understand how to create databases and tables using SQL.