Top 50 MySQL Interview Questions and Answers

Top 50 MySQL Interview Questions and Answers

03 May 2024
240 Views
69 min read

MySQL Interview Questions and Answers: An Overview

Do you want to build your career as a Database Administrator (DBA), Database Developer, Database Architect, etc.? Are you keen to learn RDBMS or MySQL? If yes, you are at the right place. In this world of data, MySQL specialists are in high demand as organizations increasingly rely on data-driven decision-making. MySQL is the world's most popular open-source database software that is fast, reliable, and easy to use. 

You can observe that there's a lot of scope for a MySQL learner or specialist. To help you in your career path, we've come up with an expert-designed comprehensive MySQL interview questions guide. Here, you will get your answer to almost every conceptual and practical question. For your convenience, we have classified the questions into beginners, intermediate, and advanced levels. This will help you understand concepts from primary keys, and basic SQL commands to triggers and cursors.

You're welcome to ask any question related to the MySQL interview you have appeared for in the past or will come across. It will help us to guide you in a more directed way. For now let's begin with our interview preparation.

MySQL Interview Questions & Answers for Beginners

1. What is MySQL? How does it get differentiated from other relational databases?

MySQL is a multithreaded, multi-user, open-source relational database management system based on Structured Query Language(SQL). It can run on various platforms like Windows, Linux & UNIX. It is highly scalable and reliable making it the most popular and widely-used open source database.

Factors that differentiate MySQL from other relational databases are:

  • Licensing and Cost: MySQL is open-source and hence free to use and distribute. Whereas other relational databases, like Oracle Database or Microsoft SQL Server, may require commercial licenses, which can incur costs.
  • Features and Functionality: While MySQL offers a comprehensive set of features for managing relational data, it may have differences in functionality compared to other databases.
  • Performance and Scalability: MySQL is known for its performance and scalability, particularly for web applications and small to medium-sized databases. However, other relational databases may excel in specific use cases or have optimizations tailored to particular workloads.
  • Ecosystem and Integration: MySQL has a large and active community of users and developers, as well as extensive documentation and third-party tools and libraries. It integrates well with popular programming languages and frameworks. Other relational databases may have their own ecosystems and integration points, which may influence the choice of database for a particular project.

2. Differentiate MySQL from SQL.

SQLMySQL
It is a structured query language that manages the relational database management system.It is a relational database management system that uses SQL.
It is not an open-source language. MySQL is an open-source platform. It allows access to anyone.
SQL supports XML and user-defined functions.It doesn’t support XML and any user-defined functions
SQL can be implemented in various RDBMS such as PostgreSQL, SQLite, Microsoft SQL Server, and others.MySQL is a specific implementation of an RDBMS that uses SQL for querying and managing databases.
SQL itself is not a product and doesn’t have a license. It’s a standard language.MySQL is open-source and available under the GNU General Public License (GPL).

3. What are SQL commands? Classify the SQL commands.

SQL commands are a set of instructions used to interact with the database like SQL Server, MySql, Oracle, etc. SQL commands are responsible for creating and doing all the manipulation on the database. These are also responsible for giving/taking out access rights to a particular database.

Classification of SQL Commands

SQL Commands Category/Types of SQL Commands

  1. Data Definition Language (DDL)

    These SQL commands are used for creating a table, deleting a table, altering a table, and truncating the table. All the commands of DDL are auto-committed i.e. it permanently saves all the changes in the database.

    In this category, we have four commands:

    1. CREATE
    2. ALTER
    3. DROP
    4. TRUNCATE
  2. Data Manipulation Language (DML)

    These SQL commands modify the database. The commands of DML are not auto-committed i.e. it can't permanently save all the changes in the database. They can be rolled back.

    In this category, we have three commands:

    1. INSERT
    2. UPDATE
    3. DELETE
  3. Data Query Language (DQL)

    This SQL command is used to fetch/retrieve data from database tables.

    In this category, we have only the SELECT command.

  4. Transaction Control Language (TCL)

    These SQL commands are used to handle changes affecting the data in the database. We use these commands within the transaction or to make a stable point during changes in the database at which we can roll back the database state if required.

    In this category, we have three commands:

    1. SAVEPOINT
    2. ROLLBACK
    3. COMMIT
  5. Data Control Language (DCL)

    These SQL commands are used to implement security on database objects like tables, views, stored procedures, etc. It consists of commands which deal with the user permissions and controls of the database system.

    In this category, we have two commands:

    1. GRANT
    2. REVOKE

Read More: Basics of SQL Commands

4. What are the frequently used MySQL functions?

Function NameFunctionality
ABS()Returns the absolute value of a number.
ROUND()Rounds a number to a specified number of decimal places.
CEIL()Returns the smallest integer greater than or equal to a given number.
FLOOR()Returns the largest integer less than or equal to a given number
EXP()Calculates the exponential value of a number
LOG()Calculates the natural logarithm of a number
NOWOReturns the current date and time as a single value
CURRDATEOReturns the current date and time
CONCAT (X, Y)Concatenates two string values creating a single string output
DATEDIFF (X, Y)Determines the difference between the two dates

5. What is a query in MySQL? What are the types of SQL queries?

A query is a request for data or information from a database. It is a way to interact with the database to perform various operations like retrieving, inserting, updating, or deleting the data. Users can query a database for specific information, and the resultant record/records are returned by MySQL.

A query consists of SQL commands, expressions, and operators that define criteria for how the database should search, filter, modify, or present the data.

Following are some common types of SQL queries:

  1. Data Retrieval Queries: These queries retrieve data from one or more tables in the database. They use the SELECT statement and may include filtering, sorting, and grouping operations.

    Example

    
    SELECT column1, column2 FROM table WHERE condition;      
  2. Data Manipulation Queries: These queries modify data in the database tables. They include INSERT, UPDATE, and DELETE statements.

    Example

    
    INSERT INTO table (column1, column2) VALUES (value1, value2);     
  3. Data Definition Queries: These queries define or alter the structure of database objects such as tables, indexes, or views. They include CREATE, ALTER, and DROP statements.

    Example

    
    CREATE TABLE table_name (column1 datatype, column2 datatype);      
  4. Data Control Queries: These queries manage access to the database objects by granting or revoking privileges to users or roles. They include GRANT and REVOKE statements.

    Example

    
    GRANT SELECT, INSERT ON table TO user;        
  5. Transaction Control Queries: These queries manage transactions in the database, such as starting, committing, or rolling back transactions. They include BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

    Example

    
    BEGIN TRANSACTION;       
  6. Join Queries: These queries retrieve data from multiple tables by joining them based on specified conditions. They use JOIN clauses, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

    Example

    
    SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;     
  7. Subquery or Nested Queries: These queries include one query (subquery) nested inside another query (outer query). They can be used in SELECT, INSERT, UPDATE, or DELETE statements.

    Example

    
    SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);    

6. What is a subquery? What are the types of SQL subqueries?

An SQL subquery also known as an inner query or nested query is a query inside another query or an outer query. A subquery may occur in the clauses such as SELECT, FROM, WHERE, UPDATE, etc. It's also possible to have a subquery inside another subquery. The innermost subquery is run first, and its result is passed to the containing query (or subquery).

The following are types of SQL subqueries

  1. Single-row: It returns at most one row of results. It is generally used with comparison operators such as =, >, <, etc.

    Example

    
    SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
    
  2. Multi-row: It returns at least two rows. It can be used with operators like IN, ANY, or ALL.

    Example

    
    SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
    
  3. Correlated Subquery: It is a subquery related to the information from the outer query.

    Example

    
    SELECT column1 FROM table1 t1 WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t1.column3 = t2.column3);
    
  4. Nested Subquery: It is a subquery inside another subquery.

    Example

    
    SELECT column1 FROM table1 WHERE column2 = (SELECT MAX(column2) FROM (SELECT * FROM table2) AS subquery);
    
  5. Multi-column: It is also known as a multi-column correlated subquery that returns multiple columns of data and is correlated with the outer query.

    Example

    
    SELECT column1, column2 
    FROM table1 t1 
    WHERE (column1, column2) IN 
          (SELECT column1, column2 
           FROM table2 t2 
           WHERE t1.column3 = t2.column3);
    

7. What are the different data types in MySQL?

The data types in MySQL can be categorized into mainly three categories:

  1. Numeric Data Types: The numeric data types are integer, fixed-point, floating-point, and bit values. They can be signed or unsigned, except BIT.
    Type NameDescription
    TINYINTVery Small Integer
    SMALLINTSmall Integer
    MEDIUMINTMedium-sized Integer
    INTStandard Integer
    BIGINTLarge Integer
    DECIMALFixed-point number
    FLOATSingle-precision floating-point number
    DOUBLEDouble-precision floating-point number
    BITBit-field

    Example

    
    CREATE TABLE example_numeric_types (
        roll INT,
        salary DECIMAL(15,2),
        score DOUBLE(7,2)
    );
    
  2. String Data Types: They are used for storing text.
    Type NameDescription
    CHARfixed-length nonbinary(character) string
    VARCHARvariable-length nonbinary string
    BINARYfixed-length binary string
    VARBINARYvariable-length binary string
    TINYBLOBVery small BLOB(binary large object)
    BLOBSmall BLOB
    MEDIUMBLOBMedium-sized BLOB
    LONGBLOBLarge BLOB
    TINYTEXTA very small nonbinary string
    TEXTSmall nonbinary string
    MEDIUMTEXTMedium-sized nonbinary string
    LONGTEXTLarge nonbinary string
    ENUMAn enumeration; each column value is assigned, and one enumeration member
    SETA set; each column value is assigned zero or more set members
    NULL

    Example

    
    CREATE TABLE example_string_types (
        name VARCHAR(300),
        father_name CHAR(30),
        comments TEXT
    );
    
  3. Temporal Data Types: They are for date and time and a combination of date and time.
    Type NameMeaning
    DATEA date value, in ' CCYY-MM-DD ' Format
    TIMEA Time value, in ' hh : mm :ss ' format
    DATETIMEDate and time value, in ' CCYY-MM-DD hh : mm :ss ' format
    TIMESTAMPA timestamp value, in ' CCYY-MM-DD hh : mm :ss ' format
    YEARA year value, in CCYY or YY format

    Example

    
    CREATE TABLE example_date_types (
        birthday DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        event_time DATETIME
    );
    
  4. Spatial Data Types: These are used to store geometric data. Some of these types are:
    Type Name Meaning
    GEOMETRYThe base data type for all spatial data types.
    POINTA single location in space
    LINESTRINGA sequence of points that form a line
    POLYGONA planar surface representing a multi-sided shape

    Example

    
    CREATE TABLE example_spatial_types (
        location POINT,
        path LINESTRING,
        area POLYGON
    );
    

8. What does a MySQL database contain?

A MySQL database contains one or many tables containing several records or rows. Within these rows, data is contained in various columns or fields.

9. What are the different tables present in MySQL?

Many tables are present in MySQL by default. But, MyISAM is the default database engine used in MySQL. Five types of tables are present:

  1. MyISAM
  2. Heap
  3. Merge
  4. INNO DB
  5. ISAM

10. What are SQL dialects? Describe with examples.

SQL (Structured Query Language) dialects are variations or extensions of the SQL standard specific to different database management systems (DBMS). In other words, the various versions of SQL, both free and paid, are also called SQL dialects. Each DBMS may implement SQL with its unique features, syntax, and optimizations, leading to differences in SQL dialects across platforms.

The various SQL dialects are:

  • Transact-SQL (T-SQL): It is developed by Microsoft and used in SQL Server and Azure SQL Database. It includes extensions for procedural programming, error handling, and additional built-in functions.
  • PL/SQL: It is developed by Oracle Corporation and used in Oracle Database. It supports procedural programming constructs like loops, conditionals, and exception handling, allowing developers to create complex stored procedures and functions.
  • MySQL SQL: Used in MySQL and MariaDB databases. It includes user-defined functions (UDFs), stored procedures, and triggers, along with support for various storage engines.
  • PostgreSQL SQL: Used in PostgreSQL database. It supports advanced features like table inheritance, common table expressions (CTEs), full-text search, and JSON data types.
  • SQLite SQL: Used in SQLite, a lightweight, serverless database engine. It provides a simplified version of SQL with a focus on portability and simplicity.

11. What is an index? What are the various ways to create an index?

An index is a special data structure related to a database table and used for storing its important parts and enabling faster data search and retrieval. Indexes are especially efficient for large databases, where they significantly enhance query performance.

There are the following ways to create an index:

  • T-SQL statements can be used to create an index.
  • We can use the SQL Server Management Studio to browse to the table where the index will be created, and then right-click on the Indexes node. We must select the New Index option over here.
  • We can identify the index indirectly by specifying the PRIMARY KEY and the UNIQUE constraint in the CREATE TABLE or ALTER TABLE statement.

12. Differentiate between Clustered and Non-Clustered Index.

Clustered IndexNon-Clustered Index
A clustered index is faster.A non-clustered index is slower.
The clustered index requires less memory for operations.A non-clustered index requires more memory for operations.
the clustered index is the main data.the non-clustered index is the copy of data.
A table can have only one clustered index.A table can have multiple non-clustered indexes.
The clustered index has the inherent ability to store data on the disk.A non-clustered index does not have the inherent ability to store data on the disk.
Clustered index store pointers and not data.The non-clustered index stores both the value and a pointer to the actual row that holds the data
Here, leaf nodes are actual data itself.Here leaf nodes are not the actual data itself rather they only contain included columns.
the index key defines the order of data within a table.the index key defines the order of data within the index.
Here table records are physically reordered to match the index.Here, the logical order of the index does not match the physical stored order of the rows on the disk.
The size of the primary clustered index is large.The size of the non-clustered index is comparatively smaller.
Primary Keys of the table by default are clustered indexes.The composite key when used with unique constraints of the table acts as the non-clustered index.

13. How char data type is different from varchar?

CHARVARCHAR
CHAR datatype is used to store character strings of fixed lengthVARCHAR datatype is used to store character strings of variable length
If the length of the string is less than set or fixed-length then it is padded with extra memory space.If the length of the string is less than the set or fixed-length then it will store as it is without padding with extra memory spaces.
CHAR stands for “Character”VARCHAR stands for “Variable Character”
Storage size of CHAR datatypes is equal to n bytes i.e. set lengthThe storage size of the VARCHAR datatype is equal to the actual length of the entered string in bytes.
We should use the CHAR datatype when we expect the data values in a column to be of the same length.We should use the VARCHAR datatype when we expect the data values in a column to be of variable length.
CHAR takes 1 byte for each characterVARCHAR takes 1 byte for each character and some extra bytes for holding length information
Better performance than VARCHARPerformance is not good as compared to CHAR

14. What is a schema?

A collection of database structural elements such as tables, stored procedures, indexes, functions, and triggers. It shows the overall database architecture, specifies the relationships between various objects of a database, and defines different access permissions for them.

15. What is an SQL comment?

A human-readable clarification of what a particular piece of code does. SQL code comments can be single-line (preceded by a double dash --) or span over multiple lines (as follows: /*comment_text*/). When the SQL engine runs, it ignores code comments. The purpose of adding SQL code comments is to make the code more comprehensive for those people who will read it in the future.

16. What makes MySQL so popular and widely used RDBMS?

  • Data Security:MySQL is the most secure and reliable database management system
  • Flexibility: It runs on all operating systems; and features 24X7 support and enterprise indemnification.
  • High Performance: powerful, designed to meet highly demanding applications while maintaining optimum speed and high performance
  • On-demand Scalability: offers on-demand scalability and complete customization
  • Enterprise-level SQL Features:the enterprise edition includes advanced features, management tools, and technical support for enterprise
  • Full-text Indexing and Searching:has support for full-text indexing and searching
  • Query Caching: unique memory caches help enhance the speed of MySQL greatly
  • Replication: one MySQL server can be duplicated on another, resulting in numerous benefits

17. How many index columns can be created in a table?

We can create at most 16 indexed columns in a table.

18. What is a constraint? What are the different types of constraints in MySQL?

Constraints in SQL are some rules that enforce the data to be entered into the database table. Constraints are used to restrict the type of data that can be inserted into a database table.

Types of Constraints in MySQL

  • Primary Key Constraints: The primary keys must have distinct values which means one of the columns of the table should have a unique value from the other. By default with the primary key, null values are not allowed in a primary key column of the table.
  • Unique Key Constraints: It is like a Primary key but it can accept only one null value and it can not have duplicate values.
  • Foreign Key Constraints: It identifies any column referencing the primary key in another different table.
  • Not Null Constraints:This constraint ensures that all rows in the database table must contain a value for the column that is specified as not null i.e. a null value is not allowed in that column.
  • DEFAULT Constraint: It provides a default value for a column.

Read More: SQL Integrity Constraints

19. What is an SQL operator? What are the types of SQL operators?

An SQL operator is a reserved character, a combination of characters, or a keyword used in SQL queries to perform a specific operation. SQL operators are commonly used with the WHERE clause to set a condition (or conditions) for filtering the data.

 What is an SQL operator? What are the types of SQL operators?

  1. Arithmetic Operators
    OperatorDescription
    +Performs addition
    -Performs subtraction
    *Performs multiplication
    /Performs division
    %Perform modulus
  2. Comparison Operators
    OperatorDescription
    =Equal to
    >Greater than
    <Less than
    >=Greater than or equal to
    <=Less than or equal to
    <>Not equal to
    !=Not equal to
    !>Not greater than
    !<Not less than
  3. Compound Operators
    OperatorDescription
    +=Add equals
    -=Subtract equals
    *=Multiply equals
    /=Divide equals
    %=Modulo equals
    &=Bitwise AND equals
    ^-=Bitwise exclusive equals
    |*=Bitwise OR equals
  4. Logical Operators
    OperatorDescription
    ALLTRUE if all of the subquery values meet the condition
    ANDTRUE if all the conditions separated by AND is TRUE
    ANYTRUE if any of the subquery values meet the condition
    BETWEENTRUE if the operand is within the range of comparisons
    EXISTSTRUE if the subquery returns one or more records
    INTRUE if the operand is equal to one of a list of expressions
    LIKETRUE if the operand matches a pattern
    NOTDisplays a record if the condition(s) is NOT TRUE
    ORTRUE if any of the conditions separated by OR is TRUE
    SOMETRUE if any of the subquery values meet the condition
  5. Bitwise Operators
    OperatorDescription
    &Bitwise AND
    |Bitwise OR
    ^Bitwise exclusive OR

20. What is the use of ENUM in MySQL?

The use of ENUM will limit the values that can go into a table. For instance, a user can create a table giving specific month values and other month values would not enter into the table.

MySQL Interview Questions & Answers for Intermediate

21. What is the CASE() function?

The CASE() function is a way to implement the if-then-else logic in SQL. This function sequentially checks the provided conditions in the WHEN clauses and returns the value from the corresponding THEN clause when the first condition is satisfied. If none of the conditions is satisfied, the function returns the value from the ELSE clause in case it's provided, otherwise, it returns NULL.

Syntax


CASE
    WHEN condition_1 THEN value_1
    WHEN condition_2 THEN value_2
    WHEN condition_3 THEN value_3
    ...
    ELSE value
END;

22. What are MySQL Triggers? How many Triggers are there in MySQL?

A trigger is a procedural code in a database. Triggers are automatically triggered when specific events occur on a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either before or immediately following any such event. During column updating, triggers are invoked automatically.

SQL Server triggers

There are various reasons for using triggers:

  • Audit Trails
  • Validation
  • Referential integrity enforcement

There are six triggers available in the MySQL database:

  1. BEFORE INSERT
  2. AFTER INSERT
  3. BEFORE UPDATE
  4. AFTER UPDATE
  5. BEFORE DELETE
  6. AFTER DELETE

Example to illustrate the working of triggers in MySQL

Let's suppose there's a table named Employees with the following schema:


CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    is_active BOOLEAN
);

We want to create a trigger that automatically updates the is_active column to true for employees whose salary is greater than $5000.


DELIMITER //

CREATE TRIGGER update_active_status
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary > 5000 THEN
        UPDATE employees
        SET is_active = true
        WHERE id = NEW.id;
    END IF;
END//

DELIMITER ;

In this trigger:

  1. AFTER INSERT ON employees specify that the trigger should activate after an insert operation on the employees table.
  2. FOR EACH ROW indicates that the trigger will be executed for each row affected by the insert operation.
  3. NEW.salary refers to the salary value of the newly inserted row.
  4. If the salary of the newly inserted employee is greater than $5000, the trigger updates the is_active column to true for that employee.

Now, let's insert a record into the employees table:


INSERT INTO employees (name, salary) VALUES ('John Doe', 6000);

After this insert operation, the is_active column for the employee with a salary of $6000 will be automatically set to true by the trigger.

23. What are views in MySQL? How do you create and execute views in MySQL?

In MySQL, a view is a virtual table based on the result set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. It is created by combining one or more tables. The difference between a view and a table is that views are definitions that build on other tables.

 Views in SQL Server

Views take very little space, simplify complex queries, limit access to the data for security reasons, enable data independence, and summarize data from multiple tables. Views do not store any data of their own but display data stored in other tables. A view is created with the CREATE VIEW statement.

Syntax to Create a View


CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example of a View


CREATE VIEW [Indian Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'India';

Execution of a View


SELECT * FROM [Indian Customers];

24. Describe the relationships in MySQL.

The relationship in MySQL is classified into three types:

  1. One-to-One: Each record in one table corresponds to only one record in another table and vice versa. This relationship is established using foreign key constraints.

    Example Illustrating a one-to-one relationship between two tables: users and profiles

    
    CREATE TABLE users (
        id INT PRIMARY KEY,
        username VARCHAR(50) UNIQUE,
        email VARCHAR(100) UNIQUE
    );
    
    CREATE TABLE profiles (
        id INT PRIMARY KEY,
        user_id INT UNIQUE,
        full_name VARCHAR(100),
        FOREIGN KEY (user_id) REFERENCES users(id)
    );
    

    The user_id column in the profiles table establishes the one-to-one relationship with the id column in the users table. This column acts as a foreign key referencing the id column in the users table.

  2. One-to-Many: Each record in one table corresponds to several records in another table. This relationship between two tables can be established using foreign keys.

    Example Illustrating a one-to-many relationship between two tables: orders and order_items

    
    CREATE TABLE orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        total_amount DECIMAL(10, 2),
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );
    
    CREATE TABLE order_items (
        item_id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT,
        product_name VARCHAR(100),
        quantity INT,
        price DECIMAL(10, 2),
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
    );
    

    Here, each order in the orders table can have multiple corresponding items in the order_items table, identified by the order_id foreign key.

  3. Many-to-many: Each record in both tables corresponds to several records in another table. To create this relationship, add a third table containing the same key column from each of the other tables.

    Let's suppose there are two entities: students and courses. Each student can enroll in multiple courses, and each course can have multiple students enrolled.

    Here, we'll create three tables:

    1. students: Stores information about students.
    2. courses: Stores information about courses.
    3. student_course: Acts as a junction table linking students to courses.

    Example Illustrating a many-to-many relationship between two tables: students and courses

    
    CREATE TABLE students (
        student_id INT AUTO_INCREMENT PRIMARY KEY,
        student_name VARCHAR(100)
    );
    
    CREATE TABLE courses (
        course_id INT AUTO_INCREMENT PRIMARY KEY,
        course_name VARCHAR(100)
    );
    
    CREATE TABLE student_course (
        student_id INT,
        course_id INT,
        PRIMARY KEY (student_id, course_id),
        FOREIGN KEY (student_id) REFERENCES students(student_id),
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
    );
    

    To insert data into these tables:

    
    -- Inserting students
    INSERT INTO students (student_name) VALUES ('John');
    INSERT INTO students (student_name) VALUES ('Alice');
    
    -- Inserting courses
    INSERT INTO courses (course_name) VALUES ('Mathematics');
    INSERT INTO courses (course_name) VALUES ('Physics');
    
    -- Enrolling students in courses
    INSERT INTO student_course (student_id, course_id) VALUES (1, 1); -- John enrolled in Mathematics
    INSERT INTO student_course (student_id, course_id) VALUES (1, 2); -- John enrolled in Physics
    INSERT INTO student_course (student_id, course_id) VALUES (2, 1); -- Alice enrolled in Mathematics
    

25. What is a heap table in MySQL?

A heap table is usually used for temporary and fast temporary storage.

  • BOLB or TEXT fields are not permitted in the heap table.
  • comparison operators like =, <,>, = >,=< can be used only.
  • The heap table didn’t support the AUTO_INCREMENT command.
  • Indexes should be NOT NULL in the heap table.

26. What are the differences between Nested Query and Correlated Query?

ParametersNested QueryCorrelated Query
DefinitionA query is written inside another query and the result of the inner query is used in the execution of the outer query.A query is nested inside another query and an inner query uses values from the outer query.
ApproachBottom-up approach i.e. Inner query runs first, and only once. The outer query is executed with the result from the Inner query.Top to Down Approach i.e. Outer query executes first and for every Outer query row Inner query is executed.
DependencyInner query execution is not dependent on Outer query.The inner query is dependent on the Outer query.
PerformancePerforms better than Correlated Query but is slower than Join Operation.Performs slower than both Nested Query and Join operations as for every outer query inner query is executed.

27. Which storage engines are used in MySQL?

Storage engines are also called table types. MySQL supports multiple storage engines, each with its characteristics and features. Some of them are:

  • InnoDB: It is the default storage engine in MySQL. It provides ACID (Atomicity, Consistency, Isolation, Durability) transactions, foreign key support, and row-level locking.
  • MyISAM: It is a popular storage engine that doesn't support transactions or foreign keys, but offers full-text search capabilities and is suitable for read-heavy workloads, such as data warehousing and logging applications.
  • MEMORY (HEAP): This engine stores data in memory useful for temporary tables, session data, and caching.
  • Archive: Optimized for storing large volumes of data with minimal storage space. It supports compression and is suitable for archiving purposes or storing historical data.
  • CSV: Stores data in comma-separated values (CSV) format and is suitable for exchanging data between different systems.
  • NDB (MySQL Cluster): A distributed storage engine designed for clustering and scalability. It provides features like data distribution, automatic sharding, and partitioning for large-scale applications.

28. How to find duplicate rows in the MySQL table?


SELECT column1, column2, ..., COUNT(*)
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

29. What is the difference between renaming a column and giving an alias to it?

Renaming a column means permanently changing its actual name in the original table using the ALTER TABLE statement. This affects the structure of the table itself.

Syntax to Rename a Column


ALTER TABLE table_name
CHANGE old_column_name new_column_name column_definition;

Example to Rename a Column


ALTER TABLE my_table
CHANGE old_column_name new_column_name VARCHAR(50);

This statement will rename the column old_column_name to new_column_name in the table my_table and change its data type to VARCHAR(50).

Giving an alias to a column means giving it a temporary name using the AS keyword while executing an SQL query. It does not affect the original column name in the table schema. Aliases are used for readability or to resolve naming conflicts in query results.

Syntax to Give an Alias to a Column


SELECT column_name AS alias_name
FROM table_name;

Example of Give an Alias to a Column


SELECT first_name AS fname, last_name AS lname
FROM employees;

The result set will have columns named fname and lname instead of first_name and last_name, respectively.

30. What is a primary key?

A primary key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It can not accept null, or duplicate values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Example of Primary Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

In the above table, Emp_Id is the primary key. Each employee has a unique ID assigned to them, ensuring that no two employees share the same ID.

31. What is a Unique Key? Elaborate with an example.

A unique key is a unique value amongst other values that are used to protect duplication of the values in a column of the table. The primary use of a unique key in a table is to prevent duplicate values. But, when it comes to the unique values, the primary key also includes them. So, there is one big difference that makes a unique key different, and it is that the unique key may have a NULL as a value but the primary key does not allow NULL as a value.

Example of Unique Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

In the Employee relation, a Unique Key could be applied to the email column, allowing null values to be present while maintaining the uniqueness requirement for non-null entries.

32. Describe foreign key in MySQL.

A foreign key is an attribute that is a Primary key in its parent table but is included as an attribute in another host table. It is a column (or columns) that references a column (most often the primary key) of another table.

Example of Foreign Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

Department Table

Dept_IdDept_NameDesignation
101VideoVideo_Maker
201Search_EngineSEO
301ContentWriter

To establish a relationship between these tables, we can introduce a foreign key in the "Employee" table that references the primary key of the "Department" table. Let's add a column called "Dept_ID" as a foreign key in the "Employee" table.

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmailDept_Id
1SakshiDelhi123456789abc@xyz.com101
2SouravHazaribagh223365796jkl@gmail.com201
3PragatiNanded175468965ghi@yahoo.com301

Read More: Different Types of SQL Keys

33. Differentiate Primary Key and Foreign Key

Comparison BasisPrimary KeyForeign Key
DefinitionA primary key is a unique identifier for each record in a table.A foreign key establishes a relationship between tables by referencing the primary key of another table.
BasicEnsures uniqueness and data integrity within a single table.Establishes relationships and maintains referential integrity between tables.
NULLThe primary key column value can never be NULL.The foreign key column can accept a NULL value
Count A table can have only one primary key. A table can have more than one foreign key.
Duplication No duplicate primary key values are allowed within the table. Can contain duplicate foreign key values, reflecting multiple records associated with the same reference.
Indexing Primary keys are automatically indexed to enhance data retrieval speed. Foreign keys can be indexed but are not automatically indexed.
Deletion The primary key value can't be removed from the table. If you want to delete it, then make sure the referencing foreign key does not contain its value. The foreign key value can be removed from the table without bothering that it refers to the primary key of another table.
Insertion Each new record must have a unique primary key value assigned. The foreign key can reference an existing primary key value or be NULL if the relationship is optional.
Temporary table Primary keys can be applied to temporary tables. Foreign keys can also be applied to temporary tables to establish relationships.
RelationshipPrimary keys define the basis for establishing relationships with other tables.Foreign keys establish relationships and connect data between related tables.

Read More: Differences between Primary Key and Foreign Key

34. What is a transaction? What are the different types of transactions in SQL Server?

We use transactions when we try to modify more than one table or view that is related to each other. Transactions affect SQL Server performance greatly. Since, when a transaction is initiated then it locks all the tables’ data that are used in the transaction. Hence during the transaction life cycle, no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

There are the following types of transactions in SQL Server as given below:

  1. Implicit Transaction
  2. Explicit Transaction

35. How to drop the primary key in MySQL?

In the following way, we can do this:


ALTER TABLE table_name  DROP PRIMARY KEY;

Example


ALTER TABLE Employees DROP PRIMARY KEY;

36. How to prevent duplicate records when making a query?

We can do this using the DISTINCT statement in combination with SELECT or creating a unique key for that table.

  1. 
    SELECT DISTINCT column1, column2
    FROM table_name;
    
  2. 
    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name UNIQUE (column1, column2);
    

MySQL Interview Questions and Answers for Experienced

37. What is a join in MySQL?

In MySQL, joins are used to query data from two or more tables. A join condition is a relationship among some columns in the data tables that take part in SQL join. Database tables are related to each other with SQL keys. We use this key relationship in SQL Joins.

Types of SQL Joins

  1. Inner Join

    The inner join in SQL selects all rows or columns that match in both tables or as long as the SQL condition is valid.

    Inner Join

  2. Left Outer Join / Left Join

    The LEFT JOIN retrieves all data from the left table (table1) and the rows or columns that match from the right table (table2). If neither table contains any matched rows or columns, it returns NULL.

    Left Outer Join / Left Join

    Left Outer Join / Left Join

  3. RIGHT JOIN / RIGHT Outer JOIN

    The RIGHT JOIN retrieves all data from the right table (table 2) as well as the matching rows or columns from the left table (table 1). If neither table contains any matched rows or columns, it returns NULL.

    RIGHT JOIN / RIGHT Outer JOIN

  4. Full Outer Join

    It is a result set that combines both LEFT JOIN & RIGHT JOIN. The connected tables return all records from both tables and place NULL if no matches are found in the table. It is also known as a FULL OUTER JOIN.

    Full Outer Join

  5. CROSS JOIN

    CARTESIAN JOIN, which returns the Cartesian product of two or more connected tables, is another name for it. The CROSS JOIN creates a table that merges each row from the first table with each row from the second table. There is no need to provide any conditions in CROSS JOIN.

    CROSS JOIN

  6. SELF JOIN- It is a SELF JOIN that was used to build a table by combining two tables. It names at least one table temporarily in an SQL statement.

    SELF JOIN

Read More: Different Types of SQL Joins

38. Describe the architecture of MySQL.

MySQL follows the client-server architecture. The client communicates with the server over the network using MySQL protocol.

Describe the architecture of MySQL.

  1. The top layer contains the services most network-based client/server tools or servers need such as connection handling, authentication, security, and so forth.
  2. The second layer contains much of MySQL’s brains. This has the code for query parsing, analysis, optimization, caching, and all the built-in functions.
  3. The third layer contains the storage engines that are responsible for storing and retrieving the data stored in MySQL.

39. Write a query to select random rows from a table.

For this, we will use the RAND() function in combination with ORDER BY and LIMIT. In some SQL flavors, such as PostgreSQL, it's called RANDOM().

Example

The below query will return five random rows from a table in MySQL


SELECT * FROM table_name
ORDER BY RAND()
LIMIT 5;

40. What is an access control list?

To provide secure access to some crucial data specific to its business, organizations create a sequence of permissions linked to various data objects. These lists are known as the access control list (ACL).

What is an access control list?

ACL serves as the basis for the server’s security that helps troubleshoot the connection problems for users. These are also known as grant tables cached by MySQL. MySQL verifies a user for authentication and grants permissions in a sequence when the user executes a command.

41. Explain the LIKE clause in MySQL.

The LIKE clause in MySQL is used to search for patterns in strings. It allows the use of wildcard characters such as '%' (matches zero or more characters) and '_' (matches any single character) to perform flexible pattern matching.

Syntax


SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Example


SELECT * FROM Employees
WHERE EmployeeName LIKE 'a%';

The above query selects all employees with EmployeeName starting with "a".


SELECT * FROM Employees
WHERE EmployeeName LIKE '%a';

The above query selects all employees with EmployeeName starting with "a".


SELECT * FROM Employees
WHERE EmployeeName LIKE '_r%';

The above query selects all employees with EmployeeName having "r" in the second position.


SELECT * FROM Employees
WHERE EmployeeName LIKE 'a%o';

The above query selects all employees with EmployeeName that starts with "a" and ends with "o".

42. What is normalization? What are the different normal forms?

Normalization or data normalization is a process of organizing the data into a tabular format (database tables) keeping four goals in mind.

  1. Reduce data redundancy
  2. Reduce data dependency
  3. Reduce data duplication
  4. Reduce data inconsistency

This leads to enhanced data integrity, more tables within the database, more efficient data access and security control, and greater query flexibility.

43. Discuss Equi Join.

Equi join is a special type of join also known as simple join in which we use only an equality operator("="). Hence, when you make a query for join using the equality operator, such a join query comes under Equi join.

The equi join creates a JOIN for equality or matching of the single or multiple column values of the relative tables. Apart from that, the equi join also creates the JOIN by using JOIN along with the ON clause and then providing the names of the columns with their relative tables to check equality using the equal operator.

Equijoin is a classified type of inner join that returns output by performing joining operations from two tables based on the common column that exists in them. The resultant result can have repeated column names.

Syntax of Inner Join


SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Inner Join Example

Below is a simple example showing the use of joins where the tables "tblDept" and "tblEmp" are being joined based on the DeptId column.


SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;

Inner Join Output

tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Ram
1
HR
1
Raju
2
IT
2
Soya
2
IT
2
Sam
3
ADMIN
3

In the join condition, you can also use other operators like <,>,<>.

44. What is Scaling in MySQL?

In MySQL, scaling capacity is the ability to handle the load, and it’s useful to think of the load from several different angles such as:

  • Quantity of data
  • Number of users
  • User activity
  • Size of related datasets

45. Can you define all the keys in a database table?

Practically in a database table, you can have only three types of keys: Primary Key, Unique Key, and Foreign Key. Other types of keys are only concepts of RDBMS that you need to know.

46. What is MySQL Workbench?

MySQL Workbench is a unified visual database designing or GUI tool used for working on MySQL databases. It is developed and maintained by Oracle which provides SQL development, data migration, and comprehensive administration tools for server configuration, user administration, backup, etc. We can use this Server Administration to create new physical data models, E-R diagrams, and SQL development. It is available for all major operating systems.

It is mainly available in three editions, which are given below:

  1. Community Edition (Open Source, GPL)
  2. Standard Edition (Commercial)
  3. Enterprise Edition (Commercial)

47. What are character manipulation functions? Give some examples.

Character manipulation functions represent a subset of character functions, and they're used to modify the text data.

Function NameDescription
CONCAT()joins two or more string values appending the second string to the end of the first one
SUBSTR()returns a part of a string satisfying the provided start and end points
LENGTH()returns the length of a string, including the blank spaces
REPLACE()replaces all occurrences of a defined substring in a provided string with another substring
INSTR()returns the numeric position of a defined substring in a provided string
LPAD() and RPAD()return the padding of the left-side/right-side character for right-justified/left-justified value
TRIM()removes all the defined characters, white spaces, from the left, right, or both ends of a provided string

48. How to find the nth highest value in a column of a table?

To find the nth highest value in a column of a table in MySQL, we can use the ORDER BY clause along with LIMIT to specify the desired row.

Syntax


SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT n-1, 1;

Example

The below query finds the 3rd highest value in the salary column of a table named employees.


SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 2, 1;

49. How to create a new user in MySQL?

A USER in MySQL is a record in the USER-TABLE. It contains the login information, account privileges, and the host information for MySQL account to access and manage the databases. We can create a new user account in the database server using the MySQL Create User statement. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts.

Syntax


CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password'; 

In the above syntax, the account_name has two parts one is the username, and another is the hostname, which is separated by @ symbol. Here, the username is the name of the user, and the hostname is the name of the host from which the user can connect with the database server.

50. How can you optimize a MySQL query?

MySQL query optimization involves various techniques such as indexing, using appropriate data types, minimizing the number of queries, optimizing table structure, avoiding unnecessary calculations, and utilizing query caching.

51. What is the use of the DELIMITER command in MySQL?

The DELIMITER command is used to change the default delimiter used by MySQL, which is a semicolon (;). DELEIMITER is used while writing TRIGGER and STORED PROCEDURES in MySQL.

Syntax


DELIMITER //

CREATE PROCEDURE my_procedure()
BEGIN
    -- SQL statements here;
END//

DELIMITER ;
Summary

In the above article, we covered from basics to advanced MySQL Interview Questions. These will be helpful for all from freshers to experienced professionals. So, if you are planning to have a good career in database development, go through these questions diligently. To learn MySQL from the beginning, enroll in our SQL Server Course

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Video Courses
  • 800+ Hands-On Labs
  • 400+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A Courses
  • 10+ Real-world Projects
  • Career Coaching Sessions
  • Email Support
Upto 60% OFF
Know More
Accept cookies & close this