12 SQL Developer Interview Questions and Answers

A RDBMS is a system that organizes data into tables called relations, which are further organized into columns (fields) and rows (often called tuples). The relational model allows data to be queried in a nearly unlimited number of ways, making it great for sorting through large volumes of data. It’s important to pick a SQL developer who’s experienced with the particular set of web technologies you plan to use to support your app. Common SQL dialects include PL/SQL for Oracle, T-SQL for MS SQL, and JET SQL for MS Access. Look up any particular dialects used for your chosen RDBMS.

The basic SQL commands can be organized into the following categories:

• Data Manipulation Language (DML)
  • INSERT: Creates records. The “Create” in CRUD.
  • SELECT: Retrieves records. The “Read” in CRUD.
  • UPDATE: Modifies records. The “Update” in CRUD.
  • DELETE: Deletes records. The “Delete” in CRUD.
• Data Definition Language (DDL)
  • CREATE: Creates a new object.
  • ALTER: Alters an existing object.
  • DROP: Deletes an existing object.
• Data Control Language: (DCL)
  • GRANT: Grants privileges to users.
  • REVOKE: Revokes privileges previously granted to a user.

In practice however, you should be aware that your typical developer is most likely going to answer this question with CRUD (Create, Read, Update, and Delete), the four essential database operations for database manipulation. Bonus points if they also mention some of the others.

A table is composed of columns (fields) and rows (records or tuples). Each record can be considered as an individual entry that exists within the table and contains multiple fields. For example, a data entry (record) for a customer might consist of the fields: ID, name, address, and purchase.

The easiest way to explain this difference is to recognize that zero is a value representing the number zero. NULL is a non-value or a placeholder for data that is not currently known or specified. The result of any operation on a NULL value, as in arithmetic, will be undefined.

Constraints are rules you can place on columns or tables to limit the type of data that can be entered into a table. This prevents errors and can improve the accuracy and reliability of the database as a whole. Common constraints include:

• NOT NULL: Prevents a column from having a NULL value.
• DEFAULT: Specifies a default value for a column where none is specified.
• PRIMARY KEY: Uniquely identifies rows/records within a database table.
• FOREIGN KEY: Uniquely identifies rows/records from external database tables.
• UNIQUE: Ensures all values are unique.
• CHECK: Checks values within a column against certain conditions.
• INDEX: Quickly creates and retrieves data from a database.

When it comes to storing data accurately, consistently, and reliably within a RDBMS, there are four general types of data integrity that you can implement:

• Entity (Row) Integrity: Avoids duplicate rows in tables.
• Domain (Column) Integrity: Restricts the type, format, or range of values to enforce valid entries.
• Referential Integrity: Ensures rows used by other records cannot be deleted.
• User-Defined Integrity: Enforces rules set by the user that do not fall into the other categories.

The primary purpose of normalization is to make databases more efficient by eliminating redundant data and ensuring data dependencies are coherent. Storing data logically and efficiently reduces the amount of space the database takes up and improves performance. The set of guidelines used to achieve normalization are called normal forms, numbered from 1NF to 5NF. A form can be thought of as a best-practice format for laying out data within a database.

An inner join is when you combine rows from two tables and create a result set based on the predicate, or joining condition. The inner join only returns rows when it finds a match in both tables. An outer join will also return unmatched rows from one table if it is a single outer join, or both tables if it is a full outer join. A solid example of this will clearly illustrate the difference and demonstrate how well the developer understands joins.

The issue here is that there must be a GROUP BY clause here. This query will get the average order amount by customer (UserId) where the customer has at least 1 order. The correct query is listed below:

SELECT UserId, AVG(Total) AS AvgOrderTotal
FROM Invoices
GROUP BY Userid
HAVING COUNT(OrderId) >= 1

The following query will retrieve all recruiters recruited by John Do. SELECT Employee.

Name FROM Employee
JOIN Recruiter ON Employee.RecruitedBy = Recruiter.Id
WHERE RecruitedBy = 3

To retrieve all employees who were not recruited by anyone in the recruiter table, you could use the following query:

SELECT Employee.Name FROM Employee
JOIN Recruiter ON Employee.RecruitedBy = Recruiter.Id
WHERE RecruitedBy Is Null

This can be accomplished using the “TOP” keyword as follows.

SELECT TOP (1) Elevation FROM
(
  SELECT DISTINCT TOP (10) Elevation FROM Mountain ORDER BY Elevation DESC
) AS Mt ORDER BY Elevation

The first query takes the top 10 mountains by elevation in the table and lists them in descending order, with the tallest mountain at the top of the list. However, since we want the 10th tallest mountain, the second query, “ AS Mount ORDER BY Elevation”, promptly reorders the list of 10 in ascending order before the top record is selected. Note that not all databases support the “TOP” keyword, so answers may vary. Another possible solution that follows a similar logic for MySQL or PostreSQL is detailed below, this time using the “LIMIT” keyword.

SELECT Elevation FROM
(
  SELECT DISTINCT Elevation FROM Mountain ORDER BY Elevation DESC LIMIT 10
) AS Mt ORDER BY Elevation LIMIT 1;

SQLite, PostgreSQL, and SQL Server all support the ever useful “except” keyword which can be employed as detailed below

select * from fibonacci
except
select * from prime;

A popular database technology that does not support “except” is MySQL, which is why it must use the “not in” keyword. Note that for Oracle, the “minus” keyword must be used instead.