Introduction
All the data in a database is stored in a Table, which is a data object. Data is organized in Rows and Columns in a table and each row represents a unique record while each column represents a field in the record
Types of Tables
SQL Server provides the following types of tables that serve special purposes in a database.
- Partitioned Tables
- Temporary Tables
- System Tables
- Wide Tables
In the upcoming articles of the series, we will learn more in detail about these table types.
Create Table
The syntax for creating a table is as below
CREATE TABLE tableName (columnName1 Datatype, columnName2 Datatype,...columnNameN Datatype)
Example
CREATE TABLE [Students].StudentDetails (StudentId int,FirstName nvarchar(25),LastName nvarchar(25))
When the above statement is executed, the new table will be created with the schema name Students. Always name the table with the proper purpose of the table. In the given example, I am storing the Student Details and thus I named it as StudentDetails. Likewise, based on the table purpose, name it. So, it will be easy for the users to understand the purpose of the table.
Insert values into Table
Now, we have created a table, then next we need to Insert data or records into the table. Inserting data is simply storing data into the table.
The syntax for Inserting values into a table
INSERT INTO TABLENAME (Column1,Column2,....ColumnN)
VALUES (value1,value2,....valueN)
Example
INSERT INTO [Students].StudentDetails (StudentId,FirstName,LastName)
VALUES (1,'Sundaram','Subramanian')
When the above statement is executed, a new row will be inserted into the table. Those who are new to SQL don’t need to worry about the Insert Statement, it will be explained in detail in my very next article.
Select values from Table
We have inserted the values into the table using insert SQL statement. Now we have to retrieve or have to view the data from the table. For that, we have to use SELECT SQL Statment.
To select the inserted values from the table, here is the syntax,
SELECT * FROM TABLENAME
Example
SELECT * FROM [Students].StudentDetails
When the above statement is executed, we will get the data in a table format; i.e., Data will be represented in Rows and Columns as mentioned earlier.
Alert Table
To make changes in the already existing table we need to use ALTER Statement. Alter statement can be used for
- Adding New Column
- Deleting a Column
- Modifying the Column
Adding New Column
We may have required to add additional columns to a table, so we have to use this SQL Statement
The syntax for adding a new column is,
ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE [Students].StudentDetails
ADD Gender Char(1)
Now, we have added a new column to the existing table. In this given example, ADD is the keyword to add a new column and Gender is the column name and char(1) is the data type for this new column. Datatypes in SQL Server will be explained in the upcoming article series.
Deleting a Column
Sometimes, we need to remove a particular column for the table. It may not be relevant to the table. The syntax for deleting an existing column is below.
Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE [Students].StudentDetails
DROP COLUMN Gender
In the given example, the DROP COLUMN is the keyword to delete the column from the table and Gender is the column to be deleted.
Modifying the Column
In order to change the datatype value, we have to use the modify statement.
The syntax for modifying an existing column is,
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Example
ALTER TABLE [Students].StudentDetails
ALTER COLUMN FirstName VARCHAR(75) NOT NULL;
In the given example, we have added one constraint as NOT NULL.
Conclusion
In this article, we have learned about Schemas and Tables in SQL Server. We can learn more in detail in my upcoming “MS SQL Server – Zero to Hero Query Master” series. Please share your feedback in the comments section.
Learn With Sundar 