Homework 1

Due Date: 2/14/2018
Points: 40
 
1) Import two data tables.
- Go to https://simple.wikipedia.org/wiki/List_of_U.S._states_by_population
- Copy and paste all 50 states into an EXCEL sheet.
- Delete Puerto Rico. It is not a state. 
- We will leave Washington DC because it creates an interesting problem.
- In EXCEL delete all columns except for State and Population Estimate.
- Format the cells in the number column so that they don’t contain commas for thousands. For example: 51,947 should be 51497. Make sure you don’t accidentally add a period with two digits after it.
- Save as file population.csv
- Go to https://www.statista.com/statistics/196010/total-number-of-registered-automobiles-in-the-us-by-state/
- Click on the XLS button to download data as EXCEL sheet.
- Click on Access Data in the new spreadsheet.
- Delete the first five rows.
- Delete the (empty) first column.
- You may delete the Overview sheet.
- Remove the commas.
- Save as file cars.csv
- There is amazingly little data cleaning required. Just remove the stars after a few of the states (Indiana, for example) and make sure that Washington DC has the same name in both spreadsheets. That’s it.
- Import population.csv into an Oracle table POPULATION using SQL Developer import functionality. This requires that you first create an empty table POPULATION with the correct two columns.
- Import cars.csv into an Oracle table CARS using SQL Developer import functionality. This requires that you first create an empty table CARS with the correct two columns.
 
 
- In SQL Developer display the two tables by using two SELECT statements.
 
Show the SELECT statements you used to show the tables in your homework printout. [1+1 points]
 
Show the two tables in your printout. [2+2 points]
 
This is a general rule. Show the Select statement (SQL statement) and show the result of the Select statement. Always. Anything that you don’t show will be assumed “not done” or “not working.”
 
Whenever the result is longer than 70 rows, just show the first 50 and the last 20 rows. (This won’t be much of a problem in this homework.)
 
There are several ways to create printouts of the SQL output.
 
You can copy and paste into a Word file, or you can do PrintScreen (button on your keyboard) dumps, or you can use the snip tool and a Word file, or you can export the data into a file from SQL Developer. All of those are fine.
 
KEEP THOSE TWO TABLES. WE WILL USE THEM IN THE NEXT HOMEWORKS TOO.
 
-------------------------
 
 
2) Write SQL Queries for ALL the following problems.
Show a printout of both the query and the result.
 
 
[13 * 1 point]
 
a) Show POPULATION in alphabetical order.
 
b) Show CARS in alphabetical order.
 
c) Every car is owned by ONE person (we will assume). Show all states in alphabetical order, and show in the second column how many people in the state do not own a car. 
 
d) Is there any state where there are more cars than people? Answer this by subtracting cars from people, sorting the result by the difference DESCENDING and looking for a negative number at the bottom. 
 
e) Show all states in alphabetical order, and show what percentage of the population of each state does not own a car.
 
Show only two digits after the decimal point. Here and in all other questions.
 
f) Compute the total number of people in the United States. 
 
g) Compute the total number of cars in the United States.
 
h) Using results from g) and f) compute what percentage of the population of the United States does not own a car.
 
i) Show all states in alphabetical order. Assuming that every person in a state has a family member who owns a car (not true in real life), compute how many people have to fit into a car for each state. Round UP to “whole people.” We don’t want half people. 
 
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm
 
j) Redo the SQL query of i) but now order the result so that the richest states come first and the poorest last. Do not order by alphabet.  A state is considered rich if few people have to fit into a car, and poor if many people have to fit into a car.    
 
k) Using the results from h) display only the states that have a percentage of the population not owning a car that is larger than the United States average (see question h)). We could call those “poor states.” Order the result in alphabetical order. Show the percentages also. 
 
l) Same as k), but now order the results so that the poorest state comes first.
 
m) Show all states that have fewer than 1 Million people and fewer than 300,000 cars. Show the states, the number of people and the number of cars for each state. 
 
 
3) Study the function sqrt in Oracle. For example, here.
 
https://docs.oracle.com/ 
 
Make sure you understand what the input/output format is.
 
Next, remind yourself what complex numbers are. Here is a very easy introduction. But hopefully you won’t need it.
 
http://www.dummies.com/education/math/calculus/how-to-perform-operations-with-complex-numbers/
 
https://www.varsitytutors.com/hotmath/hotmath_help/topics/absolute-value-complex-number
 
For each of a), b), c), d) below write its own main program to test it.
The main program should send the result to the screen.
 
a) Write a procedure COMPADD that takes four in parameters 
real1, imag1, real2, imag2 and two out parameters real3, imag3.
 
COMPADD should return the sum of (real1, imag1) and (real2, imag2)
through the out parameters real3, imag3.
 
Test data (5, 6), (3, 1). Desired output on the screen:
 
(5 + 6i) + (3 + 1i) = 8 + 7i
 
[3 points]
 
b) Write a procedure COMPSUBTRACT that takes four in parameters 
real1, imag1, real2, imag2 and two out parameters real3, imag3.
 
COMPSUBTRACT should return the difference of (real1, imag1) and (real2, imag2) through the out parameters real3, imag3.
 
Test data (5, 6), (3, 1).
 
[3 points]
 
c) Write a procedure COMPMULT that takes four in parameters 
real1, imag1, real2, imag2 and two out parameters real3, imag3.
 
COMPMULT should return the product of (real1, imag1) and (real2, imag2) through the out parameters real3, imag3.
 
Test data (5, 6), (3, 1).
 
[3 points]
 
d) Write a function ABSOLUTEVAL that takes two in parameters 
real1, imag1 and no out parameters that returns the absolute value of (real1, imag1).
 
Test data (4, 3) 
 
Desired output on the screen:
 
ABSOLUTE VALUE (4 + 3i) = 5
 
[3 points]
 
 
4) Write a new main program for that loops over every pair of (real, imag) values from
 
(-5, -5)(-5, -4)(-5, -3)…(-5,0)(-5,1)…(-5,5)
(-4, -5)(-4, -4)(-4, -3)…(-4,0)(-4,1)…(-4,5)
…
(4, -5)(4, -4)(4, -3)…(4,0)(4,1)…(4,5)
(5, -5)(5, -4)(5, -3)…(5,0)(5,1)…(5,5)
 
Use a nested FOR loop to do this.
 
 
Your program should send the absolute value of every one of
those complex numbers to the screen by calling the procedure
from 3)d). 
 
See 3) d) for the desired output format.
 
Make sure your output follows the order given above.
In other words, the first line on the screen should be for (-5, -5).
The second line should be for (-5, -4) etc.
The very last line on the screen should be the absolute value for (5, 5).
 
You are permitted to have your program write “+ -3” even though your math
professor would not allow it. But put a blank between the + and the minus. 
 
[4 points]
 
5) a) Create a table COMPLEX that has two columns REAL and IMAG.
Insert
into that table the rows:
 
(1, 0)
(1, 1)
(0,
2)
(-2, 1)
(-3, 0)
(-3, -3)
(0, -4)
(4,
-5)
 
[1 point]
 
b) Then
write a main program that MUST USE A CURSOR. 
 
The main program
should call the function ABSOLUTEVAL for every row in the table COMPLEX. In other words, your output should have 8 rows showing REAL, IMAG and ABSOLUTEVAL. Similar to question 4). The output format should be like in question 3)d).
 
[4 points]
 
================================================================
 
THE FOLLOWING APPLIES TO ALL ASSIGNMENTS:
 
- Create a nice looking cover page with 
 your name, assignment number, class number and a descriptive
 title of the assignment. You may be creative to make it nice looking. 
 
 All parts of your assignment need to be stapled 
 together, or held together by a folder. Points will be taken off
 for not complying with these rules.
 
 If you use a folder, write your name ALSO on the folder. Or use
 a folder with a transparent cover page.
 
 No handwriting anywhere.
 No handwriting anywhere.
 No handwriting anywhere.
 
 Make things look nice and professional. 
 
- Documentation:
 Do a reasonable amount of internal documentation.
 Internal documentation typically means that you explain
 your code or SQL statements.
 No external documentation unless I specifically ask for it.
 
- Test runs:
 Show your table after you build it. Show testruns 
(that means all SQL statements and their results).
 Show as much as you can. The burden of proof is up to you.
 NO TESTRUNS, NO POINTS. 
 (Or very, very few.) You may record the results in a file.
 Thus, you don't need to do screen dumps, you print the file.
 You will not be punished for too many test runs.
 
- Hardcopy:
 All code, documentation, and testruns will be accepted as Hardcopy
 (Printout) ONLY. No email, No Google documents, etc. 
 
- Grading:
 Grading takes into account correctness of the results, but also
 how well they are documented, and what their visual appearance is.
 Things should look nice and PROFESSIONAL. Not sloppy. Use a spelling
 corrector for your comments or (later on) write-ups. Some people do this
 very nicely. Don't cut corners here, or you will look bad in comparison.
 PROGRAMS MUST BE INDENTED.
 
- Cheating:
 The usual policies apply. 0 points for "suspiciously similar" results.
 Report to the Dean in case of repetition.
 
- Files:
 You must keep all your files of every assignment up to the end of
 the semester. If there is any doubt about your assignment I will
 ask you to demonstrate it online.
 
- Late policy:
 I will take off 8 points for being late up to 1 week. Programs
 that are handed in more than a week late will receive no credit
 at all, unless you have a good reason with WRITTEN documentation
 (usually medical).