Interview Questions and Answers

Ques 1. What is SQL?

SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases.

Ques 2. What is the difference between SQL and MySQL?

SQL is a language, whereas MySQL is a relational database management system that uses SQL as its query language.

Ques 3. What is a primary key?

A primary key is a unique identifier for a record in a table. It must contain unique values and cannot be NULL.

Ques 4. What is an index in a database?

An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

Ques 5. What is a foreign key?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table, establishing a link between the two tables.

Ques 6. Write a SQL query to count the number of rows in a table.

SELECT COUNT(*) FROM table;

Ques 7. What is a view in a database?

A view is a virtual table based on the result of a SELECT query, and it does not store the data itself.

Example:

CREATE VIEW view_name AS SELECT column1, column2 FROM table WHERE condition;

Ques 8. Write a SQL query to update multiple rows in a table.

UPDATE table SET column1 = value1, column2 = value2 WHERE condition;

Ques 9. Explain the purpose of the ORDER BY clause.

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns.

Example:

SELECT column1, column2 FROM table ORDER BY column1 DESC;

Ques 10. Write a SQL query to find the most recent record in a table.

SELECT * FROM table ORDER BY timestamp_column DESC LIMIT 1;

Ques 11. Explain the difference between CHAR and VARCHAR data types.

CHAR is a fixed-length string data type, while VARCHAR is a variable-length string data type. CHAR pads spaces to the maximum length, while VARCHAR only stores the actual data without padding.

Ques 12. What is the purpose of the SQL DISTINCT keyword?

The DISTINCT keyword is used to return unique values in the result set of a SELECT query by eliminating duplicate rows.

Example:

SELECT DISTINCT column FROM table;

Ques 13. What is the purpose of the SQL UPDATE statement?

The UPDATE statement is used to modify the existing records in a table by changing the values of one or more columns.

Example:

UPDATE table SET column1 = value1, column2 = value2 WHERE condition;

Ques 14. What is the purpose of the SQL LIKE operator?

The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters (% and _).

Example:

SELECT * FROM table WHERE column LIKE 'pattern%';

Ques 15. Write a SQL query to find the top N records from a table.

SELECT * FROM table ORDER BY column DESC LIMIT N;

Ques 16. Explain the SELECT statement.

The SELECT statement is used to query the database and retrieve data from one or more tables.

Example:

SELECT column1, column2 FROM table WHERE condition;

Ques 17. What is normalization?

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

Ques 18. Explain INNER JOIN and LEFT JOIN.

INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table.

Example:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Ques 19. Write a SQL query to calculate the average salary for each department.

SELECT department, AVG(salary) FROM Employee GROUP BY department;

Ques 20. Explain the difference between UNION and UNION ALL.

UNION combines and returns unique rows from multiple SELECT statements, while UNION ALL returns all rows including duplicates.

Example:

SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

Ques 21. Explain the difference between DELETE and TRUNCATE commands.

DELETE is used to remove rows from a table based on a condition. TRUNCATE removes all rows from a table without logging individual row deletions.

Example:

DELETE FROM table WHERE condition;

Ques 22. Explain the concept of ACID properties in a database.

ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability of database transactions.

Ques 23. Explain the GROUP BY clause.

The GROUP BY clause is used in a SELECT statement to arrange identical data into groups, typically used with aggregate functions.

Example:

SELECT column, COUNT(*) FROM table GROUP BY column;

Ques 24. What is a stored procedure?

A stored procedure is a set of SQL statements that can be stored in the database and executed by calling the procedure.

Ques 25. Write a SQL query to find duplicate records in a table.

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

Ques 26. Explain the purpose of the HAVING clause.

The HAVING clause is used in combination with the GROUP BY clause to filter the results of aggregate functions based on specified conditions.

Example:

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

Ques 27. Explain the difference between a primary key and a unique key.

A primary key is used to uniquely identify each record in a table and cannot contain NULL values. A unique key enforces uniqueness but can contain NULL values.

Ques 28. Explain the difference between UNION and JOIN.

UNION is used to combine the results of two or more SELECT statements, while JOIN is used to retrieve data from multiple tables based on a related column between them.

Ques 29. What is a trigger in SQL?

A trigger is a set of instructions that are automatically executed (or 'triggered') in response to certain events on a particular table or view.

Ques 30. What is a self-join?

A self-join is a regular join, but the table is joined with itself. It is useful when you want to combine rows with related data in the same table.

Example:

SELECT employee1.name, employee2.name FROM Employee employee1, Employee employee2 WHERE employee1.manager_id = employee2.employee_id;

Ques 31. Explain the concept of a composite key.

A composite key consists of more than one column and is used to uniquely identify a record in a table when a single column is not sufficient.

Ques 32. What is the difference between a stored procedure and a function?

A stored procedure does not necessarily return a value, while a function must return a value. Functions can be used in SQL statements, whereas procedures cannot.

Ques 33. Explain the purpose of the SQL CASE statement.

The CASE statement is used to perform conditional logic within a SQL query, similar to an IF-THEN-ELSE statement in other programming languages.

Example:

SELECT column1, CASE WHEN condition THEN 'Result1' ELSE 'Result2' END AS NewColumn FROM table;

Ques 34. What is a subquery? Provide an example.

A subquery is a query embedded within another query. It can be used to retrieve data that will be used in the main query. Example: SELECT column FROM table WHERE column IN (SELECT column FROM another_table);

Ques 35. Write a SQL query to find the total count of rows in each table of a database.

SELECT table_name, COUNT(*) FROM information_schema.tables GROUP BY table_name;

Ques 36. Explain the purpose of the SQL HAVING clause.

The HAVING clause is used to filter the results of aggregate functions in a SELECT statement based on specified conditions.

Example:

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

Ques 37. What is the difference between UNION and UNION ALL?

UNION combines and returns unique rows from multiple SELECT statements, while UNION ALL returns all rows including duplicates.

Example:

SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

Ques 38. Explain the purpose of the SQL INDEX.

An INDEX is a performance optimization feature that allows faster retrieval of records from a table. It is created on one or more columns of a table.

Example:

CREATE INDEX index_name ON table(column);

Ques 39. What is a self-referencing foreign key?

A self-referencing foreign key is a foreign key in a table that references the primary key of the same table, creating a relationship within the same table.

Ques 40. Write a SQL query to find the total salary expense for each department.

SELECT department, SUM(salary) FROM Employee GROUP BY department;

Ques 41. Write a SQL query to find the employees who do not belong to any department.

SELECT * FROM Employee WHERE department_id IS NULL;

Ques 42. Explain the difference between the INNER JOIN and OUTER JOIN.

INNER JOIN returns rows when there is a match in both tables, while OUTER JOIN returns all rows from one table and the matching rows from the other table, filling in the missing values with NULLs.

Example:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Ques 43. Explain the concept of a natural join.

A natural join is a type of JOIN that automatically matches columns with the same name in the joined tables. It eliminates duplicate columns in the result set.

Example:

SELECT * FROM table1 NATURAL JOIN table2;

Ques 44. Explain the concept of a CTE (Common Table Expression).

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves the readability and maintainability of complex queries.

Example:

WITH cte_name AS (SELECT * FROM table) SELECT * FROM cte_name;

Ques 45. Explain the purpose of the SQL GROUP_CONCAT() function.

The GROUP_CONCAT() function is used to concatenate values from multiple rows into a single string, grouped by a specified column.

Example:

SELECT department, GROUP_CONCAT(employee_name) FROM Employee GROUP BY department;

Ques 46. What is a common use case for the SQL COALESCE() function?

The COALESCE() function is used to return the first non-null expression among its arguments. A common use case is handling null values in a query.

Example:

SELECT COALESCE(column1, 'DefaultValue') FROM table;

Ques 47. Explain the purpose of the SQL SESSION_USER and SYSTEM_USER functions.

SESSION_USER returns the current username, and SYSTEM_USER returns the login name of the current user.

Example:

SELECT SESSION_USER, SYSTEM_USER;

Ques 48. What is the purpose of the SQL JSON functions (JSON_VALUE, JSON_QUERY, etc.)?

JSON functions in SQL are used to process JSON data. JSON_VALUE is used to extract a scalar value, and JSON_QUERY is used to extract an object or an array.

Example:

SELECT JSON_VALUE(json_column, '$.key') AS Value FROM table;

Ques 49. Explain the purpose of the SQL STRING_AGG() function.

The STRING_AGG() function is used to concatenate values from multiple rows into a single string with a specified separator.

Example:

SELECT department, STRING_AGG(employee_name, ', ') AS EmployeeList FROM Employee GROUP BY department;

Ques 50. Write a SQL query to find the employees who joined the company in the last N months.

SELECT * FROM Employee WHERE JOIN_DATE >= DATEADD(MONTH, -N, GETDATE());

Ques 51. Write a SQL query to find the second highest salary from an Employee table.

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

Ques 52. Write a SQL query to find the third highest salary from an Employee table.

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee));

Ques 53. Write a SQL query to find the nth highest salary from an Employee table.

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET n-1;

Ques 54. Write a SQL query to find the average salary of employees in each department, excluding departments with fewer than five employees.

SELECT department, AVG(salary) FROM Employee GROUP BY department HAVING COUNT(*) >= 5;

Ques 55. Explain the concept of SQL injection.

SQL injection is a security vulnerability where an attacker can insert malicious SQL code into a query, potentially gaining unauthorized access to a database.

Ques 56. What is the purpose of the SQL TRIGGER?

A TRIGGER is a set of instructions that are automatically executed (or 'triggered') in response to specific events, such as INSERTs, UPDATEs, or DELETEs, on a particular table.

Example:

CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW BEGIN -- trigger logic END;

Ques 57. Explain the purpose of the SQL ROLLUP operator.

The ROLLUP operator is used in conjunction with the GROUP BY clause to generate subtotals and grand totals for a set of columns in the result set.

Example:

SELECT department, city, SUM(salary) FROM Employee GROUP BY ROLLUP (department, city);

Ques 58. Write a SQL query to find the nth highest salary without using the LIMIT clause.

SELECT salary FROM Employee e1 WHERE n-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e1.salary <= e2.salary);

Ques 59. Explain the purpose of the SQL LEAD() and LAG() functions.

The LEAD() function is used to access the next row's data in the result set, while the LAG() function is used to access the previous row's data.

Example:

SELECT employee_name, salary, LEAD(salary) OVER (ORDER BY salary) AS NextSalary FROM Employee;

Ques 60. What is the purpose of the SQL CROSS APPLY operator?

The CROSS APPLY operator is used to invoke a table-valued function for each row returned by the outer query. It is similar to the INNER JOIN clause.

Example:

SELECT * FROM table1 CROSS APPLY function(table1.column) AS alias;

Ques 61. Write a SQL query to find the employees who have the highest salary in each department.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 1;

Ques 62. Explain the purpose of the SQL MERGE statement.

The MERGE statement is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It is also known as an 'upsert' operation.

Example:

MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES (value1, value2);

Ques 63. What is the purpose of the SQL WINDOW functions?

WINDOW functions perform a calculation across a set of table rows related to the current row. They are used with the OVER() clause to define a window or a subset of rows for the calculation.

Example:

SELECT employee_name, salary, AVG(salary) OVER (PARTITION BY department) AS AvgSalary FROM Employee;

Ques 64. Write a SQL query to find the employees with the second-highest salary in each department.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 2;

Ques 65. Explain the purpose of the SQL XML functions (XMLAGG, XMLPARSE, XMLQUERY, etc.).

XML functions in SQL are used to process XML data. XMLAGG is used to concatenate XML values, XMLPARSE is used to convert a string to XML, and XMLQUERY is used to extract data from XML.

Ques 66. What is the purpose of the SQL PIVOT and UNPIVOT operators?

The PIVOT operator is used to rotate rows into columns, while the UNPIVOT operator is used to rotate columns into rows.

Example:

SELECT * FROM (SELECT department, salary FROM Employee) AS SourceTable PIVOT (SUM(salary) FOR department IN ([Dept1], [Dept2], [Dept3])) AS PivotTable;

Ques 67. Write a SQL query to find the cumulative sum of a column in a result set.

SELECT column, SUM(column) OVER (ORDER BY some_order_column) AS CumulativeSum FROM table;

Ques 68. Explain the purpose of the SQL ROW and RANGE clauses in window functions.

ROW and RANGE are used in the OVER() clause of window functions to define the window or subset of rows for the calculation. ROW refers to a physical count, while RANGE considers values within a specified range.

Ques 69. Write a SQL query to find the employees with the highest salary in each department using the DENSE_RANK() function.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 1;

Ques 70. What is the purpose of the SQL TRY...CATCH statement?

The TRY...CATCH statement is used to handle errors in a SQL Server stored procedure. It allows for structured error handling and graceful error recovery.

Example:

BEGIN TRY -- SQL statements END TRY BEGIN CATCH -- Handling of errors END CATCH;