Top 100+ SQL Interview Question and Answer For Engineer | SQL |

1. What is SQL?

• SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.

1. What are the different types of SQL commands?

• SQL commands can be categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

1. What is the difference between SQL and MySQL?

• SQL is a language used for managing relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its language.

1. What is a primary key?

• A primary key is a unique identifier for each record in a table. It ensures that each row in a table can be uniquely identified and cannot contain null values.

1. What is a foreign key?

• A foreign key is a column or a set of columns in a table that establishes a relationship between two tables. It points to the primary key of another table and ensures referential integrity.

1. What is normalization?

• Normalization is the process of organizing data in a database efficiently. It involves splitting tables into smaller tables and defining relationships between them to reduce redundancy and dependency.

1. What is denormalization?

• Denormalization is the process of adding redundant data to a normalized database to improve query performance by reducing the need for joins.

1. What is a join in SQL?

• A join is a SQL operation used to combine rows from two or more tables based on a related column between them.

1. What are the different types of joins in SQL?

• The main types of joins in SQL are INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

1. What is the difference between INNER JOIN and OUTER JOIN?
   • INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns all rows from both tables and fills in NULL values for non-matching rows.
2. What is the difference between GROUP BY and ORDER BY?
   • GROUP BY is used to group rows that have the same values into summary rows, while ORDER BY is used to sort the result set based on one or more columns.
3. What is a subquery?
   • A subquery is a query nested within another query. It can be used to return a set of values that can be used by the outer query.
4. What is the difference between a subquery and a join?
   • A subquery is a nested query that is executed independently within another query, while a join combines columns from two or more tables based on a related column between them.
5. What is a stored procedure?
   • A stored procedure is a prepared SQL code that can be stored and executed on the database server. It can accept input parameters and return multiple values in the form of output parameters or result sets.
6. What is a trigger?
   • A trigger is a special type of stored procedure that is automatically executed when certain events occur in the database, such as inserting, updating, or deleting records from a table.
7. What is an index in SQL?
   • An index is a database object used to improve the speed of data retrieval operations on a table by providing rapid access to rows based on the values of certain columns.
8. What are the different types of indexes in SQL?
   • The main types of indexes in SQL are clustered indexes, non-clustered indexes, unique indexes, and composite indexes.
9. What is ACID in database transactions?
   • ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the four properties that guarantee the reliability of database transactions.
10. Explain the difference between CHAR and VARCHAR data types.
    • CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR always occupies the same amount of storage space, while VARCHAR only uses as much space as needed for the actual data.
11. What is the difference between TRUNCATE and DELETE statements?
    • TRUNCATE is a DDL command that removes all rows from a table without generating any transaction log entries, while DELETE is a DML command that removes specific rows from a table and generates transaction log entries.
12. What is a self-join?
    • A self-join is a join operation in which a table is joined with itself based on a related column within the same table.
13. What is the difference between UNION and UNION ALL?
    • UNION removes duplicate rows from the result set, while UNION ALL retains all rows, including duplicates.
14. What is the difference between SQL and NoSQL databases?
    • SQL databases are relational databases that use structured query language, while NoSQL databases are non-relational databases that provide a mechanism for storage and retrieval of data.
15. What is the difference between a clustered and a non-clustered index?
    • A clustered index determines the physical order of data in a table, while a non-clustered index does not affect the physical order of data and stores pointers to the rows in a separate structure.
16. What is a view in SQL?
    • A view is a virtual table based on the result set of a SELECT query. It is used to simplify complex queries and provide a layer of abstraction over the underlying tables.
17. What is the purpose of the COMMIT statement?
    • The COMMIT statement is used to permanently save any changes made during the current transaction to the database.
18. What is the purpose of the ROLLBACK statement?
    • The ROLLBACK statement is used to undo any changes made during the current transaction and restore the database to its previous state.
19. What is the purpose of the CASE statement?
    • The CASE statement is used to provide conditional logic within a SQL query. It evaluates a list of conditions and returns one of several possible result expressions.
20. What is the difference between a cross join and a natural join?
    • A cross join returns the Cartesian product of the two tables, while a natural join returns rows that have matching values in the common columns of both tables.
21. What is the purpose of the HAVING clause?
    • The HAVING clause is used to filter groups of rows returned by a GROUP BY clause based on specified conditions.
22. What is the difference between the WHERE and HAVING clauses?
    • The WHERE clause is used to filter rows before any grouping is done, while the HAVING clause is used to filter groups of rows after the grouping has been done.
23. What is the purpose of the NVL function?
    • The NVL function is used to replace NULL values with a specified default value.
24. What is the purpose of the COALESCE function?
    • The COALESCE function is used to return the first non-NULL value in a list of expressions.
25. What is the purpose of the ROW_NUMBER() function?
    • The ROW_NUMBER() function is used to assign a unique sequential integer to each row in a result set.
26. **What is the purpose of the RANK() function?**
    • The RANK() function is used to assign a rank to each row in a result set based on specified criteria.
27. What is the purpose of the DENSE_RANK() function?
    • The DENSE_RANK() function is similar to the RANK() function but does not leave gaps in the ranking sequence when there are ties.
28. What is the purpose of the LEAD() and LAG() functions?
    • The LEAD() function is used to access data from subsequent rows, while the LAG() function is used to access data from previous rows, based on a specified ordering.
29. What is the purpose of the FIRST_VALUE() and LAST_VALUE() functions?
    • The FIRST_VALUE() function is used to return the first value in an ordered set, while the LAST_VALUE() function is used to return the last value in an ordered set.
30. What is the purpose of the FETCH FIRST clause?
    • The FETCH FIRST clause is used to limit the number of rows returned by a query.
31. What is the purpose of the OFFSET clause?
    • The OFFSET clause is used to skip a specified number of rows before returning the remaining rows from a query.
32. What is the purpose of the TRIM() function?
    • The TRIM() function is used to remove leading or trailing characters (or both) from a string.
33. What is the purpose of the CONCAT() function?
    • The CONCAT() function is used to concatenate two or more strings into a single string.
34. What is the purpose of the CAST() function?
    • The CAST() function is used to convert a value from one data type to another.
35. What is the purpose of the EXTRACT() function?
    • The EXTRACT() function is used to extract a part of a date or time value, such as the year, month, day, hour, minute, or second.
36. What is the purpose of the AVG() function?
    • The AVG() function is used to calculate the average value of a set of values.
37. What is the purpose of the SUM() function?
    • The SUM() function is used to calculate the sum of a set of values.
38. What is the purpose of the COUNT() function?
    • The COUNT() function is used to count the number of rows in a result set or the number of non-NULL values in a column.
39. What is the purpose of the MAX() function?
    • The MAX() function is used to find the maximum value in a set of values.
40. What is the purpose of the MIN() function?
    • The MIN() function is used to find the minimum value in a set of values.
41. What is the purpose of the SUBSTR() function?
    • The SUBSTR() function is used to extract a substring from a string based on a specified starting position and length.
42. What is the purpose of the IN operator?
    • The IN operator is used to specify multiple values in a WHERE clause.
43. What is the purpose of the EXISTS operator?
    • The EXISTS operator is used to test for the existence of any rows in a subquery.
44. What is the purpose of the ALL operator?
    • The ALL operator is used to compare a value to all values returned by a subquery.
45. What is the purpose of the ANY operator?
    • The ANY operator is used to compare a value to any value returned by a subquery.
46. What is the purpose of the LIKE operator?
    • The LIKE operator is used to search for a specified pattern in a column.
47. What is the purpose of the BETWEEN operator?
    • The BETWEEN operator is used to retrieve values within a specified range.
48. What is the purpose of the CASE expression?
    • The CASE expression is used to provide conditional logic in SQL statements.
49. What is the purpose of the COALESCE function?
    • The COALESCE function is used to return the first non-NULL value in a list of expressions.
50. What is the purpose of the CONCAT function?
    • The CONCAT function is used to concatenate two or more strings into a single string.
51. What is the purpose of the NULLIF function?
    • The NULLIF function is used to return NULL if two expressions are equal; otherwise, it returns the first expression.
52. What is the purpose of the ISNULL function?
    • The ISNULL function is used to replace NULL values with a specified replacement value.
53. What is the purpose of the IS NOT NULL operator?
    • The IS NOT NULL operator is used to test whether a value is not NULL.
54. What is the purpose of the ROW_NUMBER function?
    • The ROW_NUMBER function is used to assign a unique sequential integer to each row in a result set.
55. What is the purpose of the RANK function?
    • The RANK function is used to assign a rank to each row in a result set based on specified criteria.
56. What is the purpose of the DENSE_RANK function?
    • The DENSE_RANK function is similar to the RANK function but does not leave gaps in the ranking sequence when there are ties.
57. What is the purpose of the FIRST_VALUE function?
    • The FIRST_VALUE function is used to return the first value in an ordered set.
58. What is the purpose of the LAST_VALUE function?
    • The LAST_VALUE function is used to return the last value in an ordered set.
59. What is the purpose of the LEAD function?
    • The LEAD function is used to access data from subsequent rows in a result set.
60. What is the purpose of the LAG function?
    • The LAG function is used to access data from previous rows in a result set.
61. What is the purpose of the PERCENT_RANK function?
    • The PERCENT_RANK function is used to calculate the relative rank of a row within a result set as a percentage.
62. What is the purpose of the CUME_DIST function?
    • The CUME_DIST function is used to calculate the cumulative distribution of a value within a result set.
63. What is the purpose of the NTILE function?
    • The NTILE function is used to divide a result set into a specified number of roughly equal groups.
64. What is the purpose of the COUNT function?
    • The COUNT function is used to count the number of rows in a result set or the number of non-NULL values in a column.
65. What is the purpose of the SUM function?
    • The SUM function is used to calculate the sum of a set of values.
66. What is the purpose of the AVG function?
    • The AVG function is used to calculate the average value of a set of values.
67. What is the purpose of the MIN function?
    • The MIN function is used to find the minimum value in a set of values.
68. What is the purpose of the MAX function?
    • The MAX function is used to find
    the maximum value in a set of values.
69. What is the purpose of the FIRST function?
    • The FIRST function is used to return the first value in a result set.
70. What is the purpose of the LAST function?
    • The LAST function is used to return the last value in a result set.
71. What is the purpose of the MEDIAN function?
    • The MEDIAN function is used to calculate the median value of a set of values.
72. What is the purpose of the MODE function?
    • The MODE function is used to calculate the mode value of a set of values.
73. What is the purpose of the VARIANCE function?
    • The VARIANCE function is used to calculate the variance of a set of values.
74. What is the purpose of the STDDEV function?
    • The STDDEV function is used to calculate the standard deviation of a set of values.
75. What is the purpose of the COVAR_POP function?
    • The COVAR_POP function is used to calculate the population covariance between two sets of values.
76. What is the purpose of the COVAR_SAMP function?
    • The COVAR_SAMP function is used to calculate the sample covariance between two sets of values.
77. What is the purpose of the CORR function?
    • The CORR function is used to calculate the Pearson correlation coefficient between two sets of values.
78. What is the purpose of the REGR_AVGX function?
    • The REGR_AVGX function is used to calculate the average of the independent variable (X) in a linear regression.
79. What is the purpose of the REGR_AVGY function?
    • The REGR_AVGY function is used to calculate the average of the dependent variable (Y) in a linear regression.
80. What is the purpose of the REGR_COUNT function?
    • The REGR_COUNT function is used to calculate the number of non-NULL pairs of values in a linear regression.
81. What is the purpose of the REGR_INTERCEPT function?
    • The REGR_INTERCEPT function is used to calculate the y-intercept of the regression line in a linear regression.
82. What is the purpose of the REGR_R2 function?
    • The REGR_R2 function is used to calculate the coefficient of determination (R-squared) in a linear regression.
83. What is the purpose of the REGR_SLOPE function?
    • The REGR_SLOPE function is used to calculate the slope of the regression line in a linear regression.
84. What is the purpose of the REGR_SXX function?
    • The REGR_SXX function is used to calculate the sum of squares of the independent variable (X) in a linear regression.
85. What is the purpose of the REGR_SXY function?
    • The REGR_SXY function is used to calculate the sum of products of the independent variable (X) and the dependent variable (Y) in a linear regression.
86. What is the purpose of the REGR_SYY function?
    • The REGR_SYY function is used to calculate the sum of squares of the dependent variable (Y) in a linear regression.
87. What is the purpose of the STDDEV_POP function?
    • The STDDEV_POP function is used to calculate the population standard deviation of a set of values.
88. What is the purpose of the STDDEV_SAMP function?
    • The STDDEV_SAMP function is used to calculate the sample standard deviation of a set of values.
89. What is the purpose of the VARIANCE_POP function?
    • The VARIANCE_POP function is used to calculate the population variance of a set of values.
90. What is the purpose of the VARIANCE_SAMP function?
    • The VARIANCE_SAMP function is used to calculate the sample variance of a set of values.
91. What is the purpose of the WIDTH_BUCKET function?
    • The WIDTH_BUCKET function is used to assign values to discrete buckets based on a specified range.
92. What is the purpose of the LISTAGG function?
    • The LISTAGG function is used to concatenate values from multiple rows into a single string with a specified delimiter.
93. What is the purpose of the PERCENTILE_CONT function?
    • The PERCENTILE_CONT function is used to calculate the percentile value of a set of values using linear interpolation.
94. What is the purpose of the PERCENTILE_DISC function?
    • The PERCENTILE_DISC function is used to calculate the percentile value of a set of values using discrete distribution.
95. What is the purpose of the RATIO_TO_REPORT function?
    • The RATIO_TO_REPORT function is used to calculate the ratio of a value to the total of a set of values.
96. What is the purpose of the FIRST_VALUE function?
    • The FIRST_VALUE function is used to return the first value in an ordered set of values.
97. What is the purpose of the LAST_VALUE function?
    • The LAST_VALUE function is used to return the last value in an ordered set of values.
98. What is the purpose of the NTH_VALUE function?
    • The NTH_VALUE function is used to return the Nth value in an ordered set of values.

These questions cover a wide range of SQL concepts, functions, and techniques commonly encountered in interviews for engineering positions. Practice and understanding these concepts will help you excel in SQL interviews.