-------------------------------------------
--DML: Data Modification Language
-------------------------------------------

DROP TABLE emps ; 

CREATE TABLE emps 
(emp_id int, -- numeric 
 emp_name nvarchar(25),  -- string 
 emp_sal  money default 5000  ,-- decimal 
 emp_hiredate  date default getdate() -- date 
 )

 SELECT * FROM emps; 
 
 -------------------------------------------
 -- SELECT INTO
 -------------------------------------------
 
 -- Create A New Table From An Existing Table
 Select * into NEW_TABLE
 from employees;
 
 select * from NEW_TABLE;

 -------------------------------------------
 -- INSERT (IMPLICIT)
 -------------------------------------------

 INSERT INTO emps 
 VALUES (1, 'moshe' , 5000 , '01-JAN-2003')

 -- error 
 INSERT INTO emps 
 VALUES ('moshe2' , 2 , 5000 , '01-JAN-2003')

 -- error 
 INSERT INTO emps 
 VALUES (3  , 'moshe3' )

 -- OK
 INSERT INTO emps 
 VALUES (4  , 'moshe4' , DEFAULT , NULL )

 SELECT * 
 FROM emps 

 -------------------------------------------
 -- INSERT (EXPLICIT)
 -------------------------------------------

 INSERT INTO emps (emp_id , emp_name , emp_sal , emp_hiredate)
 VALUES (4  , 'moshe4' , DEFAULT , NULL )

 INSERT INTO emps (emp_id , emp_name )
 VALUES (5  , 'moshe5'  )

 INSERT INTO emps (emp_id )
 VALUES (6 )

 INSERT INTO emps ( emp_name , emp_id  )
 VALUES ('зп' , 7  )

  SELECT * 
 FROM emps 

 ------------------------------------------
 -- INSERT AS SELECT (From Select)
 -------------------------------------------

 INSERT INTO emps (emp_id , emp_name)
 SELECT employeeID , lastName 
 FROM employees 
 
 INSERT INTO emps 
 SELECT employeeID+10 , lastName , NULL , GETDATE()
 FROM employees  

 SELECT * FROM emps
 ------------------------------------------
 -- UPDATE 
 ------------------------------------------
-- Update One Column
 UPDATE emps 
 SET emp_sal = 6000 
 WHERE emp_name = 'Moshe' 

select *from emps

UPDATE emps 
SET emp_sal = 0

-- Update More Then One Column
UPDATE emps 
SET emp_sal = 5000 , emp_id = 3 , emp_hiredate = DEFAULT , emp_name = NULL 
WHERE emp_name = 'Moshe' 

-- Update Using Subquery
 UPDATE emps 
 SET emp_sal = (SELECT AVG(emp_sal) FROM emps)
 WHERE emp_name = 'Moshe4' 

 SELECT * FROM emps
 SELECT * FROM Employees
 
 -- Update Using Join
 update emps
 set emps.emp_name = emp.FirstName +' ' + emp.lastname,
     emps.emp_sal = 10000,
     emps.emp_hiredate = emp.hiredate
 from emps emps join employees emp
 on emps.emp_id = emp.employeeID
 where emps.emp_id=4;
 
 SELECT * FROM emps
 
 ---------------------------------------------------
 -- DELETE
 ---------------------------------------------------
 
-- Delete specific Records 
DELETE FROM emps 
WHERE emp_ID = 4 

-- Delete All Records From The Table
DELETE FROM emps 

SELECT * FROM emps