1. What is MySQL?
Answer: MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data efficiently.
2. What are the different types of SQL commands in MySQL?
Answer:
- DDL (Data Definition Language):
CREATE,ALTER,DROP,TRUNCATE - DML (Data Manipulation Language):
INSERT,UPDATE,DELETE - DCL (Data Control Language):
GRANT,REVOKE - TCL (Transaction Control Language):
COMMIT,ROLLBACK,SAVEPOINT - DQL (Data Query Language):
SELECT
3. What is the difference between MySQL and SQL?
Answer:
- SQL is a language for managing databases.
- MySQL is a database management system that uses SQL to store and manage data.
4. What is the difference between CHAR and VARCHAR in MySQL?
Answer:
CHAR(n): Fixed-length string, takes up the defined space (nbytes).VARCHAR(n): Variable-length string, takes only the required space plus 1-2 bytes for length storage.
5. What are Primary Key and Foreign Key?
Answer:
- Primary Key: A unique identifier for a record in a table. It cannot be NULL.
- Foreign Key: A field in one table that refers to the Primary Key of another table to enforce referential integrity.
6. What is an Index in MySQL?
Answer: An index is used to speed up query execution by reducing the number of rows scanned. Types include:
- Primary Index
- Unique Index
- Full-text Index
- Composite Index
7. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
- DELETE: Removes specific rows, can use
WHERE, logs transactions, and can be rolled back. - TRUNCATE: Deletes all rows, cannot use
WHERE, faster than DELETE, and cannot be rolled back. - DROP: Deletes the table and its structure completely.
8. What is the difference between WHERE and HAVING?
Answer:
- WHERE: Filters records before aggregation.
- HAVING: Filters records after aggregation (used with
GROUP BY).
9. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
- INNER JOIN: Returns matching records from both tables.
- LEFT JOIN: Returns all records from the left table and matching records from the right table.
10. How do you find the second highest salary in MySQL?
Answer:
sqlCopyEditSELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
11. What is ACID in MySQL?
Answer: ACID properties ensure database reliability:
- Atomicity: Transactions are all-or-nothing.
- Consistency: Ensures data integrity before and after transactions.
- Isolation: Prevents conflicts between concurrent transactions.
- Durability: Ensures data is saved permanently after a transaction commits.
12. What is a View in MySQL?
Answer: A View is a virtual table based on the result of a SQL query.
sqlCopyEditCREATE VIEW employee_view AS
SELECT id, name, salary FROM employees;
13. What is a Stored Procedure?
Answer: A stored procedure is a set of SQL statements that can be executed as a single unit.
sqlCopyEditCREATE PROCEDURE GetEmployees()
BEGIN
SELECT * FROM employees;
END;
14. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines results but removes duplicates.
- UNION ALL: Combines results without removing duplicates.
15. How can you optimize MySQL queries?
Answer:
- Use indexes.
- Avoid **SELECT *** (use specific columns).
- Use EXPLAIN to analyze queries.
- Optimize JOINs and Subqueries.
- Use LIMIT for large data sets.
16. What is a MySQL Trigger?
Answer: A trigger is an automatic action executed before or after an event (INSERT, UPDATE, DELETE).
sqlCopyEditCREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = NEW.salary * 1.10;
17. What is the difference between MyISAM and InnoDB storage engines?
Answer:
| Feature | MyISAM | InnoDB |
|---|---|---|
| Transactions | No | Yes |
| Foreign Keys | No | Yes |
| Locking Mechanism | Table-level | Row-level |
| Speed | Fast for reads | Better for writes |
18. How do you find duplicate records in a table?
Answer:
sqlCopyEditSELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
19. What is a Subquery in MySQL?
Answer: A subquery is a query inside another query.
sqlCopyEditSELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
20. What is the use of the EXPLAIN statement?
Answer:EXPLAIN helps analyze query execution plans to optimize performance.
sqlCopyEditEXPLAIN SELECT * FROM employees WHERE department = 'IT';