Homework Assignment # 7

Due Wednesday October 30, 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 HW7.py (Please don't put you name on the homework for privacy reasons.)

# HW7.py
# Homework 7
#  
# 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 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 teh 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))

1. select * from emp

2. select distinct job from emp

Hint: list comprehension also works for build a set in python. So, you can build a set and then iterate through that to create a list.

3. select ename from emp where sal > 2500

4. select ename from emp where sal > 2500 and deptno = 20

5. select ename from emp where (sal > 2500 and deptno = 20) or deptno = 10

6. select ename from emp where (sal > 2500 and deptno = 20) or deptno = 10 order by 1

Hint: see the sorted() python function.

7. select ename from emp where (sal > 2500 and deptno = 20) or deptno = 10 order by 1 desc

8. select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5

This is an example of a subquery being used instead of a table. Try running the subquery by itself first to see what it returns.

9. select ename, job from emp where job like 'A%'

Hint: see the Regular Expressions Section in the Supplement Advance Topics link that can also be found at the top of the Class Calendar.

import re
regex = re.compile('A.*')

might be useful.

10. select ename, job from emp where job in ('CLERK', 'SALESMAN')