Welcome to CS347!

Instructor: Dr. Philip Cannata, phil.cannata@oracle.com, office hours: M 5:30 - 6:15 in GDC 1.304, W 3:00 - 3:45 in GDC 5.416

Proctor: Ashley Ng, ashleyliann.ng@gmail.com, office hours: Tuesday 5:30p - 7p Thursday 2:30 - 4p in 3rd floor lab of GDC

We will be using Piazza.

Oracle scott/tiger database.

SQL from class.

Group by:

  • select job, avg(sal) from emp group by job

Subqueries:

  • select ename, job, sal, (select round(avg(sal))from emp where job = e.job) from emp e order by 2, 3
  • select rownum, t.* from (select ename, sal from emp order by sal) t where rownum <= 10
  • select ename, sal from emp where sal in (select sal from (select ename, sal from emp order by sal desc) where rownum <= 5)
  • create table emp3 as (select * from emp where sal > 1000)

Views:

  • create view emp_v1(Name, Job, Manager) as select ename, job, mgr from emp
  • select * from emp_v1
  • update emp_v1 set NAME='Chris' where Name = 'phil'
  • drop view emp_v1
  • create view emp_v1(Name, Job, Manager, Department) as select ename, job, mgr, deptno from emp
  • select * from emp_v1 e, dept d where e.department = d.deptno
  • create view emp_v2(Name, Job, Department, Location) as select ename, job, e.deptno, loc from emp e, dept d where e.deptno = d.deptno
  • select * from emp_v2
  • update emp_v2 set Name = 'Smyth' where Name = 'Smith'

    This will give an eror - ORA-01779: cannot modify a column which maps to a non key-preserved table

Analytic SQL:

  • select job, rpad('*', sal/100, '*') hist from emp order by sal -- histogram

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.