Top 30 sql interview coding tasks by matthew urban pdf năm 2024

Knowing good SQL questions to ask during an interview with a developer can help you filter out the best candidates from the ones who aren’t the most qualified. There’s a huge difference between “It worked on my machine” and “It scales well in production.” These interview questions will help you filter out the bad apples before you hire them.

10. Explain why DBAs don’t like cursors.

I like to phrase this interview question this way because I’m not saying the DBA is right – I’m just asking the developer to explain the DBA’s point of view. I don’t have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.

The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that’s purely a bonus. (I wish I could say that these concepts are requirements, but in today’s economic market, companies don’t always want to pay top dollar to get the best candidates.)

9. Where do you like business logic – in the app or in the database? Why?

Personally, I like stored procedures because they’re easier for us DBAs to test, tune and tweak. On the other hand, the developer community isn’t always as fond of stored procs. For their side, see these posts by Jeff Atwood:

• Who Needs Stored Procedures, Anyway?
• Stored Procedures vs Ad-Hoc SQL
• My Database is a Web Service

I don’t mind what arguments the coder candidate uses, but I want to see ’em put some thought into it. No matter which angle they take, I’ll play the devil’s advocate and prod them with arguments just to see how they react.

8. Explain when and how transactions should be used.

Not In The Oprah Book Club, Oddly

Start with just that open-ended interview question, and if they have trouble getting started, give them a scenario.

“Say we’ve got a table for Orders, and a table for OrderDetails. Someone places an order for two books – Bacon: A Love Story and the hit bestseller Eat What You Want and Die Like A Man. Tell me what happens.”

After they’ve answered, ask them when transactions should not be used. I don’t want my developers wrapping anything inside a transaction unless it absolutely needs to be. (Unlike bacon, which should be used as often as possible for wrapping purposes.)

7. Explain referential integrity and where it can be enforced.

If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What’s an orphan? How do we make sure that we don’t end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?

6. What’s the fastest way to get a thousand records into the database?

I’m not looking for the best answers – I’m just looking to hear that they’ve done some work to performance tune their queries. If they’re doing fully logged individual record inserts, one at a time, into a data warehouse-size system, we’re going to have problems down the road. (Yes, I’ve actually worked with a BI developer that did millions of individual inserts per night in full recovery mode and thought the performance was the database’s fault.)

Bonus points if they link back to the previous interview question and talk about whether or not they should disable constraints or referential integrity during data loads. (I don’t care what their final answer is, but I just want them to know the pros and cons.)

5. What’s the difference between a primary key and a clustered index?

This is almost a bonus question. Most of the time, the candidate doesn’t know because it’s a function of the data modeler or architect, not the developer. However, I want to see how the candidate reacts to tough questions. Ideally, they say in a relaxed tone of voice, “I’m not sure, but I know who I’d ask.” If they don’t mention where they’d go, ask them where they go for SQL Server answers. Speaking of which…

Bonus Points for This Candidate

4. What’s your StackOverflow name?

I don’t need to see a high reputation, but I do want to see an awareness of the site. This interview question serves two purposes: it finds out if they’re serious enough to be active in the community, and it shows them that you’re okay with their community activity. Start a conversation with them about the level of internet time that you find acceptable in the office, and encourage them to share their knowledge with their peers. This sells the candidate on your shop.

3. Tell me about a time when a DBA got mad at you.

This is a spin on the classic interview question, “Tell me about a time when you failed.” Implemented a user-defined function, trigger, CLR in the database, or something else that made the DBA freak out? I want to hear that the candidate listened to what the DBA had to say, good or bad.

If they say it’s never happened, rest assured it’s going to happen soon.

2. How can you tell if a query will scale for production?

I want to hear that they do things like load tests or maybe look at execution plans.

I’m sometimes comfortable when a senior developer says things like, “I can pretty well tell when something isn’t going to scale, because I know the production boxes really well.” The key is asking a followup question about times when things didn’t scale.

What are the top SQL interview questions?

Top SQL Interview Questions.

Explain the differences between SQL and NoSQL databases. ( ... .

Explain different types of joins with examples. ( ... .

What is a primary key in SQL? ( ... .

What is a foreign key? ( ... .

How do you optimize SQL queries? ( ... .

How do you create a stored procedure? ( ... .

What is a trigger in SQL? (.

How do you explain SQL in an interview?

Start by explaining that SQL (Structured Query Language) is a standard programming language that is used to access and manipulate data held in relational databases.

How to prepare for SQL interview?

If you want to perform well at the SQL job interview, these are the concepts you need to know:.

Data Definition Language (DDL) keywords..

Data Manipulation Language (DML) keywords..

Data Control Language (DCL) keywords..

Transaction Control Language (TCL) keywords..

SQL constraints..

JOINs..

indexes..

transactions..