SQL Interview Questions for 0–1 Year Experience - Interview Questions and Answers

SQL (Structured Query Language) is used to communicate with relational databases to perform tasks such as querying data, updating records, and managing schemas.

• DELETE: Removes specific rows; can be rolled back.
• TRUNCATE: Removes all rows; cannot be rolled back.
• DROP: Deletes the entire table structure.

• WHERE: Filters rows before aggregation.
• HAVING: Filters groups after aggregation.

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.

A subquery is a query nested within another query.

SELECT Name FROM Employees WHERE DepartmentID = (
  SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales'
);

• GROUP BY: Aggregates data based on one or more columns.
• ORDER BY: Sorts the result set based on specified columns.

The process of organizing data to reduce redundancy and improve data integrity.

• Primary Key: Uniquely identifies each record in a table.
• Foreign Key: A field in one table that uniquely identifies a row in another table.

A virtual table based on the result-set of an SQL statement.

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1;

• CHAR: Fixed-length character data.
• VARCHAR: Variable-length character data.

Allows conditional logic in SQL queries.

SELECT Name,
       CASE 
         WHEN Salary > 50000 THEN 'High'
         ELSE 'Low'
       END AS SalaryCategory
FROM Employees;

A database object that improves the speed of data retrieval operations.

• Clustered Index: Sorts and stores data rows in the table based on the key values.
• Non-Clustered Index: Contains pointers to the actual data rows.

A sequence of operations performed as a single logical unit of work.

Atomicity, Consistency, Isolation, Durability.

Rules enforced on data columns. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

SELECT * FROM table_name ORDER BY column_name DESC LIMIT N;

• UNION: Combines results and removes duplicates.
• UNION ALL: Combines results including duplicates.

Using functions like IS NULL, IS NOT NULL, COALESCE(), and IFNULL().

SELECT MAX(Salary) 
FROM Employees 
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

UPDATE table_name 
SET column1 = value1 
WHERE condition;

• IN: Checks if a value exists within a set of values.
• BETWEEN: Checks if a value is within a range.

A subquery that uses values from the outer query.

DELETE FROM Employees
WHERE EmployeeID NOT IN (
  SELECT MIN(EmployeeID)
  FROM Employees
  GROUP BY Name, DepartmentID
);

IS NULL is used to check for NULL values; = NULL is invalid.

Checks if a subquery returns any rows.

A join where a table is joined with itself.

ALTER TABLE old_table_name RENAME TO new_table_name;

Removes duplicate records from the result set.

SELECT DepartmentID, COUNT(*) 
FROM Employees 
GROUP BY DepartmentID;

ALTER TABLE table_name ADD column_name datatype;

Both return the current date and time; NOW() is used in MySQL, GETDATE() in SQL Server.

Used for pattern matching in queries.

A set of instructions that automatically executes in response to certain events on a table.

SELECT CURRENT_DATE;

A prepared SQL code that can be saved and reused.

CREATE TABLE new_table AS 
SELECT * FROM existing_table;

Specifies the number of records to return.

• INNER JOIN: Returns records with matching values in both tables.
• OUTER JOIN: Returns all records from one table and matched records from the other.

A temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Using indexes, optimizing queries, avoiding unnecessary columns in SELECT, and analyzing execution plans.

• RANK(): Skips ranks if there are ties.
• DENSE_RANK(): No gaps between ranks.

Perform calculations across a set of table rows related to the current row.

Returns the first non-NULL value in a list.

Using transactions with TRY...CATCH blocks (in SQL Server) or error-handling mechanisms provided by the DBMS.

• LEFT JOIN: Returns all records from the left table and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table and matched records from the left table.

Indexing improves data retrieval speed. Types include clustered, non-clustered, unique, and full-text indexes.

A primary key composed of multiple columns.

Using constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

• DELETE: Removes specific rows; can be rolled back.
• TRUNCATE: Removes all rows; cannot be rolled back.

Performs insert, update, or delete operations in a single statement.

A cursor is a database object used to retrieve, manipulate, and traverse through rows in a result set one row at a time. Cursors are helpful when performing operations that must be processed sequentially rather than in a set-based manner.

A stored procedure is a precompiled set of SQL statements stored in the database. It can take input parameters, perform logic and queries, and return output values or result sets. Stored procedures improve performance and maintainability by centralizing business logic.

A trigger is a set of SQL statements that automatically execute in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Triggers help maintain data consistency, enforce business rules, and implement complex integrity constraints.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. This involves dividing large tables into smaller, related tables and defining relationships between them to ensure consistency and avoid anomalies.

Denormalization is the process of combining normalized tables into larger tables for performance reasons. It is used when complex queries and joins slow down data retrieval, and the performance benefits outweigh the drawbacks of redundancy.

A view is a virtual table created by a SELECT query. It does not store data itself but presents data from one or more tables in a structured way. Views simplify complex queries, improve readability, and enhance security by restricting access to specific rows or columns.

• CHAR: Fixed-length storage. If the defined length is not fully used, it is padded with spaces.
• VARCHAR2: Variable-length storage. Only the actual data is stored, saving space when the full length is not needed.

The DEFAULT constraint assigns a default value to a column when no value is provided during an INSERT operation. This helps maintain consistent data and simplifies data entry.

SQL commands are broadly classified into:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language): GRANT, REVOKE.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.

A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value in the primary key column(s), and it does not allow NULL values.

A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It establishes and enforces a relationship between the two tables, ensuring data integrity.

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct. This prevents duplicate values and helps maintain data integrity.

A subquery is a query nested within another query. It is often used in the WHERE clause to filter data based on the results of another query, making it easier to handle complex conditions.

• WHERE: Filters rows before any grouping takes place.
• HAVING: Filters grouped data after the GROUP BY clause has been applied.
  In short, WHERE applies to individual rows, while HAVING applies to groups.

NULL represents a missing or unknown value. It is different from zero or an empty string. NULL values indicate that the data is not available or applicable.

A table is a structured collection of related data organized into rows and columns. Columns define the type of data stored, while rows contain individual records.

A query is a SQL statement used to retrieve, update, or manipulate data in a database. The most common type of query is a SELECT statement, which fetches data from one or more tables based on specified conditions.

• Arithmetic Operators: +, -, *, /, %
• Comparison Operators: =, !=, <>, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• Set Operators: UNION, INTERSECT, EXCEPT
• Special Operators: BETWEEN, IN, LIKE, IS NULL

• INNER JOIN: Returns only rows where there is a match in both tables.
• OUTER JOIN: Returns all rows from one table (LEFT, RIGHT, or FULL), and the matching rows from the other table. If there is no match, NULL values are returned for the non-matching side.

• SQL Databases:
  • Use structured tables with rows and columns.
  • Rely on a fixed schema.
  • Offer ACID properties.
• NoSQL Databases:
  • Use flexible, schema-less structures (e.g., key-value pairs, document stores).
  • Are designed for horizontal scaling.
  • Often focus on performance and scalability over strict consistency.

The SELECT statement retrieves data from one or more tables. It is the most commonly used command in SQL, allowing users to filter, sort, and display data based on specific criteria.

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause and helps in writing recursive queries and improving readability.

• RANK(): Assigns a unique rank to each distinct row, but leaves gaps between ranks when there are ties.
• DENSE_RANK(): Assigns a unique rank to each distinct row, without gaps between ranks.

The COALESCE() function returns the first non-NULL value in a list of expressions. It is useful for handling NULL values in queries.

An alias is a temporary name given to a table or column for the purpose of a specific SQL query.

Example:

SELECT first_name AS Name FROM employees;

Wildcards are used with the LIKE operator for pattern matching:

• % represents any number of characters
• _ represents a single character
  Example: WHERE name LIKE 'A%'

• NOW() is used in MySQL to get the current date and time.
• GETDATE() is used in SQL Server for the same purpose.

ACID ensures reliable processing of database transactions:

• Atomicity: All parts of a transaction succeed or none.
• Consistency: Transactions leave the DB in a valid state.
• Isolation: Concurrent transactions don't affect each other.
• Durability: Once a transaction commits, it's saved even in case of system failure.