The project involves a series of assignments and a final exam that require using PostgreSQL to perform various data manipulation and analysis tasks.
Part I: Basic and Advanced Queries In this assignment, you'll work with a dataset related to banks. You'll define a table structure for the data, import data from a CSV file, and then perform basic and advanced queries on the data. The basic queries involve counting the number of banks for each quarter and reporting the average asset for each bank. The advanced queries involve identifying high earners in different departments based on salary.
Part II: Querying Multiple Tables and PostgreSQL API in R For this assignment, you'll work with data from multiple tables related to banks. You'll import data, perform join operations, create a new table, and then execute various queries on the combined data. Additionally, you'll use PostgreSQL API in R to connect to the database, import data from a CSV file, and retrieve data from a table.
Final Exam: SQL and User-defined Function in R The final exam consists of two parts. The SQL section involves importing bank data into a PostgreSQL database, creating primary keys, and performing various SQL queries. You'll find the highest asset observation for each bank, analyze query plans, and answer questions about the data. The second part involves writing a user-defined function in R. This function will download daily stock data for a given stock ticker and time period, calculate rolling window statistics (mean and standard deviation), store results in a dataframe, and plot the data.
Throughout these assignments and the final exam, you'll be using PostgreSQL for data manipulation and analysis tasks, and in some cases, you'll also work with R to interact with the database and perform specific tasks. The final results will be submitted as SQL scripts, PDF reports containing codes and results, and possibly CSV files for exporting data.