Posted: June 6th, 2014

Questionnaire for job interview

Questionnaire for job interview
Project description
Question1
We have an active 500GB standalone OLTP mysql database that has all of its tables stored in the Innodb engine. We have determined that we must convert it all to Myisam for performance reasons. The problem is that our cost of downtime is very high: $20,000 an hour. We would like this migration to use the least down time possible.

Your task here is to devise a project plan, including detailed steps, that describes how you would accomplish this project, especially highlighting the steps in the plan that requires downtime. We have an extra server which is identical to production, and an Innodb Backup license which is used in nightly backups. Both these can be made available if required. How do you proceed?
Answer

________________________________________
Question 2
Use the file attachment following this question to complete the question: Using the INTERVIEW schema: Table USERS columns: User_id (PK) Email Gender Age Name table SUBSCRIPTIONS columns: SUbscription_id (PK) user_id (UK) (FK from users) subscription_type (UK) active_indicator table TRANSACTIONS columns: subscription_id (PK) (FK from subscriptions) action (PK) timestamp (PK) sample data: USERS user_id email gender age name 1 [email protected] m 30 rob 2 [email protected] m 31 robert 3 [email protected] f 18 lucie 4 [email protected] f 22 lulu 5 [email protected] m 10 kim 6 [email protected] f 18 kim 7 [email protected] f 08 kim 8 [email protected] f 18 JJ 9 [email protected] m 22 Jay 10 [email protected] f 88 Bill 11 [email protected] f 88 Will 12 [email protected] f 60 Will 13 [email protected] m 70 George subscriptions subscription_id user_id subsciption_type active_indicator 1 2 Magazine Yes 2 3 Music CD No 3 3 Magazine Yes 4 3 Video Yes 5 8 Magazine Yes 6 9 Video Yes 7 10 Magazine No 8 13 Magazine yes transactions subscription_id action timestamp 1 Renewal 2002-sep-10 2 Renewal 2002-Jan-01 2 Cancellation 2002-Feb-01 3 Renewal 2002-Aug-20 4 Renewal 2002-Aug-01 4 Renewal 2002-Sep-01 5 Renewal 2002-Aug-01 6 Renewal 2001-Sep-01 7 Renewal 2002-Sep-01 7 Cancellation 2002-Sep-10

Type of business: We are a subscription company where people sign up to received products like CDs, books, magazines, etc. We always try to market new subscriptions to our existing client base, but because we have millions of customers who use the same email address, we like to send our promotional emails only once.

QUERY: Some assumptions will need to be made. Please list any such assumptions. How would you generate a list of unique email addresses with the latest name, gender and age for a user with that email?
The selection criteria limit the list to users which never subscribed to anything; or; users with inactive subscriptions; or; users with active subscriptions that renewed between Sep 1st and Sep 30th of any year. Your answer should be: [email protected] m 31 robert [email protected] f 22 lulu [email protected] f 08 kim [email protected] m 22 Jay [email protected] f 60 Will

Answer

________________________________________
Question 3
Using the INTERVIEW SCHEMA:

We require a Stored Procedure to be written that will take the data in the TSOURCE table and complete the following steps:
1. Insert rows not currently in the TDEST table and
2. Uupdate TDEST rows with a matching KEYVAL value with the data in TSOURCE.

How will you write the program so that it maximizes overall performance and efficiency?
Answer

________________________________________
Question 4
We have a system where our sales people have notebooks that they take on business trips. They are all configured as mysql slaves and they plug them in every other day to sync them up but sometimes a sales person might not sync up for several days. We use a master and several slaves since a full rebuild would take too long. We used to just remove bin logs older than X days but we had issues with that when a sales person was on an extended trip and tried to sync up after the threshold.

What we want to you do to is to write a Perl script, shell script, or stored procedure that would remove bin-logs on the master once it’s 100% certain that they have been applied on all slaves. Please remember that not all slaves will be connected while you verification script is running. How will you accomplish this goal?
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂