SQL Interview Questions with Answers Part-5: ☑️

 41. Differentiate between OLTP and OLAP databases.

⦁ OLTP (Online Transaction Processing) is optimized for transactional tasks—fast inserts, updates, and deletes with many users.
⦁ OLAP (Online Analytical Processing) is optimized for complex queries and data analysis, often dealing with large historical datasets.

42. What is schema in SQL?  
    A schema is a logical container that holds database objects like tables, views, and procedures, helping organize and manage database permissions.

43. How do you implement many-to-many relationships in SQL?  
    By creating a junction (or associative) table with foreign keys referencing the two related tables.

44. What is query optimization?  
    The process of improving query execution efficiency by rewriting queries, indexing, and analyzing execution plans to reduce resource consumption.

45. How do you handle large datasets in SQL?  
    Use partitioning, indexing, batch processing, query optimization, and sometimes materialized views or data archiving to manage performance.

46. Explain the difference between CROSS JOIN and INNER JOIN.
⦁ CROSS JOIN returns the Cartesian product (all combinations) of two tables.
⦁ INNER JOIN returns only matching rows based on join conditions.

47. What is a materialized view?  
    A stored physical copy of the result set of a query, which improves performance for complex queries by avoiding recomputation every time.

48. How do you backup and restore a database?  
    Use built-in commands/tools like BACKUP DATABASE and RESTORE DATABASE in SQL Server, or mysqldump in MySQL, often automating with scripts for regular backups.

49. Explain how indexing can degrade performance.  
    Too many indexes slow down write operations (INSERT, UPDATE, DELETE) because indexes must also be updated; large indexes can consume extra storage and memory.

50. Can you write a query to find employees with no managers?  
    Example:

SQL

SELECT * FROM employees e  
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.id = e.manager_id);