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, databases, database management systems, 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.

CREATE SCHEMA schemaName

Example

CREATE SCHEMA Students

In the given example, Studentsis 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

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

Example

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 that 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.

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

DROP SCHEMA schema_name

Example

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.

DROP TABLE [Students].StudentDetails
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.

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.

Table type

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!


Similar Articles