Congrats on making it through the entire course! You’ve learned about many different concepts in SQL that can be applied in a data analyst role so now it’s time to put your knowledge to the test!
This article will provide a list of some common SQL interview questions that you may encounter in a real interview, along with answers.
To get the most out of it, try to answer each question on your own first. Good luck!
1. What is SQL?
SQL, which stands for Structured Query Language, is a programming language to communicate with data stored in a relational database management system.
Its syntax is similar to English, making it easy to write, read, and interpret. It allows you to write queries which define the subset of data you are seeking. You can save these queries, refine them, share them, and run them on different databases.
2. What is a database?
A database is a set of data stored in a computer, where the data is structured in a way that makes it easily accessible.
3. What is a relational database?
A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to other pieces of data in the database.
Data in a relational database is often organized as tables.
4. What is a RDBMS?
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database.
Most relational database management systems use SQL language to access the databases.
The most popular RDBMS is MySQL. Others include PostgreSQL, Oracle DB, SQL Server, and SQLite.
5. What is a table?
A table is a collection of data organized into rows and columns. They are sometimes referred to as “relations”.
They can have hundreds, thousands, and sometimes even millions of rows of data.
6. What is a row and column in a table?
A row is a single record of data in a table.
A column is a set of data values of a particular type.
7. What is a data type?
A data type is an attribute that specifies the type of data a column holds.
Every column of a database has a data type.
Some common data types are
8. What is a primary key and foreign key?
A primary key is a column that uniquely identifies each row of a table.
Primary keys must satisfy the following requirements: No value can be
NULL, each value must be unique, and a table cannot have more than one primary key column.
For example, in a
customers table, the primary key would be
A foreign key is the primary key for one table that appears in a different table. For example if there was also an
orders table, each order can store the customer, such that the
customer_id column would be the foreign key.
9. What is the difference between ALTER and UPDATE?
ALTER statement is used to add a new column to a table. It changes the table structure.
UPDATE statement is used to edit a row in the table. It changes existing records in the table.
10. What is a query?
A query is a SQL statement used to retrieve information stored in a database.
They allow us to communicate with the database by asking questions and returning a result set with data relevant to the question.
11. What is a subquery?
A subquery is an internal query nested inside of an external query.
They can nested inside of
When a subquery is present, it will be executed before the external statement is run.
12. What are constraints?
Constraints are a set of rules used to tell the database to limit the type of data that can be stored for the columns. They tell the database to reject inserted data that does not adhere to the restriction.
They add information about how a column can be used, and are invoked after the data type for a column.
Some examples of constraints are:
PRIMARY KEY, which uniquely identifies each row and requires each value to be unique.
UNIQUE, which requires every value in the column to be different.
NOT NULL, which requires columns to have a value.
DEFAULT, which takes an additional argument which will be the assumed value for an inserted row if the new row does not specify a value for that column.
13. What is a statement?
A statement is text that the database recognizes as a valid command.
They can be used to perform tasks like altering he structure of a table, updating data, or retrieving data from a database.
The structure of a statement can vary, but each must end with a semi-colon
;. The number of lines used for a statement does not matter, and it can be written as a single line or split into multiple lines to make it easier to read.
14. How do you check if a field does not have a value or has a value?
When a field has no value, it is indicated with a
To check if a field does not have a value, you can use
IS NULL as the condition:
WHERE col IS NULL.
To check if a field has a value, you can use
IS NOT NULL as the condition:
WHERE col IS NOT NULL.
15. What is the difference between DISTINCT and UNIQUE?
DISTINCT is a keyword used when we want to return unique values in the output. It filters out all duplicate values in the specified column.
UNIQUE is a constraint used to ensure that all values of a column are different. It is similar to
PRIMARY KEY, except that a table can have many different
16. What are aggregate functions used for?
Aggregate functions are used to perform a calculation on one or more values, and returns a single value of more meaningful information.
Some aggregate functions are
17. What is a join?
A join is a way to combine rows from two or more tables, based on a related column between them.
18. What is the difference between an INNER JOIN and LEFT JOIN?
INNER JOIN is used to include combined rows from two tables that match the
ON condition. The final result does not include rows with no match for the
LEFT JOIN is used to keep all rows from the first table, regardless of whether there is a matching row in the second table for the
19. What is the purpose of windows functions?
Windows functions are used when you want to maintain the values of your original table while displaying grouped or summative information alongside. It is similar to aggregate functions, but does not reduce the number of rows in the result by combining or grouping them into a few result.
20. What are indexes and why are they needed?
Indexes are a powerful tool used in the background of a database to speed up querying, by acting as a lookup table for data.
They are needed to efficiently store data for quicker retrieval, which can be paramount to the success of large tech companies which need to process on the scale of petabytes of data each day.