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

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

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.