Showing 50 question(s)
Answer:
UNION removes duplicate rows from the result set, while UNION ALL includes all rows even if they are duplicates. UNION is slower because it has to check for duplicates, while UNION ALL is faster. Both require the same number of columns with compatible data types.
Code Example:
-- UNION (removes duplicates)
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;
-- UNION ALL (includes duplicates)
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Contractors;Answer:
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from left table and matching rows from right table. RIGHT JOIN returns all rows from right table and matching rows from left table. FULL OUTER JOIN returns all rows from both tables. CROSS JOIN returns Cartesian product of both tables.
Code Example:
-- INNER JOIN
SELECT * FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID;
-- LEFT JOIN
SELECT * FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.ID;
-- FULL OUTER JOIN
SELECT * FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptID = d.ID;Answer:
Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include: COUNT() - counts rows, SUM() - sums values, AVG() - calculates average, MAX() - finds maximum value, MIN() - finds minimum value, GROUP_CONCAT() - concatenates values.
Code Example:
-- Aggregate Functions
SELECT
COUNT(*) as TotalEmployees,
AVG(Salary) as AvgSalary,
MAX(Salary) as MaxSalary,
MIN(Salary) as MinSalary,
SUM(Salary) as TotalSalary
FROM Employees;Answer:
A subquery is a query nested inside another query. It can be used in SELECT, FROM, WHERE, or HAVING clauses. Subqueries can return a single value, a single column with multiple values, or multiple columns and rows. They can be correlated (referencing outer query) or non-correlated (independent).
Code Example:
-- Subquery in WHERE clause
SELECT Name FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
-- Correlated Subquery
SELECT Name FROM Employees e
WHERE EXISTS (
SELECT 1 FROM Projects p
WHERE p.EmployeeID = e.ID
);Answer:
An index is a database structure that improves query performance by enabling faster data retrieval. Indexes work like a book's index, allowing the database to find data without scanning the entire table. However, indexes slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.
Code Example:
-- Create an index
CREATE INDEX idx_email ON Users(Email);
-- Create composite index
CREATE INDEX idx_name ON Employees(FirstName, LastName);
-- Create unique index
CREATE UNIQUE INDEX idx_ssn ON Employees(SSN);
-- Drop index
DROP INDEX idx_email;Answer:
1NF: Eliminate duplicate columns from the same table. 2NF: Remove subsets of data that apply to multiple rows and place them in separate tables. 3NF: Remove columns that don't depend on the primary key. BCNF: Every determinant must be a candidate key. Higher normal forms reduce redundancy and improve data integrity.
Code Example:
-- Example of 1NF violation (multiple values in one cell)
-- BAD: Student(ID, Name, Subjects)
-- ID: 1, Name: John, Subjects: Math, English
-- GOOD: 1NF compliant
-- Student(ID, Name)
-- StudentSubjects(StudentID, Subject)Answer:
Window functions perform calculations across a set of table rows that are related to the current row. They include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), etc. Window functions don't cause rows to become grouped into a single output row unlike aggregate functions.
Code Example:
-- Window Functions
SELECT
Name,
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) as RankNum,
LAG(Salary) OVER (ORDER BY HireDate) as PrevSalary,
LEAD(Salary) OVER (ORDER BY HireDate) as NextSalary
FROM Employees;Answer:
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity: Transaction completes entirely or not at all. Consistency: Database remains valid before and after transaction. Isolation: Concurrent transactions don't interfere with each other. Durability: Committed data persists even after system failure.
Code Example:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
IF @@ERROR <> 0
ROLLBACK;
ELSE
COMMIT;Answer:
A stored procedure is a set of SQL statements that can be stored in the database and executed repeatedly. Advantages include: reduced network traffic, improved security by hiding logic, better performance through pre-compilation, code reusability, and easier maintenance. They can accept parameters and return values.
Code Example:
CREATE PROCEDURE GetEmployeesByDept
@DeptID INT
AS
BEGIN
SELECT Name, Salary FROM Employees
WHERE DepartmentID = @DeptID
END;
-- Execute
EXEC GetEmployeesByDept @DeptID = 5;Answer:
Strategies include: using indexes appropriately, avoiding SELECT *, using EXPLAIN to understand query execution, normalizing tables, using JOINs instead of subqueries when possible, reducing unnecessary WHERE conditions, batching operations, using LIMIT, avoiding functions on indexed columns, and updating statistics regularly.
Code Example:
-- Bad Query
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;
-- Better Query
SELECT OrderID, CustomerID FROM Orders
WHERE OrderDate >= '2023-01-01'
AND OrderDate < '2024-01-01';
-- Use EXPLAIN to analyze
EXPLAIN SELECT * FROM Orders WHERE CustomerID = 5;Answer:
A PRIMARY KEY uniquely identifies each row and does not allow NULL values. A table can have only one PRIMARY KEY. A UNIQUE KEY also enforces uniqueness but allows one NULL (SQL Server) or multiple NULLs depending on the database.
Code Example:
CREATE TABLE Employee(
Id INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);Answer:
A FOREIGN KEY creates a relationship between two tables and ensures referential integrity.
Code Example:
CREATE TABLE Orders(
OrderId INT PRIMARY KEY,
CustomerId INT,
FOREIGN KEY(CustomerId) REFERENCES Customers(CustomerId)
);Answer:
DDL commands define database structures. Common commands are CREATE, ALTER, DROP, TRUNCATE and RENAME.
Code Example:
CREATE TABLE Employees(
Id INT,
Name VARCHAR(50)
);Answer:
DML commands manipulate data in tables. They include INSERT, UPDATE, DELETE and MERGE.
Code Example:
INSERT INTO Employees VALUES(1,'John');
UPDATE Employees SET Name='David' WHERE Id=1;
DELETE FROM Employees WHERE Id=1;Answer:
DELETE removes rows one by one and can be rolled back. TRUNCATE removes all rows quickly, resets identity in many databases and cannot always be rolled back.
Code Example:
DELETE FROM Employees WHERE Id=1;
TRUNCATE TABLE Employees;Answer:
DROP removes the table structure and data completely. TRUNCATE only removes the data but keeps the table.
Code Example:
TRUNCATE TABLE Employees;
DROP TABLE Employees;Answer:
A View is a virtual table created using a SQL query. It does not store data itself but displays data from one or more tables.
Code Example:
CREATE VIEW EmployeeDetails AS
SELECT Name, Salary
FROM Employees;Answer:
Views improve security, simplify complex queries, hide implementation details and promote code reuse.
Code Example:
SELECT * FROM EmployeeDetails;Answer:
WHERE filters rows before grouping while HAVING filters grouped results after GROUP BY.
Code Example:
SELECT DepartmentId, COUNT(*)
FROM Employees
WHERE Salary > 30000
GROUP BY DepartmentId
HAVING COUNT(*) > 5;Answer:
COALESCE() returns the first non-NULL value from a list of expressions.
Code Example:
SELECT COALESCE(NULL,NULL,'Default');Answer:
ISNULL() accepts two parameters and is SQL Server specific. COALESCE() accepts multiple parameters and follows the SQL standard.
Code Example:
SELECT ISNULL(NULL,'A');
SELECT COALESCE(NULL,NULL,'B');Answer:
GROUP BY groups rows with the same values so aggregate functions can be applied.
Code Example:
SELECT DepartmentId,
COUNT(*)
FROM Employees
GROUP BY DepartmentId;Answer:
ORDER BY sorts the result set in ascending or descending order.
Code Example:
SELECT *
FROM Employees
ORDER BY Salary DESC;Answer:
Use TOP in SQL Server or LIMIT in MySQL/PostgreSQL.
Code Example:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;Answer:
A CTE is a temporary named result set that improves readability and supports recursive queries.
Code Example:
WITH Emp AS (
SELECT * FROM Employees
)
SELECT * FROM Emp;Answer:
A Recursive CTE repeatedly references itself until a termination condition is met.
Code Example:
WITH Numbers AS(
SELECT 1 AS Num
UNION ALL
SELECT Num+1 FROM Numbers WHERE Num<5
)
SELECT * FROM Numbers;Answer:
RANK() skips ranking numbers after ties whereas DENSE_RANK() does not.
Code Example:
SELECT Name,Salary,
RANK() OVER(ORDER BY Salary DESC),
DENSE_RANK() OVER(ORDER BY Salary DESC)
FROM Employees;Answer:
Use IS NULL or IS NOT NULL because NULL cannot be compared using =.
Code Example:
SELECT *
FROM Employees
WHERE ManagerId IS NULL;Answer:
EXISTS returns TRUE if the subquery returns one or more rows.
Code Example:
SELECT *
FROM Customers c
WHERE EXISTS(
SELECT 1
FROM Orders o
WHERE o.CustomerId=c.CustomerId
);Answer:
IN compares values while EXISTS checks for row existence. EXISTS is generally better for large datasets.
Code Example:
SELECT *
FROM Employees
WHERE DepartmentId IN
(SELECT DepartmentId FROM Departments);Answer:
An execution plan shows how the database engine executes a query and helps identify performance bottlenecks.
Code Example:
EXPLAIN
SELECT *
FROM Employees
WHERE Salary > 50000;Answer:
A clustered index stores table data physically in index order. A table can have only one clustered index.
Code Example:
CREATE CLUSTERED INDEX idx_emp
ON Employees(Id);Answer:
A non-clustered index stores pointers to data rows separately from the actual table.
Code Example:
CREATE NONCLUSTERED INDEX idx_name
ON Employees(Name);Answer:
Use OFFSET FETCH in SQL Server or LIMIT OFFSET in MySQL/PostgreSQL.
Code Example:
SELECT *
FROM Employees
ORDER BY Id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;Answer:
You can use DENSE_RANK(), ROW_NUMBER() or a subquery.
Code Example:
SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(SELECT MAX(Salary)
FROM Employees);Answer:
Use GROUP BY with HAVING COUNT(*) > 1 to identify duplicate values.
Code Example:
SELECT Email, COUNT(*)
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;Answer:
Use ROW_NUMBER() to assign row numbers and delete rows with RowNum greater than 1.
Code Example:
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Email ORDER BY Id) AS RowNum
FROM Employees
)
DELETE FROM CTE
WHERE RowNum > 1;Answer:
Common string functions include UPPER(), LOWER(), LEN(), SUBSTRING(), CONCAT(), TRIM(), REPLACE(), and LEFT()/RIGHT().
Code Example:
SELECT
UPPER(Name),
LOWER(Name),
LEN(Name),
SUBSTRING(Name,1,3)
FROM Employees;Answer:
Frequently used date functions include GETDATE(), CURRENT_DATE, DATEADD(), DATEDIFF(), YEAR(), MONTH(), and DAY().
Code Example:
SELECT
GETDATE() AS Today,
YEAR(GETDATE()) AS CurrentYear,
MONTH(GETDATE()) AS CurrentMonth;Answer:
CASE is used to implement conditional logic within SQL queries.
Code Example:
SELECT Name,
CASE
WHEN Salary >= 70000 THEN 'High'
WHEN Salary >= 40000 THEN 'Medium'
ELSE 'Low'
END AS SalaryGrade
FROM Employees;Answer:
Use DENSE_RANK() or a nested subquery to retrieve the third highest salary.
Code Example:
SELECT Salary
FROM (
SELECT Salary,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS RankNo
FROM Employees
) t
WHERE RankNo = 3;Answer:
A Self JOIN joins a table with itself and is useful for hierarchical relationships such as employees and managers.
Code Example:
SELECT e.Name AS Employee,
m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerId = m.Id;Answer:
INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the right table.
Code Example:
SELECT *
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentId = d.Id;Answer:
A transaction is a sequence of SQL operations treated as a single unit of work that either fully succeeds or fails.
Code Example:
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 100
WHERE Id = 1;
COMMIT;Answer:
SELECT * retrieves unnecessary columns, increases network traffic, reduces performance and makes applications harder to maintain.
Code Example:
-- Avoid
SELECT *
-- Preferred
SELECT Id, Name, Salary
FROM Employees;Answer:
A CHECK constraint restricts the values that can be inserted into a column.
Code Example:
CREATE TABLE Employees(
Id INT PRIMARY KEY,
Age INT CHECK(Age >= 18)
);Answer:
A DEFAULT constraint automatically inserts a predefined value when no value is supplied.
Code Example:
CREATE TABLE Employees(
Id INT,
Country VARCHAR(50) DEFAULT 'India'
);Answer:
Common reasons include missing indexes, SELECT *, large table scans, poor joins, unnecessary subqueries, outdated statistics and functions on indexed columns.
Code Example:
EXPLAIN
SELECT *
FROM Orders
WHERE CustomerId = 100;Answer:
Denormalization intentionally adds redundancy to improve read performance by reducing joins.
Code Example:
-- Example
Orders
---------
OrderId
CustomerName
CustomerCityAnswer:
Sort the table in descending order by a unique column and limit the results.
Code Example:
SELECT TOP 10 *
FROM Employees
ORDER BY Id DESC;