Here are 50 SQL interview questions:
1. What is SQL, and what does it stand for?
- Answer: SQL stands for Structured Query Language. It is a domain-specific language used for managing and querying relational databases.
2. What are the main categories of SQL commands?
- Answer: SQL commands are categorized into four main types: Data Query Language (DQL), Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).
3. What is the difference between SQL and NoSQL databases?
- Answer: SQL databases are relational databases that use structured schemas, while NoSQL databases are non-relational and often use flexible or unstructured data models.
4. Explain the purpose of the SELECT statement in SQL.
- Answer: The SELECT statement is used to retrieve data from one or more tables in a database. It is the fundamental command for querying data.
5. What is a primary key in a SQL table, and why is it important?
- Answer: A primary key is a column or a set of columns that uniquely identify each row in a table. It enforces data integrity and ensures data uniqueness.
6. How do you retrieve all columns from a SQL table named "employees"?
- Answer: You can use the SQL statement: SELECT * FROM employees; to retrieve all columns from the "employees" table.
7. Explain the purpose of the WHERE clause in SQL.
- Answer: The WHERE clause is used to filter rows from a table based on a specified condition. It is used in SELECT, UPDATE, and DELETE statements.
8. What is SQL injection, and how can it be prevented?
- Answer: SQL injection is a type of security vulnerability where an attacker inserts malicious SQL code into an application. It can be prevented by using prepared statements or parameterized queries.
9. What is a foreign key in SQL, and how does it relate to a primary key?
- Answer: A foreign key is a column or a set of columns in one table that references the primary key of another table. It establishes a relationship between tables.
10. Explain the purpose of the GROUP BY clause in SQL.
- Answer: The GROUP BY clause is used with aggregate functions to group rows that have the same values in specified columns. It is commonly used in conjunction with functions like SUM, COUNT, AVG, etc.
11. What is the difference between SQL UNION and SQL JOIN?
- Answer: SQL UNION is used to combine the result sets of two or more SELECT statements into a single result set, while SQL JOIN is used to retrieve data from multiple tables based on a related column.
12. How do you remove duplicate rows from a SQL query result?
- Answer: You can use the DISTINCT keyword in the SELECT statement to remove duplicate rows from the result set.
13. What is a subquery in SQL, and how is it different from a JOIN?
- Answer: A subquery is a query nested inside another query. It is used to retrieve data that will be used in the main query. Subqueries differ from JOINs as they are executed independently within the main query.
14. Explain the purpose of the HAVING clause in SQL.
- Answer: The HAVING clause is used with the GROUP BY clause to filter the result set based on aggregate functions. It allows you to apply conditions to grouped data.
15. What is the SQL ORDER BY clause used for, and how does it work?
- Answer: The ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order. It is often used with the SELECT statement.
16. What is a SQL view, and how is it different from a table?
- Answer: A view in SQL is a virtual table that is based on the result of a SELECT query. It does not store data on its own but provides a way to simplify complex queries and ensure data security.
17. What are SQL indexes, and why are they important?
- Answer: SQL indexes are data structures that improve the speed of data retrieval operations on a table. They are important for optimizing query performance.
18. Explain the purpose of the SQL UPDATE statement.
- Answer: The UPDATE statement is used to modify existing records in a table. It allows you to change values in specific columns based on a specified condition.
19. What is a SQL stored procedure, and how is it different from a function?
- Answer: A stored procedure in SQL is a precompiled set of one or more SQL statements that can be executed with a single call. Functions return a single value, while stored procedures may return multiple values or have no return value.
20. How do you add a new record to a SQL table using the INSERT statement?
- Answer: You can use the INSERT INTO statement to add a new record to a SQL table. For example, to add a record to a table named "customers," you would use: INSERT INTO customers (column1, column2, ...) VALUES (value1, value2, ...);.
21. What is the SQL TRUNCATE statement used for, and how does it differ from DELETE?
- Answer: The TRUNCATE statement is used to delete all records from a table but keeps the table structure intact. DELETE is used to remove specific records based on a condition, and it can be rolled back.
22. Explain the purpose of the SQL CASE statement and provide an example.
- Answer: The CASE statement is used to perform conditional logic in SQL queries. It allows you to specify different actions based on the values in a column. Example: SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS age_group FROM customers;.
23. What is the difference between SQL constraints and triggers?
- Answer: Constraints are rules applied to tables to enforce data integrity, such as primary keys and foreign keys. Triggers are database actions that automatically execute in response to events, such as data changes.
24. What is an SQL self-join, and when is it used?
- Answer: A self-join is used to join a table with itself. It is often used when a table has a hierarchical structure or when you need to compare rows within the same table.
25. What is the SQL BETWEEN operator used for, and how does it work?
- Answer: The BETWEEN operator is used to filter rows with a value within a specified range. For example, SELECT name FROM employees WHERE salary BETWEEN 40000 AND 60000; retrieves employees with salaries between 40,000 and 60,000.
26. Explain the purpose of the SQL DISTINCT keyword.
- Answer: The DISTINCT keyword is used to retrieve unique values from a column. It ensures that the result set does not contain duplicate values.
27. What is the SQL LIMIT clause, and how is it used to limit query results?
- Answer: The LIMIT clause is used to restrict the number of rows returned by a query. It is often used for pagination or to retrieve a specific number of results.
28. What is the SQL NULL value, and how is it different from an empty string or zero?
- Answer: The SQL NULL value represents the absence of a value or missing data. It is not the same as an empty string or zero, which are specific values.
29. What is a SQL join condition, and how is it specified in a JOIN clause?
- Answer: A join condition is a logical expression that specifies how rows from two or more tables are related. It is specified in the ON clause of the JOIN statement.
30. How do you delete records from a SQL table using the DELETE statement?
- Answer: You can use the DELETE statement to remove records from a table based on a specified condition. For example, to delete records from a table named "orders" where the order_id is 123, you would use: DELETE FROM orders WHERE order_id = 123;.
31. What is the SQL LIKE operator, and how is it used for pattern matching?
- Answer: The LIKE operator is used for pattern matching in SQL. It allows you to search for a specified pattern in a column using wildcard characters such as '%' and '_'.
32. Explain the purpose of the SQL UNION operator, and provide an example.
- Answer: The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. Example: SELECT name FROM customers UNION SELECT name FROM suppliers;.
33. What is the purpose of the SQL INNER JOIN, LEFT JOIN, and RIGHT JOIN clauses, and how do they differ?
- Answer: INNER JOIN retrieves rows that have matching values in both tables. LEFT JOIN retrieves all rows from the left table and the matched rows from the right table. RIGHT JOIN does the opposite, retrieving all rows from the right table and the matched rows from the left table.
34. How do you update multiple rows in a SQL table with different values using a single UPDATE statement?
- Answer: You can use a CASE statement in the SET clause of the UPDATE statement to conditionally update different rows with different values based on a specified condition.
35. What is the purpose of the SQL ROLLBACK statement, and when is it used?
- Answer: The ROLLBACK statement is used to undo changes made by a transaction. It is used when an error occurs or when you want to cancel a series of operations within a transaction.
36. Explain the SQL CHECK constraint and how it's used to restrict values in a column.
- Answer: The CHECK constraint is used to ensure that values in a column satisfy a specified condition. It restricts the allowable range of values for that column.
37. What is the purpose of the SQL COUNT() function, and how is it used for counting records?
- Answer: The COUNT() function is used to count the number of rows that match a specified condition in a SELECT statement. It is often used with GROUP BY to count records in groups.
38. How do you create an SQL index, and what are the benefits of indexing?
- Answer: You can create an index using the CREATE INDEX statement. Indexing improves the speed of data retrieval by allowing the database to quickly locate specific rows.
39. What is the SQL MAX() and MIN() functions used for, and how do they work?
- Answer: The MAX() function returns the maximum value in a column, while the MIN() function returns the minimum value. They are often used with the SELECT statement to retrieve the highest and lowest values.
40. Explain the purpose of the SQL TRIGGER statement, and provide an example of when it might be used.
- Answer: The TRIGGER statement is used to automatically execute a set of actions in response to specified events, such as data changes in a table. For example, a trigger might be used to log changes to a particular column.
41. What is the SQL AVG() function used for, and how does it calculate the average of a column?
- Answer: The AVG() function is used to calculate the average of a numeric column. It sums all the values in the column and divides by the number of values.
42. What is a SQL self-contained subquery, and how is it different from a correlated subquery?
- Answer: A self-contained subquery is independent and can be executed on its own. A correlated subquery depends on the outer query, and its results are influenced by the outer query's values.
43. What is the SQL CASE statement used for, and how can it be used for conditional logic in the SELECT statement?
- Answer: The SQL CASE statement is used to perform conditional logic in the SELECT statement. It allows you to specify different actions based on the values in a column.
44. What is the purpose of the SQL ROW_NUMBER() function, and how is it used to generate row numbers for result sets?
- Answer: The ROW_NUMBER() function assigns a unique number to each row in a result set. It is often used to perform paging and filtering operations.
45. What is the SQL CAST() function used for, and how is it used to change the data type of a column?
- Answer: The CAST() function is used to convert one data type to another. It is often used to change the data type of a column in a SELECT statement.
46. Explain the SQL EXISTS and NOT EXISTS subquery operators.
- Answer: The EXISTS operator is used to check if a subquery returns any rows. NOT EXISTS checks if a subquery returns no rows. They are often used with correlated subqueries to filter results.
47. What is the SQL MERGE statement used for, and how does it differ from the INSERT and UPDATE statements?
- Answer: The MERGE statement is used to perform an "upsert," combining INSERT and UPDATE operations. It inserts a new row or updates an existing one based on a specified condition.
48. How do you retrieve the Nth highest or lowest value in a SQL column?
- Answer: To retrieve the Nth highest value, you can use the LIMIT clause and ORDER BY in a descending order. For the Nth lowest value, you can use LIMIT and ORDER BY in an ascending order.
49. Explain the SQL self-join and provide an example of when it might be used.
- Answer: A self-join is used to join a table with itself. It can be used to establish relationships within the same table, such as connecting employees to their managers in an employee table.
50. What is the purpose of the SQL FETCH and OFFSET clauses, and how are they used for pagination?
- Answer: The FETCH clause is used to retrieve a specified number of rows from a result set, and the OFFSET clause is used to skip a certain number of rows. Together, they allow for efficient pagination of query results.
These SQL interview questions cover a wide range of SQL topics, from basic queries to more advanced concepts like subqueries, transactions, and triggers. Being familiar with these questions will help you prepare for SQL interviews and demonstrate your knowledge of database management and query optimization.