Sql Interview Question and Answer
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to interact with databases, including inserting, updating, deleting, and retrieving data.
SQL commands are classified into the following types:
- DDL (Data Definition Language): Defines the structure of the database. Examples: CREATE, ALTER, DROP.
- DML (Data Manipulation Language): Manipulates data in the database. Examples: SELECT, INSERT, UPDATE, DELETE.
- DCL (Data Control Language): Defines access control. Examples: GRANT, REVOKE.
- TCL (Transaction Control Language): Manages transaction controls. Examples: COMMIT, ROLLBACK.
A primary key is a column or a set of columns that uniquely identifies each row in a table. It cannot have NULL values and ensures that no two rows have the same value for that column.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100)
);
A foreign key is a column or a set of columns in one table that links to the primary key of another table. It enforces referential integrity between the two tables.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
The WHERE clause filters rows before grouping, while the HAVING clause filters rows after grouping.
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
These joins are used to combine rows from two or more tables based on a related column. The difference is in how unmatched rows are treated:
- INNER JOIN: Returns only matching rows.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
- FULL JOIN: Returns all rows when there is a match in either table.
Normalization is the process of organizing a database to reduce redundancy and dependency. There are several normal forms:
- 1st Normal Form (1NF): Ensures that each column contains atomic values, and there are no repeating groups.
- 2nd Normal Form (2NF): Achieves 1NF and removes partial dependency (no non-prime attribute depends on a part of a candidate key).
- 3rd Normal Form (3NF): Achieves 2NF and removes transitive dependency (non-prime attributes depend only on candidate keys).
- Boyce-Codd Normal Form (BCNF): Strengthens 3NF by removing any dependencies between attributes that are not candidate keys.
Denormalization is the process of intentionally introducing redundancy into a database to improve read performance. It involves merging tables or adding redundant data to reduce the complexity of certain queries.
Indexes are used to speed up the retrieval of rows from a table by providing a quick lookup of data based on one or more columns. They improve query performance but may slow down INSERT, UPDATE, and DELETE operations.
CREATE INDEX idx_EmployeeName
ON Employees (Name);
A view is a virtual table that contains the result of a query. It does not store data but provides a way to simplify complex queries, manage data security, and present data in a specific format.
CREATE VIEW EmployeeView AS
SELECT Name, Department FROM Employees WHERE Salary > 50000;
A subquery is a query inside another query. It can be used to return values to be used in the main query. Subqueries can be placed in SELECT, WHERE, or FROM clauses.
SELECT Name, Salary
FROM Employees
WHERE DepartmentID = (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'IT'
);
Aggregate functions perform calculations on multiple rows of data to return a single value. Common aggregate functions include:
- COUNT(): Counts the number of rows.
- SUM(): Adds up numeric values.
- AVG(): Calculates the average of numeric values.
- MIN() and MAX(): Return the minimum and maximum values.
A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. They help in reducing redundancy and improving performance.
CREATE PROCEDURE GetEmployeeDetails
AS
SELECT Name, Department FROM Employees;
GO
EXEC GetEmployeeDetails;
The main differences are:
- Function: Returns a value and can be used in SELECT statements.
- Stored Procedure: May not return a value and is executed using the EXEC keyword.
A trigger is a special type of stored procedure that automatically executes when a specific event occurs on a table (INSERT, UPDATE, DELETE).
CREATE TRIGGER EmployeeSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
IF OLD.Salary != NEW.Salary THEN
INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary)
VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary);
END IF;
END;
The difference between DELETE and TRUNCATE is:
- DELETE: Deletes rows one by one and can be rolled back.
- TRUNCATE: Removes all rows from a table and cannot be rolled back (no log entries for individual rows).
An alias is a temporary name for a table or column, used to make queries easier to read. Aliases are often used in SELECT queries.
SELECT Name AS EmployeeName, Department AS Dept
FROM Employees;
The difference between UNION and UNION ALL is:
- UNION: Combines results from two queries, eliminating duplicates.
- UNION ALL: Combines results from two queries, including duplicates.
The GROUP BY clause is used to group rows that have the same values into summary rows, like finding the number of employees in each department.
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;
CHAR is a fixed-length string, while VARCHAR is a variable-length string. CHAR takes up the same amount of space regardless of the length of the string, whereas VARCHAR uses only as much space as needed.
The difference between IN and EXISTS is:
- IN: Checks if a value is present in a list of values.
- EXISTS: Checks if a subquery returns any results. It's often used when checking for the existence of rows in a subquery.
A composite key is a combination of two or more columns in a table that can be used to uniquely identify a row in the table. It is used when no single column can uniquely identify a row.
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
PRIMARY KEY (OrderID, ProductID)
);
The DISTINCT keyword is used in a SELECT query to remove duplicate values from the result set and return only unique values.
SELECT DISTINCT Department
FROM Employees;
The difference between LEFT JOIN and RIGHT JOIN is:
- LEFT JOIN: Returns all records from the left table, and the matching records from the right table. If no match, NULL values are returned from the right table.
- RIGHT JOIN: Returns all records from the right table, and the matching records from the left table. If no match, NULL values are returned from the left table.
The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters such as % (for zero or more characters) and _ (for a single character).
SELECT Name
FROM Employees
WHERE Name LIKE 'A%';
The BETWEEN operator is used to filter the result set within a specific range of values, including the boundary values.
SELECT Name, Salary
FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;
The HAVING clause is used to filter groups based on aggregate functions in a GROUP BY query. It works similarly to the WHERE clause but is used after grouping.
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
The difference between UNION and JOIN is:
- UNION: Combines the results of two or more SELECT queries into a single result set and removes duplicates.
- JOIN: Combines rows from two or more tables based on a related column and returns matching rows.
A self-join is a regular join, but the table is joined with itself. It is useful when you have hierarchical data or need to compare rows in the same table.
SELECT A.Name AS Employee, B.Name AS Manager
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;
A materialized view is a precomputed result of a query stored as a physical object. It improves performance by storing the result, allowing quick access without recalculating the data each time.
The COALESCE function returns the first non-NULL value in a list of arguments. It's useful for handling NULL values in your queries.
SELECT COALESCE(NULL, NULL, 'Hello', 'World');
-- Returns 'Hello'
An index is a database object that improves the speed of data retrieval operations on a table. Indexes are particularly useful for large tables, helping to speed up SELECT queries.
CREATE INDEX idx_name ON Employees (Name);
There are four main types of joins in SQL:
- INNER JOIN: Returns only the rows with matching values in both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
- FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.
The difference between TRUNCATE and DELETE is:
- TRUNCATE: Removes all rows from a table and cannot be rolled back. It does not log individual row deletions.
- DELETE: Removes rows one by one and can be rolled back. It is slower compared to TRUNCATE.
A foreign key is a column or a set of columns in one table that references the primary key of another table. It ensures referential integrity by only allowing valid references between tables.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
The RANK function assigns a unique rank to each row within a partition of a result set, with ties receiving the same rank and leaving gaps between ranks.
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
The CASE statement allows you to perform conditional logic in SQL queries. It can be used to return different values based on conditions.
SELECT Name,
CASE
WHEN Salary > 50000 THEN 'High'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;
The difference between UNION and INTERSECT is:
- UNION: Combines the results of two queries and removes duplicates.
- INTERSECT: Returns only the rows that appear in both queries, eliminating duplicates.
The ROW_NUMBER function assigns a unique number to each row within a result set based on a specified order.
SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
The difference between VARCHAR and TEXT is:
- VARCHAR: Stores variable-length strings and allows you to specify a maximum length.
- TEXT: Used for storing large amounts of text. It has no length limit, unlike VARCHAR.
A trigger is a special type of stored procedure that is automatically executed when an event such as INSERT, UPDATE, or DELETE occurs on a table.
CREATE TRIGGER EmployeeAfterInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLog (Action, EmployeeID)
VALUES ('Inserted', NEW.EmployeeID);
END;
A stored procedure is a precompiled collection of one or more SQL statements stored in the database, which can be executed to perform a specific task.
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT Name, Department
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;
The difference between a stored procedure and a function is:
- Stored Procedure: It performs an action (e.g., modifying data) and doesn't return a value, although it can return output parameters.
- Function: It performs a calculation and returns a value.
A subquery is a query nested inside another query. It can be used in the SELECT, WHERE, or FROM clauses and is typically used to retrieve values to be used by the outer query.
SELECT Name, Salary
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');
A window function performs a calculation across a set of table rows that are related to the current row. It is often used to perform operations like ranking and moving averages.
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
The difference between UNION and UNION ALL is:
- UNION: Combines the results of two queries and removes duplicate rows.
- UNION ALL: Combines the results of two queries and does not remove duplicates.
The different types of indexes in SQL include:
- Unique Index: Ensures that all values in a column are unique.
- Composite Index: An index that includes more than one column.
- Full-text Index: Used for full-text searches.
The GROUP BY clause is used to group rows that have the same values in specified columns. It is commonly used with aggregate functions like COUNT, SUM, AVG, etc., to perform calculations on each group.
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
The difference between CHAR and VARCHAR is:
- CHAR: A fixed-length string. If the string is shorter than the defined length, it is padded with spaces.
- VARCHAR: A variable-length string. It only uses as much storage as needed, up to the maximum length.
The ISNULL function is used to replace NULL values with a specified value. It helps handle NULL values in queries and allows for better data consistency.
SELECT Name, ISNULL(Salary, 0) AS Salary
FROM Employees;
0 Comments