-- DROP DATABASE testDB

CREATE DATABASE testDB
GO

USE testDB
GO

--------------------------
-- DDL
--------------------------

-------------------------
-- CREATE TABLE EX1
-------------------------

CREATE TABLE employees
(employeeID int,
 lastName varchar(25))

 DROP TABLE employees 

-------------------------
-- CREATE TABLE EX2 (IDENTITY / DEFAULT)
-------------------------

CREATE TABLE employees
(employeeID int IDENTITY(1,1) ,
 lastName varchar(25),
 salary money DEFAULT 4000 )

 INSERT INTO employees
 VALUES (1, 'yossi' , 6000)

 INSERT INTO employees
 VALUES ( 'yossi' , 6000)

 SELECT * FROM employees 

 -- Implicit Insert
 INSERT INTO employees
 VALUES ('moshe' , DEFAULT)
 -- Explicit Insert
 INSERT INTO employees (lastName)
 VALUES ('haim') 

 SELECT * FROM employees 

 DROP TABLE employees 

 -------------------------
-- CREATE TABLE EX3 (CONSTRAINTS)
-------------------------

-- NOT NULL 

DROP TABLE employees 

CREATE TABLE employees
(employeeID int  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 )

 INSERT INTO employees
 VALUES (1, NULL , 6000)

 -- UNIQUE 

 DROP TABLE employees 

CREATE TABLE employees
(employeeID int  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 ,
 phoneNumber varchar(25) UNIQUE)

 INSERT INTO employees
 VALUES (1, 'Moshe' , 6000 , '03-5555555') 

 INSERT INTO employees
 VALUES (2, 'Yossi' , 7000 , '03-5555555')

 -- PRIMARY KEY 

 DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 ,
 phoneNumber varchar(25) UNIQUE)

 INSERT INTO employees
 VALUES (1, 'Moshe' , 6000 , '03-5555555')

 INSERT INTO employees
 VALUES (1, 'Yossi' , 7000 , '03-7777777')

 INSERT INTO employees
 VALUES (NULL, 'Yossi' , 7000 , '03-7777777')

 -- CHECK 1

DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE)

  INSERT INTO employees
 VALUES (1, 'Moshe' , 10001 , '03-5555555')

  INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555')

 -- CHECK 2

DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE ,
 email varchar(25) CHECK (email LIKE '%@gmail.com'))

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@walla.co.il')

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com')

 -- FOREIGN KEY 

 DROP TABLE employees 

 CREATE TABLE departments
 (department_id int PRIMARY KEY,
  department_name varchar(25))

  INSERT INTO departments
  VALUES (50 , 'IT')

  INSERT INTO departments
  VALUES (60 , 'HR')

  INSERT INTO departments
  VALUES (70 , 'SA')

 CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE ,
 email varchar(25) CHECK (email LIKE '%@gmail.com'),
 department_id int REFERENCES departments (department_id))

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com' , 50 )

 INSERT INTO employees
 VALUES (2, 'Yossi' , 9999 , '03-7777777' , 'yossi@gmail.com' , 999 )

 DELETE FROM departments
 WHERE department_id = 50 

 DELETE FROM departments
 WHERE department_id = 60