MySQL Answers

Basic MySQL Answers

#	Question	Answer	Examples
1	What is MySQL?	A relational database management system (RDBMS)	Open-source, widely used for web applications
2	What is SQL?	Structured Query Language; used for managing and querying databases	SELECT, INSERT, UPDATE, DELETE
3	What is a database?	An organized collection of data, typically stored in tables	A container for tables, views, procedures, etc.
4	What is a table?	A collection of data organized into rows and columns	A users table with columns like id, name, email
5	What is a row?	A single record or tuple in a table	One specific user in the users table
6	What is a column?	A specific attribute or field in a table	The name column in the users table
7	What is a primary key?	A column (or set of columns) that uniquely identifies each row in a table	The id column in a users table
8	What is a foreign key?	A column in one table that refers to the primary key in another table	A user_id column in an orders table referencing id in the users table
9	How do you create a database?	Using the CREATE DATABASE statement	CREATE DATABASE my_db;
10	How do you create a table?	Using the CREATE TABLE statement	CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));
11	How do you insert data into a table?	Using the INSERT INTO statement	INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
12	How do you select data from a table?	Using the SELECT statement	SELECT name, email FROM users;
13	How do you update data in a table?	Using the UPDATE statement	UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
14	How do you delete data from a table?	Using the DELETE FROM statement	DELETE FROM users WHERE id = 1;
15	What is the WHERE clause?	Used to filter rows based on a specified condition	SELECT * FROM users WHERE age > 30;

---

Intermediate MySQL Answers

#	Question	Answer	Examples
1	What is the JOIN clause?	Combines rows from two or more tables based on a related column	INNER JOIN, LEFT JOIN
2	What is an INNER JOIN?	Returns rows only when there is a match in both tables	SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;
3	What is a LEFT JOIN?	Returns all rows from the left table, and matching rows from the right table	SELECT u.name, o.order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;
4	What is the GROUP BY clause?	Groups rows that have the same values in one or more columns	SELECT city, COUNT(*) FROM users GROUP BY city;
5	What is the HAVING clause?	Filters groups created by GROUP BY	SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
6	What is the ORDER BY clause?	Sorts the result set based on one or more columns	SELECT name FROM users ORDER BY name ASC; or SELECT name FROM users ORDER BY name DESC;
7	What is the COUNT() aggregate function?	Counts the number of rows (or values)	SELECT COUNT(*) FROM users;
8	What is the SUM() aggregate function?	Calculates the sum of values in a numeric column	SELECT SUM(amount) FROM orders;
9	What is the AVG() aggregate function?	Calculates the average of values in a numeric column	SELECT AVG(price) FROM products;
10	What is the MIN() aggregate function?	Finds the minimum value in a column	SELECT MIN(price) FROM products;
11	What is the MAX() aggregate function?	Finds the maximum value in a column	SELECT MAX(price) FROM products;
12	What is the LIMIT clause?	Restricts the number of rows returned by the query	SELECT * FROM users LIMIT 10;
13	What is an Index?	A data structure that speeds up data retrieval operations	CREATE INDEX idx_email ON users (email);
14	What is a Transaction?	A sequence of SQL statements treated as a single, indivisible unit	START TRANSACTION; ... COMMIT; or ROLLBACK;
15	What are ACID properties?	Atomicity, Consistency, Isolation, Durability (properties of database transactions)	Ensure transactions are either fully completed or fully rolled back.

---

Advanced MySQL Answers

#	Question	Answer	Examples
1	What is Normalization?	The process of organizing data in a database to reduce redundancy and improve data integrity	1NF, 2NF, 3NF, BCNF
2	Explain 3NF (Third Normal Form).	A table is in 2NF, and non-key attributes are not transitively dependent on the primary key	No non-key attribute should depend on any other non-key attribute.
3	What is Denormalization?	Intentionally introducing redundancy into a database to improve query performance	Adding a user_name column to an orders table to avoid a join
4	What is a Stored Procedure?	A set of SQL statements stored within the database that can be executed	DELIMITER // CREATE PROCEDURE GetUserById(IN user_id INT) BEGIN SELECT * FROM users WHERE id = user_id; END // DELIMITER ;
5	What is a Trigger?	A stored procedure that automatically executes in response to a specific event (e.g., INSERT, UPDATE, DELETE)	CREATE TRIGGER after_insert AFTER INSERT ON users FOR EACH ROW BEGIN ... END;
6	What is a View?	A virtual table based on the result set of a SQL query	CREATE VIEW active_users AS SELECT name, email FROM users WHERE status = 'active';
7	What is the difference between UNION and UNION ALL?	UNION removes duplicate rows; UNION ALL includes all rows (including duplicates)	SELECT name FROM table1 UNION SELECT name FROM table2; vs SELECT name FROM table1 UNION ALL SELECT name FROM table2;
8	What is the purpose of the EXPLAIN command?	Shows information about how MySQL executes a SELECT statement (useful for performance tuning)	EXPLAIN SELECT * FROM users WHERE name = 'Alice';
9	How do you optimize MySQL queries?	Using indexes, rewriting queries, avoiding SELECT *, using appropriate joins, analyzing query plans	Ensure indexes are used on WHERE clauses and JOIN conditions; avoid subqueries in SELECT clauses where possible.
10	What is SQL Injection?	A security vulnerability where malicious SQL code is injected into input fields	E.g., SELECT * FROM users WHERE username = 'admin' OR '1'='1'; (bypasses login)
11	How do you prevent SQL Injection?	Using parameterized queries (prepared statements), input sanitization, escaping user input	conn.prepareStatement("SELECT * FROM users WHERE username = ?"); pstmt.setString(1, user_input);
12	What is the difference between InnoDB and MyISAM engines?	InnoDB supports transactions, foreign keys, row-level locking; MyISAM is simpler, lacks these features	InnoDB is generally preferred for transactional applications due to reliability; MyISAM can be faster for read-heavy workloads.
13	What is Database Sharding?	Splitting a large database into smaller, more manageable pieces (shards) based on a key	Distributing data across multiple servers based on user_id
14	What is Database Replication?	Copying data from one database (master) to another (slave) for redundancy or load balancing	Master-slave replication for read scaling.
15	What is a FULL OUTER JOIN?	Returns all rows when there is a match in either table. (Simulated with LEFT JOIN + RIGHT JOIN)	SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

---