General Concepts & Basics
Q: What does DBMS stand for? — A: Database Management System.
Q: What does RDBMS stand for? — A: Relational Database Management System.
Q: What is a database? — A: An organized collection of structured information, or data, typically stored electronically.
Q: What is a table? — A: A collection of related data held in a structured format within a database.
Q: What is a field? — A: A column in a table that maintains specific information about every record.
Q: What is a record (row)? — A: A single entry in a table.
Q: What is SQL? — A: Structured Query Language, used to communicate with databases.
Q: What is the difference between DBMS and RDBMS? — A: DBMS stores data as files; RDBMS stores data in tabular form with relationships.
Q: What is metadata? — A: Data about data (e.g., table names, column types).
Q: What is a primary key? — A: A column that uniquely identifies each row in a table.
Q: Can a primary key contain NULL values? — A: No, a primary key cannot be NULL.
Q: What is a foreign key? — A: A field that links to the primary key of another table to establish a relationship.
Q: What is a composite key? — A: A primary key made consisting of two or more columns.
Q: What is a candidate key? — A: Any column or set of columns that can qualify as a unique key in a database.
Q: What is a super key? — A: A set of one or more keys that can uniquely identify a record.
Q: What is a unique key? — A: A constraint that ensures all values in a column are distinct.
Q: Can a unique key have NULL values? — A: Yes, usually one NULL value is allowed (depending on the DB).
Q: What is the difference between Primary Key and Unique Key? — A: Primary Key does not allow NULLs; Unique Key allows one NULL.
Q: What is DDL? — A: Data Definition Language (CREATE, ALTER, DROP).
Q: What is DML? — A: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
Q: What is DCL? — A: Data Control Language (GRANT, REVOKE).
Q: What is TCL? — A: Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT).
Q: What is a schema? — A: The logical structure or blueprint of a database.
Q: What is a tuple? — A: Another term for a row or record in an RDBMS.
Q: What is an attribute? — A: Another term for a column or field in an RDBMS.
Q: What is data redundancy? — A: The repetition of data across a database, leading to waste and inconsistencies.
Q: What is data integrity? — A: The accuracy and consistency of data over its lifecycle.
Q: What is a constraint? — A: A rule enforced on data columns (e.g., NOT NULL, CHECK).
Q: What is the CHECK constraint? — A: A rule that limits the value range that can be placed in a column.
Q: What is the DEFAULT constraint? — A: Provides a default value for a column when none is specified.
SQL Commands & Syntax
Q: Which command is used to retrieve data? — A: SELECT.
Q: Which command is used to add new data? — A: INSERT INTO.
Q: Which command is used to modify existing data? — A: UPDATE.
Q: Which command is used to remove data? — A: DELETE.
Q: Which command is used to remove a table entirely? — A: DROP TABLE.
Q: Which command is used to delete all rows but keep the table structure? — A: TRUNCATE.
Q: Difference between DELETE and TRUNCATE? — A: DELETE is DML and slower (logs rows); TRUNCATE is DDL and faster.
Q: How do you select all columns from a table? — A: SELECT * FROM table_name;
Q: How do you filter records? — A: Using the WHERE clause.
Q: How do you sort results? — A: Using the ORDER BY clause.
Q: How do you select unique values only? — A: Using the DISTINCT keyword.
Q: What acts as a wildcard for a single character in SQL? — A: The underscore (_).
Q: What acts as a wildcard for zero or more characters in SQL? — A: The percent sign (%).
Q: How do you search for a pattern? — A: Using the LIKE operator.
Q: How do you count rows? — A: SELECT COUNT(*) FROM table;
Q: How do you find the highest value? — A: SELECT MAX(column) FROM table;
Q: How do you find the lowest value? — A: SELECT MIN(column) FROM table;
Q: How do you calculate the average? — A: SELECT AVG(column) FROM table;
Q: How do you sum values? — A: SELECT SUM(column) FROM table;
Q: What is the keyword to combine result sets of two SELECTs? — A: UNION.
Q: Difference between UNION and UNION ALL? — A: UNION removes duplicates; UNION ALL keeps duplicates.
Q: How do you group rows that have the same values? — A: Using the GROUP BY clause.
Q: How do you filter groups created by GROUP BY? — A: Using the HAVING clause.
Q: Difference between WHERE and HAVING? — A: WHERE filters rows before grouping; HAVING filters groups after grouping.
Q: What is an Alias in SQL? — A: A temporary name given to a table or column using the AS keyword.
Q: How do you check for NULL values? — A: Using IS NULL or IS NOT NULL.
Q: Can you use comparison operators (like =) with NULL? — A: No, you must use IS NULL.
Q: What is the BETWEEN operator? — A: It selects values within a given range.
Q: What is the IN operator? — A: It allows you to specify multiple values in a WHERE clause.
Q: How do you copy data from one table to another? — A: INSERT INTO ... SELECT ...
Joins & Relationships
Q: What is a Join? — A: A method to combine rows from two or more tables based on a related column.
Q: What is an INNER JOIN? — A: Returns records that have matching values in both tables.
Q: What is a LEFT JOIN? — A: Returns all records from the left table and matched records from the right.
Q: What is a RIGHT JOIN? — A: Returns all records from the right table and matched records from the left.
Q: What is a FULL OUTER JOIN? — A: Returns all records when there is a match in either left or right table.
Q: What is a CROSS JOIN? — A: Returns the Cartesian product of the two tables.
Q: What is a Self Join? — A: A regular join but the table is joined with itself.
Q: What is a Natural Join? — A: A join based on all columns with the same name in both tables.
Q: What is an Equi-Join? — A: A join containing an equality operator (=).
Q: What is a Non-Equi Join? — A: A join using operators other than equal (=) like <, >, or <>.
Q: Which join is the default in SQL? — A: INNER JOIN.
Q: What happens if you Left Join two tables with no matches? — A: The result contains NULLs for the right table columns.
Q: What is referential integrity? — A: A property ensuring that relationships between tables remain consistent.
Q: What is a One-to-One relationship? — A: A row in Table A links to one row in Table B.
Q: What is a One-to-Many relationship? — A: A row in Table A links to multiple rows in Table B.
Q: What is a Many-to-Many relationship? — A: Multiple rows in Table A link to multiple rows in Table B.
Q: How is a Many-to-Many relationship implemented? — A: Using a junction (or bridge/associative) table.
Normalization & Design
Q: What is Normalization? — A: The process of organizing data to reduce redundancy and improve integrity.
Q: What is Denormalization? — A: Adding redundant data to optimize read performance.
Q: What is 1NF (First Normal Form)? — A: Each column must contain atomic (indivisible) values.
Q: What is 2NF? — A: Must be in 1NF and have no partial dependency (all non-keys depend on the full primary key).
Q: What is 3NF? — A: Must be in 2NF and have no transitive dependency (non-keys rely only on the primary key).
Q: What is BCNF (Boyce-Codd Normal Form)? — A: A stricter version of 3NF where every determinant is a candidate key.
Q: What is 4NF? — A: Must be in BCNF and have no multi-valued dependencies.
Q: What is a partial dependency? — A: When a non-prime attribute depends on only part of a composite primary key.
Q: What is a transitive dependency? — A: When a non-prime attribute depends on another non-prime attribute.
Q: What is an ER Diagram? — A: Entity-Relationship Diagram, a visual representation of the database model.
Q: What is an Entity? — A: An object or concept stored in the database (represented by a rectangle in ERD).
Q: What is a Weak Entity? — A: An entity that cannot be uniquely identified by its own attributes alone.
Transactions & Concurrency
Q: What is a transaction? — A: A logical unit of work performed on a database (all or nothing).
Q: What are ACID properties? — A: Atomicity, Consistency, Isolation, Durability.
Q: What is Atomicity? — A: Ensures all operations in a transaction complete, or none do.
Q: What is Consistency? — A: Ensures the database remains in a valid state before and after the transaction.
Q: What is Isolation? — A: Ensures transactions occur independently without interference.
Q: What is Durability? — A: Ensures that once a transaction commits, it survives system failures.
Q: What is a Lock? — A: A mechanism to prevent multiple users from modifying the same data simultaneously.
Q: What is a Shared Lock? — A: Allows concurrent transactions to read (SELECT) specific data.
Q: What is an Exclusive Lock? — A: Prevents other transactions from reading or writing specific data.
Q: What is a Deadlock? — A: A situation where two processes wait for each other to release a lock, freezing both.
Q: What is a Dirty Read? — A: Reading uncommitted data from another transaction.
Q: What is a Phantom Read? — A: When a query returns different rows when run twice in the same transaction due to insertions by others.
Q: What is a Non-Repeatable Read? — A: When a row is read twice in a transaction and the values differ because of an external update.
Q: What are the transaction isolation levels? — A: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
Q: Which isolation level prevents Dirty Reads? — A: Read Committed (and higher).
Q: Which is the strictest isolation level? — A: Serializable.
Q: What is a Savepoint? — A: A point in a transaction to which you can roll back without rolling back the entire transaction.
Indexes & Performance
Q: What is an Index? — A: A data structure used to speed up the retrieval of data.
Q: What is a Clustered Index? — A: An index that determines the physical order of data in the table (only one per table).
Q: What is a Non-Clustered Index? — A: An index stored separately from data, pointing to the data rows.
Q: What data structure is commonly used for indexes? — A: B-Tree or B+ Tree.
Q: When should you avoid indexes? — A: On small tables or tables with frequent batch updates.
Q: What is Query Optimization? — A: The process of selecting the most efficient way to execute a SQL statement.
Q: What is an Execution Plan? — A: A roadmap showing how the database engine intends to execute a query.
Q: What is a Table Scan? — A: When the database reads every row in the table (usually slow).
Q: What is Index Seek? — A: When the database uses the index to find specific rows (fast).
Q: What is a Composite Index? — A: An index on two or more columns.
Q: What is a Unique Index? — A: An index that enforces uniqueness on the indexed columns.
Q: What is database sharding? — A: Partitioning a database horizontally across multiple servers.
Q: What is Partitioning? — A: Splitting a large table into smaller, manageable pieces (physically).
Q: What is a Heap Table? — A: A table without a clustered index.
Views, Stored Procedures & Triggers
Q: What is a View? — A: A virtual table based on the result-set of an SQL statement.
Q: Can you update data through a View? — A: Yes, generally if it maps to a single base table without aggregates.
Q: What is a Materialized View? — A: A view whose result is physically stored and periodically updated.
Q: What is a Stored Procedure? — A: A prepared SQL code that you can save and reuse.
Q: Advantages of Stored Procedures? — A: Reduced network traffic, better security, and code reusability.
Q: What is a Trigger? — A: SQL code that automatically runs in response to certain events (INSERT, UPDATE, DELETE).
Q: What are the types of triggers? — A: BEFORE, AFTER, and INSTEAD OF triggers.
Q: What is a Cursor? — A: A database object used to retrieve and manipulate data row by row.
Q: What is a Function in SQL? — A: A routine that accepts parameters, performs an action, and returns a value.
Q: Difference between Stored Procedure and Function? — A: Functions must return a value; Procedures may or may not. Functions cannot change DB state (mostly).
Q: What is a Temporary Table? — A: A table that exists temporarily for a session and is deleted automatically.
Q: What is CTE (Common Table Expression)? — A: A temporary result set defined within the execution scope of a single SELECT, INSERT, etc.
Q: How do you define a CTE? — A: Using the WITH keyword.
NoSQL & Advanced Concepts
Q: What is NoSQL? — A: Non-relational databases designed for unstructured or semi-structured data.
Q: Types of NoSQL databases? — A: Document, Key-Value, Column-Family, and Graph.
Q: Example of a Document DB? — A: MongoDB.
Q: Example of a Key-Value DB? — A: Redis or DynamoDB.
Q: Example of a Column-Family DB? — A: Cassandra or HBase.
Q: Example of a Graph DB? — A: Neo4j.
Q: What is the CAP Theorem? — A: A distributed system can only provide two of three: Consistency, Availability, Partition Tolerance.
Q: What is BASE in NoSQL? — A: Basically Available, Soft state, Eventual consistency.
Q: What is Eventual Consistency? — A: Data will become consistent over time, but not immediately after a write.
Q: What is Vertical Scaling? — A: Adding more power (CPU, RAM) to an existing machine.
Q: What is Horizontal Scaling? — A: Adding more machines to the pool of resources.
Q: Which scaling is preferred for SQL? — A: Vertical Scaling.
Q: Which scaling is preferred for NoSQL? — A: Horizontal Scaling.
Q: What is OLTP? — A: Online Transaction Processing (focus on fast, frequent transactions).
Q: What is OLAP? — A: Online Analytical Processing (focus on complex analysis and reporting).
Q: What is Data Warehousing? — A: Aggregating data from disparate sources for analysis.
Q: What is ETL? — A: Extract, Transform, Load.
Q: What is Big Data? — A: Datasets so large or complex that traditional data processing software is inadequate.
Q: What is Hadoop? — A: A framework for distributed storage and processing of big data.
Q: What is MapReduce? — A: A programming model for processing large data sets.
Specific Syntax & Operations
Q: How to get the current date in SQL Server? — A: GETDATE().
Q: How to get the current date in MySQL? — A: NOW() or CURDATE().
Q: How to get the current date in Oracle? — A: SYSDATE.
Q: What is dual table in Oracle? — A: A dummy one-column table used for calculations.
Q: What is AUTO_INCREMENT? — A: Allows a unique number to be generated automatically when a new record is inserted.
Q: What is IDENTITY in SQL Server? — A: The equivalent of AUTO_INCREMENT.
Q: How do you rename a column? — A: ALTER TABLE table_name RENAME COLUMN old TO new; (Syntax varies).
Q: How do you add a column? — A: ALTER TABLE table_name ADD column_name datatype;
Q: How do you drop a column? — A: ALTER TABLE table_name DROP COLUMN column_name;
Q: What is the Limit clause? — A: Used to specify the number of records to return (MySQL/PostgreSQL).
Q: What is the TOP clause? — A: Used to specify the number of records to return (SQL Server).
Q: What is ROWNUM? — A: Used to limit records in Oracle.
Q: What is SQL Injection? — A: A security vulnerability where an attacker interferes with queries via input.
Q: How to prevent SQL Injection? — A: Use Prepared Statements (Parameterized Queries).
Q: What is a BLOB? — A: Binary Large Object, used to store binary data like images.
Q: What is a CLOB? — A: Character Large Object, used to store large text data.
Q: What is the difference between CHAR and VARCHAR? — A: CHAR is fixed length; VARCHAR is variable length.
Q: What is the difference between VARCHAR and NVARCHAR? — A: NVARCHAR stores Unicode characters (multilingual); VARCHAR stores non-Unicode.
Q: What does the COALESCE function do? — A: Returns the first non-null value in a list.
Q: What does the NVL function do? — A: Converts NULL to a specified value (Oracle).
Q: What does IFNULL do? — A: MySQL equivalent of NVL.
Q: What is the SUBSTR function? — A: Extracts a substring from a string.
Q: What is the TRIM function? — A: Removes whitespace from the beginning and end of a string.
Q: What is the CONCAT function? — A: Joins two or more strings together.
Q: How do you find the length of a string? — A: LEN() (SQL Server) or LENGTH() (MySQL/Oracle).
Scenario-Based & Miscellaneous
Q: What is the difference between WHERE clause and HAVING clause? — A: WHERE works on rows; HAVING works on aggregated groups.
Q: Can a View have a Primary Key? — A: No, Views are virtual.
Q: What is the maximum number of columns in a table? — A: Depends on the DB (e.g., MySQL is 4096, SQL Server is 1024).
Q: What is a recursive stored procedure? — A: A procedure that calls itself.
Q: What is Logical vs Physical deletion? — A: Logical sets a flag (active=0); Physical removes the row.
Q: What is Database Mirroring? — A: Maintaining two copies of a single database on different servers for high availability.
Q: What is Database Replication? — A: Copying and distributing data/objects from one database to another.
Q: What is Log Shipping? — A: Automating the backup of transaction logs and restoring them on a standby server.
Q: What is a Linked Server? — A: Concept in SQL Server to execute commands against OLE DB datasources on remote servers.
Q: What is Collation? — A: A set of rules that determine how data is sorted and compared.
Q: What happens if you drop a table that is referenced by a view? — A: The view becomes invalid.
Q: Can you JOIN a table with itself? — A: Yes, this is called a Self Join.
Q: What is the cardinality of a relation? — A: The number of tuples (rows) in a relation.
Q: What is the degree of a relation? — A: The number of attributes (columns) in a relation.
Q: What is an orphan record? — A: A record that references a deleted or non-existent parent key.
Q: What is Cascade Delete? — A: Automatically deletes child rows when the parent row is deleted.
Q: What is an aggregate function? — A: A function performing a calculation on a set of values to return a single value (SUM, AVG).
Q: What is a scalar function? — A: A function that returns a single value based on the input value (UCASE, LEN).
Q: What is the RANK() function? — A: Gives a rank to each row in a partition; gaps in rank exist if there are ties.
Q: What is DENSE_RANK()? — A: Same as RANK(), but without gaps in the ranking numbers.
Q: What is ROW_NUMBER()? — A: Assigns a unique sequential integer to rows within a partition.
Q: What is the difference between UNION and JOIN? — A: JOIN combines columns horizontally; UNION combines rows vertically.
Q: What is a hot backup? — A: Backup taken while the database is running and accessible to users.
Q: What is a cold backup? — A: Backup taken while the database is offline.
Q: What is T-SQL? — A: Transact-SQL, Microsoft's proprietary extension to SQL.
Q: What is PL/SQL? — A: Procedural Language extensions to SQL, used by Oracle.
Q: What is dynamic SQL? — A: SQL code generated and executed programmatically at runtime.