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 skills assessment.
The database administration skills assessment is comprised of a few DBA challenges. If you have a few years of Oracle under your belt the assessment 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 an Oracle DBA there are plenty of times that you will need to lookup the syntax for a 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 Administrator Challenge
Challenge 1) Create an Oracle 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 has its own tablespace. 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:
The raw data file can be downloaded using the following link
Your solution should store the data from at least 5 different days.
Challenge 2) Create a query that shows the development of COVID-19 cases for France over the 5 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.
| Recorded Date | Confirmed | Recovered | Deaths |
| 2020-02-24 | 12 | 0 | 1 |
| 2020-02-25 | 14 | 0 | 1 |
| 2020-02-26 | 18 | 0 | 2 |
| 2020-02-27 | 38 | 0 | 2 |
| 2020-02-28 | 57 | 0 | 2 |
Challenge 3) Produce the query plan for the query that you used to produce results like those seen in the previous challenge. With the BIF interview team discuss how the query and table could be improved. 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. Make sure that you 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 AWS.
Challenge 6) Create a PL/SQL function that will take two numbers and return the sum.
Challenge 7) Create a PL/SQL function that will take two numbers and return the product.
Challenge 8) Create a PL/SQL function that will return the factorial of a given integer. Ensure that your function throws an exception if it is called with a negative parameter.
Challenge 9) Create a PL/SQL function that will take two dates as VARCHAR in the format yyyy-mm-dd. The function should return a rowset of dates between the two given dates inclusive of the boundaries.
Challenge 10) Create a PL/SQL package containing all of the functions that you created in challenges 6 through 9.
Recent Comments