Ans. INNER JOIN returns only the rows that have matching values in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table.

Ans. The LIMIT clause is used to specify the number of records to return from the result set. It helps in restricting the output size.

Ans. You can use the GROUP BY clause along with the HAVING clause to find duplicate rows. Example: SELECT column_name, COUNT(*) FROM table GROUP BY column_name HAVING COUNT(*) > 1;

Ans. A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements to retrieve data for the outer query.

Ans. A stored function is a set of SQL statements that performs a specific task and returns a single value. It can be called from SQL queries.

Ans. MySQL errors can be handled using the DECLARE CONTINUE HANDLER statement for exceptions in stored procedures and functions.

Ans. A connection pool is a collection of reusable database connections. It helps in improving performance by reusing connections instead of creating new ones for every request.

Ans. UNION combines the result sets of two or more SELECT queries and removes duplicates, while UNION ALL includes duplicates.

Ans. An auto-increment field automatically generates a unique value for every new record inserted into a table. It is typically used for primary keys.

Ans. MySQL performs case-insensitive searches by default for text data types, but you can use the COLLATE clause to enforce case insensitivity. Example: SELECT * FROM table WHERE column COLLATE utf8_general_ci = 'value';

Ans. The common types of indexes in MySQL are PRIMARY KEY, UNIQUE, INDEX, FULLTEXT, and SPATIAL indexes.

Ans. The EXPLAIN statement is used to analyze and optimize SQL queries by displaying information about how MySQL executes the query.

Ans. A self join is a join where a table is joined with itself. It is useful when you need to compare rows within the same table.

Ans. MyISAM is faster for read-heavy operations and supports full-text indexing. InnoDB supports ACID compliance, foreign key constraints, and is better for write-heavy operations.

Ans. Use the SELECT COUNT(*) FROM table_name; query to find the number of rows in a table.

Ans. You can create an index using the CREATE INDEX statement. Example: CREATE INDEX idx_name ON table_name (column_name);

Ans. The SHOW TABLES command is used to list all the tables in the current database.

Ans. Use the ALTER TABLE statement. Example: ALTER TABLE table_name MODIFY column_name new_data_type;

Ans. A deadlock occurs when two or more transactions hold locks on resources and are waiting for each other to release those locks. It results in a situation where neither transaction can proceed.

Ans. The CONCAT function is used to concatenate two or more strings into a single string. Example: SELECT CONCAT('Hello', ' ', 'World'); results in 'Hello World'.

Ans. VARCHAR is used for variable-length strings, whereas TEXT is used for long text fields. TEXT can hold up to 65,535 characters, while VARCHAR has a length limit of 255 characters.

Ans. The COALESCE function returns the first non-NULL value in a list of expressions. Example: SELECT COALESCE(NULL, NULL, 'Hello'); returns 'Hello'.

Ans. You can use the DISTINCT keyword to retrieve unique records. Example: SELECT DISTINCT column_name FROM table;

Ans. To prevent SQL injection, use prepared statements, parameterized queries, and avoid directly embedding user input in SQL queries.

Ans. The CAST function is used to convert an expression from one data type to another. Example: SELECT CAST('2025-04-15' AS DATE);

Ans. A function returns a value and can be used in queries, while a stored procedure is used to perform a set of SQL actions but does not return a value.

Ans. The GROUP_CONCAT function is used to concatenate values from multiple rows into a single string. Example: SELECT GROUP_CONCAT(column_name) FROM table;

Ans. Use the ALTER TABLE statement to add a foreign key constraint. Example: ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(column_name);

Ans. The NOW() function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'. Example: SELECT NOW();

Ans. Triggers are automated actions executed before or after an INSERT, UPDATE, or DELETE operation on a table. Example: CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW SET NEW.column_name = 'value';

Ans. Use the UPDATE statement with multiple column assignments. Example: UPDATE table_name SET column1='value1', column2='value2' WHERE condition;

Ans. The IFNULL function returns a specified value if the expression is NULL. Example: SELECT IFNULL(column_name, 'default_value');

Ans. Use the RENAME TABLE statement. Example: RENAME TABLE old_table_name TO new_table_name;

Ans. The DATE_FORMAT function is used to format date values in a specific format. Example: SELECT DATE_FORMAT(date_column, '%Y-%m-%d');

Ans. IN is used to compare a value to a list of values, while EXISTS is used to check whether a subquery returns any results.

Ans. Use the DROP TABLE statement. Example: DROP TABLE table_name;

Ans. Use the ALTER TABLE statement with the CHANGE COLUMN clause. Example: ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;

Ans. The SUBSTRING function is used to extract a substring from a string. Example: SELECT SUBSTRING('Hello World', 1, 5); returns 'Hello'.

Ans. You can prevent duplicates by using the UNIQUE constraint or by using the DISTINCT keyword in SELECT queries.

Ans. The RAND() function returns a random floating-point number between 0 and 1. Example: SELECT RAND();

Ans. The ABS() function returns the absolute value of a number. Example: SELECT ABS(-5); returns 5.

Ans. The TRIM() function is used to remove leading and trailing spaces from a string. Example: SELECT TRIM(' Hello '); returns 'Hello'.

Ans. DELETE removes rows from a table based on a condition. TRUNCATE removes all rows and resets the table, while DROP removes the table structure completely.

Ans. You can use the SHOW INDEXES statement. Example: SHOW INDEXES FROM table_name;

Ans. The DISTINCT keyword is used to remove duplicate values from a result set. Example: SELECT DISTINCT column_name FROM table;

Ans. Use the LAST_INSERT_ID() function to retrieve the ID of the last inserted record. Example: SELECT LAST_INSERT_ID();

Ans. The CURDATE() function returns the current date in the format 'YYYY-MM-DD'. Example: SELECT CURDATE();

Ans. The COUNT() function returns the number of rows that match a given condition. Example: SELECT COUNT(*) FROM table_name;

Ans. The CONCAT_WS() function concatenates strings with a specified separator. Example: SELECT CONCAT_WS(',', 'Hello', 'World'); returns 'Hello,World'.

Ans. Use the EXISTS operator to check if a value exists in a subquery. Example: SELECT * FROM table WHERE EXISTS (SELECT * FROM table WHERE column_name = 'value');

Ans. CHAR is a fixed-length string data type, while VARCHAR is a variable-length string. CHAR always uses the defined storage size, whereas VARCHAR uses only the required storage size.

Ans. Use the CURTIME() function to get the current time in the format 'HH:MM:SS'. Example: SELECT CURTIME();

Ans. Use the CREATE TABLE statement to create a table. Example: CREATE TABLE table_name (column1 datatype, column2 datatype);

Ans. Use the COUNT() function to find the total number of records. Example: SELECT COUNT(*) FROM table_name;

Ans. The REPLACE() function is used to replace all occurrences of a substring within a string. Example: SELECT REPLACE('Hello World', 'World', 'MySQL'); returns 'Hello MySQL'.

Ans. Use the ALTER TABLE statement with the DROP COLUMN clause. Example: ALTER TABLE table_name DROP COLUMN column_name;

Ans. A composite key is a primary key that consists of more than one column. It is used when a single column is not unique enough to identify records.

Ans. MySQL does not have a direct command to rename a database. You must create a new database, copy the data from the old database, and then drop the old database.

Ans. The NOW() function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS'. Example: SELECT NOW();

Ans. Query optimization can be done by indexing columns used in WHERE clauses, avoiding unnecessary JOINs, and using LIMIT for large data sets.

Ans. Use the LOCK TABLES statement to lock a table. Example: LOCK TABLES table_name WRITE;

Ans. A foreign key constraint ensures the integrity of the relationship between two tables by enforcing a rule that a value in one table must exist in another table.

Ans. Use the CREATE INDEX statement for multiple columns. Example: CREATE INDEX idx_name ON table_name (column1, column2);

Ans. Use the GROUP BY clause with the HAVING clause to find duplicate values. Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

Ans. UNION combines result sets from two or more queries and removes duplicates, while JOIN combines rows from two or more tables based on a related column.

Ans. Use the ALTER TABLE statement with the CHANGE COLUMN clause. Example: ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;

Ans. A primary key is a unique identifier for a record in a table. It ensures that no two records have the same primary key value.

Ans. An index is a data structure used to improve the speed of retrieval operations on a database table.

Ans. MySQL performs case-insensitive searches by default for text data types, but you can use the COLLATE clause to enforce case insensitivity. Example: SELECT * FROM table WHERE column COLLATE utf8_general_ci = 'value';

Ans. The LIMIT clause is used to specify the number of rows to return in a query. Example: SELECT * FROM table LIMIT 5; returns only the first 5 rows.