Eric Duan

Basic SQL Interview Questions and Answers

1. What is SQL?

   • SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It allows users to query, insert, update, and delete data, as well as create and modify schema objects like tables, indexes, and views.

2. What are the different types of SQL commands?

   • SQL commands are categorized into four types:
     • DDL (Data Definition Language): Commands that define the database structure. Examples include CREATE, ALTER, DROP.
     • DML (Data Manipulation Language): Commands used for manipulating data. Examples are INSERT, UPDATE, DELETE.
     • DCL (Data Control Language): Commands related to permissions and access controls. Examples are GRANT, REVOKE.
     • TCL (Transaction Control Language): Commands that manage transactions in the database. Examples include COMMIT, ROLLBACK.

3. What is a JOIN and what are the different types?

   • A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Different types of JOINs include:
     • INNER JOIN: Returns rows when there is at least one match in both tables.
     • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.
     • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.
     • FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.

Intermediate SQL Interview Questions and Answers

1. Explain the GROUP BY clause with an example.

   • The GROUP BY clause is used with aggregate functions like COUNT, MAX, MIN, SUM, AVG to group the result set by one or more columns. For example, to count the number of employees in each department:

     SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
     FROM Employees
     GROUP BY DepartmentID;
     

2. What are indexes and why are they used?

   • Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index in a database is akin to an index in a book. They are used to quickly locate and access the data without having to search every row in a database table every time a database table is accessed.

3. How do you optimize SQL queries?

   • Query optimization can involve several strategies, such as selecting only the necessary columns instead of using SELECT *, using joins instead of subqueries where applicable, indexing critical columns, avoiding unnecessary calculations, and analyzing query execution plans to identify bottlenecks.

Advanced SQL Interview Questions and Answers

1. Discuss window functions and provide examples where they might be used.

   • Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not collapse the rows into a single output row. Examples include ROW_NUMBER(), RANK(), LEAD(), LAG(), and they can be used for tasks such as ranking, running totals, or accessing the value of another row at a specified offset.

2. What are Common Table Expressions (CTEs) and how do they differ from temporary tables?

   • A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are non-persistent and exist only during the execution of the query. Unlike temporary tables, CTEs are not stored on disk and do not incur the overhead associated with creating and managing temporary tables. They are useful for creating recursive queries, and simplifying complex queries.

3. How do you secure data in SQL databases?

   • Securing data in SQL databases involves implementing measures like encryption for data at rest and in transit, using secure connections (like SSL/TLS), managing user access controls diligently (principle of least privilege), regularly updating and patching the database software, and monitoring for unusual access patterns or security breaches.

Practical SQL Task Example

• Given a table sales (product_id INT, sale_date DATE, quantity INT), write a query to find the top-selling product for each month.

  WITH MonthlySales AS (
    SELECT
      product_id,
      EXTRACT(YEAR FROM sale_date) AS sale_year,
      EXTRACT(MONTH FROM sale_date) AS sale_month,
      SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY product_id, sale_year, sale_month
  ),
  RankedSales AS (
    SELECT