Questions that every good database / SQL developer should be able to answer

Question

I went through Questions that every good .Net developer could answer , and was very impressed with the content and approach to this question, and therefore in the same vein, I ask this question for Database / SQL Developer.

What questions do you think a good database / SQL programmer should answer ?

Answer 1

Different types of joins:

• INNER JOIN
• LEFT AND RIGHT INTERACTION
• FULL JOIN
• CROSS JOIN

See Jeff Atwood JOINs Visual Explanation

• What is a key? Candidate Key? Primary key? Alternative key? External key?

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

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

Answer 2

Reprint of my answer here , as general recommendations for topics.

The basics

---

• SELECT columns from a table
• Aggregates Part 1: COUNT , SUM , MAX / MIN
• Aggregates Part 2: DISTINCT , GROUP BY , HAVING

Intermediate

---

4. JOIN s, ANSI-89 and ANSI-92 syntax
5. UNION vs UNION ALL
6. NULL Processing: COALESCE and Native Processing Native
7. Subqueries: IN , EXISTS and Inline Views
8. Subqueries: correlated
9. WITH Syntax: Subquery Factoring / CTE
10. representation

Additional topics

---

• Functions, Stored Procedures, Packages
• Pivot: CASE and PIVOT syntax
• Hierarchical queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized views
• Query Optimization: Indexes
• Query Optimization: Explaining Plans
• Query Optimization: Profiling
• Data Modeling: Normal Forms, 1 to 3
• Data Modeling: Primary and Foreign Keys
• Data Modeling: Table Limitations
• Data Modeling: Link / Corrollary Tables
• Full text search
• XML
• Isolation levels
• Entity Relationship Diagrams (ERDs), logical and physical
• Transactions: COMMIT , ROLLBACK , Error Handling

Answer 3

Here are a few:

• What is normalization and why is it important?
• What are the situations when you will de-normalize data?
• What is a transaction and why is it important?
• What is referential integrity and why is it important?
• What steps to take to research reports on slow database performance?

Answer 4

What is SQL injection and how do you prevent it?

What is a cursor and when do you use it (or not) and why?

Answer 5

I posted this answer because Erwin Smoot posted a response that was so wrong that he emphasized that it was probably necessary to specifically defend against it.

Erwin suggested:

"Why does every SELECT always include DISTINCT?"

A more appropriate question: if someone should have stated that: "every SELECT always includes DISTINCT"; how would you comment on the lawsuit?

If the candidate cannot withdraw the suit on fire, they either:

• I do not understand the problem with the claim.
• Lack of critical thinking skills.
• The inability to convey technical problems.

To record

• Suppose your query is correct and does not return any duplicates, and then DISTINCT simply forces RDBMS to check your result (zero advantage and a lot of additional processing).
• Suppose your request is incorrect , and returns duplicates, and then includes DISTINCT just hides the problem (again with additional processing). It would be better to identify the problem and correct your request ... it will work faster this way.

Answer 6

In our company, instead of asking a lot of SQL questions that anyone with good memory can answer, we created a test for SQL developers. The test is intended for the candidate to assemble a continuous circuit with normalization and RI considerations, check the restrictions, etc. And then you can create some queries to create the result sets that we are looking for. They create all this against the brief design specification that we give them. They are allowed to do this at home and take as much time as they need (within reasonable limits).

Answer 7

What is the difference between a clustered index and a non-clustered index?

Another question I would like to ask is not for a specific server:

What is a dead end?

Answer 8

I would give a poorly written query and ask them how they will tune performance.

I would ask about set theory. If you do not understand how to work in sets, you cannot efficiently query a relational database.

I would give them some examples of cursors and ask how they rewrite them to make them set-based.

If the task involved import and export, I would ask questions about SSIS (or other tools involved in this used by other databases). If it was about writing reports, I would like to know that they understand aggregates and groupings (as well as Crystal Reports or SSRS or any other tool that you use).

I would ask the difference in results between these three queries:

 select a.field1 , a.field2 , b.field3 from table1 a join table2 b on a.id = b.id where a.field5 = 'test' and b.field3 = 1 select a.field1 , a.field2 , b.field3 from table1 a left join table2 b on a.id = b.id where a.field5 = 'test' and b.field3 = 1 select a.field1 , a.field2 , b.field3 from table1 a left join table2 b on a.id = b.id and b.field3 = 1 where a.field5 = 'test' 

Answer 9

Knowledge not to use and WHY not to use:

 SELECT * 

Answer 10

An interesting question will include relational separation, or how to express a "for all" relationship, which requires nested not exists clauses.

The question is: this link .

Given the following tables representing pilots who can fly on airplanes and aircraft in the hangar:

 create table PilotSkills ( pilot_name char(15) not null, plane_name char(15) not null ) create table Hangar ( plane_name char(15) not null ) 

Choose the names of the pilots who can fly on each aircraft in the hangar.

Answer:

 select distinct pilot_name from PilotSkills as ps1 where not exists ( select * from hangar where not exists ( select * from PilotSkills as ps2 where ps1.pilot_name = ps2.pilot_name and ps2.plane_name = hangar.plane_name ) ) 

Or...

Select all users who accepted the answers to the questions tagged with the 10 most popular programming languages .

The answer is (possibly) (provided that Accepted_Answers and the Target_Language_Tags table with the necessary tags are presented):

 select distinct u.user_name from Users as u join Accepted_Answers as a1 on u.user_id = a1.user_id where not exists ( select * from Target_Language_Tags t where not exists ( select * from Accepted_Answers as a2 join Questions as q on a2.question_id = q.question_id join Question_Tags as qt on qt.question_id = q.question_id where qt.tag_name = t.tag_name and a1.user_id = a2.user_id ) ) 

Answer 11

Why should we hire you when we have a complex application using properly optimized ORM and implementing caching systems such as memcached ?

This is a serious question, he must be able to justify his existence. How Jeff Atwood likes to say, "Hardware is cheap, programmers are expensive. "

Answer 12

Compare and compare the differences between sql / rdbms solution and nosql solution. You cannot pretend to be an expert in any technology without knowing its strengths and weaknesses compared to its competitors.

Answer 13

Give an example where denomalization is preferred.

(I like this one because people come from college, trying to put everything in 3rd normal form)

Answer 14

• What types of databases have caused you, as a developer, more problems to understand and debug? Expected answer, IMHO, experience with problems using different types of date / time and BLOB.

• When is it convenient to use raster indexes?

Answer 15

What explain plan does and how to interpret your results.

Answer 16

• How do you detect and resolve concurrency problems at the application level?
• What blocking paradigms are usually available, and discuss their pros and cons.
• Discuss NULL values ​​and related issues.
• What is the largest database system you worked with: # tables, # rows, # users.

The following platform-specific questions are also asked (SQL Server):

• Discuss the IDENTITY columns.
• What is the timestamp data type used for?

Answer 17

The application is used 24 hours a day. Your service / update window is 2 hours per month, how do you plan to minimize the failure?

Answer 18

Almost everything is mentioned here. I would like to share one question that a senior database manager asked me. I found the question quite interesting, and if you think about it deeply, it makes a lot of sense.

Question: How would you describe the database to your 5 year old child?

Answer 19

• Explain the difference between internal and external connection.
• What is a Cartesian product?
• Explain the third normal form

Answer 20

• Explain possible table restrictions
• Explain views (and materialized)
• Explain the sequence
• Explain triggers

Answer 21

• What are the disadvantages of using adhoc / on-fly SQL queries and what will you do instead?

This area can have a huge impact on db performance and security. Get it wrong and you may find yourself in a world of pain.

• In what situations will you use adhoc / on-fly SQL queries?

Because there is always an exception to the rule :)

Answer 22

"Why does every SELECT always include DISTINCT?"