SQL Server DML Statements examples

-------------------------------------
-- DML - Data Manipulation Language
-------------------------------------

USE Northwind
GO

-------------------------------------
-- INSERT (Implicit)
-------------------------------------
DROP TABLE emps 

CREATE TABLE emps
(id int ,
 f_name varchar(25),
 hire_date date default getdate(),
 salary money default 5000) 

 INSERT INTO emps
 VALUES (1, 'yossi' , '1995-01-22' , 6000)

 -- error
 INSERT INTO emps
 VALUES ('moshe' , 2 , 6000 , '1995-01-22' )

 INSERT INTO emps
 VALUES (2 , 'moshe' , '1999-04-15' )

 INSERT INTO emps
 VALUES (2 , 'moshe' , DEFAULT , NULL )

 SELECT * FROM emps 

 -------------------------------------
-- INSERT (Explicit)
-------------------------------------

 INSERT INTO emps (id , f_name , hire_date , salary)
 VALUES (3 , 'haim' , DEFAULT , DEFAULT )

 INSERT INTO emps (id , f_name )
 VALUES (4 , 'avi'  )

 SELECT * FROM emps 

  -------------------------------------
-- INSERT SELECT ..
-------------------------------------

INSERT INTO emps (id , f_name )
--VALUES (4 , 'avi'  )
SELECT employeeID , firstName
FROM employees
WHERE lastName LIKE '%a%' 

SELECT * FROM emps 

INSERT INTO emps
--VALUES (4 , 'avi'  )
SELECT productID , SUBSTRING(productName , 1 , 5) , getdate() , 20
FROM products
WHERE categoryID = 5 

SELECT * FROM emps 

-------------------------------------
-- UPDATE
-------------------------------------
SELECT * FROM emps 

UPDATE emps
SET f_name = 'Ram'
WHERE salary IS NULL 

UPDATE emps
SET salary = 0 

UPDATE emps
SET id = 2 , f_name = 'NoName' , salary = 0
WHERE id > 5 

UPDATE emps
SET id = 2 , f_name = NULL , salary = DEFAULT
WHERE id = 2

INSERT INTO emps
SELECT employeeID , lastName ,hireDate , 5000
FROM employees 

UPDATE emps
SET salary = (SELECT AVG(unitPrice) FROM products )
WHERE id > 4 

-------------------------------------
-- DELETE  (DML)
-------------------------------------

DELETE FROM emps
WHERE id > 5 

DELETE emps
WHERE id = 3

SELECT * FROM emps 

-------------------------------------
-- TRUNCATE (DDL)
-------------------------------------

-- Much faster than DELETE
-- DELETEs all table rows / no WHERE option

TRUNCATE TABLE emps

SELECT * FROM emps 

----------------------------------------
-- TRANSACTIONS
----------------------------------------

DELETE FROM emps 

INSERT INTO emps
SELECT employeeID , lastName ,hireDate , 5000
FROM employees 

SELECT * FROM emps 

BEGIN TRAN 

	UPDATE emps SET salary = NULL
	DELETE FROM emps WHERE id IN (1,2)
	UPDATE emps SET f_name = 'AAAAAA' 

	SELECT * FROM emps 

ROLLBACK
-- or
COMMIT

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…