Microsoft SQL Server Language

Microsoft SQL Server Language

Microsoft SQL Server Language Part - 4
Image Credits: Microsoft SQL Server 2012

Now we will learn some basic things about Microsoft SQL server language. So let’s start.

Microsoft SQL Server Language

Basically there are four (4) types of SQL language mentioned below –

  1. DDL(Data Definition Language) –  CREATE, ALTER, DROP, TRUNCATE
  2. DML(Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE
  3. DCL(Data Control Language) – ALTER PASSWORD, GRANT, REVOKE, CREATE SYNONYM
  4. TCL(Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
  5. DQL(Data Query Language) – SELECT
  6. DAC(Data Administration Commands) – START AUDIT, STOP AUDIT
  1. DDL: DDL is an abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in a database, such as the creation or the deletion of a table, Index, and Views.

Example: CREATE, ALTER, DROP and TRUNCATE statements

  1. CREATE statement: Create a statement that is used to create a new database or a table.

Syntax:

Query -1

Example:

Query -2

Note: Money data type takes up to 4 decimal point, Varchar takes variable length and Char takes Fixed length.

Creating a Duplicate table:

1) Right click on table—->Script table as—->Create To—->New Query Editor Windows  Then Copy Table Script and Execute it

2) Syntax:

Query -3
  • ALTER statement: Alter statement is used to modify table structure.

Syntax:

1)            Syntax to add column:

Query -4

Example:

ALTER TABLE Customers ADD Age CHAR(2)

2)            Syntax to alter/modify column data type:

ALTER TABLE <> ALTER COLUMN <> <>

Example:

Query -5

3)            Syntax to delete/drop column:

ALTER TABLE <> DROP COLUMN <>

Example:

Query -6
  • DROP statement: Drop statement is used to remove table from Database.

Syntax:

DROP TABLE <>

Example:

Query -7

Renaming Table name or Column Name:

Syntax to rename column name:

EXEC sp_rename'<>.<>’, <>, ‘COLUMN’;

Example:

Query -8

Syntax to rename table name:

EXEC sp_rename <, <>;

Example:

Query -9
  • TRUNCATE statement: Truncate statement is used to remove all records from a table permanently even resets the identity columns.

Syntax:

TRUNCATE TABLE <>

Example:

Query -10
  • DML: DML is an abbreviation of Data Manipulation Language. It is used to retrieve, store, modify and delete data in a database.

Example: SELECT, INSERT, UPDATE and DELETE statements

Note: SQL Server run auto commit for every DML statements but if we want to control this then we need to execute “Begin Tran” or we can change this behavior. By default SQL Server Management Studio is in a Auto commit mode, which means whenever a transaction is executed then that is committed by default. If we want to disable Auto commit mode in SSMSL, then follow below steps:

    1. Connect to SQL Server using SSMS

    2. From the Menu bar, select Tools –> Options

    3. Select Query Execution –> SQL Server –> ANSI

    4. Then check the check box SET IMPLICIT_TRANSACTIONS

    5. Click on OK

OR

Run the bellow query as needed.

Query -11
  • SELECT statement: Select statement is used to retrieve records from a table.

Syntax:

SELECT * FROM <table_name> WHERE [col1/col2/col3….] = values

Example:

Query -12
  • INSERT statement: Insert statement is used to insert new records in a table.
  • Syntax:

INSERT INTO <table_name> [Col1,col2,col3,….] VALUES (value1, value2,….)

WHERE [col1, col2,….]

in above is optional

Example:

Query -13
  1. Syntax:

SELECT [* or Col_List] INTO <> FROM <>

WHERE <>

Example:

Query -14
  1. Syntax:

INSERT INTO <table_name> [col_List] SELECT [*/Col_List] FROM <>

WHERE <>

Example:

Query -15

INSERT using DEFAULT values keyword:

Syntax:

INSERT INTO<table_name> DEFAULT VALUES

Example:

Query -16
  • UPDATE statement: Update statement used to change existing records data in a table.
  • Syntax:

UPDATE <table_name> SET col1=expr1, col2=expr2,… [WHERE condition]

Example:

Query -17
  1. Syntax:

UPDATE <table_name> SET col1=(SUBQUERY) [WHERE condition]

Example:

Query -18
  • DELETE statement: Delete statement is used to remove records or rows from a table.

Syntax:

DELETE FROM <table_name> [WHERE condition]

Example:

Query -19

As discussed earlier that in SQL Server run auto commit for every DML statements but if we want to control this then we need to execute “Begin Tran”

Example:

Query -20
  • DCL: DCL is an abbreviation of Data Control Language. It is used to create roles, permissions and referential integrity as well it is used to control access of a database to secure it.

Example: ALTER PASSWORD, GRANT, REVOKE and CREATE SYNONYM statements

  • TCL: TCL is an abbreviation of Transaction Control Language. It is used to manage different transactions occurring within a database.

Example: COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION statements

  • DQL: DQL is an abbreviation of Data Query Language. It is used to retrieve data only from database table.

Example: SELECT statement

  • DAC: DAC is an abbreviation of Data Administration Commands. It is used to allow the user to perform audits and perform analysis on operations within the database.

Example: START AUDIT and STOP AUDIT statements

Difference between DELETE and TRUNCATE table statements:

          1.   DELETE is a DML command but TRUNCATE is a DDL command.

          2.   We can use WHERE CLAUSE while using DELETE command but it cannot use in TRUNCATE command.

          3.   DELETE is slower in performance than TRUNCATE, because it maintains logs for every record.

          4.   We can rollback data in DELETE but can’t be rollback data in TRUNCATE because TRUNCATE removes the records        permanently.

          5.   TRUNCATE resets the identity counter but DELETE doesn’t.

          6.   A trigger doesn’t fire in case of TRUNCATE but Triggers can be fire in case of DELETE command.

We will discuss upcoming topics in next part. So please take care of you.

Assuming this tutorial post has well acquainted aspiring to the complete requirement. If you want to download this tutorial in PDF format file, then please click here to download PDF.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply