Basic SQL: Select (Part 2)

Select statement is used to query data from database. Basic syntax is like below:
select [columns as alias]
from [tables as alias]
where [conditions]
group by [columns]
having [group conditions]
order by [columns]

Some example of select statement:
select id as ID, name as "Employee Name", dept as Department
from emp as e
where e.name like '%JAMES%
order by e.id;

select e.id as ID, e.name as "Employee Name", count(*) as Count
from emp as e
group by e.id, e.name
having count(*) > 10;

Some special select statements:

1. Inner join, where both tables must have the same data.
   select e.id, e.name, e.dept, d.name
from emp e, dept d
where e.dept = d.id;
   Or
   select e.id, e.name, e.dept, d.name
from emp e join dept d on e.dept = d.id;


2. Outer Join, where both tables might not have the same data.
   select e.id, e.name, e.dept, d.name
from emp e, dept d
where e.dept = d.id(+);
   Or
   select e.id, e.name, e.dept, d.name
from emp e left join dept d on e.dept = d.id;


3. Inner Query, a select statement within a select statement which technically is a temporary view.
   select e.id, e.name, e.dept, d.name
from emp e, (select * from dept where name like '%ACCOUNT%') d
where e.dept = d.id;


4. Union, mix two (or more) select statements' result together. Those duplicated records will only show 1 record and columns selected by all the statements must be the same.
   select e.id
from emp e
union
select e.id
from emp1 e;



5. Intersect, show only records that exist in both (or more) select statements. Same as Union, duplicated records will only show 1 record and columns selected by all the statements must be the same.
   select e.id
from emp e
intersect
select e.id
from emp1 e;


6. Minus, show only records in the first select statement that do not exist in the second statement.
   select e.id
from emp e
minus
select i.issueby
from invoice i;