Interview Questions and Answers on SQL Basics | SQL Server

Preparing for an SQL Server interview requires a solid understanding of SQL basics, including essential commands like SELECT, INSERT, UPDATE, DELETE, and various clauses like WHERE, ORDER BY, and GROUP BY. Below are 30 SQL interview questions and answers, broken down by topics, to help you master the basics and impress your interviewers.

SQL Basics | SQL Server

1. SELECT, INSERT, UPDATE, DELETE

1. What is the SELECT statement in SQL?
   • The SELECT statement is used to retrieve data from one or more tables in a database. You can specify columns and filter results using conditions.
2. How do you use the INSERT statement in SQL?
   • The INSERT statement is used to add new rows into a table. You can insert specific values into columns or use DEFAULT to insert default values.
3. What is the UPDATE statement in SQL?
   • The UPDATE statement is used to modify existing records in a table. It requires a SET clause to define the new values and a WHERE clause to filter the rows to update.
4. What is the DELETE statement in SQL?
   • The DELETE statement removes rows from a table based on a specified condition. Be cautious when using it, as without a WHERE clause, it will delete all rows.
5. Can you explain the difference between DELETE and TRUNCATE?
   • DELETE removes rows one by one, while TRUNCATE removes all rows from a table quickly, but it cannot be rolled back (unless in a transaction).

2. WHERE, ORDER BY, GROUP BY, HAVING

6. What is the purpose of the WHERE clause in SQL?
   • The WHERE clause is used to filter records based on specified conditions. It restricts the rows that are returned or affected by SQL statements like SELECT, UPDATE, and DELETE.
7. How does the ORDER BY clause work in SQL?
   • The ORDER BY clause is used to sort the results of a query in either ascending (ASC) or descending (DESC) order based on one or more columns.
8. What is the GROUP BY clause in SQL?
   • The GROUP BY clause is used to group rows that have the same values in specified columns, often used with aggregate functions like COUNT, SUM, AVG, etc.
9. What is the HAVING clause in SQL?
   • The HAVING clause is used to filter groups created by the GROUP BY clause. It allows filtering based on aggregate functions, which cannot be done with the WHERE clause.
10. What is the difference between WHERE and HAVING in SQL?
    • The WHERE clause filters rows before grouping, while the HAVING clause filters groups after they are formed by GROUP BY. WHERE cannot be used with aggregate functions directly.

3. Aggregate Functions (SUM, AVG, COUNT)

11. What is the SUM function in SQL?
    • The SUM function is an aggregate function that returns the total sum of a numeric column. It is often used with GROUP BY to calculate totals for each group.
12. What does the AVG function do in SQL?
    • The AVG function calculates the average value of a numeric column, ignoring NULL values, and is typically used in conjunction with the GROUP BY clause.
13. How does the COUNT function work in SQL?
    • The COUNT function returns the number of rows that match a specified condition. You can use COUNT(*) for total rows, or COUNT(column_name) to count non-NULL values.
14. What is the difference between COUNT(*) and COUNT(column_name)?
    • COUNT(*) counts all rows in a table, including those with NULL values, while COUNT(column_name) counts only the rows where the specified column is not NULL.
15. How do you find the average salary of employees using SQL?
    • To find the average salary, use the AVG function like this: SELECT AVG(salary) FROM employees; This will return the average salary from the employees table.
16. Can aggregate functions be used with the DISTINCT keyword?
    • Yes, you can use aggregate functions with DISTINCT to perform calculations on unique values. For example, SELECT COUNT(DISTINCT column_name) FROM table_name; counts distinct values.
17. What is the purpose of the MAX function in SQL?
    • The MAX function returns the highest value from a column. It is commonly used to find the maximum number in a set of data, like the highest salary or most recent date.
18. How do you use the MIN function in SQL?
    • The MIN function returns the smallest value in a specified column. It can be used with numeric, date, and other comparable data types.
19. Can you use aggregate functions without a GROUP BY clause?
    • Yes, aggregate functions can be used without GROUP BY to calculate a single result for the entire table. For example, SELECT AVG(salary) FROM employees; returns the average for the whole table.
20. How do you calculate the total number of employees in each department using SQL?
    • To calculate the total number of employees per department, use GROUP BY with COUNT: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;.

4. Additional SQL Functions

21. What is the purpose of the LIKE operator in SQL?
    • The LIKE operator is used to search for a specified pattern in a column. It supports wildcards such as % (for any sequence of characters) and _ (for a single character).
22. How do you filter records between a range of values in SQL?
    • You can use the BETWEEN operator to filter values within a specific range. For example: SELECT * FROM table_name WHERE column_name BETWEEN 10 AND 20;.
23. What is the difference between INNER JOIN and LEFT JOIN in SQL?
    • An INNER JOIN returns only the matching rows from both tables, while a LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULL for non-matching rows.
24. What is the purpose of the DISTINCT keyword in SQL?
    • The DISTINCT keyword removes duplicate rows from the result set. It ensures that each row is unique based on the selected columns.
25. What does the NULL value represent in SQL?
    • The NULL value represents the absence of a value in a column. It is different from an empty string or zero and requires special handling in queries.
26. What is a subquery in SQL?
    • A subquery is a query nested within another query. It can return a single value, multiple values, or a table, and is used to filter or perform operations within a parent query.
27. How do you combine results from multiple SELECT statements in SQL?
    • You can use the UNION or UNION ALL operators to combine results from multiple SELECT statements. UNION removes duplicates, while UNION ALL includes duplicates.
28. What is the difference between the IN and EXISTS operators in SQL?
    • The IN operator is used to check if a value exists in a list of values, while EXISTS checks if a subquery returns any rows. IN is typically used with lists, and EXISTS with subqueries.
29. How do you update only specific rows in SQL?
    • Use the UPDATE statement with a WHERE clause to specify which rows to update. For example: UPDATE table_name SET column_name = value WHERE condition;.
30. What is the purpose of the CASE statement in SQL?
    • The CASE statement is used for conditional logic. It allows you to return different values based on specified conditions, similar to an IF-ELSE structure.