Course 51145
MW 4:00 to 5:30 in GDC 2.216
Syllabus
Honesty
Code of Conduct

 

 

cs347 Data Management Dr. Philip Cannata

Exercises and Homework

Book Exercises:

Use the following schemas for the exercises in the Book

---------------------------------------------

Homework:

  • SQL HW Guidelines
  • The My Guitar Shop Schema for the SQL Homework is at this link.
  • A picture of the My Guiitar Shop Logical Model is at this link.
  • The My Guitar Shop Data Model is at this link.
  • Chapter 3 of the book, which is needed for this homework, is online at this link undert the "Free Downloads" tab under the book's description.

Homework 1- Due Monday January 25, 2016

Homework 1

Solutions

Homework 2- Due Wednesday February 3, 2016

Homework 2

Solutions (there is also a solution named HW2.py that you can load into dist/bin/jython)

Homework 3- Due Wednesday February 10, 2016

Homework 3

Solutions

Midterm Exam - TBD.

Solutions

Final Exam - TBD

Project Details

Requirements

Initial Project Review - TBD

Register your Project Group here.

Initial Project Review Schedule

Final Project Review - TBD

Project Grading Rubric

Register your Project Group here.

Final Project Review Schedule

Grades are posted on Canvas

Grade Distribution, click here for a larger view.

Data Management

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

TA: Chia-Chen Hsu, cchsu@utexas.edu; Office Hours: Tuesday/Thursday 9-10 am at TA station, Desk 4.

Proctor: Brandon Hollowell, cchsu@utexas.edu; Office Hours: Tuesday/Thursday from 3:30 to 4:30 on the lobby of the fifth floor of GDC

Canvas link.

We will be using Piazza.

We will be using Top Hat, course number 504612

Make sure you are registered with piazza for this course and be sure to check and read your piazza email several times a day because this will be the primary means of communication outside of class time. Dr. Cannata will not alter his proceedure of always sending all of his messages to the entire class.

Students with disabilities link.

Students with disabilities may request appropriate academic accommodations from the Division of Diversity and Community Engagement, Services for Students with Disabilities, 512-471-6259, and the link above.

This is the Database Management System (DBMS) Architecture that we will be studying in this course. See the following articles for a detailed discussion of the Oracle Architecture:
  1. Oracle Architecture Overview
  2. Oracle Instance Architecture
  3. Oracle Architecture
  4. Oracle DBMS Concepts

Click here for a larger view.

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.

Views:

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

Subqueries and Group By:

  • select e.*, (select avg(sal) from emp where mgr = e.empno) avg_sal
    from (select empno, mgr, sal from emp) e
    where empno in (select mgr from emp)
    order by 1;


  • select mgr, avg(sal) from emp
    where sal >= 3000
    group by mgr
    having avg(sal) > 3000
    order by 1;

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