Math Probabilities
Homework #4 - SQL
Use the following (denormalized) database schema (and the attached tables) to write the queries.
Publishers (custid, name, city, phone, creditcode)
Bookjobs (jobid, custid, jobdate, descr, jobtype)
POS (jobid, poid, podate, vendorid)
Items (itemid, descr, onhand, price)
Po_Items (jobid, poid, itemid, quantity)
For each question, turn in the Oracle SQL query and the output. You should feel free to do these by hand (paper and pencil), or you may actually run them. I have created the tables and granted everyone in the class access to them. I would urge you to FIRST write them on paper, and THEN run them – just to be sure you understand them. You will not have access to Oracle SQL during the final exam. These take time, so please start early.
I have granted you all access to query the tables in my account. You can access them by typing suebrown., so to access the publishers table, type suebrown.publishers. As a second alternative, you can use the scripts in blackboard to create the tables in your own oracle space. It is your choice how you want to do this – just make sure that if you create the tables in your own directory that you do not change the data.
[6 pts] 1. How many different jobs are listed in the POS table?
[8 pts] 2. How many publishers fall into each of the three credit codes?
[10 pts] 3. List the jobid, podate, custid, and name for any jobs with purchase orders dated (podate) since February 1, 2006.
[10 pts] 4. List all publishers (at least custid and name), and for those with bookjobs, list all corresponding bookjob information.
[12 pts] 5. What is the combined value of the Purchase Orders for all kinds of paper?
Keep it general (without requiring user input) – we may add more types of paper to our inventory!
[12 pts] 6. List the details for all items with a price that is below the overall average price of the other items.
[12 pts] 7. Find all publishers that have not had any
Use the following (denormalized) database schema (and the attached tables) to write the queries.
Publishers (custid, name, city, phone, creditcode)
Bookjobs (jobid, custid, jobdate, descr, jobtype)
POS (jobid, poid, podate, vendorid)
Items (itemid, descr, onhand, price)
Po_Items (jobid, poid, itemid, quantity)
For each question, turn in the Oracle SQL query and the output. You should feel free to do these by hand (paper and pencil), or you may actually run them. I have created the tables and granted everyone in the class access to them. I would urge you to FIRST write them on paper, and THEN run them – just to be sure you understand them. You will not have access to Oracle SQL during the final exam. These take time, so please start early.
I have granted you all access to query the tables in my account. You can access them by typing suebrown., so to access the publishers table, type suebrown.publishers. As a second alternative, you can use the scripts in blackboard to create the tables in your own oracle space. It is your choice how you want to do this – just make sure that if you create the tables in your own directory that you do not change the data.
[6 pts] 1. How many different jobs are listed in the POS table?
[8 pts] 2. How many publishers fall into each of the three credit codes?
[10 pts] 3. List the jobid, podate, custid, and name for any jobs with purchase orders dated (podate) since February 1, 2006.
[10 pts] 4. List all publishers (at least custid and name), and for those with bookjobs, list all corresponding bookjob information.
[12 pts] 5. What is the combined value of the Purchase Orders for all kinds of paper?
Keep it general (without requiring user input) – we may add more types of paper to our inventory!
[12 pts] 6. List the details for all items with a price that is below the overall average price of the other items.
[12 pts] 7. Find all publishers that have not had any