Imagine that you knew what your interviewers were going to ask in advance.

Job interviews don’t sound so scary now do they!

If you’re applying for a data scientist, database or SQL job then we’re here to help.

We asked Acuity Training, a leading SQL training company in the UK, to give us 15 SQL questions that are commonly asked during job interviews.

They’ve broken the questions down into sections, depending on how experienced you are.

1. Beginner (0–2 years experience)

2. Intermediate (3–5 years experience)

3. Expert (5 years experience)

5 Questions – Beginner Positions

The key in this interview is to show you have strong fundamentals.

Understanding why the following questions are asked, and just what you can show off when they are asked is the most important thing.

Question 1:

Let’s say you want to SELECT all records from Table One, and all matching records from Table Two. Which JOIN statement should you use?

Answer:

LEFT Join.

For more details see this article by Acuity Training on using Joins.

Question 2:

What is a Subquery?

Answer:

A subquery is simply a query inside another query.

Question 3:

What is a foreign key column?

Answer:

Foreign keys are used to create a relationship between two tables.

It points to a column with unique values in another table.

Question 4

Define the DELETE and TRUNCATE statements.

Answer:

DELETE: Allows you to delete rows one by one, keeping the identity key column in place.

TRUNCATE: Removes all rows by deallocating memory pages that stored the table records, while resetting the identity key to its seed value.

Question 5

Can the “Name” column of a “Pet” table be a unique key?

Answer:

Yes, any column, like the “Name” column for pet names, can be a unique key, but it’s best to choose a column with unique values.

5 Questions – Intermediate Positions

An intermediate position means you have experience.

In this interview you have to demonstrate your experience or you don’t have a hope!

When reading the questions below, think not only about the answers you can give but how you can demonstrate practical experience by linking them to your past experience.

Remember to think broadly as the example may be as part of a broader role and working in conjunction with other technologies like hadoop for example.

Question 1:

List three benefits of normalising a database.

Answer:

• 1. Enhances data consistency

  2. Minimises duplicate data

  3. Improves data protection

Question 2:

What constraint ensures only specific data types or values are entered in a column?

Answer:

A CHECK constraint.

Question 3:

Describe what a SQL server query execution plan is.

Answer:

It’s a visual representation showing the breakdown and execution strategy of a SQL query, useful for pinpointing slow segments of a query.

The HAVING clause is typically paired with GROUP BY.

Question 4:

In SQL Server, under what circumstances is HAVING preferred over WHERE?

Answer:

WHERE filters specific rows, whereas HAVING is used to filter groups of rows aggregated by functions, often alongside GROUP BY.

Question 5:

How are clustered and non-clustered indexes different?

Answer:

Clustered indexes sort table records to align with the index, allowing only one per table, leading to quicker searches.

Non-clustered indexes do not order physical records according to the index, making them slightly slower. You can also have several in each table.

5 Questions – Expert Positions

This is where SQL interviews can get very scary very quickly.

Expert level positions are only applied to by the best of the best. Only a small percentage of these applicants even get asked to come for an interview.

Preparation isn’t just important here, it’s the bare minimum.

Question 1:

What’s the purpose of using synonyms in SQL Server?

Answer:

Synonyms serve as alternative names for database objects like tables, views, stored procedures, etc.,

They facilitate the abstraction of object names and locations. They enable seamless query execution against an object even after its renaming or relocation.

Question 2:

Describe deferred name resolution and its utility in SQL Server.

Answer:

Deferred name resolution allows for the reference of uncreated objects within SQL queries, particularly useful in stored procedures.

This feature lets you reference tables in stored procedures that don’t exist at creation time, enhancing flexibility. Functions, however, do not support this.

Question 3:

Define dynamic SQL and provide a use case scenario.

Answer:

Dynamic SQL involves queries that are constructed and executed at runtime, using the sp_executesql procedure for secure parameter integration.

It’s particularly useful in scenarios requiring adaptable query structures, significantly enhancing security against SQL injection attacks.

Question 4:

How do deadlocks occur in SQL Server, and what are the strategies to prevent them?

Answer:

Deadlocks arise when multiple queries simultaneously wait on resources held by each other.

To prevent deadlocks, employ strategies like minimising transaction lengths, adopting a consistent resource access order, or ensuring automatic resource release.

Question 5:

Explain triggers and their primary applications.

Answer:

Triggers activate on specific database events.

They come in two varieties: AFTER and INSTEAD OF. They are commonly used to maintain data integrity across related tables in different databases through actions like enforcing foreign key constraints.

Conclusion

Hopefully you’re now ready to ace your interview.

Remember – stay calm, be yourself – you’re ready!