SQLTeam.com | Weblogs | Forums

Sql queries interview questions


#1

Hi,

What queries would you ask an interviewee to write based on diagrams to test an interviewee's sql skills?

Thanks


#2

based on what kind of diagrams? ERM?

I'd be asking about basic stuff first -- e.g. query processing order -- difference between where and having, e.g. and why you can't use a column alias until the ORDER BY clause. Then I'd add some questions on aggregation, including window functions, different kinds of joins and where to use which one, subqueries and CTEs, importance of SARGable predicates, how foreign keys work and how they help, declarative vs imperative constraint checking, why indexes and statistics are important, basic execution plan understanding, how to create tables, procs, views, functions and probably lots of other stuff depending on what answers I get.


#3

Well I was told that i would have to write queries based on data modelling. The interview isn't face to face. It is a computer exam. I work with sql on a daily basis but i am a bit stressed for the interview and want to practice queries that could be asked.


#4

I don't quite understand what it means when they say "queries based on data modelling". Perhaps you will be given a data model and asked to write queries based on that?

When I used to interview SQL candidates, I used to start out by asking the very basics.

  1. Do you know what types of table joins are there in SQL - INNER, LEFT, RIGHT, FULL, CROSS
  2. Give them a customer table, orders table and ask them to find the number of orders each customer has placed.
  3. Find customers who had no orders, or write the query in #3 to include customers with no orders
  4. Find the most recent order for each customer etc.

Perhaps that type of questions - based on a customer and orders table as the data model in my example - are what they are thinking of when they say "queries based on data modeling"?


#5

Thanks. I think they probably mean a diagram and queries based on that.
There is no face to face interview. Just a webex exam.
What do you suggest I go over in terms of writing queries?


#6

I would think you are on the right track.

You might perhaps be given a database diagram with Customers, Orders, Order Details, Vendors, Supplies etc. (may be less, may be fewer) and asked to write queries to extract information such as what I had described above.

If you are not familiar with database diagrams - representing primary keys, foreign key relationships, one-to-one vs one-to-many etc. - get familiar with that.

Then, you need to be able to write queries by joining tables as appropriate to get the information they will be asking you.