Post
Ask Question

Zero To Hero In MS SQL Server - Part Three

Introduction

This is part three of "Zero To Hero in MS SQL Server", and in this article, you will learn about Schemas and Tables. You can view my previous articles in this series from the below links.
In this article, the following is the flow. 
  • Background 
  • A glimpse of the previous articles 
  • Schemas in MS SQL Server
  • Tables in MS SQL Server
  • Points to Remember 
  • Conclusion
Background

My intention is to share what I learned and thus when I was preparing for my next job a few months ago, I used to make notes on it. Those prepared notes are the content that I am sharing as an article. These will be more useful for new learners and also for experienced persons to freshen up their SQL skills. 

A glimpse of previous articles

Part One  
  • In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.
Part Two 
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.
Schemas in MS SQL Server 

What is Schema?

Schemas are like folders within a database and are mainly used to group logical objects together. It is a way of categorizing the objects in a database. 

Why do we need a schema? 

The main use of schema was to allow multiple users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users.

Creating a Schema 

The syntax for creating a schema is given below.
  1. CREATE SCHEMA schemaName  
Example
  1. CREATE SCHEMA Students  
In the given example, Students is Schema while CREATE and SCHEMA are the keywords in the SQL statement. When this statement is executed, a schema will be created.

Using Created Schema 

To use the created schema, we need to use it before the table name, i.e., at the suffix. We need to give the Schema name. 

Syntax
  1. CREATE TABLE [schemaName].[tableName] (column1 datatype.....columnN datatype)  
Example
  1. CREATE TABLE [Students].StudentDetails (StudentId int,FirstName nvarchar(25),LastName nvarchar(25))  
In the given example, we have implemented or used the schema which is created already. Before the table name, we need to add the schema name. As a good practice, give a proper Schema Name which is related to the table, as in the given example.

Below, I have shown a picture of how the schema and table name look in the database.


Picture 1. Schema and Table under Database DemoWorks

The default schema for all objects in a database is [dbo].

Dropping a Schema

To drop a created schema, we need to use the below-mentioned syntax.

Syntax
  1. DROP SCHEMA schema_name  
Example 
  1. DROP SCHEMA [Students]  
When you run the above SQL statement, you will get an error message as "Cannot drop schema 'Students' because it is being referenced by object 'StudentDetails'. ", because it is used in one table. So you need to drop the table and then drop the schema.
  1. DROP TABLE [Students].StudentDetails  
  2. DROP SCHEMA [Students]  
So now, both, Schema and Table are dropped. Dropping a table is explained in detail in this same article below.

Tables in MS SQL Server 

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, 
  1. CREATE TABLE tableName (columnName1 Datatype, columnName2 Datatype,...columnNameN Datatype)  
Example 
  1. 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, 
  1. INSERT INTO TABLENAME (Column1,Column2,....ColumnN)  
  2. VALUES (value1,value2,....valueN)  
Example
  1. INSERT INTO [Students].StudentDetails (StudentId,FirstName,LastName)  
  2. 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, 
  1. SELECT * FROM TABLENAME  
Example 
  1. 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 required to add additional columns to a table, so we have to use this SQL Statement

The syntax for adding a new column is, 
  1. ALTER TABLE table_name  
  2. ADD column_name datatype;  
Example
  1. ALTER TABLE [Students].StudentDetails  
  2. 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 
  1. ALTER TABLE table_name  
  2. DROP COLUMN column_name;  
Example
  1. ALTER TABLE [Students].StudentDetails  
  2. 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,
  1. ALTER TABLE table_name  
  2. ALTER COLUMN column_name datatype;  
Example 
  1. ALTER TABLE [Students].StudentDetails  
  2. 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 always 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 Schemas 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 leaning and happy coding!

Similar Articles