Job preparationDatabase
Database interview questions and answers
SQL versus NoSQL, keys, normalisation, indexes, JOINs, transactions and ACID — compulsory for any developer role.
Front-end or back-end, database questions come up in nearly every IT interview. There is no avoiding them.
The good news is that they are fairly fixed. The ten below are the ones asked most, and the answers are deliberately short — that is the length you would actually say aloud.
The questions
What is a database and what kinds are there?
What to coverA database is an organised collection of data where information can be accessed, managed and updated easily. The kinds are relational (RDBMS), NoSQL, hierarchical and network databases.
What is the difference between SQL and NoSQL?
What to coverSQL databases are relational, have a fixed schema and support complex queries. NoSQL databases are non-relational, have a flexible schema, and generally suit large, fast-moving volumes of data.
What is the difference between a primary key and a foreign key?
What to coverA primary key is a column or set of columns that uniquely identifies every record in a table. A foreign key is a column or set of columns that links one table to the primary key of another.
What are normalisation and denormalisation, and what forms are there?
What to coverNormalisation is designing a database so data is not duplicated and integrity is preserved; the forms are 1NF, 2NF, 3NF, BCNF and so on. Denormalisation merges normalised tables back together, usually to make reads faster.
What is an index and why is it used?
What to coverAn index is a database structure built over a particular column to make lookups on it faster. It speeds up searching and sorting.
What is a JOIN and what kinds are there?
What to coverA JOIN matches records across more than one table. The kinds are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS JOIN.
What is a subquery in SQL and what kinds are there?
What to coverA subquery is a query sitting inside another query. The kinds are single-row, multi-row and correlated subqueries.
What is a transaction and what are the ACID properties?
What to coverA transaction is a single unit of database operations that must either complete entirely or fail entirely. The ACID properties are Atomicity, Consistency, Isolation and Durability.
What is the difference between a stored procedure and a trigger?
What to coverA stored procedure is executable code kept on the database server, used to carry out several operations. A trigger is a special kind of stored procedure that fires automatically in response to particular events.
What is a database constraint and what kinds are there?
What to coverA constraint is a rule placed on a table to guarantee data integrity. The kinds are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT.
Tips for this round
- On normalisation, walking 1NF to 3NF with an example makes the strongest impression.
- After the benefits of an index, give the cost — writes slow down and it takes space. That tells a board you have actually used one.
- Remember the JOIN types by sketching them; you can draw the same sketch in the room.