In the ever-evolving landscape of technology, SQL (Structured Query Language) remains a cornerstone for managing and manipulating relational databases. As companies depend more and more on data-driven insights, there is a growing need for qualified SQL specialists. You must prepare for SQL interviews, regardless of your experience level as a database administrator or your interest in data.
This article aims to provide a comprehensive guide to SQL interview questions and their answers, helping you navigate the challenges and stand out during the interview process.
Why SQL is important for an interview?
SQL enables users to interact with relational databases, extract insights, and design efficient database structures. SQL proficiency is often listed as a prerequisite in job descriptions for various positions like data administrator, data analyst, software developer, etc. making it a key factor in candidate selection. It demonstrates problem-solving abilities, understanding of data integrity, and scalability considerations. As a standardized language, SQL’s versatility ensures its relevance across industries, emphasizing its importance in job interviews.
SQL Interview Questions and Answers
1. Explain SQL and its importance.
SQL stands for Structured Query Language and is a domain-specific language used for storing data and information in tabular form. It is also used for managing and manipulating relational databases. It is important for data retrieval, updates, and management, providing a standardized way to interact with databases.
2. Explain the between SQL and MySQL.
SQL is a language, while MySQL is a relational database management system (RDBMS) that utilizes SQL. SQL is used for querying, inserting, updating, and managing data, while MySQL is the software implementing and managing databases.
SELECT * FROM employees;3. Fetch all the columns from the “employees” table.
| SELECT * FROM employees; |
4. List the unique job titles from the “jobs” table.
| SELECT DISTINCT job_title FROM jobs; |
5. How to Count the number of employees in each department.
| SELECT department_id, COUNT(*) as employee_count FROM employees GROUP BY department_id; |
6. Retrieve the names of employees and their corresponding managers.
| SELECT e.employee_name, m.manager_name FROM employees e LEFT JOIN managers m ON e.manager_id = m.manager_id; |
7. Differentiate between INNER JOIN and LEFT JOIN.
Only the rows in both tables with matching values are returned by INNER JOIN; non-matching rows are not included. A LEFT JOIN returns all rows from the left table and matching rows from the right table, replacing non-matching rows with NULLs.
8. How do you remove duplicate records from a table?
We can use the DISTINCT keyword in conjunction with the SELECT statement to eliminate duplicate records. For example:-
| SELECT DISTINCT column1, column2 FROM table_name; |
9. Describe the primary key.
A primary key is a special code that every entry in a table has, guaranteeing its uniqueness and serving as a link between tables.
10. Find the highest salary in the “salaries” table.
| SELECT MAX(salary) FROM salaries; |
11. List employees who have a salary greater than $50,000.
| SELECT employee_name FROM employees WHERE salary > 50000; |
12. Explain the concept of normalization.
Normalization is the process of organizing data in a database to reduce dependencies and redundancies. It entails breaking up big tables into more manageable, related tables and establishing connections between them.
13. How to Calculate the average salary for each department.
| SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id; |
14. Clarify the purpose of the GROUP BY clause.
To create summary rows, combine data in designated columns that have the same values using the GROUP BY clause. It is commonly used with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN.
15. Retrieve employees whose names start with the letter ‘A’.
| SELECT employee_name FROM employees WHERE employee_name LIKE ‘A%’; |
16. How does the WHERE clause differ from the HAVING clause?
While the HAVING clause filters the results after grouping and aggregation, the WHERE clause filters the rows before grouping.
17. Count the number of employees in the “sales” department.
| SELECT COUNT(*) as employee_count FROM employees WHERE department_id = ‘sales’; |
18. Explain the concept of a foreign key.
A foreign key is a column or set of columns in a table that points to the primary key of another table. This connection between tables ensures referential integrity is maintained.
19. List the top 5 highest-paid employees from the table.
| SELECT employee_name, salary FROM employees ORDER BY salary DESC LIMIT 5; |
20. Explain the term self-join?
A table that is linked to itself is called a self-join. It is useful when creating relationships within a single table, such as in hierarchical data structures.
21. List employees who joined in the last year.
| SELECT employee_name FROM employees WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR); |
22. How to prevent SQL injection?
To prevent SQL injection, use parameterized queries or prepared statements. By using these methods, user input is handled more like data than executable code.
23. What is the purpose of the UNION and UNION ALL operators?
Duplicate rows are eliminated when two or more SELECT queries’ result sets are combined using UNION. UNION ALL includes all rows, including duplicates.
24. Find the second highest salary.
| SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); |
25. Retrieve the average salary excluding the lowest and highest salaries.
| SELECT AVG(salary) FROM employees WHERE salary NOT IN (SELECT MIN(salary) FROM employees UNION SELECT MAX(salary) FROM employees); |
26. Describe the concept of a view.
A view is a simulated table created from the outcome of a SELECT query. It provides a way to represent data from one or more tables without storing the data itself.
27. Calculate the total salary expense for the company.
| SELECT SUM(salary) as total_salary_expense FROM employees; |
28. What are stored procedures?
Precompiled SQL statements are kept in the database as stored procedures. They can be executed by invoking the procedure’s name, providing abstraction and reusability.
29. Find the employees with the same job title and department.
| SELECT job_title, department_id, COUNT(*) as employee_count FROM employees GROUP BY job_title, department_id HAVING COUNT(*) > 1; |
30. Differentiate between TRUNCATE and DELETE statements.
A DDL command called TRUNCATE deletes every record from a table without recording the deletion of any individual rows. A DML statement called DELETE deletes particular rows while logging each one.
31. Retrieve the oldest employee in the company.
| SELECT employee_name, MAX(birth_date) as oldest_birth_date FROM employees; |
32. What is the purpose of the ORDER BY clause?
We can arrange the result set of a query using the ORDER BY clause, sorting it in either ascending or descending order based on one or more columns.
33. List employees who have not been assigned a project.
| SELECT employee_name FROM employees WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM projects); |
34. Calculate the total number of projects for each department.
| SELECT department_id, COUNT(DISTINCT project_id) as project_count FROM projects GROUP BY department_id; |
35. Explain the concept of ACID properties in databases.
ACID properties (Atomicity, Consistency, Isolation, Durability) ensure the reliability of database transactions by guaranteeing atomic and consistent operations, isolation from concurrent transactions, and durability of committed changes.
36. Find employees with a salary above the department’s average salary.
| SELECT employee_name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id); |
37. Retrieve the second-highest salary from the Employee table.
To determine the second-highest salary, employ a subquery with the MAX and WHERE clauses:
| SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); |
38. Explain the trigger.
A trigger is a collection of commands that, when certain events happen, such as INSERT, UPDATE, DELETE, or DDL statements, cause the instructions to run automatically.
39. Retrieve the employee with the highest salary in each department.
| SELECT department_id, employee_name, salary FROM employees a WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = a.department_id); |
40. Differentiate between a clustered and a non-clustered index.
A clustered index dictates the physical arrangement of data in a table, unlike a non-clustered index, which doesn’t influence the data’s physical order. A table is limited to one clustered index, but it can accommodate multiple non-clustered indexes.
41. List employees who have worked for more than 5 years.
| SELECT employee_name FROM employees WHERE DATEDIFF(NOW(), hire_date) > 1825; — Assuming 365 days in a year |
42. How do we manage NULL values in SQL?
To manage NULL values in the WHERE clause, use the conditions IS NULL or IS NOT NULL. Moreover, NULL values can be changed to specified alternatives by using the COALESCE or ISNULL functions.
43. How to List the departments with more than 10 employees.
| SELECT department_id, COUNT(*) as employee_count FROM employees GROUP BY department_id HAVING COUNT(*) > 10; |
43. Explain the term cross join.
Each row from the initial table is paired with every row from the second table, resulting in the Cartesian product of the two tables, commonly referred to as a cross join or Cartesian join.
44. How to Count the number of employees in each job title and department combination.
| SELECT job_title, department_id, COUNT(*) as employee_count FROM employees GROUP BY job_title, department_id; |
45. Explain the purpose of the ROLLBACK statement.
The ROLLBACK statement is employed to reverse transactions that have not been committed, restoring the database to its state before the initiation of the transaction.
45. Retrieve the average salary for employees in each job title.
| SELECT job_title, AVG(salary) as avg_salary FROM employees GROUP BY job_title; |
46. How to find the nth highest salary in a table.
To determine the salary ranked nth highest, one can utilize the LIMIT and OFFSET clauses:
| SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET n-1; |
47. What is CTE?
A CTE stands for Common Table Expression which is a named temporary result set defined within the scope of a SELECT, INSERT, UPDATE, or DELETE statement. It enhances the readability of complex queries.
48. Find employees who have the same manager.
| SELECT manager_id, COUNT(employee_id) as team_size FROM employees GROUP BY manager_id HAVING COUNT(employee_id) > 1; |
49. Explain the purpose of the LIKE operator.
The purpose of using the LIKE operator is to search for a specified pattern in a column, allowing the use of wildcard characters for flexible string matching.
50. Calculate the total number of employees in the company.
| SELECT COUNT(*) as total_employees FROM employees; |
51. Calculate the total number of rows in a table.
The COUNT() function is employed to compute the overall number of rows in a table:
| SELECT COUNT( ) FROM table_name; |
52. What is the purpose of the DISTINCT keyword?
To remove duplicate rows from a result set, use the DISTINCT keyword to get unique values from a particular column or column.
53. List employees who have a salary higher than their manager.
| SELECT e.employee_name, e.salary, m.manager_name, m.manager_salary FROM employees e JOIN managers m ON e.manager_id = m.manager_id WHERE e.salary > m.manager_salary; |
54. Retrieve the latest 5 projects started in the company.
| SELECT project_name, start_date FROM projects ORDER BY start_date DESC LIMIT 5; |
55. Explain the concept of database transactions.
A database transaction is a sequence of one or more SQL statements executed as a single, indivisible unit. The consistency and integrity of the database are guaranteed by transactions.
56. Calculate the average age of employees.
| SELECT AVG(YEAR(NOW()) – YEAR(birth_date)) as avg_age FROM employees; |
57. Find employees who have worked on all projects.
| SELECT employee_id, employee_name FROM employees e WHERE NOT EXISTS ( SELECT DISTINCT project_id FROM projects WHERE project_id NOT IN ( SELECT project_id FROM employee_projects WHERE employee_id = e.employee_id ) ); |
58. Locate employees without a designated manager.
To find employees without a manager, you can use a LEFT JOIN and filter for NULL values in the manager column:
| SELECT employees.employee_id, employees.employee_name FROM employees LEFT JOIN managers ON employees.manager_id = managers.manager_id WHERE managers.manager_id IS NULL; |
Conclusion
By thoroughly understanding the fundamental concepts and practicing with real-world scenarios, you can confidently navigate SQL interviews. With this comprehensive guide, you’re well-equipped to tackle a wide range of SQL interview questions and make an impactful lasting impression on potential employers.
