FE 513 Practical Aspects of Database Design Assignment II Please complete the assignment using PostgreSQL and submit the SQL script. Also please provide a pdf report putting the results and codes...

Attached as PDF


FE 513 Practical Aspects of Database Design Assignment II Please complete the assignment using PostgreSQL and submit the SQL script. Also please provide a pdf report putting the results and codes together. It’s a good way to write your report in the following manner: (1) quote the original question (2) your PostgreSQL codes, (3) and result(snapshot are accepted) if it’s not trivial to show. 1 Basic Query (50pt) 1. Given the data of banks al 2001.csv , define the table structure accordingly in the database. 2. Import data from banks al 2001.csv 3. Query the table and count the number of banks for each quarter. 4. Query the table and report the average of asset for each bank. 5. Query the table and report the bank id who has the second largest asset for second quarter. 6. Query the table and report the bank id whose equity is over 10% of its asset in the first quarter(hint: equity = asset-liability). 7. What is the average liability value for the banks whose first quarter asset value is higher than the average asset value of the first quarter. Complete this task in one query. 2 Query Multiple Tables (50pt) 1. Given the data of banks sec 2002.csv and banks al 2002.csv , define the table structure for each one in the database. 2. Import data from banks sec 2002.csv and banks al 2002.csv . 3. Delete duplicate rows from banks sec 2002 4. Select proper join manner to join banks sec 2002 and banks al 2002. Make sure that all data from banks sec 2002 are kept in the joint table. Report the first 10 observations of joint table. 5. Create a new table banks total. Insert the values of previous joint table into banks total. And set a primary key for the table. 6. How many banks have security over 10% of its’ asset value for each quarter of 2002. 7. How many banks have liability over 90% of assets in the first quarter of 2002 but goes below 90% in the second quarter of 2002. 8. Define a function that takes a list of numbers as input and outputs the mean, maximum and minimum. Test the function with your result from question 2.6.(In Q 2.6, one number is obtained from each quarter. So the result of Q 2.6 are four numbers in total. Test your function with these four numbers.) 9. Export the joint table banks total to a csv file. 1