SQL SERVER -History of SQL SERVER_BASICs of DDL,DML,DCL,and DTL

Today we will learn about history of SQL and some basic commands in SQL Server.

History:

SQL was initially developed at IBM by in the early 1970s and in this version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company

Basic Commands:

DDL (Data Definition Language) : CREATE , ALTER, DROP,TRUNCATE
DML: ( Data Manipulation Language): UPDATE, DELETE, INSERT
DCL: ( Data Control Language): GRANT, REVOKE
DTL: (Data Transactional language): COMMIT, ROLLBACK

 Now we will see one by one. 

SELECT: SELECT Command is used to retrieve the data from a SQL table. 

Syntax: SELECT Col1, Col2 FROM Table1 WHERE <Condition>

Script:

CREATE TABLE TEST

(a int, b int)

GO

INSERT INTO TEST

VALUES(1,10)

GO

INSERT INTO TEST

VALUES(2,20)

Now we will write SELECT command in different ways 

SELECT * FROM TEST; -- If we want to retrieve all the columns and all the rows

SELECT a, b FROM TEST; -- If we want to retrieve the data based on the column names

SELECT a as firstcolumn, b as SecondColumn FROM TEST;  -- If we want to give our own names to the columns 

In Microsoft SQL Server, you can write SELECT command without FROM clause.

E.g.: SELECT 'HI'

We will use below table for ALTER, DELETE,TRUNCATE, DROP commands demo purpose

CREATE TABLE Emp_Test

 (EmpID int,

 Empname varchar(10))

ALTER: ALTER command is used to do below things

•    Add column/constrains
•    Modify column
•    Drop column/Constraint

Add Column syntax:

ALTER TABLE <<Table>>

ADD Column_name datatype [size]

Example:

ALTER TABLE Emp_test

ADD Dateofbirth date

We can also add constraints to the existing table. We will discuss about constrains in future sessions. For now we will learn how to add constraints 

Add Constraint Syntax:

ALTER TABLE <<tablename>>

ADD Constraint  Constraint_name ConstraintType (Columns…)

Example:

ALTER TABLE Emp_test

ADD constraint EMP_Unique UNIQUE(EMPID) – Here Unique means EMPID column data should not be repeated

Modify column:

We can also modify the existing column data type and its size if table is empty and it table has data and then we are trying to modify the existing column data type then if the new data type is compatible to the existing data in the table then it will allow other it will through error. E.g. existing column was created using char(10) then if we are trying to modify to Varchar(20)  then it won’t throw any error. Compatibility data types are always we can change. 

Syntax:

ALTER TABLE <<Table_name>> 

ALTER Column Col_name datatype

Example:

ALTER TABLE Emp_test

ALTER Column Empname char(12)

Drop column/Constraint: We can  drop existing column/constraint using ALTER command 

Syntax: 

ALTER TABLE <<table_name>>

DROP <<Column or Constraint>> <<Column_name>>

Example:

Drop Empname column

ALTER TABLE Emp_test

DROP Column Empname  

Drop unique constraint (EMP_Unique)

ALTER TABLE Emp_test

DROP CONSTRAINT EMP_Unique

DELETE:  DELETE Command is used to delete table rows. We can delete all the rows from a table or we can also delete specific rows by using WHERE condition. 

Syntax:

DELETE FROM <<table name>> 

<<WHERE some conditions>>

Example:

DELETE FROM Emp_test--Delete all the rows

DELETE FROM Emp_test WHERE EmpID=10--Delete rows which are having empid=10

DELETE FROM Emp_test WHERE EmpID<10 --Delete rows which are having empid<10

DELETE FROM Emp_test WHERE EmpID<>10 ----Delete rows which are having empid<>10 

In SQL server we can also write DELETE statement like below

DELETE emp_test

TRUNCATE: TRUNCATE Command is also used to delete the data from table

Syntax:

TRUNCATE TABLE <<Tablename>>

Example:

TRUNCATE TABLE emp_test

Here we should get one question like

what is the difference between DELETE and TRUNCATE?

Both commands will be used to deleting table data, however there are some differences are there while deleting the data.

      1.     For DELETE We can use WHERE condition whereas TRUNCATE We can’t give 

      2.     DELETE Command will maintain log for each deleted row whereas TRUNCATE will not 

              maintain any log

      3.     We can use some joins to delete the data where as in TRUNATE we can’t use any joins

      4.     DELETE Will not reset IDENTITY [We will discuss in future sessions about IDENTITY] 

              Whereas TRUNCATE Will reset IDENTITY

      5.     We can Roll back the data when we use DELETE whereas TRUNCATE we can’t rollback

Overall, if we want to delete all the row then it’s always go with TRUNCATE Command

DROP: DROP Command is used to drop the table

Syntax:

DROP TABLE <<tablename>>

Example:

DROP TABLE emp_test