2

Introduction:

So, I have an interview tomorrow and I'm trying to review SQL and databases. The job posting says that they want someone with:

  • Experience with database design and development
    • Strong knowledge of SQL
    • Experience with SQL Server and/or Postgres

I've read through Questions every good database SQL developer should be able to answer, and a bunch of questions tagged with SQL and interview-questions. So I realize that I need to know about SELECT, JOIN and WHERE.

Questions:

  • What are essential SQL, Postgres and database concepts that I need to know in order to do well in the interview?

  • What do I need to know about transaction and normalization?

  • What are some general ways to optimize slow queries?

  • Should I learn about the functions, keywords or both?

Community
  • 1
  • 1
Jeffrey Greenham
  • 1,382
  • 5
  • 16
  • 33

2 Answers2

2

It depends on how much of the role is based around database development and design. For your SQL syntax, you should also understand the difference between the types of joins, and be able to use GROUP BY, ORDER BY, HAVING as well as the aggregate functions that can be used in conjunction with them.

In terms of performance monitoring, I would be looking at execeution plans (not sure about the Postgres equivalent) and how they can provide tips on increasing performance, as well as using SQL Profiler to see what instructions the server is executing in real time.

Transactions can be useful for rolling back, well, transactions (stored procs, ad-hoc queries etc.) that require queries to complete in a certain way to maintain data consistency. Some people (myself included) have a practice of placing any statements that make any changes to data into a transaction that automatically rolls back (BEGIN TRAN ... ROLLBACK TRAN) to check that the correct amount of data is manipulated before pushing changes to a live server. Have a look at the ACID model - Atomicity, Consistency, Isolation, Durability.

Normalization is something that can take a little time to go through, but just know and partially understand up to 3rd form normalization and that will get you started.

Optimisation can be a huge topic. Just remember to try and do things like UPDATE using set based queries, rather than row based (updating in a WHILE loop is an example of row based updating, but it CAN have its uses).

I hope this helps a little.

1

Besides the basics of sql syntax, which you listed, you should know some things about query performance. What are some common causes of slow queries and what are the remedies for those, and how can you evaluate the performance of a query.

digitaljoel
  • 26,265
  • 15
  • 89
  • 115