Welcome to CS347!

Instructor: Dr. Philip Cannata, phil.cannata@oracle.com, office hours: MW 3:00 - 3:45 in GDC 5.402.

TA: Yuepeng Wang, ypwang@cs.utexas.edu, office hours: TTH 2-3pm @ GDC 1.302 TA station Desk 3

TA: Cheng Fu, chengfu16@gmail.com, office hours: TTH 1-2pm @ GDC 1.302 TA station Desk 3

Canvas link.

We will be using Piazza.

Oracle scott/tiger database.

SQL from Class.

Types of Joins:

Inner Joins - the following 3 are essentially the same except for syntax:

select * from emp e, dept d where e.deptno = d.deptno

select * from emp e join dept d on e.deptno = d.deptno

select * from emp e inner join dept d on e.deptno = d.deptno

Outer Joins:

select * from emp e right outer join dept d on e.deptno = d.deptno

select * from emp e left outer join dept d on e.deptno = d.deptno

select * from emp e full outer join dept d on e.deptno = d.deptno

Self Join:

SELECT e1.*, 'Manager is: ' || e2.ename
FROM EMP e1 left outer join EMP e2
on e2.empno = e1.mgr

See also http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins for more details.

Group By:

select job, round(avg(sal), 2) from emp
where sal > 2000 
group by job
having round(avg(sal), 2) > 3000

Views:

create view emp_v1 as select ename, job, mgr from emp

select * from emp_v1

Interesting Queries:

select e.*, nvl(sal * comm, 0) from emp e

select t.*, (select dname from dept where deptno = 10) job, "Num" * "Percent" as nump
from (select 10 "Num", 0.7 "Percent" from dual) t
where "Num" = (select deptno from dept where dname = 'ACCOUNTING')

Oracle DBMS Architecture.

  1. Oracle Architecture Overview
  2. Oracle Instance Architecture
  3. Oracle Architecture
  4. Oracle DBMS Concepts

Data Management Salaries.

Click here to see the full report. This image comes from page 7 of the report.