General Questions
Q1: What is the difference between SQL and MySQL?
SQL is a standard language for managing relational databases, while MySQL is a specific implementation of SQL by Oracle.
Q2: What are the different types of SQL commands?
SQL commands are categorized into DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).
Q3: What is normalization? Why is it important?
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It ensures efficient data storage and retrieval.
Q4: What is a primary key and how is it different from a unique key?
A primary key uniquely identifies each row and doesn't allow NULLs. A unique key ensures uniqueness but allows one NULL.
Q5: What are joins in SQL?
Joins are used to retrieve data from multiple tables based on a related column. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Q6: What is the difference between WHERE and HAVING clause?
WHERE is used to filter rows before aggregation, while HAVING is used to filter groups after aggregation.
Q7: What are indexes?
Indexes are data structures that improve query performance by allowing fast data retrieval.
Q8: What`s the difference between DELETE, TRUNCATE, and DROP?
- DELETE: Removes rows, logs each row, can be rolled back.
- TRUNCATE: Removes all rows quickly, can't be rolled back in some systems.
- DROP: Removes entire table structure.
Scenario Based Questions
Q1: How would you find duplicate records in a table?
Use GROUP BY with HAVING clause to find duplicates: `SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1`.
Q2: How do you retrieve the second highest salary from an Employee table?
Using subquery: `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);`
Q3: How can you update rows in one table based on values from another table?
Using UPDATE with JOIN: `UPDATE t1 SET t1.col = t2.col FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;`
Q4: Write a query to get the top 3 highest-paid employees.
`SELECT * FROM employees ORDER BY salary DESC LIMIT 3;`
Q5: How would you delete duplicate rows while retaining one instance?
Use CTE with ROW_NUMBER() in PostgreSQL: `DELETE FROM cte WHERE rn > 1;`
Q6: How do you pivot rows to columns in PostgreSQL?
Use the `crosstab` function from the `tablefunc` module.
Q1: How can indexing improve SQL query performance?
Indexes allow faster retrieval of records by creating pointers to the data, especially for search conditions and joins.
Q2: What is the EXPLAIN plan in SQL?
EXPLAIN shows the query execution plan, helping identify slow parts and optimize queries.
Q3: When should you use indexing and when should you avoid it?
Use indexing on frequently filtered or joined columns. Avoid on columns with high update frequency or low cardinality.
Q4: How does using LIMIT help in query performance?
`LIMIT` reduces result set size, improving performance especially for UI queries or previews.
Q5: How to identify long-running queries in MySQL?
`SHOW FULL PROCESSLIST;`
Q6: Why should SELECT * be avoided in production?
SELECT * can fetch unnecessary columns, increasing I/O and memory usage. It also makes maintenance harder.
Architectural Level Questions
Q1: What is the ACID property in SQL databases?
ACID stands for Atomicity, Consistency, Isolation, Durability - ensuring reliable transactions.
Q2: How does a relational database ensure data integrity?
Using constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.
Q3: What is a view and how does it work?
A view is a virtual table based on a SELECT query. It does not store data itself but represents a stored query.
Q4: What is the difference between a materialized view and a regular view?
Materialized views are not natively supported in MySQL, but can be simulated using tables and scheduled updates. Regular views are virtual and always reflect live data.
Q5: Can MySQL views be indexed?
No, indexes cannot be created directly on views in MySQL. However, if the view is based on indexed base tables, those indexes can still be used during query execution.
Q1: How do you bulk import data into MySQL?
You can use the `LOAD DATA INFILE` command, for example: `LOAD DATA INFILE '/path/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;`
Q2: How can you transform data during SELECT in MySQL?
You can use built-in functions like CASE, COALESCE, CONCAT, CAST, ROUND, IFNULL, and mathematical operations in SELECT statements.
Q3: How do you handle NULL values in transformations in MySQL?
Functions like `IFNULL()` or `COALESCE()` allow you to substitute default values when NULLs are encountered.
Q4: What is the difference between TRUNCATE and DELETE in MySQL?
`TRUNCATE` is faster and removes all rows without logging individual row deletions. `DELETE` allows conditions and logs each row deletion, which is slower.
Q5: How do you merge data from two tables with mismatched data in MySQL?
Use `LEFT JOIN`, `RIGHT JOIN`, or `UNION` along with NULL handling functions like `IFNULL()` to address missing data.
Q6: How can you insert data from one table into another in MySQL?
Use `INSERT INTO target_table (columns) SELECT columns FROM source_table WHERE conditions;` to copy data conditionally.
Security and Compliance Questions
Q1: What is SQL injection and how can you prevent it?
SQL injection is a vulnerability allowing attackers to manipulate queries. Prevent it using parameterized queries (e.g., prepared statements), input validation, and stored procedures.
Q2: How do you implement role-based access control in MySQL?
Create users and assign them roles using `CREATE ROLE`, then grant permissions with `GRANT SELECT, INSERT ON database.table TO 'user'@'host';`
Q3: How do you audit data access in MySQL?
Enable the general or audit log plugin (e.g., `audit_log_plugin`), and use triggers or third-party tools to monitor access.
Q4: What permissions should be granted to read-only users in MySQL?
`GRANT SELECT ON database.* TO 'readonly_user'@'host';` ensures the user can only read data.
Q5: How do you encrypt data in MySQL?
Use SSL/TLS for data in transit and functions like `AES_ENCRYPT()` and `AES_DECRYPT()` for encryption at rest. You can also use Transparent Data Encryption (TDE) in MySQL Enterprise Edition.
Q6: How can password policies be enforced in MySQL?
Enable the `validate_password` plugin and configure its settings like `validate_password.length`, `mixed_case_count`, and `number_count`.
Miscellaneous Questions
Q1: What is the difference between DELETE and DROP?
DELETE removes rows but keeps the table structure. DROP removes the entire table definition and data from the database.
Q2: What is a surrogate key?
A surrogate key is a unique identifier (often auto-incremented) that substitutes natural primary keys in a database table.
Q3: How do you handle timezone data in MySQL?
MySQL supports time zone conversions using `CONVERT_TZ()` and stores timezone-aware timestamps using `DATETIME` or `TIMESTAMP` with system or session-level time zones.
Q4: How to schedule SQL jobs in MySQL?
Use MySQL Events with the `CREATE EVENT` statement, or use external tools like `cron` with a MySQL client command.
Q5: How to find the size of a table in MySQL?
Query the `information_schema`: `SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema = 'your_db';`
Q6: How to reclaim unused space in MySQL?
Use `OPTIMIZE TABLE table_name;` to defragment and reclaim unused space.