Tsql Interview Questions You Ask

TSQL interview questions you ask

Here are some of the most common questions I've been asked as an ASP.Net developer with strong SQL Server skills:

• Name and describe the different kinds of JOINs
• What is COALESCE?
• Explain primary and foreign keys
• What would you do to optimize slow-running queries?
• What is the difference between DELETE and TRUNCATE?

TSQL interview questions you ask

Here are some of the most common questions I've been asked as an ASP.Net developer with strong SQL Server skills:

• Name and describe the different kinds of JOINs
• What is COALESCE?
• Explain primary and foreign keys
• What would you do to optimize slow-running queries?
• What is the difference between DELETE and TRUNCATE?

Questions every good Database/SQL developer should be able to answer

The different types of JOINs:

• INNER JOIN
• LEFT and RIGHT OUTER JOIN
• FULL JOIN
• CROSS JOIN

See Jeff Atwood's Visual Explanation of JOINs

• What is a key? A candidate key? A primary key? An alternate key? A foreign key?

• What is an index and how does it help your database?

• What are the data types available and when to use which ones?

SQL Server interview questions

Sql Server is a massive topic. You're not going to know everything, especially as a developer. You'll be expected to know about PKeys, foreign keys, design etc. Just be honest when you don't know something. Don't guess and don't try to blls&t your way through anything. It never plays well. I've seen it from both angles (interviewee and interviewer.)

Honesty gets a lot of respect from the interviewer. It makes you a real person and it makes their job easier. Not necessarily to say yes or no to your qualifications but because they have less guessing to do and they will remember that when it comes time to cull through the applicants.

SQL interview question

While this is pretty much the same as Phil Sandler's answer, this should return two separate tables (and I think it looks cleaner) (it works in SQL Server, at least):

DECLARE @temp TABLE (num int)
INSERT INTO @temp VALUES (1),(2),(4),(5),(8),(9),(13)

DECLARE @min INT, @max INT
SELECT @min = MIN(num), @max = MAX(num) FROM @temp

SELECT t.num + 1 AS range_start
    FROM @temp t
    LEFT JOIN @temp t2 ON t.num + 1 = t2.num
    WHERE t.num < @max AND t2.num IS NULL

SELECT t.num - 1 AS range_end
    FROM @temp t
    LEFT JOIN @temp t2 ON t.num - 1 = t2.num
    WHERE t.num > @min AND t2.num IS NULL

SQL & Postgres Interview Concepts

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.

I was asked the following pseudocode in a SQL interview based on joining condition. Can anyone explain what went went wrong with my approach?

if the data in the screenshot was the data they gave you , all item ids are -1
which means no matter if you do inner join or outer join , its gonna be a cross join meaning you will have all combination of table1 with table 2 which will be 12 rows

---

Related Topics