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 12-1pm @ 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')

Hierarchical (Recursive) Queries:

  • select lpad(ename, length(ename) + (level*2)-2, '-') from emp
    start with ename = 'KING'
    connect by prior empno = mgr;
  • select lpad(ename, length(ename) + (level*2)-2, '-') from emp
    start with ename = 'SMITH'
    connect by prior mgr = empno;

Analytic SQL:

  • select job, rpad('*', sal/100, '*') hist from emp order by sal -- histogram
  • select job, nvl(ename, 'Subtotal') as ename, sum(sal) salsum from emp
    group by rollup (job, ename)
    order by 1,2
  • select nvl(job, 'Total') job, 
    CASE
    WHEN job is null and ename is null THEN ' '
    ELSE nvl(ename, 'Subtotal') 
    END ename, sum(sal) salsum from emp
    group by rollup (job, ename)
    order by 1,2
  • select nvl(job, 'Total'), nvl(ename, 'Subtotal'), round(sum(sal)) from emp
    group by rollup (job, ename) having ename is null
    order by 1
  • select nvl(job,'Total'), 
    CASE
    WHEN job is null and ename is null THEN ' '
    ELSE 'Subtotal'
    END,
    sum(sal) "SalSum", round(avg(sal)) "SalAvg" 
    from emp group by rollup (job, ename) having ename is null

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 16 of the report.