SQL Master Top 50 Q&A

A Database Management System is software used to manage databases, providing an interface for users to interact with data.

SQL is a language used to interact with databases. MySQL is an open-source Relational Database Management System that uses SQL.

DDL (Data Definition Language): CREATE, DROP. DML (Data Manipulation Language): INSERT, UPDATE, DELETE. DCL (Data Control Language): GRANT, REVOKE. TCL (Transaction Control Language): COMMIT, ROLLBACK.

A unique identifier for each record in a table. It cannot contain NULL values and must be unique.

A field in one table that uniquely identifies a row of another table, establishing a link between the two.

CHAR is a fixed-length string. VARCHAR is a variable-length string. CHAR is faster but consumes more space if the data is shorter than the fixed length.

A clause used to combine rows from two or more tables based on a related column between them.

Inner Join returns only matching records from both tables. Left Join returns all records from the left table and matching records from the right table.

DELETE is a DML command that removes rows and can be rolled back. TRUNCATE is a DDL command that removes all rows from a table and cannot be rolled back easily (it resets the identity).

Rules applied to table columns to limit the type of data (e.g., NOT NULL, UNIQUE, CHECK, DEFAULT).

An index is a performance-tuning tool that allows the database to find and retrieve data much faster.

A clustered index determines the physical order of data in the table. A non-clustered index is a separate structure from the data rows.

A virtual table based on the result set of an SQL statement. It doesn't store data itself.

A query nested inside another query (SELECT, INSERT, UPDATE, or DELETE).

UNION removes duplicates; UNION ALL includes them.

Functions like SUM, AVG, COUNT, MIN, MAX that perform calculations on multiple values and return a single value.

GROUP BY organizes rows into summary rows. HAVING is like WHERE but for groups.

A prepared SQL code that you can save and reuse over and over again.

A special type of stored procedure that automatically runs when an event (INSERT, UPDATE, DELETE) occurs in the database server.

A single unit of work that contains one or more SQL statements. Transactions follow ACID properties.

Functions that perform calculations across a set of table rows related to the current row (e.g., RANK, OVER).

A temporary result set defined with the 'WITH' clause that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

The process of organizing data to minimize redundancy (1NF, 2NF, 3NF).

The process of adding redundant data to improve read performance, often used in data warehousing.

Joining a table with itself. Useful for hierarchical data.

COALESCE returns the first non-null expression from a list. ISNULL is a database-specific function to replace NULL with a value.

A database object used to retrieve and manipulate data from a result set row by row.

Used to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Dividing a large table into smaller, more manageable pieces (partitions) while keeping a single logical entry point.

Functions must return a value and cannot have DML statements (usually). Procedures can return multiple values (or none) and can contain DML.

Entity-Relationship Diagram. A visual representation of entities and their relationships within a system.

One-to-One, One-to-Many, and Many-to-Many. M:N usually requires a junction table.

Ensures that relationships between tables remain consistent through foreign key constraints.

A central repository of integrated data from one or more disparate sources, used for reporting and data analysis.

Star Schema is simpler with one fact table and denormalized dimensions. Snowflake Schema normalizes dimension tables.

Extract, Transform, Load. The process of moving data from source systems to a target database.

Horizontal partitioning of data across multiple database instances to improve scalability.

A situation where two or more transactions are waiting for each other to release locks, causing a permanent block.

A database is the collection of all data. A schema is a logical container or structural definition within the database.

Non-relational databases that provide a mechanism for storage and retrieval of data modeled in means other than tabular relations (e.g., MongoDB, Redis).

SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1.

Using a CTE with ROW_NUMBER() and deleting where the row number is greater than 1.

Using DENSE_RANK() in a subquery or using OFFSET in some SQL dialects.

A tool used to analyze how the database engine executes a query, helping in optimization.

A security vulnerability where an attacker can execute arbitrary SQL code by manipulating input fields. Prevented using parameterized queries.

By using COALESCE or ISNULL to replace NULL with 0 before performing the calculation.

Used for pagination. LIMIT restricts the number of rows returned; OFFSET skips a specified number of rows.

ANY returns true if any of the subquery values meet the condition. ALL returns true only if all subquery values meet the condition.

A combination of Update and Insert. If the record exists, update it; otherwise, insert it.

Add indexes, avoid 'SELECT *', use joins instead of subqueries where possible, and check the explain plan.