D211203 Project Solution

All source files are in individual_project.zip.

The main source file for schools is schools_src.csv. The main source file for degree_salary is degree_salary_src.csv. The main source file for school_salary is school_salary_src.csv.


The salaries in each of the salary relations are the starting median starting salary for students, the mid-career median salary for graduates, and the 90th percentile (highest 10%) salaries for graduates at mid-career.

Some of the attributes in the relations are unknown and, should therefore, be set to NULL. If the attribute is NULL in a source file, the attribute will be an empty string ''. You will need to do arithmetic on the salaries, so be sure that ultimately, you load them as numbers.





The United States
Military
Academy (Army) Northeastern 74000.00 120000.00
United States
Naval Academiy (Navy) Southern 77100.00 131000.00

Miami Southern 58760.00
Pittsburgh (Pitt) Northeastern 45700.00 74000.00
150000.00


Answer the following questions by performing SQL operations on the database. Your SQL answer must work for any data or changes to data in this database. Your data answer must be in a single table you generated using SQL, not several tables that need to be combined manually to get the answer to the question. You cannot use data values in a query unless they are specified in the question. If you need another value, generate it with a query and use that query or its resulting table in the query that ultimately produces the answer. Unless specified otherwise, remove duplicates from your answers as appropriate. Show the answer (that is a table of data that answers the question) and the SQL used to generate the answer.

a. What is the SQL for creating the relations and loading the files into the relations. Be sure to include any key, foreign key, or check constraints that you see when creating the relations. Include any transformations you do on the data to enable the data to be loaded/used and made consistent. (20 points)




b. What is all the information in the school_salary relation about tech schools in descending order by starting median salary? (10 points)

c. What is the degree and salary information for the degree with the highest 90th percentile midcareer salary? (10 points)

d. What is all the salary information for the schools in the Big Ten in decreasing order of midcareer 90th percentile salary? (10 points)


f. List the degree and starting median salary in descending order by median salary for degrees about information, marketing, accounting, finance, or business. Use FORMAT and CONCAT to create a string for the salary that has a starting $ and a comma after the thousands place, for example: $49,200.00 . The result columns should be named degree and starting_median. (10 points)

g. What schools in the Big Ten have a higher median starting salary than the median starting salary of Management Information Systems (MIS), and what are their median starting salaries? Format the starting salaries with a starting $ and a comma after the thousands place in a result column called starting_median. List the schools in the answer in descending order by median starting salary. (10 points)