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