1. What is SQL?

Answer: SQL, which stands for Structured Query Language, is a domain-specific language used for managing and querying relational databases, providing a standardized way to interact with data.

2. What does the SELECT statement do?

Answer: The SELECT statement is fundamental in SQL, serving as a means to retrieve data from a database table. It allows you to specify which columns you want to retrieve and apply various filtering and aggregation operations on the data.

3. How do you retrieve all columns from a table named “employees”?

Answer: To retrieve all columns from a table, you can use the wildcard symbol (*) in the SELECT statement, like this: SELECT * FROM employees;. This returns all available data from the “employees” table.

4. What is the purpose of the WHERE clause in a SELECT statement?

Answer: The WHERE clause is essential for filtering data in a SELECT statement based on specific conditions. It allows you to narrow down your result set by specifying criteria that rows must meet to be included in the output.

5. How do you retrieve unique values from a column in a table?

Answer: To fetch distinct values from a specific column in a table, you can use the DISTINCT keyword in the SELECT statement, like this: SELECT DISTINCT column_name FROM table_name;. This ensures that only unique values are returned in the result.

6. What is the INSERT statement used for?

Answer: The INSERT statement is a key SQL command for adding new records (rows) to a table. It allows you to provide values for each column in the table, effectively inserting new data into the database.

7. How can you insert data into specific columns in a table?

Answer: To insert data into specific columns, you can list the column names in the INSERT statement, like this: INSERT INTO table_name (column1, column2) VALUES (value1, value2);. This ensures that the data is inserted into the designated columns.

8. What is the purpose of the UPDATE statement?

Answer: The UPDATE statement serves to modify existing data in a table. It allows you to change the values in one or more columns for specified rows, making it a crucial tool for data maintenance and updates in a database.

9. How do you delete records from a table using the DELETE statement?

Answer: To remove rows from a table, you can use the DELETE statement with a WHERE clause, defining the conditions that rows must meet to be deleted. This is a core operation for data removal in SQL.

10. What is a primary key?

Answer: A primary key is a unique identifier for each row in a table. It ensures that every row is distinct and can be used to reference or join with other tables.

11. What is a foreign key?

Answer: A foreign key establishes a relationship between two tables, ensuring referential integrity. It enforces a connection between data in the current table and data in another table, typically using the primary key of the referenced table.

12. Explain the difference between CHAR and VARCHAR data types.

Answer: CHAR is a fixed-length data type that always occupies the same amount of storage, while VARCHAR is a variable-length data type, consuming storage only for the actual data entered. VARCHAR is more space-efficient for columns with varying content lengths.

13. What is the purpose of the ORDER BY clause?

Answer: The ORDER BY clause is used to sort the result set generated by a SELECT statement. You can specify one or more columns for sorting, and you can order the data in either ascending (ASC) or descending (DESC) sequence.

14. How can you limit the number of rows returned in a result set?

Answer: To restrict the number of rows returned, you can utilize the LIMIT clause. For example, you can use SELECT * FROM table_name LIMIT 10; to limit the result to the first 10 rows, making it handy for pagination or controlling query output.

15. What does the asterisk (*) symbol represent in a SQL query?

Answer: The asterisk (*) is a wildcard symbol that stands for “all columns.” In a SQL query, using SELECT * means you want to retrieve data from all columns in the specified table.

16. How do you comment in SQL?

Answer: In SQL, you can include comments for documentation or explanation purposes. Single-line comments are typically denoted with double hyphens (–), while multi-line comments are enclosed within /* */.

17. What is the purpose of the AS keyword in SQL?

Answer: The AS keyword is used to provide aliases to columns or tables in the result set. It allows you to give custom names to columns for more readable or descriptive output.

18. How do you concatenate strings in SQL?

Answer: In SQL, you can concatenate (combine) strings using functions like CONCAT() or operators like ||. For instance, you can concatenate the first name and last name as SELECT CONCAT(first_name, ' ', last_name) FROM employees;.

19. What is a NULL value in SQL?

Answer: NULL represents the absence of a value in a database column. It signifies that the data is missing or unknown. Handling NULL values is crucial when working with SQL databases.

20. How do you count the number of rows in a table?

Answer: To count the number of rows in a table, you can use the COUNT() function. For example, SELECT COUNT(*) FROM table_name; will return the total number of rows in the specified table.

21. What is the purpose of the LIKE operator?

Answer: The LIKE operator is employed for pattern matching in SQL queries. It allows you to search for strings that match a specified pattern, using wildcard characters like % and _ to represent variable content.

22. How can you retrieve data from multiple tables in a single query?

Answer: Retrieving data from multiple tables in a single query is accomplished through JOIN operations. By using JOIN clauses (e.g., INNER JOIN, LEFT JOIN), you can combine data from different tables based on related columns.

23. What is the purpose of the GROUP BY clause?

Answer: The GROUP BY clause is used to group rows in the result set based on the values in specified columns. It is typically used with aggregate functions to generate summary information, like total sales or average scores.

24. How do you retrieve the first ‘n’ rows from a result set?

Answer: To obtain the first ‘n’ rows from a result set, you can utilize the LIMIT clause in your SQL query. For instance, SELECT * FROM table_name LIMIT n; will return the first ‘n’ rows.

25. How do you update data in a table using the UPDATE statement?

Answer: The UPDATE statement is used to modify existing data in a table. It requires you to specify the new values for one or more columns in the affected rows using the SET clause.

26. What is the purpose of the HAVING clause?

Answer: While the WHERE clause filters rows before they are grouped, the HAVING clause filters the results of an aggregate query after grouping. It allows you to set conditions on grouped data.

27. How can you calculate the average of a numeric column in SQL?

Answer: To calculate the average of a numeric column, you can use the AVG() function. For example, SELECT AVG(column_name) FROM table_name; will return the average value of the specified column.

28. Explain the difference between the UNION and UNION ALL operators.

Answer: UNION combines and returns distinct rows from multiple SELECT statements, removing duplicates. On the other hand, UNION ALL includes all rows, even if they are duplicates, in the result set.

29. What is a subquery?

Answer: A subquery is a query nested within another query. It is often used to retrieve data that is then used in the main query. Subqueries can be placed in various parts of a SQL statement, such as in the SELECT clause, the FROM clause, or the WHERE clause.

30. What is the purpose of the CASE statement in SQL?

Answer: The CASE statement provides conditional logic within SQL queries, allowing you to return different values or perform various actions based on specified conditions. It is akin to the “if-then-else” construct in programming.

31. What is a self-join?

Answer: A self-join occurs when a table is joined to itself. It is useful when you want to find relationships or comparisons between rows within the same table. To perform a self-join, you typically use table aliases to distinguish between the instances of the same table.

32. How can you add a new record to a table using the INSERT INTO statement?

Answer: To insert a new record into a table, you can employ the INSERT INTO statement, specifying the target table and providing values for the columns. The new data is added as a new row in the table.

33. Explain the difference between DELETE and TRUNCATE statements.

Answer: The DELETE statement removes specific rows from a table based on a given condition and allows for a transaction rollback. In contrast, the TRUNCATE statement removes all rows from a table without specifying conditions and is not easily reversible, making it faster but less flexible.

34. What is the purpose of the SQL LIKE operator?

Answer: The SQL LIKE operator is used for pattern matching within SQL queries. It enables you to search for strings that match a specific pattern, making it valuable for tasks like finding names that contain a certain substring.

35. What is the purpose of the COALESCE function?

Answer: The COALESCE function returns the first non-null value from a list of expressions. It is useful when you want to retrieve the first non-null value from a set of possibilities, ensuring that you get meaningful data.

36. How do you round a numeric value to a specific number of decimal places in SQL?

Answer: To round a numeric value to a specified number of decimal places, you can use the ROUND() function. By providing the value and the number of decimal places as arguments, you can control the precision of the result.

37. What is a database schema in SQL?

Answer: A database schema is a logical container that organizes and groups database objects, such as tables, views, indexes, and procedures. It provides structure and organization to the database, making it easier to manage and maintain.

38. How can you retrieve the last ‘n’ rows from a result set in SQL?

Answer: To retrieve the last ‘n’ rows from a result set, you can use the ORDER BY clause to sort the data in descending order and then apply the LIMIT clause to restrict the number of rows returned.

39. What is a correlated subquery?

Answer: A correlated subquery is a subquery that references columns from the outer query. It depends on the outer query’s results and is executed for each row in the outer query’s result set. Correlated subqueries are used to filter or perform operations based on values from the outer query.

40. What is an SQL injection?

Answer: SQL injection is a malicious attack that involves injecting unauthorized SQL code into an application’s input fields. The attacker seeks to manipulate the database by exploiting vulnerabilities and gain unauthorized access or execute harmful operations on the database.

41. What is the purpose of the ROLLBACK statement in SQL?

Answer: The ROLLBACK statement is used to undo changes made within a transaction. It ensures that all changes are reverted, and the data is restored to its previous state in case of an error or unwanted modifications, maintaining data consistency.

42. What is the difference between the MIN() and MAX() functions in SQL?

Answer: MIN() is an aggregate function that returns the minimum value from a specified column, while MAX() is another aggregate function that returns the maximum value. Both functions are used to find extremum values within a dataset.

43. How can you delete duplicate rows from a table in SQL?

Answer: You can eliminate duplicate rows from a table by using the ROW_NUMBER() function in combination with a common table expression (CTE). By numbering the rows and filtering out duplicates based on the row number, you can remove redundant data.

44. What is an aggregate function in SQL?

Answer: An aggregate function, such as SUM, AVG, COUNT, or MAX, is used to perform calculations on a set of values, typically within a specific column. These functions return a single value that summarizes the data, making them vital for generating insights from large datasets.

45. What is the purpose of the SQL TRUNCATE statement?

Answer: The SQL TRUNCATE statement is used to remove all rows from a table, effectively emptying the table. Unlike the DELETE statement, which removes specific rows, TRUNCATE is a more efficient way to delete all data but lacks the flexibility to filter rows based on conditions.

46. What is the purpose of the LEFT JOIN in SQL?

Answer: A LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. If there is no match in the right table, the result will still contain all the rows from the left table, with NULL values in the columns from the right table.

47. What is the difference between the SQL JOIN and UNION operations?

Answer: JOIN operations are used to combine rows from multiple tables based on related columns, providing a way to create a unified result set. UNION operations, on the other hand, merge rows from multiple SELECT statements into a single result set, ensuring that duplicate rows are removed.

48. How can you update data in multiple rows simultaneously using the UPDATE statement?

Answer: Use the WHERE clause to specify the condition for updating multiple rows in the UPDATE statement. By defining the condition that identifies the rows to be updated, you can apply changes to a subset of rows that meet the specified criteria.

49. What is the purpose of the SQL TRIGGER?

Answer: A trigger is a set of instructions that are automatically executed in response to a specific event or action in a database. Triggers can be defined to respond to events such as INSERT, UPDATE, or DELETE operations on a table. They are often used for enforcing business rules, auditing changes, or maintaining data integrity.

50. What is the purpose of the LEFT JOIN in SQL?

Answer: A LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. If there is no match in the right table, the result will still contain all the rows from the left table, with NULL values in the columns from the right table.