Intermediate SQL Questions and Answers

1. What are stored procedures in SQL, and why use them?

• A stored procedure is a prepared SQL code that you can save and reuse. It takes input parameters, performs actions, and returns results. Stored procedures enhance performance, reduce network traffic, and promote code reuse.

2. How do SQL functions differ from stored procedures?

• SQL functions return a single value and can be used in SQL statements. Stored procedures do not return values directly and can contain more complex logic, including transaction management.

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

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN: Returns all records from the left table and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table and matched records from the left table.
• FULL JOIN: Returns records when there is a match in one of the tables.

4. Explain the purpose of a UNION statement in SQL.

• The UNION operator combines the results of two or more SELECT queries, returning distinct records that appear in any query.

5. How does the GROUP BY clause work in SQL?

• GROUP BY groups rows that have the same values in specified columns into summary rows, like “find the total salary by department.”

6. Describe the HAVING clause and its use case.

• The HAVING clause is used to filter groups after the GROUP BY clause, similar to the WHERE clause but for aggregated data.

7. What is a subquery, and when would you use one?

• A subquery is a query nested within another query. It’s used to perform operations that need a result from another query, like filtering results based on aggregated data.

8. How do you create a temporary table in SQL?

• Temporary tables are created using the CREATE TEMPORARY TABLE statement and are stored in the tempdb database.

  CREATE TEMPORARY TABLE temp_table (column1 datatype, column2 datatype);

9. What are window functions, and why are they useful?

• Window functions perform calculations across a set of table rows related to the current row. They are useful for running totals, moving averages, and ranking.

10. Explain the difference between RANK() and DENSE_RANK().

• RANK() assigns a rank to each row with gaps for ties. DENSE_RANK() assigns ranks without gaps.

11. How can you improve the performance of an SQL query?

• Use proper indexing, avoid unnecessary columns in SELECT, use joins instead of subqueries, and optimize SQL statements.

12. What is the purpose of the EXPLAIN statement in SQL?

• EXPLAIN provides details about how SQL statements are executed, helping to analyze and optimize query performance.

13. Describe the ACID properties in SQL databases.

• Atomicity: Transactions are all-or-nothing.
• Consistency: Transactions bring the database from one valid state to another.
• Isolation: Transactions do not interfere with each other.
• Durability: Once a transaction is committed, it remains so.

14. What is a transaction in SQL, and how do you manage it?

• A transaction is a sequence of SQL operations performed as a single unit. Managed with BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

15. How does the ISNULL function work in SQL?

• ISNULL replaces NULL values with a specified replacement value.

16. What is a CTE (Common Table Expression) in SQL?

• A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

  WITH CTE AS (
      SELECT column1, column2
      FROM table_name
  )
  SELECT * FROM CTE;

17. How do you handle NULL values in SQL?

• Use IS NULL and IS NOT NULL in conditions, and functions like COALESCE and ISNULL to handle NULL values.

18. 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.

19. How do you perform a case-insensitive search in SQL?

• Use the LOWER or UPPER functions on both the column and the search term.

  SELECT * FROM table_name
  WHERE LOWER(column_name) = LOWER('search_term');

20. What is a trigger in SQL, and when would you use it?

• A trigger is a set of SQL statements that automatically execute in response to certain events on a particular table or view.

21. How do you optimize a query that uses multiple joins?

• Ensure proper indexing on join columns, limit the number of columns selected, and analyze the query execution plan.

22. What is the difference between IN and EXISTS clauses?

• IN checks if a value is within a set of values. EXISTS checks for the existence of rows returned by a subquery.

23. How do you handle errors in SQL?

• Use TRY…CATCH blocks in SQL Server to handle errors.

  BEGIN TRY
      -- SQL statements
  END TRY
  BEGIN CATCH
      -- Error handling code
  END CATCH;

24. What is the purpose of indexing in SQL databases?

• Indexes improve the speed of data retrieval operations at the cost of slower data modification operations.

25. How do you create a unique constraint in SQL?

• Use the UNIQUE constraint when creating or altering a table.

  CREATE TABLE table_name (
      column1 datatype UNIQUE
  );

26. How do you delete duplicate rows in SQL?

• Use the ROW_NUMBER() function with a common table expression (CTE).

  WITH CTE AS (
      SELECT *,
             ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) AS row_num
      FROM table_name
  )
  DELETE FROM CTE WHERE row_num > 1;

27. How do you update data in a table with values from another table?

• Use the UPDATE statement with a JOIN.

  UPDATE table1
  SET table1.column = table2.column
  FROM table1
  JOIN table2 ON table1.id = table2.id;

28. Explain the concept of a self-join and provide an example.

• A self-join is a regular join but the table is joined with itself.

  SELECT a.column1, b.column2
  FROM table_name a
  JOIN table_name b ON a.common_column = b.common_column;

29. How do you pivot data in SQL?

• Use the PIVOT function to convert rows to columns.

  SELECT *
  FROM (SELECT column1, column2, column3 FROM table_name)
  PIVOT (SUM(column3) FOR column2 IN ([value1], [value2])) AS pivot_table;

30. What is a non-equijoin, and when would you use it?

• A non-equijoin is a join condition that uses a condition other than equality.

  SELECT *
  FROM table1 t1
  JOIN table2 t2 ON t1.column1 < t2.column2;

31. How do you generate a sequence in SQL?

• Use the SEQUENCE object to generate a sequence of numeric values.

  CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
  SELECT NEXT VALUE FOR seq_name;

32. How do you rename a table in SQL?

• Use the RENAME TABLE statement.

  RENAME TABLE old_table_name TO new_table_name;

33. What is a lateral join, and how does it work?

• A lateral join allows subqueries in the FROM clause to reference columns from preceding tables.

  SELECT *
  FROM table1 t1
  JOIN LATERAL (SELECT * FROM table2 t2 WHERE t2.id = t1.id) AS t2;

34. How do you retrieve the current date and time in SQL?

• Use the CURRENT_TIMESTAMP function.

  SELECT CURRENT_TIMESTAMP;

35. How do you calculate the difference between two dates in SQL?

• Use the DATEDIFF function.

  SELECT DATEDIFF(day, start_date, end_date) FROM table_name;

36. How do you convert data from one data type to another in SQL?

• Use the CAST or CONVERT functions.

  SELECT CAST(column_name AS datatype) FROM table_name;
  SELECT CONVERT(datatype, column_name) FROM table_name;

37. How do you find the second highest salary in a table?

• Use the DENSE_RANK function.

  SELECT salary
  FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees)
  WHERE rank = 2;

38. How do you check if a table exists before creating it?

• Use the IF NOT EXISTS clause.

  CREATE TABLE IF NOT EXISTS table_name (
      column1

 datatype,
      column2 datatype
  );

39. How do you create a foreign key constraint in SQL?

• Use the FOREIGN KEY constraint when creating or altering a table.

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      FOREIGN KEY (column2) REFERENCES other_table(column)
  );

40. How do you perform a full outer join in SQL?

• Use the FULL OUTER JOIN keyword.

  SELECT *
  FROM table1
  FULL OUTER JOIN table2 ON table1.column = table2.column;

---