Tables in Database- MS SQL Server – Zero to Hero Query Master – Part 5

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. 

Fig.1 Data in Rows and Column format in Table 

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.

Other Articles in this Series

One thought on “Tables in Database- MS SQL Server – Zero to Hero Query Master – Part 5

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: