Intermediate MySQL Interview Questions

/in /by

1. What are views in MySQL?

Ans: A view in MySQL is a virtual table that displays the result set of a predefined query. It does not store data itself but presents data from one or more underlying tables.

2. What are MySQL triggers?

Ans: A trigger is a database object that executes automatically in response to a specific event on a particular table. It is activated by predefined operations such as INSERT, DELETE, or UPDATE.

3. How to create and execute views?

Ans: The CREATE VIEW command in MySQL is used to create a virtual table (view) based on the result of a SELECT query.

Syntax: CREATE VIEW [database_name.]view_name [(column_list)] AS
SELECT ...;

  • database_name (optional): Specifies the database where the view will be created.

  • view_name: The name of the view.

  • column_list (optional): Specifies the names of the columns in the view.

  • SELECT: The query that defines the data shown in the view.

4. How many triggers are possible in MySQL?

Ans: MySQL supports 6 types of triggers:

  1. BEFORE INSERT – Executes before a new row is inserted.

  2. AFTER INSERT – Executes after a new row is inserted.

  3. BEFORE UPDATE – Executes before an existing row is updated.

  4. AFTER UPDATE – Executes after an existing row is updated.

  5. BEFORE DELETE – Executes before a row is deleted.

  6. AFTER DELETE – Executes after a row is deleted.

5. What are the clients and utilities in MySQL?

Ans: Several MySQL programs are available to help users interact with the server. Some key tools for administrative tasks include:

  • mysql – An interactive program used to send SQL statements to the server and view results. It can also execute batch scripts.

  • mysqladmin – A command-line administrative tool used for tasks such as shutting down the server, checking configuration settings, and monitoring server status.

  • mysqldump – Used for backing up databases or copying them to another server.

  • mysqlcheck and myisamchk – These tools perform table checking, analysis, optimization, and repair of corrupted tables.

6. What is MySQL server?

Ans: The ‘mysqld’ server is the core MySQL service responsible for handling all database and table operations.

7. What types of relationships are used in MySQL?

Ans: Three types of relationships are commonly used in MySQL:

  • One-to-One – Each row in one table is linked to only one row in another table. These relationships can often be implemented as additional columns within the same table.

  • One-to-Many (or Many-to-One) – A single row in one table is associated with multiple rows in another table. This is the most common type of relationship.

  • Many-to-Many – Multiple rows in one table are related to multiple rows in another table. This is typically managed through a junction (linking) table.

8. What are the types of relationships used in MySQL?

Ans: There are three main types of relationships in MySQL:

1. One-to-One:
In a one-to-one relationship, each row in a table is linked to only one row in another table. Typically, such related data can be stored as columns within the same table.

2. One-to-Many (or Many-to-One):
This relationship occurs when a single row in one table corresponds to multiple rows in another. It is the most common type of relationship in relational databases.

3. Many-to-Many:
In a many-to-many relationship, multiple rows in one table are associated with multiple rows in another. To implement this, a junction (or bridge) table is used, containing foreign keys referencing the primary keys of both related tables.

9. What are the MySQL clients and utilities?

Ans: Several MySQL programs are available to help you interact with the server. For administrative purposes, some of the key tools include:

  • mysql – An interactive client that allows you to send SQL statements to the server and view the results. It can also execute batch scripts containing SQL commands.

  • mysqladmin – A command-line administrative tool used for tasks such as shutting down the server, checking configurations, or monitoring server status during issues.

  • mysqldump – A utility for backing up databases or transferring them to another server.

  • mysqlcheck and myisamchk – Tools for checking, analyzing, optimizing, and repairing tables. mysqlcheck supports MyISAM tables and, to some extent, other storage engines, while myisamchk is exclusively for MyISAM tables.

10. What is the MySQL server?

Ans: The MySQL server, mysqld, serves as the core component of a MySQL installation, handling all operations related to databases and tables.

11. How do you create and execute views in MySQL?

Ans: A view is created using the CREATE VIEW statement. The general syntax is as follows:

CREATE
[OR REPLACE]
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
[DEFINER = {user | CURRENT_USER}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

12.What are MySQL “Views”?

Ans: In MySQL, a view is a virtual table that represents the result set of a stored query. It does not store data itself but returns rows from the underlying tables whenever the view is queried. Views simplify complex queries by providing an alias to a query result, making data retrieval more intuitive.

Advantages of using views:

  • Simplicity – Encapsulate complex SQL logic into a single view for easier reuse.

  • Security – Restrict user access to specific rows or columns without exposing the entire table.

  • Maintainability – Centralize query logic in one place, making updates and changes easier to manage.