SQL Interview Questions

SQL (Structured Query Language) is a specialized language designed for handling and modifying relational databases. It allows users to perform tasks such as querying data, inserting, updating, and deleting records, as well as creating and modifying database structures.

• WHERE is used to filter rows before any grouping is done.
• HAVING is used to filter groups after a GROUP BY operation.

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

The main disadvantage of deleting data from an existing table using the DROP TABLE command is that it permanently removes the entire table and all associated data. Once the table is dropped, it cannot be easily recovered. This can lead to loss of valuable or critical information if a backup is not available. Additionally, if there are dependencies on the table (e.g., foreign key relationships or applications relying on the data), removing the table can disrupt workflows and lead to errors or downtime.

A primary key is a column or a combination of columns that uniquely identifies a row in a table.It must include distinct and non-empty values.

A table cannot have more than one primary key, but a primary key can consist of multiple columns (composite key).

Database management systems can be classified into various types. Below are some of the key categories:

• Hierarchical databases (DBMS)
• Network databases (IDMS)
• Relational databases (RDBMS)
• Object-oriented databases
• Document databases (Document DB)
• Graph databases
• ER model databases
• NoSQL databases

Using a subquery:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

• UNION: Combines the results of two queries and removes duplicate rows.
• UNION ALL: Combines the results of two queries but includes duplicate rows.

SELECT name FROM employees
UNION
SELECT name FROM managers;

Indexes are database objects used to improve the speed of data retrieval operations on a table. They work like pointers to the data. However, they can slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.

CREATE INDEX idx_employee_name ON employees(name);

The CASE statement enables conditional logic in SQL queries.

SELECT name,
       salary,
       CASE
           WHEN salary > 80000 THEN 'High'
           WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

The RANK(), DENSE_RANK(), and ROW_NUMBER() functions in SQL are window functions used to assign a unique rank or number to rows within a partitioned result set, based on a specified order. While they appear similar, their behavior differs in how they handle ranking in the presence of duplicate values.

Use Cases

• RANK() is useful when you want to reflect true positions with gaps for tied values, such as determining the top 3 players in a competition where ties are allowed.
• DENSE_RANK() is preferred when you require continuous ranks, such as generating grouped reports without skipping ranks.
• ROW_NUMBER() is ideal when you need a unique identifier for each row in a result set or for pagination purposes.

Use the CREATE TABLE ... AS or SELECT INTO statement with a WHERE clause that evaluates to false:

CREATE TABLE NewTable AS SELECT * FROM OldTable WHERE 1 = 0;

• %: Matches zero or more characters.
• _: Matches exactly one character. Example:

SELECT * FROM studentData WHERE Name LIKE 'B%';

This query retrieves all students names starting with 'B'.

SQL provides a built-in function named GetDate() that retrieves the current timestamp.

A recursive stored procedure is a stored procedure that calls itself until a specific condition is met.

CREATE PROCEDURE CalculateFactorial (@Number  INT, @Result  INT OUTPUT)
AS
BEGIN
    IF @Number  > 1
    BEGIN
        SET @Result  = @Result  * @Number;
        EXEC CalculateFactorial @Number  - 1, @Result  OUTPUT;
    END
END;

Collation defines the rules for sorting and comparing data within a databas It includes rules for character case sensitivity, accent sensitivity, kana sensitivity, and width sensitivity. Types of Collation Sensitivity:

• Case Sensitivity: Differentiates between uppercase and lowercase letters (e.g., 'A' ≠ 'a').
• Accent Sensitivity: Differentiates between accented and non-accented characters (e.g., "e" ≠ "é").
• Kana Sensitivity: Differentiates between Japanese Hiragana and Katakana characters.
• Width Sensitivity: Differentiates between single-byte and double-byte characters (e.g., 'a' ≠ 'ａ').

• UNION: Combines results from two SELECT queries, removing duplicates.
• MINUS: Returns rows from the first SELECT query that are not present in the second.
• INTERSECT: Returns rows that are present in both SELECT queries.

• DELETE: Removes rows from a table based on a condition. It can be rolled back using transactions.
• TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster but cannot be rolled back.

• Table: A collection of data organized in rows and columns in a relational database.
• Field (Column): Represents an individual attribute of the data, containing a single unit of information within a table.

• Entities: Objects or things in a database that can be distinctly identified (e.g., Student, Employee).
• Relationships: The association between entities (e.g., "Student enrolls in Course").