Database interview questions have been a critical component of technical hiring for decades. If you’re a data scientist, data engineer, or software engineer on the job market, the ability to demonstrate your skills in a database interview is critical to landing your next role.
To succeed in a database interview, you’ll need to stay up to date on the latest advancements and prepare for the styles of problems you might encounter. In this post, we’ll review the conceptual, basic, and advanced database interview questions you need to know to land your dream job.
What a Database Interview Looks Like
A database is an “organized collection of structured information,” typically stored in a database management system (DBMS).
During a database interview, candidates are challenged to complete a series of queries or functions using accompanying sets of data tables, input formats, and output formats.
Database interview questions can cover a wide range of database concepts, including:
- Queries
- Normalization
- De-normalization
- Transactions
- Subqueries
- Joining
- Ordering
- OLAP operations
- XML queries
- MapReduce
Depending on the format, the interview may also include questions about database technologies, including:
- Database management systems
- Relational databases
- Non-relational databases
- SQL
- NoSQL
- Ruby
- Python
Multiple Choice Database Questions
Multiple choice questions will test your basic knowledge of database concepts and functions. It’s worth noting that during an assessment or interview, you may encounter similar questions in an open-ended format, with no answers to choose from.
Relational Algebra
In precedence of set operators the expression is evaluated from:
- Depends on the expression
- Left to right
- Right to left
- From user specification
Database Query Languages
Using which language can a user request information from a database ?
- Query
- Relational
- Structural
- Compiler
Procedural Language
Which one of the following is a procedural language ?
- Domain relational calculus
- Tuple relational calculus
- Relational algebra
- Query language
Index Architecture Types
How many index architecture type classifications are there in MS SQL Server?
- 1
- 2
- 3
- 4
OLAP Operations
This OLAP operation involves computing all of the data relationships for one or more dimensions.
- Dice
- Slice
- Pivot
- Roll-up
Basic Database Coding Questions
Below are five examples of basic problems a data scientist or software engineer might face during a technical interview. These questions are simple in nature, testing only one database concept. They are meant to be solved in a collaborative integrated development environment (IDE).
Basics of Sets and Relations #1
You are given two sets.
Set A = {1,2,3,4,5,6}
Set B = {2,3,4,5,6,7,8}
How many elements are present in A U B?
Only enter the correct integer in the editor below. Do not include any extra spaces, tabs or newlines.
Basics of Sets and Relations #3
You are given two sets.
Set A = {1,2,3,4,5,6}
Set B = {2,3,4,5,6,7,8}
How many elements are present in A – B?
Only enter the correct integer in the answering box. Do not include any extra spaces, tabs or newlines.
Database Normalization #1 – 1NF
The following unnormalized table named PRODUCT is transformed to first normal form (1NF) by splitting it into two tables which have X and Y rows (such that X < Y) respectively. Both the tables have Z columns.
*Product-ID* *Colors* *Price*
1 Red,Green 15.0
2 Blue 18.0
3 Yellow,Pink 2.5
What are the values of X, Y, Z? Enter these integers, each on a new line, in the text-box below. Do not leave any leading or trailing spaces.
Database Normalization #4
A database, normalized as per 2NF rules, has been split into 10 tables. Each of the tables has exactly two columns: one key attribute and one non-key attribute. What is the minimum number of tables required to express this database in 3NF form? Enter the integer in the text box below. Do not leave any leading or trailing spaces.
Querying XML Datastores with XPath #1
XPath is a valuable tool often used for querying XML databases. XPath queries (or close variants) are also used in the process of Web Scraping while retrieving data from structured XHTML-compliant web pages, specially those with tabulated data.
(A quick XPath tutorial is available here.)
Assume that you have been provided a fragment of XML, which you can view here. Your task is to write the XPath selector for listing the titles of the movies in the same order as which they occur in the given XML. The Ruby code for handling input, output, and document construction has already been provided. You only need to fill up the required blanks as indicated in the template code provided. This challenge is agnostic to language-specific knowledge and you do not require to know Ruby – as you only need to complete the XPath selector.
Advanced Database Coding Questions
Below are five examples of more challenging database problems, with difficulties ranging from medium to advanced. These questions cover more specific concepts and language-specific knowledge, and are also meant to be solved in a collaborative IDE.
MapReduce Advanced – Relational Join
A SQL join combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.
The input is a number of lines with records from two tables Employee and Department. A tuple from the Employee table will look like:
Employee [Person_Name] [SSN]
A tuple from the Department table will look like:
Department [SSN] [Department_Name]
The required output is to print the JOIN of the two tables Employee and Department, in the format shown. The code for the MapReduce class, parts related to IO etc. has already been provided. However, the mapper and reducer functions are incomplete. Your task is to fill up the mapper and reducer functions appropriately, such that the program works, and outputs the JOIN of the two tables, in lexicographical order.
Database Normalization #3
A database used by a college’s application stores the relationship between students and the courses they are enrolled in. We have information for each STUDENT (such as name, date of birth, date of enrollment, student-id) and COURSE (course code, instructor, etc.). In real life, a student takes several courses simultaneously while a subject is studied by several students. We need to capture this many-to-many relationship in our database. From the above information, what is the minimum number of tables required to structure this database in accordance with the rules of 2NF normalization?
Database Normalization #9
Consider the following relation and determinants. The key(s) are bolded.
R(a, b ,c,d,e)
Which of these determinants is a NON-CANDIDATE key? In the text box, only enter the index number (1-3) of the dependency which you have identified as non-key.
- a,c -> b,d,e
- a,d -> b
- a,c,e -> b,d
Database Keys
A database table with three fields (bookname, author, language) has been created. If the table is as provided below, which of these three fields may be used as the primary key?
bookname-author-language
A Tale of Two Cities, Charles Dickens, English
Oliver Twist, Charles Dickens, English
Godaan, Premchand, Hindi
Chandrakanta, Devaki Nandan Khatri, Hindi
Hamlet, William Shakespeare, English
The Merchant of Venice, William Shakespeare, English
Only fill in the name of the field which may be used as the primary key. Grading is case-sensitive.
Database Differences
Relation R(A,B,C) has the following tuples:
A B C
1 2 3
4 2 3
4 5 6
2 5 3
1 2 6
Relation S(A,B,C) has the following tuples:
A B C
2 5 3
2 5 4
4 5 6
1 2 3
The difference (R-S) is computed and the following tuple is found to be present in the result. Assume that the schema of the result is (A,B,C).
4, b, c
Find the integers b and c. Fill in the values in the answer box, each on a new line.
Resources to Improve Database Knowledge
HackerRank SQL Certification (Basic)
HackerRank SQL Certification (Intermediate)