Top MySQL Interview Questions and Answers

Here are top MySQL interview questions,

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) for querying and managing databases.

 

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

To create a new database, you can use the following SQL command:

      ```sql

      CREATE DATABASE database_name;

      ```

 

3. How do you connect to a MySQL database using the command-line client?

You can connect to MySQL using the command-line client with the following command:

      ```

      mysql -u username -p

      ```

      Replace `username` with your MySQL username. The client will prompt you to enter the password.

 

4. What is the command to create a new table in MySQL?

The SQL command to create a new table is as follows:

      ```sql

      CREATE TABLE table_name (

          column1 datatype1 constraints,

          column2 datatype2 constraints,

          ...

      );

      ```

 

5. How do you insert data into a MySQL table?

To insert data into a table, use the `INSERT INTO` statement as follows:

      ```sql

      INSERT INTO table_name (column1, column2, ...)

      VALUES (value1, value2, ...);

      ```

 

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

CHAR is a fixed-length string type, while VARCHAR is a variable-length string type in MySQL. VARCHAR will use only as much storage as needed, whereas CHAR will always use the specified length.

 

7. How can you retrieve all records from a table in MySQL?

You can use the `SELECT` statement without specifying any conditions to retrieve all records:

      ```sql

      SELECT * FROM table_name;

      ```

 

8. What is the purpose of the `WHERE` clause in MySQL?

The `WHERE` clause is used to filter records in a `SELECT`, `UPDATE`, or `DELETE` statement based on specified conditions.

 

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

To update data in a table, use the `UPDATE` statement as follows:

      ```sql

      UPDATE table_name

      SET column1 = value1, column2 = value2, ...

      WHERE condition;

      ```

 

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

To delete records from a table, use the `DELETE FROM` statement along with the `WHERE` clause to specify the condition for deletion:

       ```sql

       DELETE FROM table_name

       WHERE condition;

       ```

 

11. What is the use of the `LIMIT` clause in MySQL?

The `LIMIT` clause is used to restrict the number of rows returned by a `SELECT` query. It helps in pagination or when you want to retrieve a specific number of rows.

 

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

To add a new column to an existing table, use the `ALTER TABLE` statement:

       ```sql

       ALTER TABLE table_name

       ADD column_name datatype constraints;

       ```

 

13. How can you define a primary key when creating a table?

You can define a primary key using the `PRIMARY KEY` constraint within the `CREATE TABLE` statement:

       ```sql

       CREATE TABLE table_name (

           column1 datatype PRIMARY KEY,

           column2 datatype,

           ...

       );

       ```

 

14. What is the AUTO_INCREMENT attribute used for?

The `AUTO_INCREMENT` attribute is used with a numeric column to automatically generate a unique value for each new row added to the table.

 

15. How do you create an index in MySQL?

To create an index on a column for faster data retrieval, use the `CREATE INDEX` statement:

       ```sql

       CREATE INDEX index_name ON table_name (column_name);

       ```

 

16. Explain the concept of foreign keys in MySQL.

In MySQL, foreign keys establish a relationship between two tables, where the column with a foreign key references the primary key of another table.

 

17. What is the difference between `INNER JOIN` and `LEFT JOIN` in MySQL?

`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.

 

18. How can you perform a backup and restore in MySQL?

To perform a backup, you can use the `mysqldump` utility, and to restore the backup, use the `mysql` command with the SQL backup file.

 

19. What is the purpose of the `HAVING` clause in MySQL?

The `HAVING` clause is used to filter the results of aggregate functions in a `GROUP BY` query, similar to the `WHERE` clause for individual rows.

 

20. How do you drop a table in MySQL?

To drop a table and remove it from the database, use the `DROP TABLE` statement:

       ```sql

       DROP TABLE table_name;

       ```

Above are few top mysql interview questions. Remember to prepare and expand on these answers.

Good luck with your interview!  👍