Homework Assignment # 9

Due Thursday November 21, 2013 by 11:59 PM. See the following link for turnin instructions:

https://piazza.com/class/hkuo0jreo4q3wa?cid=39

[10 Points] Please have the following at the top of HW9.py (Please don't put you name on the homework for privacy reasons.)

# HW9.py
# Homework 9
#  
# UT EID: XXX999, UTCS username: XXXXXXXX 
# CS313e, Fall 2013, Dr. P. Cannata 
# Department of Computer Science, The University of Texas at Austin --

Using the emp tuple and dept tuple shown below, write python code that uses list comprehension
to create a list that contains results similar to the results gotten from executing
the SQL code found in each problem below. (See the SQL tutorial link on the class calendar for help understanding the SQL.)
The python code should be similar to the following, where the column name(s) are printed
as the first line of the results: print [ (i[1], i[2]) for i in emp[0:1]] print [ (i[1], i[2]) for i in emp[1:] ]

emp = (('EMPNO', 'ENAME', 'JOB', 'MGR', 'HIREDATE', 'SAL', 'COMM', 'DEPTNO'), (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.0, 0.0,
20), (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.0, 300.0, 30), (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.0, 500.0, 30), (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.0, 0.0, 20), (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.0, 1400.0, 30), (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.0, 0.0, 30), (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.0, 0.0, 10), (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.0, 0.0, 20), (7839, 'KING', 'PRESIDENT', 0, '1981-11-17 00:00:00', 5000.0, 0.0, 10), (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.0, 0.0, 30), (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.0, 0.0, 20), (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.0, 0.0, 30), (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.0, 0.0, 20), (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.0, 0.0, 10))

dept = (('DEPTNO', 'DNAME', 'LOC'), (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'))

1. select ename, dname, emp.deptno, dept.deptno from emp, dept

2. select ename, dname, emp.deptno, dept.deptno from emp, dept where emp.deptno = dept.deptno

3. select ename, dname, emp.deptno, dept.deptno from emp, dept where emp.deptno = dept.deptno and sal > 2000

4. select sal, ename, dname, emp.deptno, dept.deptno from emp, dept where emp.deptno = dept.deptno and sal > 2000
union
select sal, ename, dname, emp.deptno, dept.deptno from emp, dept where emp.deptno = dept.deptno and sal < 1000
order by 1

5. select dept.deptno, emp.deptno, emp.ename, dept.dname from emp left outer join dept on (emp.deptno = dept.deptno) order by 1

Before doing this query, run the following in sqlDeveloper

INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7999, 'SMITH', 'MANAGER', 7782, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 3500, null, 50 );

and add a similar tuple to the emp tuple

Use the RightOuterJoin.py code on the class calendar as a guide for solving this problem.

6. [2 Points of Extra Credit] select dept.deptno, emp.deptno, emp.ename, dept.dname from emp full outer join dept on (emp.deptno = dept.deptno) order by 1

Don't make this too hard, you're allowed to think in terms of the union of two sets. I'm not looking for an optimal solution.