SWEN-344 DB Practicum

Make sure you have completed the initial setup instructions for the Practicum.

Output Format:

Your output must print to the console and should be (similar to) the following AND MUST BE IN NUMERICAL ORDER OF THE QUESTIONS (1-10)!! Do not reset the DB in between questions.
You MUST print the question # as the 1st line of your answer to receive full credit.

Question-#:
[The answer … ]
All numeric values must be printed with 2 decimal values precision (e.g. xxx.yy)
If your answer has multiple rows of data, each row must be on a separate line and each field value must be separated by a comma (',')
e.g.

    Question-1:  
    Olivia,Young,Biology,Assistant Professor,69000.00,2020-04-12,2020-04-12,None,
    Daniel,Wilson,Biology,Assistant Professor,75000.00,2023-02-08,2023-02-08,None,
    Christopher,Hall,Biology,Assistant Professor,68000.00,2019-03-25,2019-03-25,None,
    Jason,Brooks,Biology,Assistant Professor,67000.00,2018-11-23,2018-11-23,None,

Commit and push your work per the deadlines .

Test Questions:

Answer the following questions, using SQL and Python. Run the code as a unit test, that executes and runs code/ queries to satisify the questions. All output must be to the console. These tests will be implemented in test/test_finance.py. Corresponding DB logic and code should be in in src/finance.py. You may add other files if you wish

Part 1: (Tag as ‘part-1’ in gitlab when finished)

Question-1

  • Output a report that lists all faculty by dept, lastname, firstname, title, salary, hire date, contract start, contract end. Order by dept name then hire date. Department names should be in A-Z order, and hire date should be by earliest date first.

Question-2

  • Add a new hire ‘John Watson’, Adjunct in Biology, hired 4/1/1899, contract start same date as hire date; Salary 2,100. You will need to modify multiple tables. After creating the new entries, list all faculty again, using the same format as Question-1.

Question-3

  • Modify Sherlock Holmes’ contract to end in 12/15/1893
  • List the all the contracts after making the changes, Make sure you display the actual values of the joined fields, not the IDs. Order the output by contract start date (earliest date first)

Question-4

  • List all faculty hired after 2021. Use the same format output as Question-1.

Grading:

  • Full credit requires correct and maximum use of SQL

  • 100 points total

  • Q#1: 10 points

  • Q#2: 15 points

  • Q#3: 10 points

  • Q#4: 5 points

  • On-time submission of initial tests: 5 points

  • Correct CI output & coding style (see expectations), following instructions: 15 points