Top 20 JavaScript Interview Questions
1. What is SQL and what are its main components?
SQL (Structured Query Language) is a standard language for managing relational databases. Main components include: DDL (Data Definition Language) for creating/modifying database structure, DML (Data Manipulation Language) for inserting/updating/deleting data, DQL (Data Query Language) for retrieving data, and DCL (Data Control Language) for managing permissions and access control.
2. Explain the different types of JOINs in SQL
INNER JOIN returns records matching in both tables. LEFT JOIN returns all records from left table and matched records from right table. RIGHT JOIN returns all records from right table and matched records from left table. FULL OUTER JOIN returns all records when there's a match in either table. CROSS JOIN returns the Cartesian product of both tables.
3. What is database normalization and explain the normal forms?
Normalization eliminates data redundancy and ensures data integrity. 1NF: Each column contains atomic values, no repeating groups. 2NF: Meets 1NF and all non-key attributes depend on the entire primary key. 3NF: Meets 2NF and eliminates transitive dependencies. BCNF: Every determinant is a candidate key. Higher normal forms (4NF, 5NF) address multi-valued dependencies.
4. What are indexes and how do they improve query performance?
Indexes are data structures that improve query performance by creating shortcuts to data. They work like book indexes, allowing the database to quickly locate rows without scanning entire tables. Types include clustered (physically orders data), non-clustered (separate structure pointing to data), unique, composite, and partial indexes. Trade-off: faster reads but slower writes and additional storage.
5. Explain ACID properties in database transactions
ACID ensures reliable database transactions. Atomicity: Transaction is all-or-nothing, either all operations succeed or all fail. Consistency: Database remains in valid state before and after transaction. Isolation: Concurrent transactions don't interfere with each other. Durability: Committed transactions persist even after system failure. These properties maintain data integrity in multi-user environments.
6. What are stored procedures and their advantages?
Stored procedures are precompiled SQL code blocks stored in the database. Advantages include: improved performance through compilation and caching, reduced network traffic, centralized business logic, enhanced security through parameterized queries, code reusability, and better maintainability. They can accept parameters and return values, making them powerful for complex operations.
7. Explain the difference between UNION and UNION ALL
UNION combines results from multiple SELECT statements and removes duplicate rows, requiring additional processing to identify and eliminate duplicates. UNION ALL combines results without removing duplicates, making it faster. Both require the same number of columns with compatible data types. Use UNION when duplicates must be removed, UNION ALL when duplicates are acceptable or known not to exist.
8. What are window functions and how do they work?
Window functions perform calculations across a set of table rows related to the current row, without grouping rows into a single output row like aggregate functions. They use OVER() clause to define the window. Examples include ROW_NUMBER(), RANK(), LAG(), LEAD(), and aggregate functions with OVER(). They enable complex analytics like running totals, rankings, and comparisons with previous/next rows.
9. Explain database locking and isolation levels
Database locking prevents concurrent transactions from interfering with each other. Isolation levels define how transactions interact: READ UNCOMMITTED (lowest isolation, dirty reads possible), READ COMMITTED (prevents dirty reads), REPEATABLE READ (prevents dirty and non-repeatable reads), SERIALIZABLE (highest isolation, prevents all phenomena). Higher isolation levels provide more consistency but may reduce concurrency and performance.
10. What is a subquery and what are its types?
A subquery is a query nested inside another query. Types include: Scalar subquery (returns single value), Row subquery (returns single row), Table subquery (returns multiple rows/columns). By location: WHERE clause, FROM clause (derived table), SELECT clause. By correlation: Correlated (references outer query) vs Non-correlated (independent). Used for filtering, calculations, and complex data retrieval scenarios.
11. Explain the difference between DELETE, TRUNCATE, and DROP
DELETE removes specific rows based on WHERE clause, is logged, triggers fire, and can be rolled back. TRUNCATE removes all rows from table, is minimally logged, faster than DELETE, resets identity columns, but cannot be used with foreign key constraints. DROP removes the entire table structure and data from database, is a DDL operation, and cannot be rolled back in most databases.
12. What are constraints and their types in SQL?
Constraints enforce data integrity rules. PRIMARY KEY ensures unique identification and non-null values. FOREIGN KEY maintains referential integrity between tables. UNIQUE prevents duplicate values. NOT NULL ensures required data. CHECK validates data against specific conditions. DEFAULT provides default values. These constraints prevent invalid data entry and maintain database consistency.
13. Explain query optimization techniques
Query optimization improves performance through: proper indexing on frequently queried columns, avoiding SELECT *, using WHERE clauses to filter early, optimizing JOINs (order and type), using LIMIT/TOP for large result sets, analyzing execution plans, avoiding functions in WHERE clauses, using EXISTS instead of IN for subqueries, partitioning large tables, and updating database statistics regularly.
14. What is database partitioning and its types?
Partitioning divides large tables into smaller, manageable pieces while maintaining logical unity. Horizontal partitioning (sharding) splits rows across multiple tables/databases. Vertical partitioning splits columns. Range partitioning uses value ranges, Hash partitioning uses hash functions, List partitioning uses predefined values. Benefits include improved performance, easier maintenance, and parallel processing capabilities.
15. Explain the concept of database views and materialized views
Views are virtual tables based on SQL queries that don't store data physically but provide a layer of abstraction. They simplify complex queries, enhance security by hiding sensitive data, and provide consistent interfaces. Materialized views store query results physically, improving performance for complex aggregations but requiring refresh strategies. Both support modularity and can be updatable under certain conditions.
16. What is NoSQL and what are its main types?
NoSQL (Not Only SQL) databases are designed for specific data models and have flexible schemas for building modern applications. Main types: Document databases (MongoDB, CouchDB) store data in document format, Key-Value stores (Redis, DynamoDB) use simple key-value pairs, Column-family (Cassandra, HBase) organize data in column families, and Graph databases (Neo4j, Amazon Neptune) store data as nodes and relationships.
17. Explain the CAP theorem and its implications
CAP theorem states that distributed systems can only guarantee two of three properties: Consistency (all nodes see same data simultaneously), Availability (system remains operational), and Partition tolerance (system continues despite network failures). NoSQL databases choose different trade-offs: CP systems (MongoDB, HBase) sacrifice availability, AP systems (Cassandra, DynamoDB) sacrifice strong consistency for eventual consistency.
18. What is eventual consistency in NoSQL databases?
Eventual consistency means that while the system doesn't guarantee immediate consistency across all nodes, it will become consistent over time once updates stop. This model allows for high availability and partition tolerance. Examples include DNS updates, social media feeds, and distributed caches. It's suitable for applications where immediate consistency isn't critical but high availability is essential.
19. Compare SQL vs NoSQL databases - when to use each?
Use SQL for: complex relationships, ACID compliance requirements, complex queries and reporting, well-defined schema, financial applications. Use NoSQL for: rapid development with changing requirements, horizontal scaling needs, big data and real-time analytics, simple queries, content management, IoT applications, and when schema flexibility is important. Choice depends on data structure, scalability needs, and consistency requirements.
20. Explain sharding and replication in NoSQL databases
Sharding distributes data across multiple servers based on a shard key, enabling horizontal scaling and improved performance. Each shard contains a subset of data. Replication creates copies of data across multiple nodes for fault tolerance and read scalability. Master-slave replication has one write node, master-master allows multiple write nodes. NoSQL databases often combine both techniques for scalability and reliability.