Post #2465

✅ Top SQL Interview Questions with Answers: Part-5🧠 41. What are set operations in SQL? Set operations combine results from multiple SELECT queries: • UNION: Combines results and removes duplicates. • UNION ALL: Combines all results, including duplicates. • INTERSECT: Returns only the common records between two queries. • EXCEPT / MINUS: Returns records from the first query that are not in the second. 42. What is a materialized view? Unlike a normal view (which is virtual), a materialized view stores actual data physically on disk. It improves performance for complex queries by pre-computing and storing the results, and it can be refreshed manually or automatically to reflect changes in the underlying data. 43. Explain the BETWEEN operator. The BETWEEN operator is used to filter data within a specified range, including both endpoints. Example: SELECT * FROM products WHERE price BETWEEN 100 AND 500; 44. What is a pivot table in SQL? A pivot table transforms rows into columns, which is helpful for summarizing data. It can be created using GROUP BY, CASE statements, or database-specific PIVOT keywords. Example: Monthly sales data pivoted by region. 45. How do you optimize SQL queries? To optimize SQL queries, consider the following strategies: • Use indexes effectively on frequently queried columns. • Avoid using SELECT *; specify only the needed columns. • Use WHERE clauses to filter data as early as possible. • Prefer EXISTS over IN for subqueries to improve performance. • Analyze execution plans to identify bottlenecks. • Avoid unnecessary joins or deeply nested subqueries. 46. How do you handle slow queries? To address slow queries, you can: • Check and optimize indexes on columns used in filters. • Break large queries into smaller, more manageable parts. • Implement caching strategies to reduce load times. • Limit the number of returned rows using LIMIT or TOP clauses. • Use EXPLAIN or QUERY PLAN to analyze and diagnose performance issues. 47. What’s the use of execution plan in SQL? An execution plan illustrates how the database engine will execute a given query. It helps identify slow operations (like full table scans) and suggests areas for optimization. You can view execution plans using EXPLAIN in MySQL/PostgreSQL or SET SHOWPLAN_ALL in SQL Server. 48. What’s the use of LIMIT / OFFSET? • LIMIT: Restricts the number of rows returned by a query. • OFFSET: Skips a specified number of rows before starting to return results. Example: SELECT * FROM users LIMIT 10 OFFSET 20; This is particularly useful for implementing pagination. 49. How do you import/export data in SQL? • Importing Data: Use commands like LOAD DATA INFILE, BULK INSERT, or utilize import tools provided by database management systems. • Exporting Data: Use SELECT INTO OUTFILE, mysqldump, pg_dump, or export data to CSV from GUI tools. 50. How would you clean messy data using SQL? To clean messy data, you can apply several functions: • Use TRIM() to remove leading and trailing spaces. • Use REPLACE() to eliminate unwanted characters or strings. • Handle NULL values with COALESCE() to provide default values. • Use CASE statements for conditional transformations of data. • Utilize subqueries or Common Table Expressions (CTEs) to identify and remove duplicates or invalid entries. 💡Double Tap ♥️ For More