1. What is SQL?
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.
Example:
SELECT * FROM Employees;
2. What are the different types of SQL statements?
SQL statements are categorized into:
- DDL (Data Definition Language): CREATE, ALTER, DROP
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language): GRANT, REVOKE
- TCL (Transaction Control Language): COMMIT, ROLLBACK
3. What is the difference between DELETE and TRUNCATE?
DELETE removes rows one by one and can be rolled back.
DELETE FROM Employees WHERE Department = 'HR';
TRUNCATE removes all rows quickly and cannot be rolled back in most RDBMS.
TRUNCATE TABLE Employees;
4. What is a Primary Key?
A Primary Key uniquely identifies each record in a table and cannot have NULL or duplicate values.
Example:
CREATE TABLE Employees ( ID INT PRIMARY KEY, Name VARCHAR(100) );
5. What is a Foreign Key?
A Foreign Key in one table refers to the Primary Key in another table to maintain referential integrity.
Example:
CREATE TABLE Orders ( OrderID INT, EmployeeID INT, FOREIGN KEY (EmployeeID) REFERENCES Employees(ID) );
6. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping.
HAVING filters groups after aggregation.
Example:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
7. What is a JOIN in SQL?
A JOIN combines rows from two or more tables based on a related column.
Example:
SELECT Employees.Name, Orders.OrderID FROM Employees JOIN Orders ON Employees.ID = Orders.EmployeeID;
8. Types of JOINs in SQL?
- INNER JOIN: Returns matching records.
- LEFT JOIN: All records from left table and matched records from right.
- RIGHT JOIN: All records from right table and matched records from left.
- FULL OUTER JOIN: All records when there is a match in one of the tables.
9. What is a Subquery?
A Subquery is a query nested inside another query.
Example:
SELECT Name FROM Employees WHERE ID IN (SELECT EmployeeID FROM Orders);
10. What is a View?
A View is a virtual table based on the result of a SELECT query.
Example:
CREATE VIEW ActiveEmployees AS SELECT * FROM Employees WHERE Status = 'Active';
11. Difference between UNION and UNION ALL?
UNION removes duplicates.
UNION ALL includes duplicates.
Example:
SELECT City FROM Customers UNION SELECT City FROM Suppliers;
12. What is Normalization?
Normalization is the process of organizing data to reduce redundancy. Forms include 1NF, 2NF, 3NF, BCNF.
13. What is Denormalization?
Denormalization is the process of combining tables to improve read performance at the cost of redundancy.
14. What is an Index?
An Index speeds up query execution on a database table by reducing the number of data pages to scan.
Example:
CREATE INDEX idx_name ON Employees(Name);
15. What is a Stored Procedure?
A stored procedure is a precompiled set of SQL statements stored in the database.
Example:
CREATE PROCEDURE GetEmployeeByID (@ID INT) AS BEGIN SELECT * FROM Employees WHERE ID = @ID; END;
16. What is a Trigger?
A trigger automatically executes in response to certain events on a table.
Example:
CREATE TRIGGER trg_after_insert ON Employees AFTER INSERT AS BEGIN PRINT 'Record Inserted'; END;
17. What are Aggregate Functions?
Aggregate functions perform a calculation on a set of values and return a single value.
Examples: COUNT(), SUM(), AVG(), MAX(), MIN() Example:
SELECT AVG(Salary) FROM Employees;
18. What is GROUP BY?
GROUP BY groups rows that have the same values in specified columns.
Example:
SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
19. What is ORDER BY?
ORDER BY sorts the result-set by one or more columns.
Example:
SELECT * FROM Employees ORDER BY Name ASC;
20. What is a NULL value?
NULL represents missing or unknown data.
Example:
SELECT * FROM Employees WHERE ManagerID IS NULL;
21. What is the difference between WHERE and HAVING?
- WHERE filters rows before grouping.
- HAVING filters rows after grouping (used with aggregate functions).
-- Using WHERE SELECT * FROM Employees WHERE Department = 'Sales'; -- Using HAVING SELECT Department, COUNT(*) as Total FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
22. What is a Self Join in SQL?
A self join is a regular join where a table is joined with itself.
Example:
SELECT A.EmployeeName AS Employee, B.EmployeeName AS Manager FROM Employees A JOIN Employees B ON A.ManagerID = B.EmployeeID;
23. What is the difference between CHAR and VARCHAR?
- CHAR(n) is a fixed-length string.
- VARCHAR(n) is a variable-length string.
Example:
CREATE TABLE Test ( FixedName CHAR(10), VarName VARCHAR(10) );
24. What is a Correlated Subquery?
A correlated subquery uses values from the outer query and is executed for each row.
Example:
SELECT e1.Name, e1.Salary FROM Employees e1 WHERE e1.Salary > ( SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID );
25. What is CASE statement in SQL?
CASE is used for conditional logic in SQL queries.
Example:
SELECT Name, Salary, CASE WHEN Salary > 50000 THEN 'High' WHEN Salary BETWEEN 30000 AND 50000 THEN 'Medium' ELSE 'Low' END AS SalaryBand FROM Employees;
26. What is the difference between COUNT(*) and COUNT(column_name)?
- COUNT(*) counts all rows.
- COUNT(column) counts only non-null values in the column.
Example:
SELECT COUNT(*) AS TotalRows, COUNT(Email) AS NonNullEmails
FROM Employees;
27. What are Constraints in SQL?
Rules applied on columns to restrict the type of data that can go into a table.
Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
Example:
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Age INT CHECK (Age >= 18) );
28. What is the IF EXISTS clause used for?
It prevents errors by checking if a table or object exists before dropping it.
Example:
DROP TABLE IF EXISTS TempEmployees;
29. How do you handle NULL values in SQL?
Use functions like IS NULL, IS NOT NULL, and COALESCE.
Example:
SELECT Name, COALESCE(Email, 'Not Provided') AS EmailAddress FROM Employees;
30. What is the BETWEEN operator?
Checks whether a value lies within a range.
Example:
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
31. What is the IN operator?
Allows you to specify multiple values in a WHERE clause.
Example:
SELECT * FROM Products WHERE CategoryID IN (1, 3, 5);
32. What is LIKE operator?
Used for pattern matching.
Example:
SELECT * FROM Customers WHERE Name LIKE 'A%'; -- Names starting with A
33. What is a Composite Key?
A primary key composed of two or more columns.
Example:
CREATE TABLE CourseRegistration ( StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID) );
34. What are Temporary Tables?
Temporary tables exist only during the session and are deleted automatically.
Example:
CREATE TEMPORARY TABLE TempSales ( ProductID INT, Quantity INT );
35. What is a CTE (Common Table Expression)?
A CTE is a temporary result set used within a query.
Example:
WITH HighSalary AS ( SELECT * FROM Employees WHERE Salary > 60000 ) SELECT * FROM HighSalary;
36. What is an Alias in SQL?
A temporary name given to a table or column.
Example:
SELECT FirstName AS Name FROM Employees;
37. What is TRY_CAST() in SQL Server?
Attempts to cast a value to a specified data type and returns NULL if it fails.
Example:
SELECT TRY_CAST('abc' AS INT) AS InvalidConversion; SELECT TRY_CAST('123' AS INT) AS ValidConversion;
38. How can you find duplicate records in a table?
Use GROUP BY and HAVING to find duplicates based on one or more columns.
Example:
SELECT Name, COUNT(*) AS Count FROM Employees GROUP BY Name HAVING COUNT(*) > 1;
39. How to delete duplicate rows in SQL?
Use CTE and ROW_NUMBER() to identify and delete duplicates.
Example:
WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY ID) AS RowNum FROM Employees ) DELETE FROM CTE WHERE RowNum > 1;
40. What is the difference between DATETIME, DATE, and TIME in SQL?
DATETIME: Stores both date and time (e.g., 2025-07-19 14:30:00).
DATE: Stores only date (e.g., 2025-07-19).
TIME: Stores only time (e.g., 14:30:00).
Example:
SELECT CAST(GETDATE() AS DATE) AS OnlyDate, CAST(GETDATE() AS TIME) AS OnlyTime;
41. How do you rename a column in SQL?
Use the sp_rename system stored procedure in SQL Server.
Example:
EXEC sp_rename 'Employees.OldColumnName', 'NewColumnName', 'COLUMN';
42. What is the TOP clause in SQL Server?
Limits the number of rows returned by a query.
Example:
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;
43. What is NTILE() function in SQL?
Divides the result set into a number of roughly equal parts or buckets.
Example:
SELECT Name, Salary, NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile FROM Employees;
44. What is EXCEPT in SQL?
Returns rows from the first query that are not present in the second query. Removes duplicates by default.
Example:
SELECT Email FROM Customers EXCEPT SELECT Email FROM NewsletterSubscribers;
45. What is INTERSECT in SQL?
Returns rows that are common to both queries. Removes duplicates.
Example:
SELECT Email FROM Customers INTERSECT SELECT Email FROM NewsletterSubscribers;
46.How do you retrieve even and odd rows from a table?
Using modulo operator:
-- Even IDs SELECT * FROM Employees WHERE ID % 2 = 0; -- Odd IDs SELECT * FROM Employees WHERE ID % 2 <> 0;
47. What is the use of SET vs SELECT for variable assignment?
SET is ANSI standard and assigns a value to only one variable.
SELECT can assign values to multiple variables at once.
Example:
DECLARE @name VARCHAR(50); SET @name = (SELECT TOP 1 Name FROM Employees); -- or SELECT @name = Name FROM Employees WHERE ID = 101;
48. How do you find the number of columns in a table?
Using system views:
SELECT COUNT(*) AS ColumnCount FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees';
49. What is IDENTITY in SQL Server?
Auto-increments a numeric column.
Example:
CREATE TABLE Orders ( OrderID INT IDENTITY(1,1), CustomerName VARCHAR(100) );
50. What is the difference between ISNULL() and NULLIF()?
ISNULL()
Replaces NULL with a specified value.
NULLIF()
Returns NULL if two expressions are equal, otherwise returns the first expression.
Example:
SELECT ISNULL(NULL, 'Default') AS Result1; -- Returns 'Default' SELECT NULLIF(100, 100) AS Result2; -- Returns NULL
