Zero To Hero In MS SQL Server - Part Three

CREATE SCHEMA schemaName

CREATE SCHEMA Students

CREATE TABLE [schemaName].[tableName] (column1 datatype, ... columnN datatype)

CREATE TABLE [Students].StudentDetails (StudentId int, FirstName nvarchar(25), LastName nvarchar(25))

DROP SCHEMA schema_name

DROP SCHEMA [Students]

DROP TABLE [Students].StudentDetails
DROP SCHEMA [Students]

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.

Picture 2. 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 been 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 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 from 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.

Points to Remember

• The data are stored in Rows and Columns in a Table
• The best practice is to create a table with Schema
• Give relevant names for Schema and Table
• Alter statement can be used for adding a new column, dropping, and modifying an existing column in a table.
• A table can have N numbers of records

Conclusion

In this article, we have learned about Schemes and Tables in SQL Server. We can learn more in detail in my upcoming "Zero To Hero in MS SQL Server" series. Please share your feedback in the comments section. Happing learning and happy coding!