1.  What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses structured query language (SQL) for managing and manipulating data in a database.

2. How do you create a new database in MySQL?

Use the CREATE DATABASE statement. For example:

CREATE DATABASE dbname;

3. How do you select all records from a table named "employees"?

Use the SELECT statement. For example:

SELECT * FROM employees;

4. What is the difference between CHAR and VARCHAR data types?

CHAR is a fixed-length string, while VARCHAR is a variable-length string. CHAR always allocates the specified length, while VARCHAR only allocates the necessary storage for the actual data.

5. How can you retrieve unique values from a column in a MySQL table?

Use the DISTINCT keyword with the SELECT statement. For example:

SELECT DISTINCT column_name FROM table_name;

6. Explain the purpose of the WHERE clause in a SQL query.

The WHERE clause is used to filter records based on a specified condition. It is used to extract only the records that fulfill the specified criteria.

7. What is the primary key in a MySQL table, and why is it important?

A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified and is used for referencing records in relationships between tables.

8. How do you update data in a MySQL table?

Use the UPDATE statement with the SET clause and a WHERE clause to specify the conditions for updating. For example:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

9. What is the purpose of the ORDER BY clause in a SQL query?

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns.

10. How do you delete records from a MySQL table?

Use the DELETE FROM statement with a WHERE clause to specify the conditions for deletion. For example:

DELETE FROM table_name WHERE condition;

11. Explain the difference between INNER JOIN and LEFT JOIN in MySQL.

INNER JOIN returns only the matching rows from both tables, while LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

12.  What is the role of the AUTO_INCREMENT attribute in MySQL?

The AUTO_INCREMENT attribute is used to automatically generate a unique integer value for a column, typically used for primary key columns. It increments by 1 for each new record.

13. How do you add a new column to an existing table in MySQL?

 Use the ALTER TABLE statement with the ADD clause. For example:

ALTER TABLE table_name ADD COLUMN new_column INT;

14.  What is a foreign key in MySQL, and how is it used?

A foreign key is a field in a table that is a primary key in another table. It establishes a link between the two tables, enforcing referential integrity and allowing the creation of relationships.

15. How do you calculate the total number of rows in a table?

Use the COUNT function in a SELECT statement. For example:

SELECT COUNT(*) FROM table_name;

16. What is the purpose of the GROUP BY clause in a SQL query?

 The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used in conjunction with aggregate functions like SUM, COUNT, or AVG to perform calculations on grouped data.

17. How can you insert data into a MySQL table?

Use the INSERT INTO statement to add new records to a table. For example:

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

18. What is a unique key constraint, and why is it used?

 A unique key constraint ensures that all values in a column (or a set of columns) are unique across the table. It is often used to enforce the uniqueness of data in a specific column.

19. How do you retrieve data from multiple tables using a JOIN statement?

Use the JOIN clause in a SELECT statement to combine rows from two or more tables based on a related column. For example:

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column = table2.column;

20. Explain the difference between a VARCHAR and TEXT data type in MySQL.

Both VARCHAR and TEXT are used to store variable-length strings. The main difference is that VARCHAR has a specified maximum length, while TEXT can store larger amounts of text without a predefined limit.

21. What is normalization in the context of databases?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down large tables into smaller ones and establishing relationships between them.

22. How can you retrieve the last inserted ID after an INSERT statement?

Use the LAST_INSERT_ID() function to obtain the last automatically generated value in a column with the AUTO_INCREMENT attribute. For example:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
SELECT LAST_INSERT_ID();

23. What is the purpose of the HAVING clause in a SQL query?

The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on aggregate conditions. It is applied after the grouping and is similar to the WHERE clause.

24. How do you remove a table from a MySQL database?

Use the DROP TABLE statement. For example:

DROP TABLE table_name;

25. Explain the ACID properties in the context of database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably:
 

• Atomicity: Transactions are treated as a single, indivisible unit.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Transactions are executed independently of each other.
• Durability: Once a transaction is committed, its changes are permanent.

26. How do you add a unique constraint to a column in an existing MySQL table?

Use the ALTER TABLE statement with the ADD CONSTRAINT clause. For example:

ALTER TABLE table_name ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

27. What is the purpose of the SQL LIMIT clause?

The LIMIT clause is used to limit the number of rows returned by a query. It is often used for pagination or when you only need a specific number of results.

28. How can you perform a case-insensitive search in MySQL?

Use the COLLATE keyword with a case-insensitive collation, such as COLLATE utf8_general_ci. For example:

SELECT * FROM table_name WHERE column_name COLLATE utf8_general_ci = 'value';

29. What is the purpose of the MySQL NULL value?

The NULL value represents an unknown or undefined value in a database. It is used when the actual data is missing or not applicable.

30. How do you grant SELECT privileges on a specific table to a user in MySQL?

Use the GRANT statement. For example:

GRANT SELECT ON database_name.table_name TO 'username'@'localhost';

31. What is the purpose of the INDEX in a MySQL table?

An INDEX is used to improve the speed of data retrieval operations on a database table. It enhances the efficiency of queries by allowing the database engine to quickly locate and access specific rows based on the indexed columns.

32. How can you update data in multiple rows simultaneously in MySQL?

Use the UPDATE statement with the CASE statement to perform conditional updates on multiple rows. For example:

UPDATE table_name
SET column1 = CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE column1
END,
column2 = CASE
  WHEN condition3 THEN value3
  WHEN condition4 THEN value4
  ELSE column2
END;

33. Explain the difference between the INNER JOIN and OUTER JOIN.

INNER JOIN returns only the matched rows from both tables, excluding non-matching rows. OUTER JOIN is a broader term that includes various types such as LEFT JOIN, RIGHT JOIN, and FULL JOIN, which include non-matching rows from one or both tables.

34. How can you retrieve distinct values from a column along with the count of occurrences in MySQL?

Use the COUNT function with the GROUP BY clause. For example:

SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name;

35. What is the purpose of the MySQL LIKE operator in a query?

The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters (% and _) to match partial strings.

36. How do you remove duplicate rows from a MySQL table?

Use the DELETE statement with a self-join. For example:

DELETE t1
FROM table_name t1
INNER JOIN table_name t2
WHERE t1.id < t2.id AND t1.column_name = t2.column_name;

37. What is a stored procedure in MySQL, and how is it created?

A stored procedure is a set of SQL statements that can be stored in the database and executed as a single unit. It is created using the CREATE PROCEDURE statement. For example:

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements
END //
DELIMITER ;

38. How can you retrieve the current date and time in MySQL?

Use the NOW() function or CURRENT_TIMESTAMP keyword to retrieve the current date and time. For example:

SELECT NOW();

39. Explain the purpose of the MySQL IN operator in a query.

The IN operator is used to specify multiple values in a WHERE clause. It is equivalent to multiple OR conditions. For example:

SELECT column_name FROM table_name WHERE column_name IN (value1, value2, value3);

40. How do you add a foreign key to an existing MySQL table?

Use the ALTER TABLE statement with the ADD FOREIGN KEY clause. For example:

Use the ALTER TABLE statement with the ADD FOREIGN KEY clause. For example:

41.  What is the purpose of the MySQL DISTINCT keyword?

 The DISTINCT keyword is used in a SELECT statement to retrieve unique values from a specified column or columns.

42. How can you backup and restore a MySQL database?

Use the mysqldump command to create a backup and the mysql command to restore it. For example:

• Backup: mysqldump -u username -p dbname > backup.sql
• Restore: mysql -u username -p dbname < backup.sql

43. What is the difference between a view and a table in MySQL?

A table is a physical storage structure for data, while a view is a virtual table that is based on the result of a SELECT query. Views do not store data themselves but provide a way to represent data from one or more tables.