If you are on this page it is likely that you are considering a job with BIF Technologies, Corp as a Database Administrator (DBA). The interviewing process for a BIF DBA involves (1) a meetup to make sure that you are a human being, (2) a technical interview to see if you are conversant in the Oracle technologies that we use at BIF, and (3) a database administration ability assessment.
The database administration ability assessment is comprised of a few DBA challenges. If you have a few years of Oracle under your belt these will be no problem. Furthermore, if you make it this far we are already considering you for the team. When attempting these challenges bear a few things in mind:
1) You can ask for help. If you do not understand an aspect of the challenge or would like a hint, speak up.
2) This part of the interview process is not a test of your ability to work from memory. In the day-to-day activities of the typical Oracle DBA there are plenty of times that you will need to lookup the syntax for statement, find clarification on how to accomplish a task, or learn best practices for dealing with an issue being seen with an application. Accordingly, during the assessment you can look up things on the internet.
3) You have one hour to finish the work.
Relax and try to have fun.
The Coronavirus Database Challenge
Challenge 1) Create a database for storing the data being captured on COVID-19 cases. The database will need to be created with a script. Ensure that the table data is in a separate tablespace that you create. The data for the database can be found at the site https://github.com/CSSEGISandData/COVID-19. A sample data file can be obtained from the page below:
Your solution should store the data from at least 10 different days.
Challenge 2) Create a query that shows the development of COVID-19 cases for France over the 10 days you loaded in Challenge 1. Your query should return the the record date, confirmed count, recovered count, and death count. The table below shows the the kind of results we expect to get from your table query against the table you loaded.
Challenge 3) Produce the query plan for the query that you used to produce the results seen in the previous challenge. Discuss how the query and table could be improved with the BIF interview team. Make sure that you are familiar with the terms found in a query Explain Plan document.
Challenge 4) Create two indexes on the table created in Challenge 2. Create the indexes in the tablespace specified in Challenge 1.
Challenge 5) Partition the table from Challenge 1 by region. Note: Your partition strategy will be implemented in a cloud environment; you will not have access to the actual file system. You should create a solution that works through SQL scripts that would work in an environment like Amazon.
If you would like to see an example dashboard based on this challenge see the wiki page at Communion Chapel EFCA.