How to do modifications to columns in SQL

First let us create Employee table in our database.

CREATE TABLE [dbo].[Employee]([EmpID] [int] NOT NULL,[FirstName] [nvarchar](250) NOT NULL,[LastName] [nvarchar](250) NULL,[Salary] [money] NULL,[DeptID] [int] NULL)

Modification to existing column in table:

ALTER TABLE [dbo].[Employee]ALTER COLUMN [DeptID] INT NOT NULL

Adding new column into the table:

ALTER TABLE [dbo].[Employee]ADD Phone INT NULL

Removing the column from a table:

ALTER TABLE [dbo].[Employee]DROP COLUMN Phone

Removing the table from database:

DROP TABLE [dbo].[Employee]

Create table script in SQL

In order to create table in SQL we have to use CREATE keyword.

To create a table we need to have below details in hand with us:

a. You should know what should be the table name

b. What are all the columns required in your table

c. Data types of all those columns

d. Constraints of every column, we will learn about different constraints available in SQL in separate post. As of now I will mention whether column accept NULL values or not

Syntax:

CREATE TABLE <<tableName>>

(

<<columnName>> <<dataType>> <<constraint>>

)

Find below Employee table creation script:

CREATE TABLE Employee

(

     EmpID INT NOT NULL

    ,FirstName NVARCHAR(250) NOT NULL

    ,LastName NVARCHAR(250) NULL

    ,Salary MONEY

)

On execution of above script table will be created in the database in below format:

EmpID

FirstName

LastName

Salary

If we see the above script, we have declared EmpID and FirstName as NOT NULL and other two columns as NULL that means those two fields are not mandatory to fill.

In SQL if we don't mention anything then the default constraint will be taken as NULL that is the reason for Salary field the default constraint will be NULL.

SQL Server - DDL, DML, DQL, DCL and TCL

Today we are going to learn different categories of languages available in SQL:

We have four categories of languages in SQL:

DDL – Data Definition Language:

It is used to modify the structure of the table

      CREATE, ALTER, DROP, TRUNCATE

There are too many posts where we are seeing whether TRUNCATE is DDL/DML command.

Its DDL command because

a. It internally performs DROP and CREATE of the table

b. If you want to TRUNCATE the table you require ALTER permissions not only Write permissions

c. As it is DDL command, it will reset IDENTITY property of the table

DML – Data Manipulation Language:

It is used to manipulate the data available in table

      INSERT, UPDATE, DELETE

DQL – Data Query Language:

It is used to fetch the data from table

     SELECT

DCL – Data Control Language:

It is used to create roles, permissions, and referential integrity and also to control access to database by securing it

     GRANT, REVOKE

TCL – Transaction Control Language:

It is used to control/manage different transactions performed on a table

     COMMIT, ROLLBACK