Scenario-Based Interview Questions and Answers for Freshers | SQL Server

Scenario-based questions are commonly asked in SQL Server interviews to test problem-solving abilities. By understanding how to write queries for specific patterns, generate basic reports, and debug errors, you’ll excel in interviews. Below are 30 scenario-based interview questions and answers tailored for freshers to help you master SQL Server.

Scenario-Based: Freshers | SQL Server

1. Writing Queries to Fetch Specific Patterns

1. How would you write a query to find all employees whose names start with “J”?
   • You can use the LIKE operator with a wildcard: SELECT * FROM employees WHERE employee_name LIKE 'J%';
   This query fetches all employees whose names start with “J”.
2. How can you find records that contain the word “Sales” anywhere in a column?
   • Use the LIKE operator with wildcards: SELECT * FROM departments WHERE department_name LIKE '%Sales%';
   This fetches all departments containing “Sales” in their name.
3. Write a query to find employees who were hired after January 1, 2020.
   • Use the WHERE clause to filter based on the hire date: SELECT * FROM employees WHERE hire_date > '2020-01-01';
4. How would you retrieve all products with a price greater than 100 and less than 500?
   • You can use the BETWEEN operator: SELECT * FROM products WHERE price BETWEEN 100 AND 500;
5. Write a query to retrieve all customers whose names contain more than 5 characters.
   • Use the LEN() function to filter by length: SELECT * FROM customers WHERE LEN(customer_name) > 5;
6. How do you find all orders that were placed in the last 30 days?
   • Use the GETDATE() function with a date comparison: SELECT * FROM orders WHERE order_date > DATEADD(day, -30, GETDATE());
7. Write a query to find employees who do not have an email address listed.
   • Use IS NULL to filter out records with no email: SELECT * FROM employees WHERE email IS NULL;
8. How would you find all employees whose names contain both ‘John’ and ‘Smith’?
   • Use LIKE for both patterns: SELECT * FROM employees WHERE employee_name LIKE '%John%' AND employee_name LIKE '%Smith%';
9. Write a query to fetch the top 5 highest-paid employees.
   • Use TOP with ORDER BY to sort by salary: SELECT TOP 5 * FROM employees ORDER BY salary DESC;
10. How would you fetch all records where the customer’s city is either “New York” or “Los Angeles”?
    • Use the IN operator: SELECT * FROM customers WHERE city IN ('New York', 'Los Angeles');

2. Creating Basic Reports from Datasets

11. How do you generate a report showing the total sales per product?
    • Use GROUP BY with aggregate functions: SELECT product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_id;
12. Write a query to list the number of employees in each department.
    • Use GROUP BY with the COUNT() function: SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
13. How would you generate a report of the total number of orders placed each month?
    • Use GROUP BY and YEAR(), MONTH(): SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS total_orders FROM orders GROUP BY YEAR(order_date), MONTH(order_date);
14. Write a query to show the average salary for employees in each department.
    • Use GROUP BY with the AVG() function: SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
15. How would you generate a report that shows the number of products in each category?
    • Use GROUP BY with COUNT(): SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id;
16. Write a query to generate a report of customers’ total purchase amounts, grouped by city.
    • Use GROUP BY with SUM() for aggregation: SELECT city, SUM(purchase_amount) AS total_purchases FROM customers GROUP BY city;
17. How do you find the highest paid employee in each department?
    • Use GROUP BY with MAX() and a subquery: SELECT department_id, MAX(salary) AS highest_salary FROM employees GROUP BY department_id;
18. Write a query to generate a list of products with their average rating, sorted by rating.
    • Use GROUP BY with AVG(): SELECT product_id, AVG(rating) AS avg_rating FROM product_reviews GROUP BY product_id ORDER BY avg_rating DESC;
19. How would you generate a report of the number of products sold by each sales representative?
    • Use GROUP BY and COUNT(): SELECT sales_rep_id, COUNT(*) AS products_sold FROM sales GROUP BY sales_rep_id;
20. Write a query to get a summary report showing the total number of employees by gender.
    • Use GROUP BY with COUNT(): SELECT gender, COUNT(*) AS total_employees FROM employees GROUP BY gender;

3. Debugging Simple Query Errors

21. What would you do if you get an error saying “invalid column name” in a query?
    • Check the column name for typos or verify that the column exists in the specified table.
22. How would you troubleshoot a query that returns the wrong number of rows?
    • Check the WHERE clause for correct conditions and ensure there are no unnecessary filters or joins.
23. What is the cause of the “conversion failed when converting the varchar value” error?
    • This error occurs when you’re trying to perform an operation between incompatible data types, like trying to compare a string to a number. Ensure the data types match.
24. What does the error “subquery returned more than one value” mean, and how do you fix it?
    • This error occurs when a subquery returns multiple rows where a single value is expected. Use IN instead of =, or ensure the subquery returns only one value.
25. How would you fix the “Ambiguous column name” error?
    • The error arises when multiple tables in a join have columns with the same name. Use table aliases to disambiguate the column names.
26. What should you do if you get an error when using an aggregate function without a GROUP BY clause?
    • Ensure that all non-aggregated columns in the SELECT statement are included in the GROUP BY clause.
27. How would you resolve a “syntax error” in a query?
    • Check for missing or extra commas, parentheses, and keywords, and verify that the SQL syntax matches the expected structure.
28. What does the “divide by zero” error mean, and how can you fix it?
    • This error occurs when a division operation has a denominator of zero. You can fix it by using a CASE statement to handle zero values before performing division.
29. How would you debug a query that runs but returns incorrect results?
    • Check the logic in the JOIN conditions, WHERE clause, and aggregate functions. Also, validate the data types of columns involved in the query.
30. How do you troubleshoot a “deadlock” error in SQL Server?
    • A deadlock occurs when two queries are waiting for each other to release resources. Review the queries involved, optimize indexing, and consider using SET DEADLOCK_PRIORITY to prevent deadlocks.

These 30 scenario-based SQL Server interview questions and answers cover key skills like writing queries, generating reports, and debugging common errors. By practicing these scenarios, you’ll be well-prepared for any SQL Server interview.