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: 2pm-3pm TTH at GDC1.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

Group By:

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

Group By in a Subquery:

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

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

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.