Creating Tables
A table is used to store data in rows and columns inside a SQL Server database. In this section, we will learn how to create tables, define columns, select data types and insert records.
What is a Table?
A table is a database object that stores related information in the form of rows and columns.
Each column defines a particular type of information, while each row represents one complete record.
Students
------------------------------------------------
StudentId | StudentName | Age | Course
------------------------------------------------
1 | Rahul | 22 | Java
2 | Priya | 21 | .NET
3 | Amit | 23 | Python
CREATE TABLE Syntax
The CREATE TABLE statement is used to create a new table.
CREATE TABLE TableName
(
Column1 DataType,
Column2 DataType,
Column3 DataType
);
Create a Simple Table
First select the database in which you want to create the table.
USE StudentDB;
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Age INT,
Course VARCHAR(50)
);
Defining Columns and Data Types
Every column should have an appropriate data type according to the kind of data it will store.
| Column | Data Type | Purpose |
|---|---|---|
| StudentId | INT | Stores student number. |
| StudentName | VARCHAR(100) | Stores student name. |
| Age | INT | Stores student's age. |
| Course | VARCHAR(50) | Stores course name. |
Insert Data into the Table
After creating a table, the INSERT INTO statement can be used to add records.
INSERT INTO Students
(
StudentId,
StudentName,
Age,
Course
)
VALUES
(
1,
'Rahul',
22,
'Java'
);
Insert Multiple Records
Multiple records can also be inserted using a single INSERT statement.
INSERT INTO Students
(
StudentId,
StudentName,
Age,
Course
)
VALUES
(1, 'Rahul', 22, 'Java'),
(2, 'Priya', 21, '.NET'),
(3, 'Amit', 23, 'Python');
Display Table Data
The SELECT statement is used to retrieve records from the table.
SELECT *
FROM Students;
Output
| StudentId | StudentName | Age | Course |
|---|---|---|---|
| 1 | Rahul | 22 | Java |
| 2 | Priya | 21 | .NET |
| 3 | Amit | 23 | Python |
Practical Example
The following example creates an Employee table with commonly used columns.
CREATE TABLE Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Email VARCHAR(150),
Age INT,
Salary DECIMAL(10,2),
JoiningDate DATE
);
Verify the Table
The following query can be used to view the columns and structure of a table.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';
Table Creation Flow
Select Database
↓
CREATE TABLE
↓
Define Columns
↓
Choose Data Types
↓
Insert Records
↓
SELECT Data
Important
Always choose appropriate data types for columns. A good table structure makes data easier to store, retrieve and maintain.
Summary
In this section, we learned how to create a table using CREATE TABLE, define columns and data types, insert records and retrieve table data using SELECT.