Solved–Database Systems Homework #6 –Solution

Description

1. Export and back-up your investment database using the pg_dump.

pg_dump will dump an entire database with the ‘-d’ flag, or individual tables with the ‘-t’ flag:

Example: pg_dump -U postgres -d investments > backup.sql

Example: pg_dump -U postgres -t high_performers -t candidates -t final_choices > backup.sql

This will create a backup of all your schema, tables, and the data that lives in them.

You can compress, encrypt, and store this data off-site – to be restored easily if needed.

You can re-create the entire database by simply running the backup file as a regular SQL script:

psql -U postgres -f backup.sql

1. Create a VIEW of your portfolio – which will be a query of the current status, company name, most recent price, and other summary information, so you and your boss can quickly monitor your investments.

(we are presuming that new data is being added to the prices table daily).

2. Export the results of that VIEW to .csv so you can email it to your boss, and so your

non-programmer colleagues can track your portfolio status by viewing it in a spreadsheet.

Hint: use the following flag when you run your script: -tAF,

Example: psql -U postgres -tAF, -f your_script.sql > output_file.csv

4. Look up the last price of 2017 of each of the equities you chose, and determine the percent return you (your company) would have made on each of them if you had invested in your portfolio

on Dec 30, 2016.

Submit the SQL code you used to create your VIEW, and the pg_dump and psql commands that you used to export the data.

Submit the annual return for 2017 of each of your 10 choices, as well as the total return of your portfolio.