What is MySQL?

• MySQL is an open-source relational database management system (RDBMS).
• It stores data in tables with rows and columns, allowing structured organization.
• Uses SQL (Structured Query Language) for data manipulation and queries.
• Supports ACID-compliant transactions, ensuring data consistency and reliability.
• Provides indexes, joins, and constraints for performance and data integrity.
• Widely used in web applications and enterprise systems, including LAMP stack.
• Supports scalability features like replication, clustering, and partitioning.
• Offers security features like authentication, role-based access, and SSL connections.

Tip: MySQL is a structured and reliable RDBMS widely used for managing relational data efficiently.

Difference between SQL and NoSQL.

• SQL Databases are relational, storing data in tables with rows and columns; NoSQL databases are non-relational, storing data in document, key-value, column, or graph formats.
• SQL databases use Structured Query Language (SQL) for defining and manipulating data; NoSQL databases use dynamic queries, APIs, or their own query languages.
• SQL databases are schema-based, requiring a predefined structure; NoSQL databases are schema-less, allowing flexible and evolving data structures.
• SQL databases are vertically scalable (scale by upgrading hardware); NoSQL databases are horizontally scalable (scale by adding servers).
• SQL databases are ideal for complex queries and transactions, supporting ACID properties; NoSQL is ideal for large, unstructured data, high-velocity, and distributed systems.

Tip: SQL = structured and relational, NoSQL = flexible, scalable, and non-relational

Difference between MySQL and MongoDB.

• Database Type: MySQL is a relational database (RDBMS), whereas MongoDB is a NoSQL document database.
• Data Storage: MySQL stores data in tables with rows and columns; MongoDB stores data in collections of JSON-like documents (BSON).
• Schema: MySQL has a fixed schema, while MongoDB is schema-less, allowing flexible and dynamic data structures.
• Query Language: MySQL uses SQL; MongoDB uses its own query language with methods like find(), insert(), and aggregation pipelines.
• Transactions: MySQL supports ACID transactions natively; MongoDB supports ACID transactions from version 4.0 but typically for single documents.
• Scalability: MySQL is vertically scalable; MongoDB is horizontally scalable using sharding.
• Joins: MySQL supports joins to combine tables; MongoDB uses embedding or $lookup for relationships.
• Use Cases: MySQL is ideal for structured data and complex queries; MongoDB is ideal for big data, flexible schemas, and fast development.

Tip: MySQL = structured, relational, SQL-based, MongoDB = flexible, document-based, NoSQL.

What are databases, tables, rows, and columns?

• Database: A database is a structured collection of data stored in an organized way so it can be easily accessed, managed, and updated.
• Table: A table is a collection of related data organized in rows and columns within a database. Each table stores data about a specific entity.
• Row: A row (or record) represents a single data entry in a table, containing values for each column.
• Column: A column (or field) represents a specific attribute of the data, defining the type of information stored for each row.

Tip: Think of it as a spreadsheet: database = workbook, table = sheet, row = one entry, column = one attribute.

What is a primary key?

• A primary key is a column or a set of columns in a table that uniquely identifies each row.
• It cannot have NULL values and must be unique for every record.
• Each table can have only one primary key, which ensures data integrity.
• Primary keys are often used to establish relationships with foreign keys in other tables.

Tip: A primary key = unique identifier for each record in a table.

What is a foreign key?

• A foreign key is a column (or set of columns) in one table that refers to the primary key of another table.
• It is used to establish and enforce a link between the data in two tables.
• Foreign keys ensure referential integrity, meaning that a value in the foreign key column must exist in the referenced primary key column.
• They are commonly used in one-to-many or many-to-many relationships between tables.

Tip: A foreign key = a reference to a primary key in another table to maintain relationships.

What is a unique key?

• A unique key is a column or set of columns in a table that must have unique values, ensuring no duplicate entries.
• Unlike a primary key, a table can have multiple unique keys.
• A unique key can accept one NULL value (depending on the database), whereas a primary key cannot be NULL.
• Unique keys help maintain data integrity by preventing duplicate records in specific columns.

Tip: A unique key = ensures column values are unique but allows multiple unique constraints per table.

What is an index, and why is it used?

• An index is a database structure that improves the speed of data retrieval from a table.
• It works like a table of contents in a book, allowing the database to quickly locate rows without scanning the entire table.
• Indexes can be created on one or more columns of a table.
• They are used to optimize query performance, especially for SELECT, WHERE, ORDER BY, and JOIN operations.
• Common types include primary key index, unique index, and composite (compound) index.

Tip: An index = speeds up data lookup but may slightly slow down inserts, updates, and deletes.

Types of indexes in MySQL.

• Primary Key Index: Automatically created on the primary key column; ensures uniqueness and fast lookup.
• Unique Index: Ensures that all values in the indexed column(s) are unique.
• Regular (Non-Unique) Index: Speeds up queries but does not enforce uniqueness.
• Composite (Compound) Index: Created on multiple columns; useful for queries filtering on more than one column.
• Full-Text Index: Used for text searching in large text fields; supports MATCH…AGAINST queries.
• Spatial Index: Used for geospatial data like points, lines, and polygons.

Tip: Index types = Primary, Unique, Regular, Composite, Full-Text, Spatial, each serving specific query optimization purposes.

What is an auto-increment column?

• An auto-increment column is a table column whose value is automatically incremented by the database whenever a new row is inserted.
• It is commonly used for primary keys to generate unique identifiers for each record.
• Only one auto-increment column is allowed per table.
• You don’t need to manually provide a value when inserting; MySQL automatically assigns the next sequential number.

Tip: Auto-increment = automatic unique numbering for new rows, usually for primary keys.

Difference between CHAR and VARCHAR.

• CHAR:
  • Fixed-length string. If the defined length is not filled, MySQL pads with spaces.
  • Faster for fixed-size data like codes or IDs.
  • Uses less storage for consistent-length data.
• VARCHAR:
  • Variable-length string. Stores only the actual characters plus 1 or 2 bytes for length.
  • Efficient for varying-length data like names or emails.
  • Slightly slower than CHAR for fixed-length data due to dynamic storage.

Tip: CHAR = fixed-length, VARCHAR = variable-length; choose based on data consistency.

What is normalization? Explain 1NF, 2NF, 3NF.

What is denormalization, and when is it used?

What are constraints in MySQL?

What is a stored procedure?

What is a view in MySQL?

What is a trigger?

What is a transaction?

Explain ACID properties.

Difference between MyISAM and InnoDB storage engines.