SQL Statements and Database in SQL Server

SQL Statements/Commands

• Used to communicate with database to perform specific tasks.

• There are four types of Statements/Commands

1. DDL(Data Definition Language) 

• Used for creating, modifying and dropping the structure of database objects.

• Create,Alter,Drop,Rename and Truncate

2. DML (Data Manipulation Language)

• Used for storing,retrieving,modifying and deleting data.

• Commands are select,insert,update and delete

3. DCL (Data Control Language)

• Used for providing security to database objects.

• Commands are grant and revoke

4. TCL(Transaction Control Language)

• Used for managing changes that affect the data.

• Commands are commit,rollback and savepoint.

Database

• Database is nothing but a collection of objects.

• Objects are table,view,stored procedure,function,trigger etc.

• Types of Databases

1. System Database

• System Databases are created automatically when we install MS SQL Server.

• Master,model,MSDB,Tempdb

2. User Databases

• User databases are created by users (Administrators,Tester,developer)

• Create Database

Syntax:
    Create database Your_Database_Name

Example 

Create database person

When we create a database there are 2 files are created 

• .MDF file - Data file (Contains actual data)

• .LDF file - Transaction log file (used to recover database)

-  Modify name of the database

Syntax:
Alter database databasename modify name = newdatabasename

Example:

Alter database person modify name = persondb

- To Delete or Drop Database

Syntax:

Drop database dbname

Example:
Drop database persondb

Note: System Databases can’t be dropped