DDL & DML Language In SQL Server Database With Example

Learn DDL & DML Command With Syntax And Example In SQL Server & My SQL Database
\\ If you have any query or questions on my post then please comment below //
 SQL Server command divides in two category: DDL & DML.

DDL (Data Definition Language In SQL Server):
 It is used to create or destroy the database objects. These commands will be used by database during the setup and removal phases of a database project. Here you can learn how to write ddl command in sql server.


  1. ALTER : After creating a table if we want to modify the table ,we use ALTER command. (Use of alter command in sql server)
     Ex.  create database demo1                  // create database
       create table EmpDetail                   // create table
      (
       EmpId int identity(1,1),                   // identity is auto generated
       EmpName varchar(50),
       EmpDept varchar(50),
       EmpSal float )
 
•       Increasing the width of a column                          
             Syntax - ALTER table EmpDetail ALTER cloumn EmpName varchar(100)   
•       Decreasing the width of a column :
             Syntax – ALTER table EmpDetail ALTER column EmpName varchar(50)
•       Adding / Removing (Not Null / Null) constraint :
             Syntax – ALTER table EmpDetail ALTER column EmpName varchar(50) Not Null
•       Adding new column :
             Syntax – ALTER table EmpDetail ADD EmpPost varchar(50)
•       Dropping an existing column :
             Syntax – ALTER table EmpDetail DROP column EmpPost
•       Adding constraint to any column :
             Syntax – ALTER table EmpDetail ADD constraint EmpId_pk      //primary key
•       Dropping an existing constraint :
             Syntax – ALTER table EmpDetail DROP constraint EmpId_pk
 
  1. DROP: If we want to destroy the existing tables present in the database we use the DROP command. (Use of drop command in sql server)
•       Dropping the table :                     //Remove table
           Syntax – DROP table EmpDetail 

  1. TRUNCATE: Removes all rows from table. It is same as DELETE  statement with no “where” clause. (Use of truncate command in sql server)
•     Truncating the table :                   // Move table
           Syntax – TRUNCATE table EmpDetail


Ø  DML (Data Manipulating Language In SQL Server):  
 Making changes in data that already exists. Here you can learn use of dml command in sql server.
                                                                          
1.   INSERT: To INSERT the values in the column of the table. (Use of insert command in sql server)
Syntax – INSERT into EmpDetail(EmpName,EmpDept,EmpSal) values(Martin,EEE,10000)              // if the EmpId is auto generated then no   
                                                                       need to generate it
 2.  SELECT: To display the result or table as output we use SELECT command. (Use of select command in sql server)
Syntax – SELECT * from EmpDetail 

  1. UPDATE : If we want to the data existing in the table we use UPDATE statement. (Use of update command in sql server)
Syntax – UPDATE EmpDetail Set EmpName=Mike 

  1. DELETE : If we want to delete rows of data present in the table we use DELETE  statement. (Use of delete command in sql server)
Syntax – DELETE from EmpDetail where EmpName=Mike 


Data Types Used In SQL Server :
  1. Int                                           12.  DateTime                           23.   TimeStamp
  2. Bingint                                    13.  SmallDateTime                 24.   Xml
  3. Smallint                                  14.  Char                                   25.   Cursor
  4. Tinyint                                    15.  Varchar
  5. Big                                           16.  Text
  6. Decimal                                   17.  Nchar
  7. Numeric                                  18.  Nvarchar
  8. Money                                     19.  Ntext
  9. Float                                        20.  Binary
  10. Real                                         21.  Varbinary
  11. Date                                        22.  Image
       Logic Operators Used In SQL Server:
  1. ALL                                          6.  IN
  2. AND                                         7.  LIKE
  3. ANY                                         8.  NOT
  4. BETWEEN                              9.  OR
  5. EXISTS                                   10.  SOME                        

Labels: ,