Querying the Employees table of the HR schema.

--to see what tables exist:
SELECT * FROM cat;

--to see the definition of a table:
DESC employees

--all fields of all rows:
SELECT * FROM employees;

--specific fields of all rows:
SELECT last_name,employee_id,salary FROM employees;

--all fields of some rows:
Equality and relational operators: = != <> > < <= >=
SELECT * FROM employees WHERE salary=10000;
SELECT * FROM employees WHERE salary>=10000;

--specific fields of some rows:
SELECT last_name,employee_id,salary FROM employees WHERE salary=10000;

--character data too:
SELECT * FROM employees WHERE last_name>'S';
SELECT * FROM employees WHERE job_id!='SA_REP';

--date data too:
SELECT * FROM employees WHERE hire_date<='31-dec-1990';
SELECT * FROM employees WHERE hire_date>='1-jan-2000';

--Match Nulls or not Nulls. can not use =
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

--BETWEEN x AND y
SELECT * FROM employees WHERE salary BETWEEN 9000 AND 10000;
SELECT * FROM employees WHERE hire_date BETWEEN '1-jan-1995' AND '31-dec-1995';
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'D';
SELECT * FROM employees WHERE last_name NOT BETWEEN 'A' AND 'D';

--IN() a list of values
SELECT * FROM employees WHERE job_id IN ('MK_MAN','PU_MAN','SA_MAN');
SELECT * FROM employees WHERE commission_pct IN (.25,.15,.35);
SELECT * FROM employees WHERE commission_pct NOT IN (.25,.15,.35);

--ANY()  ALL()  tricky...
SELECT * FROM employees WHERE job_id=ANY('MK_MAN','PU_MAN','SA_MAN');
-- =Any == IN

SELECT * FROM employees WHERE job_id!=ALL('MK_MAN','PU_MAN','SA_MAN');
-- !=ALL == NOT IN

-- also < > <= >=



--LIKE pattern matching. % is 0 or more of any characters, _ is one character. Case-sensitive.
SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE last_name LIKE 'Sull%';
SELECT * FROM employees WHERE last_name LIKE '%son';
SELECT * FROM employees WHERE last_name LIKE '___';
SELECT * FROM employees WHERE job_id LIKE '%MAN%';
SELECT * FROM employees WHERE job_id NOT LIKE '%MAN%';
--to match % or _ literally:    Like 'arf\%' Escape '\'

--Logical AND and OR
SELECT * FROM employees WHERE salary>=10000 AND hire_date<'1-jan-1995';
SELECT * FROM employees WHERE salary>=10000 AND job_id LIKE '%REP%';
SELECT * FROM employees WHERE salary>=10000 AND job_id LIKE '%REP%' AND hire_date<'1-jan-1995';
SELECT * FROM employees WHERE salary>=10000 OR hire_date<'1-jan-1995';

--the DISTINCT values of a field:
SELECT DISTINCT department_id FROM employees;
--distinct combinations:
SELECT DISTINCT job_id,department_id FROM employees;
SELECT DISTINCT manager_id,job_id FROM employees;

--Count() number of rows:
SELECT Count(*) FROM employees;
SELECT Count(1234) FROM employees;
SELECT Count(*) FROM employees WHERE salary=10000;
SELECT Count(*) FROM employees WHERE commission_pct IS NOT NULL;
--same. Nulls do not count.
SELECT Count(commission_pct) FROM employees;
SELECT Count(DISTINCT department_id) FROM employees;

--ORDER BY
SELECT * FROM employees ORDER BY last_name;
SELECT * FROM employees ORDER BY department_id;
--cascading sorts:
SELECT * FROM employees ORDER BY department_id,last_name;
--Descending order sort:
SELECT * FROM employees ORDER BY salary DESC;

--Null is "biggest" (counterintuitive)

--Calculated columns. Aliases
SELECT last_name,salary,salary*1.2 FROM employees;
SELECT last_name,salary,salary*1.2 AS "+20% raise" FROM employees;

--alt. syntax: Order By 1,2
SELECT last_name,salary,salary*1.2 AS "+20% raise" FROM employees ORDER BY 3,1;

--column aliases can not be used in Where, Group By or Having clauses.

--if table aliases defined in From clause, can not use table name in
  Select or Where clauses.


--Group functions
--Count() see above examples
SELECT Sum(salary) FROM employees;
SELECT Avg(salary) FROM employees;
SELECT Min(salary) FROM employees;
SELECT Max(salary) FROM employees;
SELECT Min(hire_date) FROM employees;
SELECT Min(last_name) FROM employees;
--more aggregate functions:
SELECT Median(salary),Stddev(salary) FROM employees;
--correlation coefficient.   any Nulls are skipped
SELECT Corr(salary,commission_pct) FROM employees;
SELECT Corr(weight,cuteness) FROM pet;


--GROUP BY
SELECT department_id,Count(*) FROM employees GROUP BY department_id;
SELECT department_id,Sum(salary) FROM employees GROUP BY department_id;
SELECT job_id,Avg(salary) FROM employees GROUP BY job_id;
SELECT job_id,Count(*),Min(salary),Avg(salary),Max(salary) FROM employees GROUP BY job_id;
SELECT manager_id,Count(*) FROM employees GROUP BY manager_id;
SELECT department_id,Min(hire_date) FROM employees GROUP BY department_id;
SELECT department_id,Min(hire_date) FROM employees GROUP BY department_id ORDER BY Min(hire_date);
SELECT department_id,Min(hire_date) FROM employees GROUP BY department_id ORDER BY 2;
SELECT department_id,Min(hire_date) minhiredate FROM employees GROUP BY department_id ORDER BY minhiredate;
SELECT department_id,Min(hire_date) "min hire date" FROM employees GROUP BY department_id ORDER BY "min hire date";


--HAVING filters the group values
SELECT department_id,Count(*) FROM employees GROUP BY department_id HAVING Count(*)>10;
SELECT department_id,Sum(salary) FROM employees GROUP BY department_id HAVING Sum(salary) BETWEEN 10000 AND 30000;
--alias can not be used in Having


GROUP BY ROLLUP
--totals for each subgroup
--one grouping, get summary of it at end:
SELECT department_id,Sum(salary) FROM employees GROUP BY ROLLUP(department_id);
--two groupings, get summary of each combo and total summary at end:
SELECT department_id,job_id,Sum(salary) FROM employees GROUP BY ROLLUP(department_id,job_id);

SELECT department_id,job_id,Sum(salary) FROM employees GROUP BY CUBE(department_id,job_id);



Set operators.     all same precedence.
--union of rows.  must be same number of columns of same datatypes.
Select x,y,z From t1
 UNION [ALL]          --ALL: duplicates too
Select a,b,c From t2
[Order By 2]          --column position

--intersection
Select x,y,z From t1
 INTERSECT
Select a,b,c From t2

--difference
Select x,y,z From t1
 MINUS
Select a,b,c From t2