SQL and Database Interview Questions and Answers

SQL and Database Interview Questions and Answers

1. What is normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

2. Difference between TRUNCATE and DELETE

TRUNCATE removes all rows instantly without logging individual row deletions and cannot be rolled back (DDL). DELETE removes rows one by one and can be rolled back (DML).

3. Explain DDL and DML

DDL (Data Definition Language) defines schema (CREATE, ALTER). DML (Data Manipulation Language) manipulates data (SELECT, INSERT, UPDATE, DELETE).

4. What is an index?

An index improves the speed of data retrieval operations on a table.

5. Explain joins

Joins combine rows from two or more tables based on a related column. Types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

6. Definition of SQL, DDL, JOIN

SQL is Structured Query Language. DDL defines schema structure (e.g., CREATE). JOIN combines rows from two tables.

7. What is a primary key and its advantages?

A primary key uniquely identifies each record in a table and ensures entity integrity, preventing NULLs and duplicates.

8. Write a query for employee-manager hierarchy

SELECT m.empname AS Manager, e.empname AS Employee
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

9. How to sum two values without using functions?

SELECT col1 + col2 AS total FROM table_name;

10. What are constraints in SQL? Why are they used?

Constraints enforce rules on data columns: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

11. Fetch duplicate records from a table

SELECT column1, COUNT(*) FROM table GROUP BY column1 HAVING COUNT(*) > 1;

12. Write SQL to get the third highest salary

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 2;

13. Get manager name using self join

SELECT e.name AS Employee, m.name AS Manager 
FROM employees e 
JOIN employees m ON e.manager_id = m.emp_id;

14. What is a view?

A view is a virtual table based on a SQL query. It does not store data itself.

15. What is a materialized view? Difference from a normal view?

A materialized view stores the query result physically and is refreshed periodically, unlike a regular view which fetches data dynamically.

16. Can we update a view?

Yes, if the view is based on a single table without aggregations or GROUP BY.

17. Can we use DELETE without WHERE clause?

Yes, but it will delete all rows from the table. Use cautiously.

18. Reasons for SQL procedure delay?

Could include locks, missing indexes, large data volumes, suboptimal query plans, outdated statistics.

19. What are triggers?

Triggers are automatic actions fired in response to INSERT, UPDATE, or DELETE events on a table.

20. How to establish DB connection?

Use a connection string with hostname, port, database name, username, password, and driver details (JDBC/ODBC).

21. What is EXPLAIN PLAN?

EXPLAIN PLAN shows the execution path a SQL query will follow, helping to optimize performance.

22. Difference between DELETE, DROP, and TRUNCATE

DELETE removes specific rows, DROP removes the table itself, TRUNCATE removes all rows quickly without logging each removal.

23. Use of views?

Views simplify query complexity, enhance security, and help in modular database design.

24. Types of joins in Oracle?

Oracle supports INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and old-style (+) joins.

25. Write query where empid = managerid

SELECT empname FROM employee WHERE empid = managerid;

26. What are JDBC parameters?

Include database URL, username, password, driver class, and optional settings like timeouts and SSL properties.

27. What is SQLException?

SQLException is thrown when there is an issue accessing the database, like syntax error, connection failure, constraint violation, etc.