8 Common Entry Level SQL Developer Interview Questions

Have you ever been interviewed for an entry-level SQL position? Are you planning to apply for such a job? This article will help you to know what to expect and prepare for common SQL developer interview questions.

After gaining some basic knowledge of SQL and programming, you're probably wondering what to do next. Or maybe you're not wondering! You know that what you've learned will soon disappear if you don't use it regularly. And the best way to maintain your knowledge and, of course, to learn even more is to start working as an SQL developer. But you don't know what questions to expect in an interview. So, you’re hesitating in applying for a job. Still, knowing what you’ll be asked won’t help you much, will it? What you need are the answers! So, I will provide you with entry-level SQL developer interview questions and, more importantly, answers to those questions.

Let’s get to the questions and answers!

Question 1: What is a Relational Data Model? Explain Several Other Logical Data Models

Relational Data Model

The relational data model is the most widely used data model. It was introduced in 1969 by Edgar F. Codd. A relational data model is based on the mathematical concept of relation. The purpose of relations is to avoid data redundancy, ensure data integrity, and speed up data retrieval.

In the context of the database, a relational data model sees the database as a collection of relations, in which a relation is simply a table. The table, of course, contains values. The three main elements of a table are columns, rows, and values.

Columns are also called attributes, which means they represent certain properties for which the data is stored. It’s important to note that columns are unique, i.e. no columns with the same name are allowed in a table.

Every row is a single occurrence of the attributes. Rows are also called records.

Values are found at the intersections of the columns and rows.

Network Model

Another common model is the network model. It was invented by Charles Bachman in 1969 to improve the hierarchical data model. A network model is represented by a graph that shows object types as nodes and relationship types as arcs.

Hierarchical Data Model

This model is similar to the network model, except it's represented by a tree-like structure instead of a graph. Like the network model, object types are represented by nodes, but relationship types are represented by the "owners-members" link between the data.

Object-Relational Database

This model is similar to the relational data model, and its creation was inspired by object-oriented programming languages, such as Java, C++, Visual Basic, .Net, or C#. This model views a database as a collection of stored objects which consist of custom data types and methods for handling the data. Every object belongs to a certain class. Between every class, there are established inheritance and aggregation relationships.

There are, of course, many other data models such as the document model, star schema, correlational model, XML database, etc. But you don't need to know all of those. The relational data model is the most commonly used. And if you've applied for an SQL developer job, you'll probably work with a relational database. Being able to explain these four data models should be enough for an SQL developer interview.

Question 2: What is RDBMS?

DBMS stands for “Database Management System.” It’s a software that stands between the database user and the database itself. The DBMS operates with data according to instructions given by the database user. It allows entry, storage, and retrieval of the data. Following that logic, RDBMS is a Relational Database Management System, a DBMS created specifically for handling relational databases. According to the logic of the relational database, RDBMS stores data in tables that consist of columns and rows. The most popular RDBMSs are Microsoft SQL Server, Oracle Database, MySQL, and IBM DB2.

Question 3: What is the ER Diagram, and What are its Components?

As someone who is aspiring to perform well in an SQL programmer interview, especially an entry-level one, you should know something about the ER diagram. An ER diagram is an entity relationship diagram, and it shows the relations between entities in a database. It is one of the important tools used when designing a database. There are three components of every ER diagram:

• Entity
• Relationship
• Attribute

Entity

The entity is broadly defined as anything we can collect data about. It is an object or concept in the business process for which you want to store data. For example, a company has employees, these employees have unique personal identification numbers (PINs), they work in certain departments, and they produce certain products. This is a business process, and in this example, employees, departments, and products are entities. An entity consists of attributes. Several entities can represent entity sets. An entity set is any number of entities that can be viewed as similar according to certain properties.

Relationship

Relationships show how one entity is connected or associated with another entity or other entities. In the example above, employees and PINs are entities. One employee can have only one PIN. Also, one PIN can be allocated to only one employee: a PIN is unique. This is called a one-to-one relationship. Such relationships are quite rare in databases. If one PIN can be allocated to only one employee and vice versa, there’s no need to have PIN as a separate entity. It can be the employees’ entity attribute. Another example is the employees and departments entities. The relationship between them is that one employee can work only in one department. However, in one department there can be one or more employees. This is called a one-to-many relationship. The relationship between employees and products is such that one employee can produce more than one product. Also, one product can be produced by more than one employee. This relationship is called many-to-many.

Attribute

An attribute represents the property of an entity or relationship that can have a certain value assigned to it. A set of attributes describes an entity or relationship. For example, if employees are an entity, then attributes can be name, surname, date of birth, etc. Attributes for departments as an entity can be department name, department head, number of employees, etc. Products as an entity can be described with the following attributes: ID, product name, product color, product price, etc.

Question 4: What is Database Normalization? Explain Normal Forms

Normalization is the process of organizing data in a relational database used to reduce data redundancy and increase data integrity. The goal is to create an efficient, reliable, and flexible database. Normalization is implemented by following rules called normal forms. A database is usually considered normalized when it meets the requirements of 3NF:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

First Normal Form (1NF)

It requires attributes (table columns) to contain only atomic and single values. That being said, the purpose of 1NF is to ensure there is no set of data shown as a single value.

Second Normal Form (2NF)

For a database to be in 2NF, it has to be in 1NF. It also needs to ensure that all the attributes depend only on a table’s primary key. The primary key is an attribute or a set of attributes that uniquely defines every row in a table. Meaning, one primary key can be allocated only to one row in a table.

Third Normal Form (3NF)

3NF requires a database to be in 1NF and 2NF, of course. In addition, no transitive dependencies are allowed, meaning all attributes that are not a primary key have to be mutually independent. It is not allowed for an attribute to depend on another attribute, which depends on a primary key.

Even though there is also Boyce-Codd's normal form, fourth normal form, fifth normal form, and some other additional forms, knowing the first three normal forms should be enough for an entry-level SQL interview.

Question 5: What are Attribute Constraints?

An attribute constraint is a rule that limits the type of data that can be entered as a specific attribute. It is used to ensure the integrity of the database. The five most common attribute constraints are:

• NOT NULL – used to ensure the attribute can't have a NULL value
• CHECK – used when the attribute value has to satisfy a certain condition
• UNIQUE – used when a certain attribute value can be entered only once
• PRIMARY KEY – used for designating an attribute as a primary key, which has to be UNIQUE and NOT NULL
• FOREIGN KEY – used to show the relationship between the tables, i.e. the attribute value in one table is retrieved from the attribute of another table

Question 6: What are the SQL Sublanguages and Main Keywords?

SQL can be divided into three groups or sublanguages:

• Data Definition Language (DDL)
• Data Control Language (DCL)
• Data Manipulation Language (DML)

Data Definition Language (DDL)

This sublanguage is used for creating, deleting, or modifying a certain database object. The three main keywords are:

• CREATE – creates an object (e.g. a table) in a database
• DROP – deletes an object in a database
• ALTER – changes an already-existing object in a database

Data Control Language (DCL)

The DCL is used for authorization, creating roles, and controlling access to certain data in a database. The two main keywords are:

• GRANT – permits a certain database user to use a database or certain database objects
• REVOKE – revokes or decreases permission given to a certain database user

Data Manipulation Language (DML)

This SQL sublanguage consists of keywords used to manipulate data, i.e. add, modify, or remove data. The main keywords are:

• SELECT – used to retrieve data, and occasionally used with other keywords, such as:
  • FROM – used to choose which table data is retrieved from
  • WHERE – used to retrieve data containing a specific value
  • GROUP BY – used to group the retrieved data according to a certain attribute
  • HAVING – used with GROUP BY to identify the rows to be retrieved
  • ORDER BY – used to sort the retrieved data by a certain attribute
• INSERT – used to insert rows in an already existing table
• UPDATE – used to change values of an already existing row
• MERGE – used to merge data from two or more tables
• DELETE – used to delete already existing rows from a table
• TRUNCATE – used to delete all data from a table
• BEGIN WORK – used to mark the start of a transaction
• COMMIT – used to mark the end of a transaction
• ROLLBACK – used to discard all changes since the last COMMIT or ROLLBACK

Please note that according to some documentation, TRUNCATE is a DDL keyword instead of a DML keyword.

Question 7: Explain the Difference Between TRUNCATE, DELETE, and DROP

Now that you know the answer to the previous question, this one should be easy. As you've already learned, TRUNCATE deletes all data from the table. If using DELETE, you can delete some rows (if using the WHERE clause), or you can delete all rows, same as TRUNCATE. The main difference is that DELETE removes data row by row with every deleted row being logged by RDBMS. If you're trying to delete a huge amount of data, using DELETE would be much slower than TRUNCATE, since TRUNCATE logs only one action. Also, it’s good to know that TRUNCATE can’t be used if there is a foreign key in a table. Using TRUNCATE will also lock the table, so it’s not advised to use it on shared tables.

But what does DROP do? It surely deletes something, but what? The main difference is that TRUNCATE and DELETE are used to delete data from a table, but not the table itself. By using DROP, however, what you’ll do is delete the whole table. Deleting a table means your data will be gone, of course. But you will also delete the indexes, triggers, constraints, and permission specifications.

Question 8: What are the Types of Joins in SQL?

Joins in SQL are used if you're trying to use data from two or more tables at the same time. To do this, you have to somehow connect the tables, which can be done using the five SQL join types:

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

Cross Join

This type of join means that every row from one table is joined to every row from another table. It multiplies rows. If you're working with a huge amount of data, this is not advisable. This type of join is also called the Cartesian product. You can find more details about cross join in this article.

Inner Join

Inner join retrieves data where the records from the first table are equal to the records from the second table, based on a set condition. There’s also an article explaining inner join in more detail that could help you in preparing for your interview.

Left Outer Join

When using this type of join, you will retrieve all the rows from the first (or left) table and the matching rows from the second (or right) table. If there is no matching row in the second table, you will get a NULL value.

Right Outer Join

The principle of using this join is the same as with the left outer join. It will return all the rows from the second (or right) table and the matching rows from the first (or left) table. No matching rows in the first table will result in a NULL value.

Full Outer Join

Using this join will result in retrieving all the rows from the first table and all the rows from the second table. If there are non-matching rows, both from the first and second tables, they will be shown as a NULL value. There’s a great article explaining everything you need to know about outer joins, together with the examples.

Do you Feel Ready for SQL Developer Interview Questions?

In this article, I've covered some basic questions and answers that could come up in an entry-level SQL interview. I consider these questions essential for everyone aspiring for a job as an SQL developer. Of course, that doesn't mean you don’t need to know anything but those eight questions and answers. This should serve only as a guideline, so feel free to dive deeper into these topics. There are also plenty of other questions that could arise, but these eight should give you a feeling of what you can expect. If you're not sure you understand the concepts above, practice a little by taking some courses. The LearnSQL.com platform offers practical courses in the basics of SQL (in general) and joining tables.

Did you find this article helpful? Did it encourage you to go for the job you saw advertised a few days ago? Let me know how it went and whether you were asked some of these questions. Or maybe you have already gone to several job interviews? What were you asked? Did you find some of the above answers insufficient or too detailed? Let me know in the comment section! I would be glad to hear about your experience.