-------------------------------------
-- 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