SQL.txt

--------------------------------------------------------------------------------------------------------------------------------
-- Database -- 

    -- Schema : blueprint --- it shows the database objects like Tables and 
                    -- relationshionship between those 
    -- Tables : Fields (Columns) , Records ( Rows )
    -- PRIMARY_KEY : the unique not null column that identify a record from another 
    
    
-- SQL Language -- 
-- DESCRIBING AT ABLE 
    Describe Employees ; 
-- GETTING ALL DATA IN ROW 
    SELECT * 
    FROM countries; 
    
-- GETTING CERTAIN COLUMN 
    SELECT country_id, country_name
    FROM COUNTRIES ; 
    
-- CONCETENATION USING ||  AND ALIASES USING  AS OR SPACE 
 SELECT country_id || ' IS ID FOR ' || country_name AS DESCRIPTION 
    FROM COUNTRIES ; 
   
-- RESTRCIT THE RESULT USING WHERE CLAUSE --- IT'S JUST LIKE IF STATEMENT IN JAVA 
--- USE JOBS TABLE FOR THIS TASK 
-- FIND OUT THE JOBS THAT HAVE MINUMUM SALARY MORE THAN 10000 
SELECT * 
FROM JOBS 
WHERE min_salary > 10000; 
-- FIND OUT THE JOBS THAT HAVE MAXIMUM SALARY MORE THAN 15000 
SELECT job_title AS "RICH GUYS" , mAX_salary AS MONEY
FROM JOBS 
WHERE mAX_salary > 15000; 
-- FIND OUT THE JOBS THAT HAVE MINUMUM SALARY MORE THAN 10000 
    -- AND MAXIMUM SALARY MORE THAN 15000
Select JOB_TITLE
from JOBS
WHERE MIN_SALARY >10000 AND MAX_SALARY>15000;
    
-- FIND OUT THE JOBS TITLE THAT HAVE MINUMUM SALARY BETWEEN 10000 AND  15000
Select JOB_TITLE
from JOBS
WHERE MIN_SALARY BETWEEN 10000 AND 15000;
--- FIND OUT JOBS THAT HAVE MAX SALARY OF MORE THAN 15000 OR MAX SALARY OF LESS THAN 6000
SELECT job_title, max_salary 
from jobs 
where  max_salary<6000 or max_salary>15000;
--- FIND OUT THE PRESIDENT'S MAX AND MIN SALARY 
SELECT *
from jobs 
WHERE job_title = 'President' 
        or job_title = 'Finance Manager' 
        or  job_title = 'Sales Manager' ; 
--- IN OPERATOR 
SELECT *
from jobs 
WHERE job_title IN ('President','Finance Manager','Sales Manager') ;
       
-- USE LOCATION TABLE FOR THIS TASK
-- FIND OUT ALL THE INFORMATION ABOUT THE LOCATIONS IN US AND CANADA
Select * from locations
where country_id='US' or country_id='CA';
SELECT *
from locations
where country_id in ('US','CA');
-- USE LOCATION TABLE FOR THIS TASK
-- FIND OUT ALL THE INFORMATION ABOUT THE LOCATIONS NOT IN US AND CANADA
SELECT *
from locations
where country_id NOT in ('US','CA');
--- LIKE OPERATOR USING %  FOR PARTIAL STRING SEARCH 
--- FIND ALL EMPLOYEES THAT HAS NAME START WITH A 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name LIKE 'A%' ; 
--- FIND ALL EMPLOYEES THAT HAS NAME END WITH a 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name LIKE '%a' ; 
--- FIND ALL EMPLOYEES THAT HAS NAME END WITH ea 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name LIKE '%ea' ; 
--- FIND ALL EMPLOYEES THAT HAS ea NAME within the name 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name LIKE '%ea%' ; 
--- FIND ALL EMPLOYEES THAT HAS e a NAME within the name at any location 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name LIKE '%e%a%' ; 
--- FIND ALL EMPLOYEES THAT HAS NAME DOES NOT START WITH A 
Select EMPLOYEE_ID , first_name
from employees
WHERE first_name NOT LIKE 'A%' ; 
-------  FINDING OUT UNIQUE VALUES USING DISTINCT KEYWORD 
Select DISTINCT first_name
from employees;
--- FIND OUT ALL DISTINCT SALARY FROM EMPLOYEES 
Select DISTINCT salary
from employees;

----- ARITHMATIC OPERATION IN SQL ----  + - * / %  
SELECT * FROM JOBS  ; 

-- TASK 1 ----  RAISE MINUIMUM SALARY IN JOBS TABLE BY 2000 
SELECT job_title , MIN_SALARY , MIN_SALARY+10000 AS RAISED_SALARY 
FROM JOBS ; 
-- TASK 1 ---- 
/*
RAISE MINUIMUM SALARY IN JOBS TABLE BY 2000 
REDUCE MINUIMUM SALARY IN JOBS TABLE BY 100
YEARLY MIN_SALARY OF ALL JOBS_TITLE 
BI-WEEKLY MIN_SALARY OF ALL JOBS TITLE 
OPTIONALLY , FIND OUT AVERAGE OF MAX AND MIN SALARY 
*/
SELECT job_title , MIN_SALARY , MIN_SALARY+2000 AS RAISED_SALARY 
FROM JOBS ;
SELECT job_title , MIN_SALARY , MIN_SALARY-1000 AS "PAY CUT" 
FROM JOBS ;    
SELECT job_title , MIN_SALARY , MIN_SALARY * 12 AS "YEARLY SALARY " 
FROM JOBS ;    
SELECT job_title , MIN_SALARY , MIN_SALARY /2 AS "BI-WEEKLY SALARY " 
FROM JOBS ;    
SELECT job_title , MIN_SALARY , MIN_SALARY+2000 AS RAISED_SALARY
                             ,  MIN_SALARY-100 AS "PAY CUT" 
                             ,  MIN_SALARY * 12 AS "YEARLY SALARY " 
                             , MIN_SALARY /2 AS "BI-WEEKLY SALARY " 
                             , (MIN_SALARY+ MAX_SALARY)/2 AS "AVERAGE MIN_MAX"
FROM JOBS ;    
-------------------- ORDERING IN SQL   --- ORDER BY ASC/ DESC
SELECT * FROM EMPLOYEES 
ORDER BY first_name ASC; -- OR DESC 
SELECT * FROM EMPLOYEES 
ORDER BY  first_name ASC , LAST_NAME DESC; -- OR DESC 
--- GET ALL EMPLOYEES BY MANAGER ORDER DESC , DEPARTMENT_ID ASC 
SELECT * FROM EMPLOYEES 
ORDER BY HIRE_DATE DESC; 
SELECT * FROM EMPLOYEES 
ORDER BY 3 DESC; 
----  WORKING WITH NULL VALUE  ---> IS NULL | IS NOT NULLL 
--- NULL MEANS EMPTY VALUE 
SELECT FIRST_NAME , commission_pct FROM EMPLOYEES 
WHERE commission_pct IS NULL ; 
SELECT * FROM EMPLOYEES 
WHERE commission_pct IS NULL ; 
-- FIND OUT AN EMPLOYEE WITHOUT A MANAGER 
SELECT * FROM EMPLOYEES 
WHERE MANAGER_ID IS NULL ; 
---- NOT NULL IS USED TO GET NON-EMPTY VALUE
SELECT * FROM EMPLOYEES 
WHERE commission_pct IS NOT NULL ; 
---------- WORKING WITH FUNCTIONS--------------
--- SINGLE ROW FUNCTION 
    --- IT WILL GO TO EACH AND EVERY ROW AND EXECUTE THE FUNCTION 
--- UPPER  LOWER  , INITCAP (make first letter upper case the rest lowecase)
SELECT FIRST_NAME , UPPER(FIRST_NAME) , LOWER(LAST_NAME), EMAIL , INITCAP(EMAIL)
FROM EMPLOYEES; 
select first_name from employees
WHERE LOWER(first_name) = 'ellen' ; 
select first_name, last_name from employees
WHERE LOWER(first_name) LIKE  '%a%' 
ORDER BY first_name
; 
--- FIND OUT THE LENGH OF A STRING 
SELECT FIRST_NAME, LENGTH(first_name) FROM EMPLOYEES ; 


--- FIND OUT ALL FIRST NAME HAS LENGTH BETWEEN 4-6
SELECT FIRST_NAME, LENGTH(first_name) 
FROM EMPLOYEES 
WHERE LENGTH(first_name) BETWEEN 4 AND 6 ; 
SELECT FIRST_NAME, LENGTH(first_name) 
FROM EMPLOYEES 
WHERE LENGTH(first_name)  IN (3,4,7) ; 


--- A SPECIAL TABLE CALLED DUAL , IT'S A DUMMY TABLE THAT WE CAN QUICKLY TEST OUT EXPRESSIONS  -----
SELECT 'HELLO WORLD' || + ' , SPARTANS' AS HW FROM DUAL ; 
SELECT 7 + 29  FROM DUAL ; 


--- ROUND , TRUNC , MOD --- 

SELECT  ROUND(8 / 3) ,  TRUNC(8 / 3) , MOD(10, 3) RESULT  FROM DUAL ; 

--- MULTI-ROW FUNCTION --- AGGREGATE FUNCTION 
    --- IT WILL MANY ROW AND GENERATE AGGREGATED RESULT IN ONE ROW 
    
----------------------------------------new day 2-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
------------ 
select * from employees; 

--- SINGLE ROW FUNCTIONS --- 
SELECT FIRST_NAME,LAST_NAME, UPPER(first_name) as uppercase, 
                    LOWER(first_name) as lowercase, 
                    LENGTH(first_name) as COUNT , 
                    email, 
                    INITCAP(EMAIL)
FROM EMPLOYEES
ORDER BY first_name ASC , 2 DESC
-- ORDERING BY MULTIPLE COLUMN WILL ALWAYS ORDER BY 1st one , 
-- IF FIRST ONE HAS same data then go for second one
; 



---  DUMMY ORACLE TABLE you can use to test out things 
SELECT 'HELLO WORLD' , 10+10 AS RESULT
FROM DUAL; 



SELECT  ROUND(10/3) ROUNDED, TRUNC(10/3) BASE , MOD(10,3) AS REMAINDER
FROM DUAL; 


-- TASK 1 -- FIND OUT DAYLY SALARY OF EMPLOYEES WITH NAME 30 
select first_name||' '||last_name AS "full name" ,ROUND (SALARY/30)
fROM employees
ORDER BY first_name ;

--- MULTI ROW FUNCTION || AGGREGRATE FUNCTION ----- COUNT, AVG , MAX, MIN , SUM 
SELECT * FROM EMPLOYEES ; 



--- COUNTING ALL THE ROWS 
SELECT COUNT(*) , COUNT(first_name)  FROM EMPLOYEES ; 

--- COUNTING ONLY UNIQUE FIRST NAMES -----

SELECT  COUNT( DISTINCT FIRST_NAME) as uniqe_Name FROM EMPLOYEES; 



--- this is bad!!!! SELECT COUNT(FIRST_NAME) , LAST_NAME FROM EMPLOYEES; 

-- task 2  get the count of everyone in DEPARTMENT 90 : 
SELECT COUNT(*) as dep90_count  FROM EMPLOYEES 
where department_id = 90 ; 

---   AVG 

SELECT  13.113334 , ROUND(13.113334) AS ROUNDED_NO_DIGIT ,  
                    ROUND(13.113334  , 3) AS ROUNDED_3_DIGIT
FROM DUAL ; 

SELECT AVG(SALARY) AS AVERRAGE_SALARY  , 
        ROUND( AVG(SALARY) ) AS ROUNDED , 
        ROUND( AVG(SALARY) , 2 ) AS ROUNDED_2  
            
FROM EMPLOYEES; 
------

--- FIND OUT EVERYONE MAKES MORE THAN AVERAGE SALARY 

SELECT AVG(SALARY) AS AVERRAGE_SALARY FROM EMPLOYEES;

SELECT * FROM EMPLOYEES 
WHERE SALARY > 6461 ; 




SELECT          COUNT(*)  AS COUNT, 
                AVG(SALARY) AS AVERAGE_SALARY,
                SUM(SALARY) AS SUM_ALL,
                MAX(SALARY) AS HIGHEST, 
                MIN(SALARY) AS LOWEST
FROM EMPLOYEES ; 



------- GROUPING THE RESULT AND GENERATE OUTPUT 
--- FIND OUT COUNT OF EMPLOYEES IN EACH DEPARTMENTS 

--- GROUY BY CLAUSE CAN ONLY BE USED WITH MULTI ROW | AGGREGATE | GROUP FUNCTIONS 

SELECT DEPARTMENT_ID , COUNT(*)  AS EMP_COUNT
                    
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID
;

--- TASK 3 , FIND OUT MAX SALARY MIN SALARY , AVERAGE SALARY , SUM OF ALL SALARY OF EACH DEPARTMENST 
SELECT DEPARTMENT_ID , COUNT(*)  AS EMP_COUNT, 
                        Max(Salary) "Maximum_Salary", 
                        Min(Salary) "Minimum_Salary",
                        Round(Avg(Salary)) "Average_Salary"
                       
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID
;

--- HAVING CLAUSE CAN BE USED TO RESTRICT THE GROUPED RESULT AFTER GROUP BY  , 
--- AND IT CAN BE ONLY USED AFTER GROUP BY 

SELECT DEPARTMENT_ID , COUNT(*)  AS EmP_COUNt          
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)  > 10  ; 


---DML --->> DATA MAIPULATION LANGUAGE ------ SELECT , UPDATE , INSERT , DELETE
---DDL --->> DATA DEFINITION LANGUEG  ------  CREATE TABLES , DROPPING TABLES AND SO ON .. 
--- USING ALIAS FOR TABLE 
--- we use space and the aliases to give nickname for tabel 
--- this will help to identify column better when we have multiple 
--- tables with same column name 


SELECT * FROM employees ; 
SELECT e.first_name
FROM  employees e; 
SELECT * FROM DEPARTMENTS ; 
SELECT  d.department_name
from departments d ; 
SELECT e.first_name , d.department_name
from Employees e 
join departments d on e.department_id = d.department_id; 
---- one table join another table on some common condition 
--- task 6 -- get country name and region name by joining country and region table 
SELECT c.country_name , r.region_name
FROM COUNTRIES c
join REGIONS r on r.region_id = c.region_id; 
--- task 7 , find out the name of each Employees in Job history 
SELECT e.first_name , jh.end_date
FROM EMPLOYEES e
JOIN job_history jh on jh.employee_id = e.employee_id ; 
---- we can join more than 2 tables togeger on top of each other 
--- GOAL IS TO FIND OUT , ACCORDING TO A LOCATION -->>> COUNTRY NAME --->>> REGION NAME 
SELECT CITY , c.country_name , c.region_id , r.region_name
FROM LOCATIONS l
JOIN countries c on l.country_id = c.country_id
JOIN regions r on c.region_id = r.region_id
; 
--- GOAL IS TO FIND OUT , ACCORDING TO department_id --->>    A LOCATION -->>> COUNTRY NAME --->>> REGION NAME 
SELECT d.department_name,  l.CITY , c.country_name , c.region_id , r.region_name
FROM LOCATIONS l
JOIN departments d on l.location_id = d.location_id
JOIN countries c on l.country_id = c.country_id
JOIN regions r on c.region_id = r.region_id
; 




----------------SQL DAY 3-------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------


--                INNER JOIN VS JOIN 
-------------->>> THEY ARE SAME THING 
---------->>> RETURN DATA THAT MATCH FROM BOTH SIDE 


--Find out all employees count , by department nam


SELECT d.department_name , COUNT(*)
FROM employees e
JOIN departments d on e.department_id = d.department_id
--JOIN jobs j on e.job_id = j.job_id
GROUP BY d.department_name
ORDER BY 2 ; 

--- FIND OUT AVERAGE SALARY FOR EACH JOBS TITLE 



--- FIND OUT DEPARTMENTS COUNT FOR EACH LOCATION BY NAME:  CITY 
-----  DEPARTMENT TABLE + LOCATION TABLE 
SELECT COUNT(DEPARTMENT_NAME), l.CITY 
FROM departments d
JOIN LOCATIONS l on d.location_id= l.location_id
GROUP BY l.CITY --- this is the only column can show up in select statement


; 


--- FIND OUT DEPARTMENTS COUNT FOR EACH LOCATION BY NAME:  CITY 
----- restrict the result for city name start with S
-----  DEPARTMENT TABLE + LOCATION TABLE 
SELECT COUNT(DEPARTMENT_NAME), l.CITY 
FROM departments d
JOIN LOCATIONS l on d.location_id= l.location_id
--WHERE l.city LIKE 'S%'
GROUP BY l.CITY --- this is the only column can show up in select statement
HAVING l.city LIKE 'S%'
; 

--- FIND OUT DEPARTMENTS COUNT FOR EACH LOCATION BY NAME:  CITY 
----- restrict the result for DEPARTMENT COUNT MORE THAN 2 
-----  DEPARTMENT TABLE + LOCATION TABLE 
SELECT COUNT(DEPARTMENT_NAME), l.CITY 
FROM departments d
JOIN LOCATIONS l on d.location_id= l.location_id
--WHERE COUNT(DEPARTMENT_NAME)>2  --->> DO NOT USE AGGREGATE FUNCTION WITH WHERE CLAUSE
GROUP BY l.CITY --- this is the only column can show up in select statement
HAVING COUNT(DEPARTMENT_NAME)>2
; 



---- INNTER JOIN --->> JOIN 
    -- MATCHING PART FROM BOTH TABLE , ELIMINATE NO MATCH 
   
  --- FIND OUT MANAGER'S NAME OF EACH DEPARTMENT 
    --- return 2 column  MANAGER_NAME , DEPARTMENT_NAME
    
    --- I HAVE BUNCH OF PEOPLE WHO ARE MANAGERS , I ONLY HAVE THEIR ID 
    --- I WANNA KNOW THEIR NAME , AND THE NAME COME FROM EMPLOYEE TABLE 
    --- I LOOK UP THE NAME BY LOOKING AT EMPLOYEE_ID  
    
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d 
    join employees e on d.manager_id = e.employee_id
    ; 

--- JOIN = INNER JOIN 
--- RIGHT JOIN =  RIGHT OUTER JOIN 
--  LEFT JOIN = LEFT OUTER JOIN 
--- FULL JOIN = FULL OUTER JOIN 

--- HOW DO I KNOW WHICH TABLE IS RIGHT WHICH TABLE IS LEFT 
--- FIRST COME FIRST SERVE :  FIRST ONE IS LEFT , SECOND ONE IS RIGHT 
--- 


--- RIGHT (OUTER) JOIN 
     -- RETURN EVERYTHING PART FROM RIGHT TABLE AND ONLY MATCHING PART FROM LEFT TABLE
    
    --- WE GOT ONLY MATCHING DEPARTMENTS NAME 
    --- AND WE GOT ALL MATCHING AND NOT-MATCHING NAMES FROM EMPLOYESS
    --- FOR NON-MATCHING DEPARTMENTS WE GOT NULL CELLS 
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d RIGHT join employees e 
                     on d.manager_id = e.employee_id
    ; 
    --- WITH ABOVE QUERY , RETURN PEOPLE ARE NOT DEPARTMENT MANAGER 
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d RIGHT join employees e 
                     on d.manager_id = e.employee_id
    where d.manager_id is null ; 
    
    

--- LEFT (OUTER) JOIN 
     -- RETURN EVERYTHING PART FROM LEFT TABLE AND ONLY MATCHING PART FROM RIGHT TABLE
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d LEFT OUTER join employees e 
                     on d.manager_id = e.employee_id ; 

--- GET THOSE DEPARTMENTS THAT DOES NOT HAVE MANAGERS 
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d LEFT OUTER join employees e 
                     on d.manager_id = e.employee_id 
                     WHERE d.manager_id is null
                     ; 



-- FULL OUTER JOIN 
     -- RETURN MATCHING PART FROM BOTH TABLE + UNMACHING PART TOGETHER
    SELECT d.department_name , d.manager_id , e.first_name
    FROM departments d full OUTER join employees e 
                     on d.manager_id = e.employee_id
          ; 
          
--- SELF JOIN --->> JOINING A TABLE TO ITSELF 
--- FIND OUT ALL EMPLOYEES THAT REPORT TO A GUY WITH ID OF 100 ---
SELECT COUNT(*) FROM EMPLOYEES 
WHERE manager_id = 100 ; 



SELECT * FROM EMPLOYEES 
; 



--- USE THIS TO GERATE SENTENCE LIKE BELOW 
-- William	report to Gerald
--SELECT  emp.first_name as EMP_NAME , mng.first_name AS MNG_NAME
SELECT emp.first_name ||' REPORTS TO '|| MNG.first_name
FROM EMPLOYEES emp 
LEFT join EMPLOYEES mng on emp.manager_id = mng.employee_id;



SELECT
    first_name,
    job_title
FROM
    employees
    --JOIN jobs ON jobs.job_id = employees.job_id
    JOIN jobs USING(JOB_ID) ; 
    -- IF YOU HAVE SAME COLUMN NAME IN 2 TABLES THAT YOU ARE JOINING 
    --- YOU CAN USE USING(JOINING COLUNM) to make it shorter 
    
    --- THIS CAN BE ONLY USED 
    --  IF YOU HAVE SAME COLUMN NAME IN 2 TABLES THAT YOU ARE JOINING 
    
    
    
--- sub query
SELECT FIRST_NAME , SALARY
FROM EMPLOYEES
WHERE SALARY = 24000
; 
SELECT  MAX(SALARY) FROM EMPLOYEES ; 

--- SUB QUERY IS A QUERY INSIDE ANOTHER QUERY --- 
SELECT FIRST_NAME , SALARY
FROM EMPLOYEES
WHERE SALARY = ( SELECT  MAX(SALARY) FROM EMPLOYEES   )
; 

--- FIND OUT ALL EMPLOYEES WHO MAKE MORE THAN AVERAGE SALARY

SELECT  AVG(SALARY) FROM EMPLOYEES ; 

SELECT FIRST_NAME , SALARY
FROM EMPLOYEES
WHERE SALARY > 6461
ORDER BY 1 
; 

SELECT FIRST_NAME , SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT  AVG(SALARY) FROM EMPLOYEES )

;

--- TASK -- 
SELECT FIRST_NAME , DEPARTMENT_ID 
FROM EMPLOYEES 
WHERE employee_id IN (200,201,114,203 , 121 ,103,204,145,100,108,205 ); 


select  manager_id from departments where manager_id is not null; 

SELECT FIRST_NAME , DEPARTMENT_ID 
FROM EMPLOYEES 
WHERE employee_id IN (select  manager_id from departments where manager_id is not null ); 



--- select * from some table 
--- select * from (result of some sub query as a table)

SELECT * FROM 
            (
            SELECT FIRST_NAME , SALARY
            FROM EMPLOYEES
            WHERE SALARY > 6461 
            ) MORE_THAN_AVG
; 

---- limiting the result by column name 

SELECT * FROM (
                SELECT FIRST_NAME , ROWNUM AS MY_ROW 
                FROM EMPLOYEES 
            ) 
WHERE MY_ROW = 6 
; 



--- set operator --- ddl 
--- union , union all , minus , intersect 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY > 12000
--- ABOVE QUERY WILL RETURN 8 VALUE 
UNION ALL

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY BETWEEN 11000 AND 15000
--- ABOVE QUERY WILL RETURN 10 VALUE  AND HAS DUPLICATE
; 



SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY > 12000
-- RETURN 8 VALUE
UNION 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY BETWEEN 11000 AND 15000
-- THIS RETURN 10 VALUE AND 5 OF THEM ARE DUPLICATE SO THE RESULT IS 15
; 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY > 12000
-- RETURN 8 VALUE
MINUS 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY BETWEEN 11000 AND 15000
-- THIS RETURN 10 VALUE AND 5 OF THEM ARE DUPLICATE 
; 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY > 12000
-- RETURN 8 VALUE
INTERSECT 

SELECT FIRST_NAME , SALARY 
FROM EMPLOYEES 
WHERE SALARY BETWEEN 11000 AND 15000
-- THIS RETURN 10 VALUE AND 5 OF THEM ARE DUPLICATE 
; 
---  IMAGIVE YOU HAVE 2 TABLE WITH ONLY ONE COLUMN 
        --A                 B 
--- 10,9,8,7,6,5  MINUS  7,8,9    --->> 10,6,5
--- 10,9,8,7,6,5  UNION ALL  7,8,9    --->> 10,9,8,7,6,5,7,8,9  
--- 10,9,8,7,6,5  UNION  7,8,9    --->> 5,6,7,8,9,10
--- 10,9,8,7,6,5  INTERSECT  7,8,9    --->> 7,8,9



----->> SELECT 
/*  TYPE OF COMMANDS : 
DML   --- DATA MANIPULATION LANGUAGE : 
CREATE(INSERT) , READ(SELECT) , UPDATE , DELETE   : CRUD OPERATION 

DDL  ----- DATA DEFINITION LANGUAGE  : 
            CREATE TABLE , ALTER TABLE , DROP , TRUNCATE ........

DCL  ----- DATA CONTROL LANGUAGE 
           GRANT (ACCESSS ..) USER (PRIVILIGE) , REVOKE 
           
TCL  ----  TRANSACTION CONTROL LANGUAGE
          COMMIT , ROLLBACK , SAVEPOINT

*/

DROP TABLE SPARTAN; 


CREATE TABLE SPARTAN 
(
  ID NUMBER NOT NULL 
, NAME VARCHAR2(20 BYTE) NOT NULL 
, OFFER_COUNT NUMBER 
, BATCH NUMBER NOT NULL 
) 

SELECT * FROM SPARTAN; 
 
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (1, 'Lorene', 46, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (2, 'Westleigh', 13, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (3, 'Ambrosi', 42, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (4, 'Stephie', 10, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (5, 'Angel', 4, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (6, 'Fabien', 5, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (7, 'Flory', 83, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (8, 'Cherlyn', 75, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (9, 'Benedetta', 29, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (10, 'Adeline', 79, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (11, 'Marilee', 10, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (12, 'Miguelita', 87, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (13, 'Valentia', 6, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (14, 'Candi', 37, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (15, 'Ulises', 37, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (16, 'Ilene', 98, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (17, 'Chrissie', 1, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (18, 'Torrie', 90, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (19, 'Esdras', 62, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (20, 'Dagmar', 84, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (21, 'Lorens', 82, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (22, 'Chick', 6, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (23, 'Sibella', 53, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (24, 'Philipa', 65, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (25, 'Kati', 91, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (26, 'Rees', 91, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (27, 'Yulma', 50, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (28, 'Tadd', 94, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (29, 'Way', 14, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (30, 'Ronni', 81, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (31, 'Kali', 38, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (32, 'Ronald', 31, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (33, 'Marsh', 33, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (34, 'Trumann', 9, 12);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (35, 'Zacharia', 59, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (36, 'Wilek', 32, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (37, 'Joellyn', 2, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (38, 'Lorry', 50, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (39, 'Jerrie', 12, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (40, 'Nikolos', 17, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (41, 'Perl', 52, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (42, 'Aldric', 80, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (43, 'Rhea', 67, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (44, 'Torrance', 52, 10);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (45, 'Gwenette', 68, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (46, 'Emelen', 53, 13);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (47, 'Thadeus', 40, 14);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (48, 'Leicester', 55, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (49, 'Joann', 62, 11);
insert into SPARTAN (id, NAME, OFFER_COUNT, BATCH) values (50, 'Aeriel', 54, 12);


DELETE FROM SPARTAN 
WHERE ID = 50 ; 


UPDATE SPARTAN
  SET offer_count = 20
  WHERE ID = 49 ;