We asked John Zukowski, an experienced IT pro who has interviewed and hired many database administrators and developers, to share his top tips on preparing for job interview questions about SQL and other database skills.
Database jobs may be hot, but it’s also a competitive market. So how do you get hired for one of these hot jobs?
In this article we’ll cover:
- Database interview questions and answers
- Analyzing the job description
- SQL interview tips and advice
Database Job Interview Questions and Answers
Once you’ve landed the job interview, that’s when the fun begins. As with any other tech job interview, if the technology is listed on your resume, you must be prepared to answer questions about it (especially if that technology is featured prominently in the job description).
Data Modeling Questions
Before getting to the database-specific questions, a prospective candidate must be able to handle the task of taking a set of requirements from a conceptual data model to a physical one. Here is where performance considerations are addressed, tables are designed, and triggers are addressed.
Some questions seen here are related to a candidate’s specific background, like what tools have been used to model data? Other questions might be more related to data modeling fundamentals. Here are some questions with their answers for common questions on the fundamentals.
1. What is cardinality?
Thinking mathematically, it is the number of elements in a set. Thinking in the database world, cardinality has to do with the counts in a relationship, one-to-one, one-to-many, or many-to-many.
2. Describe the differences in the first through fifth normalization forms.
Database candidates should be familiar with most if not all of these without needing to look up definitions. Some of the other normalization forms are less commonly known/used, but could theoretically be asked. Knowing the differences between second and third is probably a good idea.
From Wikipedia, here are their definitions:
- First: The domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.
- Second: No non-prime attribute in the table is functionally dependent on a proper subset of any candidate key.
- Third: Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed.
- Fourth: Every non-trivial multivalued dependency in the table is a dependency on a superkey.
- Fifth: Every non-trivial join dependency in the table is implied by the superkeys of the table.
3. When might someone denormalize their data?
Typically done for performance reasons, to reduce the number of table joins. This is not a good idea in a transactional environment as there are inherent data integrity risks or performance risks due to excessive locking to maintain data integrity.
Questions related to the Unified Modeling Language (UML) or Entity-Relationship Diagrams (ERDs) may also be asked here.
4. What are the elements of an ERD?
The three elements include the entities for which someone is seeking information, the attributes of those entities, and the relationships between the entities.
SQL Job Interview Questions
After getting some foundations out of the way, an interviewer can switch over to questions specific to SQL. The difficulties here will be varied. An introductory candidate might be asked only the introductory questions. A more seasoned candidate would be asked those as well as the more advanced questions.
Introductory questions are typically related to syntax and basic SQL commands that are typically common across database vendors.
5) Which SQL command is used to add a row?
INSERT
6) Write the command to remove all employees named John from the EMPLOYEE table.
DELETE from EMPLOYEE WHERE firstName = ‘John’
7) What are the differences between primary and foreign keys?
The primary key is the column or set of columns used to uniquely identify the items in a table. A foreign key is used to uniquely identify the items in a different table, allowing join operations to happen.
8) What does SQL stand for?
Structured Query Language
Advanced SQL Job Interview Questions
Advanced SQL questions would involve more advanced SQL operations, but could also get into the details of Oracle or SQL Server-specific constructs. If asked a question about a technology a candidate isn’t familiar with, they should not be afraid to respond as such. There are many database systems out there, and most people will not be familiar with the ins and out of every one of them.
9) What is the difference between an inner and outer join?
An inner join involves joining two tables where a common id/key exists in both. An outer join is the joining of two tables, but where there is no match in the second (or first).
10) How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?
You create a trigger that will automatically delete elements in the second table when elements from the first table are removed.
11) What port does SQL server run on?
1433 is the standard port for the SQL server.
12) What is the SQL CASE statement used for and give an example?
It allows you to embed an if-else like a clause in the SELECT clause.
SELECT Employee_Name, CASE Location WHEN 'Boston' THEN Bonus * 2 WHEN 'Austin' THEN Bonus * ,5 ELSE Bonus END "New Bonus" FROM Employee;
13) Table problem solving
Candidates are almost guaranteed to be asked a series of questions related to a deriving data from a provided set of tables. Typically, this might be something along the lines of finding the average grades for the students who took a particular class or perhaps related to finding the top salesperson for a company.
Here’s a sample problem based on the following tables:
Salesperson ID Name Salary Commission Rate Hire Date 1 John 100000 6 4/1/2006 2 Amy 120000 5 5/1/2010 3 Mark 65000 12 12/25/2008 4 Pam 25000 25 1/1/2005 5 Alex 50000 10 2/3/2007 Customer ID Name City 1 Red Boston 2 Orange New York 3 Yellow Boston 4 Green Austin Order ID Date Cust_ID Sales_ID Amount 1 1/1/2014 3 4 100000 2 2/1/2014 4 5 5000 3 3/1/2014 1 1 50000 4 4/1/2014 1 4 25000
Sample questions based on the above tables:
14. Given the tables above, write a query that will calculate the total commission by a salesperson.
SELECT o.amount * s.commission / 100 from salesperson s, order o where o.sales_id = s.id
15. Name all salespersons who did not sell to company Red.
SELECT s.name from salesperson s WHERE s.ID NOT IN ( SELECT o.sales_id FROM orders o, customer c WHERE o.cust_id = c.ID AND c.Name = 'RED')
More Database Job Interview Questions — Beyond SQL
The world used to revolve around SQL-based databases. Times have changed and the world did not go to an object-based database world. It is good for the database job candidate to have a working knowledge of what the alternatives are all about.
These are minimums here on the necessary knowledge. The more advanced a job, the more depth is needed.
16) What is Hibernate?
Hibernate is an object-relational mapping library that takes Java objects and maps them into relational database tables. It provides its own query language (Hibernate Query Language / HQL) that fills in where SQL falls short when dealing with objects. The latest version as of June 2014 is 4.3.5.
17) What are Hadoop and Hive?
Hadoop is an Apache project for dealing with large data sets, basically providing a file system with libraries for large scale data processing like map-reduce. Hive provides the layer on top of Hadoop for query and analysis. The query language is calle HiveQL and does not support transactions.
18) What is NoSQL?
It stands for Not Only SQL and provides an alternative to relational databases. Instead of tabular data stores, they use graph stores, key-value stores, document databases, and wide-column stores. It is popular in the agile development world as developers don’t have to finalize the data model before storing information.
On the more in-depth side, consider a question like:
19) What are the risks of storing a hibernate-managed object in a cache? And, how do you overcome the problems?
With an answer of something along the lines of the following; The primary problem here is the object will outlive the session it came from. Lazily loaded properties won’t get loaded if needed later. To overcome the problem, cache just the object’s id and class, then have retrieved the object in the current session context.
Analyzing the Job Description
Openings in the database/SQL arena tend to be more varied than in software development, where one often sits in front of the computer all day and deals with a programming language that transfers easily between operating systems or companies.
For a database-related position, it might make sense for the candidate to customize their resume for the technology needed for the job. Someone needed for a Hibernate role could be totally out of their element going for a NoSQL spot. However, if a candidate knew both technologies, that’s when a customized resume might be appropriate.
More Database / SQL Interview Tips and Advice
First you need the database skills and then you need the interview skills.
The database skills. Make sure your resume is current, accurate, and realistic. Don’t lie and don’t stretch the truth. If your experience is a little bit light, brush up as much as you can. Take the time to review the latest trends in the database arena and cram in as much database-related knowledge to at least sound authoritarian.
The interview skills. Be prepared to showcase your technical skills in an interview setting (read on for much more on that). Just as importantly, you must prepare to make the case for why you’re the right person for the job. This is particularly important if you are new to the field or aren’t quite the perfect match for the job.
Going for a dbadmin position but you’ve only been data modeling for the last five years? Be prepared to explain why you’re a fit for the admin spot. If you’ve been working with MySQL and are going for a Hive/Hadoop spot, you’ll need to document the transferability of skills.
Even if you are a perfect match, it takes skill to make them see it in the course of a 30-60 minute interview. We’ve got your prep guide for acing your next database administrator or database developer job.
Even in highly technical job interviews, it’s not all about the technical questions. Candidates need to prepare to give examples from past job experiences, sharing stories that allow the potential employer to get a deeper understanding of existing skillset.
You must also be ready to eloquently answer the necessary “fit” questions, which generally center around your career goals, your strengths and weaknesses, and your soft skills.
This is where the dreaded “tell me about yourself” question usually comes into play. The interviewers want to feel confident that you have the right skills, but also that you will be a productive and pleasant coworker.
This is also where it helps to be able to demonstrate your knowledge of the company and its needs. More and more company details are becoming public these days and if a candidate can conduct some basic research and then ask a smart question about the active development of the company, it will show initiative and interest on the candidate’s part. If an employer is on the fence between two candidates, that initiative and interest could push them over the edge.
In the end, try not to sweat it. Nerves can be a killer. Relax. Being prepared is more than half the battle.
_____________________________
Need a hand? There are 3 ways we can help:
- Learn how to turn job interviews into offers. (Rated 4.9/5 by 1,000,000 users)
- Learn how to stand out from other candidates by taking this free interview course
- Check out the most common interview questions for all kinds of interview scenarios
