Below are approximately 80 SQL related interview questions that a Software Test Analyst could well face. There are basic questions as well as more advanced ones, hopefully, catering to both SQL beginners and more experienced technicians.
Some of these questions are ones I have asked as a Manager, they are also questions that I’ve either been frequently asked at interviews or could most likely be asked.
To help you organise things in your mind, I have grouped the questions as per the following table of contents:
Table of contents
- Database Basics
- SQL Basics
- DQL (Data Query Language)
- DML (Data Manipulation Language)
- DDL (Data Definition Language)
1. Database Basics
Q1. What is a Database?
Answer:
It is a store for a collection of related data
Q2. What is a (Database Management System) DBMS?
Answer:
It is a software system that allows the management of databases
Q3. What is meant by Relational in RDBMS?
Answer:
There are many different types of DBs, such as Hierarchical, Relational, Network etc.
Relational DBs are where its main data storage types (Tables) are related to each other by certain data (keys) that it holds
Q4. What makes tables relational?
Answer:
They have key columns (foreign keys) that are related to other tables’ columns (primary keys)
Q5. What is a Database table made up of?
Answer:
Rows and columns
Q6. What is a row also known as?
Answer:
A record
Q7. What is a column also known as?
Answer:
A field
Q8. What uniquely identifies a row in a table?
Answer:
A Primary Key
Q9. What restricts the type of info a column holds
Answer:
Its Data Type
Q10. What best describes a Data Type?
Answer:
It determines what type of data can be stored in a column
Q11. Can you name some Data Types?
Answer:
INT, DECIMAL, VARCHAR, DATE, TIMESTAMP
Q12. What is a VARCHAR?
Answer:
It stands for variable character. A VARCHAR cell stores a character string of variable length
Q13. What does INT stand for?
Answer:
Integer
Q14. What is a NULL value in a database cell?
Answer:
It is an empty cell
Q15. What can be stored in a BOOLEAN data field?
Answer:
Either True or False
Q16. What best describes a Primary Key?
Answer:
It uniquely identifies each row in a table
Q17. How many Primary Keys can you have in a table?
Answer:
1
Q18. How many columns can a Primary Key have?
Answer:
As many as it takes to make a row unique
Q19. What is a database index?
Answer:
It is a data structure that improves the speed of data retrieval operations on a database table (at the cost of additional writes and storage)
Q20. What is a Foreign Key?
Answer:
It is a field that links (relates) one table to another table’s primary key
Q21. What is meant by data integrity?
Answer:
It defines:
- the accuracy and consistency of data stored in a database
- integrity constraints: enforcing business rules on the data when it is entered into the database
2. SQL Basics
Q1. What is SQL?
Answer:
It stands for Structured Query Language. It is a standard language for accessing and manipulating databases
Q2. What are the 4 main SQL sub languages?
Answer:
DDL – Data Definition Language
DCL – Data Control Language
DQL – Data Query Language
DML – Data Manipulation Language
Q3. What is the DDL used for?
Answer:
It is used to create and manage Databases
Q4. What is the DCL used for?
Answer:
It controls access / permissions to a database
Q5. What is the DQL used for?
Answer:
This allows the retrieval of data from a database, i.e. SELECT
Q6. What is the DML used for?
Answer:
This allows the manipulation of data on a database
Q7. Name some common DDL SQL statements?
Answer:
CREATE, ALTER, DROP, RENAME, TRUNCATE
Q8. Name some common DCL SQL statements?
Answer:
GRANT, REVOKE
Q9. What are the 3 main DML SQL statements?
Answer:
INSERT, UPDATE, DELETE
Q10. What does it mean SQL is NOT case sensitive?
Answer:
You can enter SQL statements in either lowercase or uppercase or even a mix of both
Q11. What is used to terminate an SQL statement?
Answer:
; (semi colon)
Q12. How do you comment out SQL code?
Answer:
Using 2 hyphens together at the start of a line you want commented out, e.g:
– – this line is commented out
Q13. What is a keyword in SQL?
Answer:
They are reserved words that are used to form part of the SQL language and, therefore, cannot be used as table names, column names or values
3. DQL (Data Query Language) Questions
3.1 Data Retrieval
Q1. What SQL statement would you use to retrieve data from a database?
Answer:
SELECT
Q2. What does * do in a SELECT statement?
Answer:
It retrieves all columns for each row retrieved
Q3. How do you retrieve only certain columns in a SELECT statement?
Answer:
By specifying the columns you wish to retrieve after the SELECT keyword
3.2 Identifiers:
Q4. What is an identifier in an SQL statement?
Answer:
It is a name of a database object, e.g. names of Database Objects such as Tables, Views, Columns, Indexes, Aliases etc
3.3 Clauses
Q5. What is a CLAUSE in SQL?
Answer:
It helps to limit the result of an SQL query by providing a condition, by filtering the rows from the entire set of records, example clauses: FROM, WHERE, GROUP BY, HAVING etc
Q6. What clause is used to filter retrieved data?
Answer:
WHERE
Q7. What clause is used to sort retrieved data in a SELECT statement?
Answer:
ORDER BY
Q8. What keywords are used to sort data upwards and downwards?
Answer:
ASC / DESC
Q9. What statement can be used to summarise data into categories?
Answer:
GROUP BY
Q10. Why would you use a HAVING clause?
Answer:
It filters data the same way as a WHERE clause, however, whereas the WHERE clause cannot work with aggregate functions, the HAVING clause can
Q11. What is the correct order in a SELECT statement of ORDER BY, HAVING, FROM, SELECT, WHERE, GROUP BY?
Answer:
SELECT, FROM, WHERE, GROUP BY, HAVING, SORT BY
3.4 SQL Functions:
Q12. What function is used in a SELECT statement to return only unique values in a column?
Answer:
DISTINCT()
Q13. What function would you use to find the number of rows in a table?
Answer:
COUNT()
Q14. What function would you use to find the total of a customer’s bank transactions?
Answer:
SUM()
Q15. What function would you use to find the average number of customers that open a bank account each month?
Answer:
AVG()
Q16. What function would you use to find the transaction with the highest amount a customer has paid into their bank account?
Answer:
MAX()
Q17. What function would you use to find the transaction with the lowest amount a customer has paid into their bank account?
Answer:
MIN()
3.5 SQL Operators
Q18. What are the 3 types of SQL Operators?
Answer:
Arithmetic, Comparison, Logical
Q19. What are some common Arithmetic operators?
Answer:
+ (addition sign), – (subtraction), * (multiplication), / (division) etc
Q20. What are some common Comparison operators?
Answer:
=, >, <, !=, >= etc
Q21. What are some common Logical operators?
Answer:
AND, OR, NOT LIKE, BETWEEN, IN, EXISTS etc
Q22. What operator do we use to find a snippet of text in a column?
Answer:
LIKE
Q23. What character do we use for a wildcard search when using LIKE?
Answer:
%
Q24. Why would we use the IN operator?
Answer:
To specify multiple OR statements in one statement
Q25. Which operator would we use to search for something where a column’s value could start at 2 and end at 10?
Answer:
BETWEEN
Q26. Why would we use an EXISTS operator?
Answer:
To test for the existence of any record in a subquery
3.6 Aliases
Q27. What keyword is used to create an alias for a column?
Answer:
AS
Q28. What keyword is used to create an alias for a table?
Answer:
AS
3.7 Subquerying
Q29. What is a subquery in SQL?
Answer:
A subquery is a query inside another query (the main query). Its role is to retrieve data that can be used in the main query’s filtering of data.
Subqueries are always executed first, therefore handing data back to the main query before that is actually executed
3.8 Joining tables
Q30. What is used to allow data to be retrieved from more than one table in a single SQL statement?
Answer:
A JOIN
Q31. What columns would you normally JOIN on?
Answer:
One table’s column (Foreign Key) linked to another table’s Primary Key
Q32. What are foreign keys?
Answer:
It is a field that links one table to another table’s primary key
Q33. Can you name different types of JOINs that you can have?
Answer:
INNER, OUTER, RIGHT, LEFT
Q34. What type of JOINs are you familiar with?
Answer:
Me…personally, the join I use is an INNER join (the most commonly used join in the development world), which is set implicitly, meaning that you don’t have to code JOIN in the SQL query…which I don’t! The OUTER joins (LEFT, RIGHT and FULL)…in order to use them, you do have to explicitly code them in SQL queries.
4. DML (Data Manipulation Language) Questions
Q1. What are the 3 DML statements?
Answer:
INSERT, UPDATE (which includes SET), DELETE
Q2. Which statement is used to add a record to a database?
Answer:
INSERT
Q3. Which statement is used to amend data in a database?
Answer:
UPDATE
Q4. Which statement is used to erase data from a database?
Answer:
DELETE
Q5. Which statement is used to define how to amend data in an UPDATE statement?
Answer:
SET e.g. UPDATE bank_transaction SET category = ‘car insurance’ WHERE category = ‘car ins’;
Q6. What does a COMMIT do?
Answer:
Saves all DB changes made by the execution of an SQL statement
Q7. What is a Trigger?
Answer:
A Trigger is a stored procedure (block of procedural code) that automatically runs when an event occurs in a database, such as data being added, modified or deleted
5. DDL Questions
Q1. What does DDL stand for?
Answer:
Data Definition Language
Q2. What is DDL used for?
Answer:
It is a language used to create and manipulate (modify) the structure of a database and all its objects (tables, columns etc)
Q3. What statement is used to produce a database or a table?
Answer:
CREATE
Q4. What statement is used to delete a database, table or column?
Answer:
DROP
Q5. What statement is used to add, delete, or modify columns in an existing table?
Answer:
ALTER
Q6. How can you delete all the data from a table?
Answer:
Using TRUNCATE