Basic MySQL Questions (Detailed)
1. What is MySQL?
MySQL is an open-source Relational Database Management System (RDBMS) that uses
Structured Query Language (SQL) to manage and manipulate relational databases. It’s widely
used in web applications and supports multi-user access. MySQL is known for its speed,
reliability, and ease of use.
2. What are the different data types in MySQL?
MySQL provides several categories:
o Numeric: INT, TINYINT, FLOAT, DOUBLE, DECIMAL – used for numbers.
o String: CHAR, VARCHAR, TEXT, BLOB – used for textual data.
o Date/Time: DATE, DATETIME, TIMESTAMP, TIME, YEAR.
o Spatial (geometric data): POINT, LINESTRING, etc.
3. How do you create a database and table in MySQL?
4. CREATE DATABASE CompanyDB;
5. USE CompanyDB;
6.
7. CREATE TABLE Employees (
8. ID INT AUTO_INCREMENT PRIMARY KEY,
9. Name VARCHAR(100),
10. Age INT,
11. JoinDate DATE
12. );
13. What are the different constraints in MySQL?
Constraints ensure data integrity:
o NOT NULL: prevents NULL values.
o UNIQUE: ensures all values are unique.
o PRIMARY KEY: uniquely identifies each row.
o FOREIGN KEY: links to another table’s primary key.
o CHECK: validates data based on condition.
o DEFAULT: assigns a default value.
14. Difference between CHAR and VARCHAR?
o CHAR(n): Fixed-length string. Always stores exactly n characters.
o VARCHAR(n): Variable-length string. Stores up to n characters but only uses needed
space.
15. How do you insert data into a MySQL table?
16. INSERT INTO Employees (Name, Age, JoinDate)
17. VALUES ('Alice', 28, '2023-06-01');
18. How to retrieve data from a MySQL table?
19. SELECT * FROM Employees;
20. SELECT Name FROM Employees WHERE Age > 30;
21. Difference between WHERE and HAVING?
o WHERE filters rows before aggregation.
o HAVING filters after aggregation.
22. SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) >
5;
23. Types of joins in MySQL:
o INNER JOIN: Returns only matching rows.
o LEFT JOIN: All rows from the left + matching from right.
o RIGHT JOIN: All rows from the right + matching from left.
o CROSS JOIN: Cartesian product.
o MySQL lacks FULL OUTER JOIN directly.
24. What is a primary key?
A column or group of columns that uniquely identifies each row in a table. Only one primary
key per table, but it can be composite (multi-column).
Intermediate Questions (Detailed)
11. What is normalization?
A database design technique to minimize redundancy and dependency.
o 1NF: No repeating groups, atomic values.
o 2NF: No partial dependency (applies to composite keys).
o 3NF: No transitive dependency.
o BCNF: Stricter form of 3NF.
12. Difference between INNER JOIN and OUTER JOIN?
o INNER JOIN: Matches from both tables.
o LEFT/RIGHT OUTER JOIN: Includes unmatched rows from one side.
13. SELECT * FROM A INNER JOIN B ON A.id = B.a_id;
14. What is an index?
An index improves search performance by allowing faster data retrieval. However, it can slow
down INSERT/UPDATE due to maintenance overhead.
15. CREATE INDEX idx_name ON Employees(Name);
16. How to update data in MySQL?
17. UPDATE Employees SET Age = 35 WHERE Name = 'Bob';
18. How to delete data?
19. DELETE FROM Employees WHERE Age < 25;
20. What is GROUP BY?
It groups rows sharing a value. Common with aggregate functions.
21. SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;
22. What does LIMIT do?
Restricts number of rows returned. Useful for pagination.
23. SELECT * FROM Products LIMIT 5 OFFSET 10;
24. What is a view in MySQL?
A virtual table based on a SELECT query.
25. CREATE VIEW HighEarners AS
26. SELECT Name, Salary FROM Employees WHERE Salary > 80000;
27. Difference between DELETE, TRUNCATE, DROP:
o DELETE: Removes rows conditionally, can rollback.
o TRUNCATE: Removes all rows, resets auto-increment, cannot rollback.
o DROP: Deletes the table structure.
28. What are stored procedures?
Precompiled SQL code blocks stored in the database.
29. DELIMITER //
30. CREATE PROCEDURE GetAllEmployees()
31. BEGIN
32. SELECT * FROM Employees;
33. END //
34. DELIMITER ;
Advanced Questions (Detailed)
21. What is a trigger?
A trigger automatically executes SQL on table events (INSERT, UPDATE, DELETE).
22. CREATE TRIGGER before_insert BEFORE INSERT ON Employees
23. FOR EACH ROW SET NEW.JoinDate = CURDATE();
24. What is a transaction?
A transaction is a group of operations treated as a single unit. Use:
o START TRANSACTION
o COMMIT: Save changes
o ROLLBACK: Undo changes
25. START TRANSACTION;
26. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
27. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
28. COMMIT;
29. MyISAM vs InnoDB:
o MyISAM: Fast reads, no transactions, no foreign keys.
o InnoDB: Slower reads, supports transactions and foreign keys (ACID).
30. What is replication?
MySQL Replication duplicates data from one (master) to another (slave) server. Useful for
backups, failover, and read scaling.
31. How indexing affects performance?
o Speeds up SELECT queries.
o Slows down writes (INSERT/UPDATE).
o Can be B-Tree or Full-text.
32. What is EXPLAIN used for?
Shows how MySQL will execute a query – indexes used, join types, etc.
33. EXPLAIN SELECT * FROM Employees WHERE Age > 30;
34. What are foreign keys?
A constraint that enforces referential integrity between two tables.
35. FOREIGN KEY (DeptID) REFERENCES Departments(ID)
36. ON DELETE CASCADE ON UPDATE CASCADE;
37. How to handle NULL values?
Use IS NULL / IS NOT NULL.
38. SELECT * FROM Employees WHERE Email IS NULL;
39. What is ACID?
o Atomicity: All or nothing.
o Consistency: Data must be valid.
o Isolation: Transactions don’t interfere.
o Durability: Changes are permanent.
InnoDB in MySQL supports ACID.
40. Performance optimization tips:
o Use indexes smartly.
o Avoid SELECT *.
o Normalize data properly.
o Use LIMIT, EXPLAIN, and caching.
o Optimize queries with joins and subqueries.
Practical MySQL Queries (Detailed)
31. Second highest salary:
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
32. Find duplicate entries:
SELECT Name, COUNT(*) FROM Employees
GROUP BY Name HAVING COUNT(*) > 1;
33. Full outer join workaround:
SELECT * FROM A
LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A
RIGHT JOIN B ON A.id = B.id;
34. Prevent SQL Injection:
• Use prepared statements (? placeholders).
• Never concatenate user inputs in queries.
• Use stored procedures or ORM frameworks.
35. Backup & Restore:
• Backup:
• mysqldump -u root -p mydb > backup.sql
• Restore:
• mysql -u root -p mydb < backup.sql
To create a new user in MySQL, you can use the CREATE USER statement followed by optional
privilege assignments using GRANT.
Basic Syntax
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• 'username': the name of the user.
• 'host': where the user is allowed to connect from (use 'localhost' or '%' for any host).
• 'password': user’s password.
Example 1: Create user for local access
CREATE USER 'john'@'localhost' IDENTIFIED BY 'StrongP@ss123';
Example 2: Create user for remote access
CREATE USER 'john'@'%' IDENTIFIED BY 'StrongP@ss123';
Granting Privileges
After creating the user, assign privileges using GRANT.
Example: Give full access to a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';
Example: Grant only SELECT and INSERT on a table
GRANT SELECT, INSERT ON mydb.employees TO 'john'@'localhost';
Then apply changes:
FLUSH PRIVILEGES;
View Existing Users
SELECT user, host FROM mysql.user;
Change Password
ALTER USER 'john'@'localhost' IDENTIFIED BY 'NewPass123!';
Delete User
DROP USER 'john'@'localhost';
Creating a MySQL user at the production level involves more than just executing CREATE USER. You
need to follow best practices for security, access control, and auditing. Here's a detailed,
production-grade guide.
Step-by-Step: Creating a Secure MySQL User in Production
1. Connect as a Privileged User
Use the root user or another admin-level account:
mysql -u root -p
2. Create a New User with Limited Access
Use a strong password and restrict access to specific hosts (e.g., an application server IP):
CREATE USER 'app_user'@'192.168.10.10' IDENTIFIED BY 'Strong$Passw0rd!';
Best Practice:
Avoid using '%' for host in production unless absolutely necessary. Restrict to a known IP or host.
3. Grant Minimal Required Privileges
Apply the principle of least privilege—grant only what's needed.
Example 1: Web app needs full access to a single database
GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'app_user'@'192.168.10.10';
Example 2: Read-only access
GRANT SELECT ON production_db.* TO 'report_user'@'192.168.10.15';
4. Flush Privileges
FLUSH PRIVILEGES;
5. Secure Connections (Optional but Recommended)
Force the user to connect using SSL:
GRANT USAGE ON *.* TO 'app_user'@'192.168.10.10' REQUIRE SSL;
This requires your MySQL server to have SSL certificates configured (require_secure_transport = ON in
my.cnf).
6. Audit and Log User Actions
Enable general logs or use a MySQL audit plugin (e.g., Audit Plugin from Percona or Oracle
Enterprise).
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';
Or use MySQL Enterprise Audit plugin for detailed tracking.
7. Rotate Credentials Regularly
Change passwords every few months:
ALTER USER 'app_user'@'192.168.10.10' IDENTIFIED BY 'NewSecureP@ss!';
8. Revoke When No Longer Needed
If access is no longer needed:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'192.168.10.10';
DROP USER 'app_user'@'192.168.10.10';
Extra Production-Level Tips
• Disable remote root login in production (skip-name-resolve, bind-address, etc.).
• Use firewalls or MySQL user access IP restrictions.
• Enable backups and monitor user access logs.
• Never use GRANT ALL PRIVILEGES ON *.* in production unless absolutely necessary.
