SQL Interview Questions: A Beginner’s Guide

SQL Interview Questions: A Beginner’s Guide

Landing a job involving data often requires a solid grasp of SQL. This beginner’s guide outlines common SQL interview questions, categorized by topic, providing example questions and explanations to help you prepare.

I. Basic Concepts:

• What is SQL? Structured Query Language (SQL) is a specialized programming language designed for managing and manipulating data in relational database management systems (RDBMS).

• What are the different types of SQL statements? Broadly, SQL statements fall into these categories:

  • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE – Used to define, modify, or delete database objects.
  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE – Used to retrieve, add, modify, or remove data from tables.
  • DCL (Data Control Language): GRANT, REVOKE – Used to manage user access and permissions.
  • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT – Used to manage transactions and ensure data integrity.

• What is a relational database? A relational database organizes data into interconnected tables with defined relationships between them, enforced by primary and foreign keys.

II. SELECT Statements:

• Write a query to select all columns from the ’employees’ table.
  sql
SELECT * FROM employees;

• Select the ‘name’ and ‘salary’ columns from the ’employees’ table where the salary is greater than $50,000.
  sql
SELECT name, salary FROM employees WHERE salary > 50000;

• Retrieve the unique department names from the ’employees’ table.
  sql
SELECT DISTINCT department FROM employees;

• What is the difference between WHERE and HAVING? WHERE filters rows before aggregation, while HAVING filters groups after aggregation (used with GROUP BY).

• Explain the use of LIKE and wildcard characters. LIKE is used for pattern matching. % matches any sequence of characters, _ matches any single character. Example: SELECT * FROM products WHERE name LIKE 'Apple%';

III. Joins:

• What are the different types of joins?

  • INNER JOIN: Returns rows only when there is a match in both tables.
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If no match, right table columns are NULL.
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table. If no match, left table columns are NULL.
  • FULL OUTER JOIN: Returns all rows from both tables. If no match on one side, the other side’s columns are NULL.

• Write a query to join the ’employees’ and ‘departments’ tables on the ‘department_id’ column.
  sql
SELECT *
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

IV. Aggregate Functions:

• What are some common aggregate functions? COUNT, SUM, AVG, MIN, MAX.

• Calculate the average salary of all employees.
  sql
SELECT AVG(salary) FROM employees;

• Count the number of employees in each department.
  sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

V. Subqueries:

• What is a subquery? A query nested inside another query.

• Find the names of employees who earn more than the average salary.
  sql
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

VI. Indexes:

• What is an index? A data structure that improves the speed of data retrieval operations on a database table.

• What are the advantages and disadvantages of using indexes? Advantages: faster queries. Disadvantages: slower inserts, updates, and deletes; increased storage space.

This guide provides a foundational understanding of SQL concepts frequently tested in interviews. Remember to practice writing queries and exploring different scenarios to solidify your understanding and boost your confidence. Good luck!