SQL commands

SQL statements are classified into the below categories,

DDL - Data Definition Language
DML - Data Manipulation Language
DCL - Data Control Language
TCL - Transaction Control Language
DQL - Data Query Language

DDL (Data Definition Language)- is used for to construct and modify the structure of database objects in the database. DDL commands are auto-committed, which means they store all database changes permanently.

Create Database 

Alter Database

Drop Database

Database Objects as, 

Table 

View

SP- Stored Procedure

Trigger

Cursor , etc.

USE master;

GO

CREATE DATABASE Test

USE master;

GO

ALTER DATABASE Test

Modify Name = TestNew ;

GO

USE master;

GO

Drop Database Test

Select * from sys.databases;

Select * FROM sys.database_files

USE master;

GO

CREATE DATABASE Test

ON

( NAME = Test,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Test.mdf',

    SIZE = 10,

    MAXSIZE = 50,

    FILEGROWTH = 5 )

LOG ON

( NAME = Test_log,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Test_log.ldf',

    SIZE = 5MB,

    MAXSIZE = 25MB,

    FILEGROWTH = 5MB ) ;

GO

Table Related DDL commands -

Use Test

Go

Create Table Student 

(

Student_Id Int Identity(1,1) Primary Key,

Name Varchar(50) Not Null,

City Varchar(50) Null,

PhoneNumber Int

)

Alter Table Student

Add Mobile Int

Alter Table Student

Drop column Mobile 

Drop Table Student

Truncate Table Student

DML (Data Manipulation Language) - SQL commands that deal with manipulating data in a database. The DML command is not auto-committed. we can do Rollback.

Select * from Student

Insert Into Student(Name,City,PhoneNumber) Values ('Rahul','Pune',45454)

Insert Into Student(Name,City,PhoneNumber) Values ('Kiran','Mumbai',54545)

Insert Into Student(Name,City,PhoneNumber) Values ('Manish','Delhi',42121)

Insert Into Student(Name,City,PhoneNumber) Values ('Ketan','Pune',12112)

Update Student Set City='Indore' where Student_Id=4

Delete from Student where Student_Id=4

please scroll below to know more commands,

DCL (Data Control Language)-DCL command related with permission / Rights /Security , for the particular user to access database object.

Use Master

Go

Create LOGIN sqllogin WITH PASSWORD = '32487^%^%$#!@hdgfgsdfjh'

Use [Test]

Go

create USER sqllogin FROM LOGIN sqllogin;

Go

Use [Test]

Go

GRANT SELECT ON dbo.student TO sqllogin
Revoke SELECT ON dbo.student TO sqllogin
deny SELECT ON dbo.student TO sqllogin

Use [Test]

Go

Drop USER sqllogin

Use Master

Go

Drop LOGIN sqllogin

TCL  (Transaction Control Language)- used to manage transactions. This command is used for to handle the DML statements (Insert, Update, Delete) modifications.

Commit - to Save the Transaction.

Rollback - to rollback changes , to undo(reverse) the changes done by transaction.

Savepoint - to rollback a transaction to a specific point rather than the entire transaction.

Example 1- 

BEGIN TRY

BEGIN TRAN

                Insert Into Student(Name,City,PhoneNumber) Values ('manoj','Pune',12112)

                COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

SELECT @@trancount

Example 2 - Savepoint

BEGIN TRY

    BEGIN TRAN

Insert Into Student(Name,City,PhoneNumber) Values ('Sunil','Pune',12112)
                 SAVE TRAN Savepoint1
Insert Into Student(Name,City,PhoneNumber) Values ('Anil','Pune',12112)
                 ROLLBACK TRAN Savepoint1

    COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

DQL (Data Query Language)- To get data from a database, or we can say to extract data from a database. for this we are using Select Statement.

Select ColumnName From TableName Where Condition

Group By Column Name

Order By Column Name ASC/ DESC

Select * from Student

Select city,count(Student_id) as cnt from Student

group by city

----create Database

create Database Test

----Rename Database

ALTER DATABASE [Test] MODIFY NAME = [ReplicaTest2]

----Rename logical files of Database

ALTER DATABASE [ReplicaTest2] MODIFY FILE (NAME = Test2, NEWNAME = ReplicaTest2);

ALTER DATABASE [ReplicaTest2] MODIFY FILE (NAME = Test2_log, NEWNAME = ReplicaTest2_log);

----Drop Database

Drop Database [ReplicaTest2]

----create Table

CREATE TABLE TestOrder

( 

Id INT PRIMARY KEY IDENTITY(1, 1), 

OrderCode VARCHAR(100), 

OrderName VARCHAR(100), 

TDate DATETIME

)

--1) Insert data

INSERT INTO TestOrder(OrderCode,OrderName,TDate)

VALUES

( 'vidyalekhan', 'Notebook', '20130705' ), 

( 'Navneet', 'PC', '2023-07-03' )

--2) Insert data

INSERT INTO TestOrder(OrderCode,OrderName,TDate)VALUES('navneet','Notebook','20230205')

INSERT INTO TestOrder(OrderCode,OrderName,TDate)VALUES('classic','Notebook','2023-08-04')

--3) Insert data in temp table

Select * into #TestOrderReplica from TestOrder

--4) Insert data in physical table

Select * into TestOrderReplica from TestOrder

--5) Insert Into Select * from

CREATE TABLE [dbo].[TestOrderCopy](

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[OrderCode] [varchar](100) NULL,

[OrderName] [varchar](100) NULL,

[TDate] [datetime] NULL

) 

Insert Into TestOrderCopy

Select OrderCode,OrderName,TDate from TestOrder

----Rename Table

sp_rename 'dbo.TestOrderCopy' ,'TestOrderCopy1'

----Drop Table

Drop Table TestOrder

----create new table as TestOrder

CREATE TABLE TestOrder

( 

Id INT PRIMARY KEY IDENTITY(1, 1), 

OrderCode VARCHAR(100), 

OrderName VARCHAR(100), 

TDate DATETIME

)

----Rename Column

EXEC SP_RENAME 'TestOrder.TDate', 'TransDate', 'COLUMN'  

-----Change Datatype

Alter Table TestOrder

Alter Column OrderCode NVARCHAR(250)

-----Update statement without join

Update TestOrder Set OrderName='Notebook' where id=2

-----Update statement Using join

Update R

Set R.Status=0

from Registration R left join DailyMilkCollection D

On R.id=D.CustomerID

where D.CustomerID IS NULL

-----Delete statement without join

Delete from TestOrder where id=2

-----Delete statement Using join

Delete R

from Registration R left jOin  DailyMilkCollection D

On R.id=D.CustomerID

where D.CustomerID IS NULL