To create a SQL table, we need one of the following RDBMS like MS SQL, MySQL, PostgreSQL, and more. In this blog, we will be using MySQL and its command line client. Before digging deep, let’s see what is SQL.
What is SQL?
SQL (Structured Query Language) is a global standard language when dealing with Relational Databases. SQL is used to create, read, update, and perform delete operations on a database.
In a Relational Database, the data is stored in the form of tables, and to create tables we use SQL. Let’s create a table in MySQL.
SQL Table Creation
The sql table creation command comes under Data Definition Language (DDL) as the name suggests it is used to define the database and tables.
Before SQL table creation, let’s discuss the syntax for Create Table.
The SQL table creation syntax are:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
…
);Step 1: Run MySQL Command Line Client
After successfully installing MySQL, run the MySQL Command line client.
Step 2: Creating a Database
For SQL table creation, we first need to have a database.
- Use SQL’s CREATE statement to create a MySql database. Let’s begin with creating a database named geekster.
CREATE DATABASE geekster;
- You can check for databases by running the show databases command.
SHOW DATABASES;The terminal prints out a list of databases and information about the time it took to perform the query:
- Now we Select the database using the USE statement.
USE geekster;The terminal should print the Database change.
Step 3: Create a SQL Table (SQL Table Creation Example)
Let’s create a SQL table with two rows of data with students’ data.
To create a table like this we need to run the following command in the SQL command line client:
CREATE TABLE students (
ID INT(5) NOT NULL,
Name VARCHAR(20) NOT NULL,
Course VARCHAR(60) NOT NULL,
Company VARCHAR(20),
PRIMARY KEY (ID));Let’s discuss the SQL statement further:
We used the SQL CREATE command for creating a table named students along with the following details.
- Column names: In this case, they are ID, Name, Course, and Placed.
- Datatypes: Datatypes for the Name, Course, and Company column is VARCHAR(size) where varchar signifies variable length string and size defines the maximum length (0 to 65535). For the ID column datatype is INT(size) INT denotes the column’s data is of integer type. The size parameter specifies the maximum display width (which is 255).
- NOT NULL: It is used to set a rule which says there can be no NULL values in the column.
- Primary Key: We use the primary key to set a column that uniquely defines every row. It can not be NULL and it should be UNIQUE.
To check the tables present in the Geekster database you can run the SHOW TABLES; command.
To verify the Type, Fields, and other columns related information use DESCRIBE students; statement.
- The field shows the column name
- Type specifies the data type for the columns
- Null shows whether the column can contain null values or not
- Key shows whether the column is indexed or not, it can be empty, PRI for the primary key, UNI for unique index (allows multiple null values), or MUL (documentation).
- Default shows the column’s default value.
- Extra shows additional information about the column.
Woohoo! At this point, you’ve successfully created a Table but Data is still missing so in the later part of this blog we’ll learn about inserting rows into a SQL table.
Step 4: Inserting Data into the SQL Table
Now, let’s just add these two records to our student’s table.
Inserting a row in SQL Table
There are two ways of adding rows in a SQL table
- Mention Columns name: In this, you need to specify the columns in the order you want to insert the column data.
INSERT INTO table_name (col 1, col 2, col 3, ..., col n) VALUES (val 1, val 2, ..., val n);- Without column names: In this, you need to enter the data in the same order as we specified columns in the table.
INSERT INTO table_name VALUES (val 1, val 2, ..., val n);Let’s enter the required data in our student table
It will look like this:
INSERT INTO students VALUE (21, "Rishabh", "Full Stack Web Development Program", "PayPal");INSERT INTO students VALUE (22, "Naved", "Career kickstarter", "ZOLO");The terminal should print “OK, 1 row affected (0.01 sec)”.
To view the changes in the table, use the select statement and it should print the result set as mentioned below.
Congratulations! You’ve successfully created a table using SQL. In this article, we learned about basic operations in SQL and how to create a table in SQL. 🔥
FAQs
Answer – SQL is a standard language for accessing and manipulating databases.
MySQL is an RDBMS and SQL is a query language used to access and manipulate databases.
Answer – SQL constraints are used to specify rules for data in a table, these constraints are specified when the table is created with the CREATE TABLE statement. We can also add constraints with ALTER TABLE statements.
Some of the common constraints used in SQL are:
1. NOT NULL – column cannot have a NULL value
2. UNIQUE – values in a column are different
3. CHECK – values in a column satisfy a specific condition/ rule
4. DEFAULT – the default value for a column if no value is associated
5. PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
6. FOREIGN KEY – Prevents actions that would destroy links between tables
Answer – Primary key is a column or set of attributes that uniquely identifies each row in a table. The primary key defined from multiple columns is known as the composite primary key.
1. A primary key cannot have a null value.
2. Only one primary key per table is allowed.
3. Each value in the primary key column is unique.
Answer – In order to save a table in SQL, you need to utilize the CREATE TABLE statement to establish the structure of the table, followed by the INSERT INTO statement to insert data into the table. Once completed, the table and all of its contents will be securely saved within the database.
