database interview questions
Top database frequently asked interview questions
I added a table that I thought I was going to need, but now no longer plan on using it. How should I remove that table?
I've already ran migrations, so the table is in my database. I figure rails generate migration should be able to handle this, but I haven't figured out how yet.
I've tried
rails generate migration drop_tablename, but that just generated an empty migration.
What is the "official" way to drop a table in Rails?
Source: (StackOverflow)
I want to copy a live production database into my local development database. Is there a way to do this without locking the production database?
I'm currently using:
mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1
But it's locking each table as it runs.
Source: (StackOverflow)
Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?
Source: (StackOverflow)
Is there a way to restrict certain tables from the mysqldump command?
For example, I'd use the following syntax to dump only table1 and table2:
mysqldump -u username -p database table1 table2 > database.sql
But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentation, so is brute-force (specifying all the table names) the only way to go?
Source: (StackOverflow)
What's the Hi/Lo algorithm?
I've found this in the NHibernate documentation (it's one method to generate unique keys, section 5.1.4.2), but I haven't found a good explanation of how it works.
I know that Nhibernate handles it, and I don't need to know the inside, but I'm just curious.
Source: (StackOverflow)
SELECT id, amount FROM report
I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
Source: (StackOverflow)
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run update command
ELSE
run insert command
What's the best performing way to write this?
Source: (StackOverflow)
I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?
Source: (StackOverflow)
I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to rely on your last line of defense in this way.
Surprisingly, this seems to be a common thread. Many shops I have spoken to ignore this issue because their databases don't change often, and they basically just try to be meticulous.
However, I know how that story goes. It's only a matter of time before things line up just wrong and something goes missing.
Are there any best practices for this? What are some strategies that have worked for you?
Source: (StackOverflow)
What would be considered the best practices when executing queries on an SQLite db within an Android app?
Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground ? Or should I use the UI Thread ? I suppose that db queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an ANR.
If I have several AsyncTasks, should they share a connection or should they open a connection each?
Are there any best practices for these scenarios?
Source: (StackOverflow)
What are the technical reasons why I shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?
Why should I use something else even if they work on my site?
Source: (StackOverflow)
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:
- Should table names be plural?
- Should column names be singular?
- Should I prefix tables or columns?
- Should I use any case in naming items?
Are there any recommended guidelines out there for naming items in a database?
Source: (StackOverflow)