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

Question

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?

A reprint of my answer here, as general guidelines for topics.

Basics

---

1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

---

4. JOINs, ANSI-89 and ANSI-92 syntax
5. UNION vs UNION ALL
6. NULL handling: COALESCE & Native NULL handling
7. Subqueries: IN, EXISTS, and inline views
8. Subqueries: Correlated
9. WITH syntax: Subquery Factoring/CTE
10. Views

Advanced Topics

---

• Functions, Stored Procedures, Packages
• Pivoting data: CASE & PIVOT syntax
• Hierarchical Queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized Views
• Query Optimization: Indexes
• Query Optimization: Explain Plans
• Query Optimization: Profiling
• Data Modelling: Normal Forms, 1 through 3
• Data Modelling: Primary & Foreign Keys
• Data Modelling: Table Constraints
• Data Modelling: Link/Corrollary Tables
• Full Text Searching
• XML
• Isolation Levels
• Entity Relationship Diagrams (ERDs), Logical and Physical
• Transactions: COMMIT, ROLLBACK, Error Handling

Here are a few:

• What is normalization and why is it important?
• What are some situations where you would de-normalize data?
• What is a transaction and why is it important?
• What is referential integrity and why is it important?
• What steps would to take to investigate reports of slow database performance?

What is sql injection and how do you prevent it?

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

I've placed this answer because Erwin Smout posted a answer that was so wrong it highlighted that there is probably a need to specifically guard against it.

Erwin suggested:

"Why should every SELECT always include DISTINCT ?"

A more appropriate question would be: If someone were to make the claim that: "every SELECT always include DISTINCT"; how would you comment on the claim?

If a candidate is unable to shoot the claim down in flames they either:

• Don't understand the problem with the claim.
• Lack in critical thinking skills.
• Lack in ability to communicate technical issues.

For the record

1. Suppose your query is correct, and does not return any duplicates, then including DISTINCT simply forces the RDBMS to check your result (zero benefit, and a lot of additional processing).
2. Suppose your query is incorrect, and does return duplicates, then including DISTINCT simply hides the problem (again with additional processing). It would be better to spot the problem and fix your query... it'll run faster that way.

At our company, instead of asking a lot of SQL questions that anyone with a good memory can answer, we created a SQL Developers test. The test is designed to have the candidate put together a solid schema with normalization and RI considerations, check constraints etc. And then be able to create some queries to produce results sets we're looking for. They create all this against a brief design specification we give them. They are allowed to do this at home, and take as much time as they need (within reason).

I would give a badly written query and ask them how they would go about performance tuning it.

I would ask about set theory. If you don't understand operating in sets, you can't effectively query a relational database.

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

If the job involved imports and exports I would ask questions about SSIS (or other tools involved in doing this used by other datbases). If it involved writing reports, I would want to know that they understand aggregates and grouping (As well as Crystal Reports or SSRS or whatever ereporting tool 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'