T-SQL STATEMENTS — DML and DDL

What is data definition language (DDL) ?

  1. CREATE
  2. ALTER
  3. DROP
  4. TRUNCATE
  5. RENAME
  6. ENABLE/DISABLE TRIGGER
  7. COLLATIONS

1. CREATE

create database new_database

1.2 The following SQL statement creates a table called new_table

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

2. ALTER

2.1 ALTER -DROP COLUMN

ALTER TABLE new_table DROP COLUMN City

2.2 ALTER -MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

ALTER TABLE new_table ALTER COLUMN Adress nchar(30)

3. DROP

DROP DATABASE new_database

3.2 The following SQL statement drops the existing table ‘new_table’

DROP TABLE new_database

4. TRUNCATE

truncate table new_table

select * from new_table

5. RENAME

5.1 To change table name use following syntax.

sp_rename ‘new_table’, ‘new_named_table’

5.2 To change a column name use following syntax.

sp_rename ‘dbo.new_named_table.Address’, ‘new_Address’, ‘COLUMN’

select * from new_named_table

6. ENABLE/DISABLE TRIGGER

CREATE TRIGGER simple_trigger_message

ON new_named_table AFTER INSERT

AS BEGIN

PRINT ‘A new member has been inserted’;

END

6.1 Disable trigger

To disable ‘simple_trigger_message’ trigger, use the following disable trigger statement

DISABLE trigger simple_trigger_message ON new_named_table

To disable all triggers on the current database, you use the following statement

DISABLE TRIGGER ALL ON new_database

7. COLLATIONS

select SERVERPROPERTY(‘collation’) as servercollation

7.2 To find the current database collation, use the T-SQL shown below

SELECT name, collation_name DbCollation FROM sys.databases

What is data manipulation language (DML) ?

  1. SELECT
  2. UPDATE
  3. INSERT
  4. DELETE

1. SELECT

SELECT * FROM items

SELECT pro_com, pro_name FROM items

2. UPDATE

update items set pro_com = 100, pro_price = 110 where pro_id > 106

SELECT * FROM items

3. INSERT

INSERT INTO items (pro_id, pro_name) values (1000, 1001)

SELECT * FROM items

3.2 If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.

insert into items values (500,505,510,515)

SELECT * FROM items

4. DELETE

delete from items where pro_id > 110

select * from items

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store