MySQL Interview Questions and Answers PDF By ScholarHat.pdf

MySQL Interview Questions and Answers PDF By ScholarHat.pdf

1. Top 50 MySQL Interview Questions and Answers
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.
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. 
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
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.

2. have their own ecosystems and integration points, which may influence the choice of database for a particular project.
SQL
It is a structured query language that manages the relational
database management system.
MySQL
It is a relational database management system that uses SQL.
It is not an open-source language.
SQL supports XML and user-defined functions.
SQL can be implemented in various RDBMS such as PostgreSQL,
SQLite, Microsoft SQL Server, and others.
SQL itself is not a product and doesn’t have a license. It’s a standard
language.
MySQL is an open-source platform. It allows access to anyone.
It doesn’t support XML and any user-defined functions
MySQL is a specific implementation of an RDBMS that uses SQL for
querying and managing databases.
MySQL is open-source and available under the GNU General Public
License (GPL).
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
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
2. Differentiate MySQL from SQL.
3. What are SQL commands? Classify the SQL commands.

3. Function Name
ABS()
ROUND()
CEIL()
FLOOR()
EXP() LOG()
NOWO
CURRDATEO
CONCAT (X, Y)
DATEDIFF (X, Y)
Functionality
Returns the absolute value of a number.
Rounds a number to a specified number of decimal places.
Returns the smallest integer greater than or equal to a given number.
Returns the largest integer less than or equal to a given number
Calculates the exponential value of a number
Calculates the natural logarithm of a number
Returns the current date and time as a single value
Returns the current date and time
Concatenates two string values creating a single string output
Determines the difference between the two dates
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.
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?
5. What is a query in MySQL? What are the types of SQL queries?

4. Example
Example
Example
Example
Example
Example
Example
2. Data Manipulation Queries: These queries modify data in the database tables. They include INSERT, UPDATE, and DELETE statements.
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.
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.
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.
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.
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
 . 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.
6. What is a subquery? What are the types of SQL subqueries?
BEGIN TRANSACTION;
GRANT SELECT, INSERT ON table TO user;
SELECT column1, column2 FROM table WHERE condition;
INSERT INTO table (column1, column2) VALUES (value1, value2);
CREATE TABLE table_name (column1 datatype, column2 datatype);
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);

5. Type Name
TINYINT
SMALLINT
Description
Very Small Integer
Small Integer
4. Nested Subquery: It is a subquery inside another subquery.
3. Correlated Subquery: It is a subquery related to the information from the outer query.
2. Multi-row: It returns at least two rows. It can be used with operators like IN, ANY, or ALL.
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.
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.
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.
Example
Example
Example
Example
Example
7. What are the different data types in MySQL?
SELECT column1, column2
FROM table1 t1
WHERE (column1, column2) IN
(SELECT column1, column2
FROM table2 t2
WHERE t1.column3 = t2.column3);
SELECT column1 FROM table1 WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
SELECT column1 FROM table1 WHERE column2 = (SELECT MAX(column2) FROM (SELECT * FROM table2) AS subquery);
SELECT column1 FROM table1 t1 WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t1.column3 = t2.column3);

6. MEDIUMINT
INT
BIGINT
DECIMAL
FLOAT
DOUBLE
BIT
Type Name
CHAR
VARCHAR
BINARY
VARBINARY
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET
NULL
2. String Data Types: They are used for storing text.
Medium-sized Integer
Standard Integer
Large Integer
Fixed-point number
Single-precision floating-point number
Double-precision floating-point number
Bit-field
Description
fixed-length nonbinary(character) string
variable-length nonbinary string
fixed-length binary string
variable-length binary string
Very small BLOB(binary large object)
Small BLOB
Medium-sized BLOB
Large BLOB
A very small nonbinary string
Small nonbinary string
Medium-sized nonbinary string
Large nonbinary string
An enumeration; each column value is assigned, and one enumeration member
A set; each column value is assigned zero or more set members
Example
Example
CREATE TABLE example_string_types (


name VARCHAR(300),
father_name CHAR(30),
CREATE TABLE example_numeric_types (



);
roll INT,
salary DECIMAL(15,2),
score DOUBLE(7,2)

7. Type Name
GEOMETRY
POINT
LINESTRING
POLYGON
Type Name
DATE
TIME
DATETIME
TIMESTAMP
YEAR
Meaning
The base data type for all spatial data types.
A single location in space
A sequence of points that form a line
A planar surface representing a multi-sided shape
4. Spatial Data Types: These are used to store geometric data. Some of these types are:
3. Temporal Data Types: They are for date and time and a combination of date and time.
Meaning
A date value, in ' CCYY-MM-DD ' Format
A Time value, in ' hh : mm :ss ' format
Date and time value, in ' CCYY-MM-DD hh : mm :ss ' format
A timestamp value, in ' CCYY-MM-DD hh : mm :ss ' format
A year value, in CCYY or YY format
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
A MySQL database contains one or many tables containing several records or rows. Within these rows, data is contained in various columns or
fields.
Example
Example
8. What does a MySQL database contain?
9. What are the different tables present in MySQL?

);
comments TEXT
CREATE TABLE example_spatial_types (



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



);
birthday DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
event_time DATETIME

8. 5. ISAM
Clustered Index
A clustered index is faster.
The clustered index requires less memory for
operations.
the clustered index is the main data.
Non-Clustered Index
A non-clustered index is slower.
A non-clustered index requires more memory for operations.
the non-clustered index is the copy of data.
A table can have only one clustered index.
The clustered index has the inherent ability to store data
on the disk.
Clustered index store pointers and not data.
A table can have multiple non-clustered indexes.
A non-clustered index does not have the inherent ability to store data on the disk.
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 the index.
Here, the logical order of the index does not match the physical stored order of
the rows on the disk.
the index key defines the order of data within a table.
Here table records are physically reordered to match the
index.
The size of the primary clustered index is large. The size of the non-clustered index is comparatively smaller.
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.
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.
10. What are SQL dialects? Describe with examples.
12. Differentiate between Clustered and Non-Clustered Index.
11. What is an index? What are the various ways to create an index?

9. We can create at most 16 indexed columns in a table.
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.
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
CHAR
CHAR datatype is used to store character strings of fixed
length
If the length of the string is less than set or fixed-length then it
is padded with extra memory space.
VARCHAR
VARCHAR datatype is used to store character strings of variable length
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”
Storage size of CHAR datatypes is equal to n bytes i.e. set
length
VARCHAR stands for “Variable Character”
The 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 dataWe should use the VARCHAR datatype when we expect the data values in a
values in a column to be of the same length. column to be of variable length.
CHAR takes 1 byte for each character VARCHAR takes 1 byte for each character and some extra bytes for holding
length information
Performance is not good as compared to CHAR
Better performance than VARCHAR
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
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.
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.
14. What is a schema?
15. What is an SQL comment?
13. How char data type is different from varchar?
17. How many index columns can be created in a table?
16. What makes MySQL so popular and widely used RDBMS?
18. What is a constraint? What are the different types of constraints in MySQL?

10. Operator
+
-
*
/
%
Operator
=
>
<
>
=
<
=
<
>
1. Arithmetic Operators
2. Comparison Operators
Description
Equal to
Greater than
Less than
Greater than or equal to
Less than or equal to
Not equal to
Description
Performs addition
Performs subtraction
Performs multiplication
Performs division
Perform modulus
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.
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?

11. Operator
+=
-=
*=
/=
%=
&=
^-
=
|*
=
Operator
&
|
^
Operator
ALL
AND ANY
BETWEEN
EXISTS IN
LIKE
NOT
OR
SOME
4. Logical Operators
5. Bitwise Operators
!
=
!
>
!
<
3. Compound Operators
Not equal to
Not greater than
Not less than
Description
Add equals
Subtract equals
Multiply equals
Divide equals
Modulo equals
Bitwise AND equals
Bitwise exclusive equals
Bitwise OR equals
Description
Bitwise AND
Bitwise OR
Bitwise exclusive OR
Description
TRUE if all of the subquery values meet the condition
TRUE if all the conditions separated by AND is TRUE
TRUE if any of the subquery values meet the condition
TRUE if the operand is within the range of comparisons
TRUE if the subquery returns one or more records
TRUE if the operand is equal to one of a list of expressions
TRUE if the operand matches a pattern
Displays a record if the condition(s) is NOT TRUE
TRUE if any of the conditions separated by OR is TRUE
TRUE if any of the subquery values meet the condition
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.
20. What is the use of ENUM in MySQL?
MySQL Interview Questions & Answers for Intermediate

12. 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.
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.
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
 . AFTER DELETE
Let's suppose there's a table named Employees with the following schema:
We want to create a trigger that automatically updates the is_active column to true for employees whose salary is greater than $5000.
Syntax
Example to illustrate the working of triggers in MySQL
DELIMITER //
CASE





WHEN condition_1 THEN value_1
WHEN condition_2 THEN
value_2 WHEN condition_3
THEN value_3 ... ELSE value
END;
CREATE TABLE employees (




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

13. 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.
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:
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 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.
23. What are views in MySQL? How do you create and execute views in MySQL?
Example of a View
Syntax to Create a View
DELIMITER ;
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE VIEW [Indian Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'India';
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//
INSERT INTO employees (name, salary) VALUES ('John Doe', 6000);

14. Execution of a View
Example Illustrating a one-to-one relationship between two tables: users and profiles
Example Illustrating a one-to-many relationship between two tables: orders and order_items
24. Describe the relationships in MySQL.
The relationship in MySQL is classified into three types:
Here, each order in the orders table can have multiple corresponding items in the order_items table, identified by the order_id foreign key.
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.
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.
SELECT * FROM [Indian Customers];
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)
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)
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)

15. To insert data into these tables:
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.
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
25. What is a heap table in MySQL?
CREATE TABLE courses (


);
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100)
CREATE TABLE students (


);
student_id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100)
-- 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');
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)
-- 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

16. 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:
28. How to find duplicate rows in the MySQL table?
26. What are the differences between Nested Query and Correlated Query?
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.
Parameters
Definition
Nested Query
A query is written inside another query and the result of the inner
query is used in the execution of the outer query.
Bottom-up approach i.e. Inner query runs first, and only once.
The outer query is executed with the result from the Inner query.
Inner query execution is not dependent on Outer query.
Performs better than Correlated Query but is slower than Join
Operation.
Correlated Query A query is nested inside another query
and an inner query
uses values from the outer query.
Top to Down Approach i.e. Outer query executes first and
for every Outer query row Inner query is executed.
The inner query is dependent on the Outer query.
Performs slower than both Nested Query and Join
operations as for every outer query inner query is
executed.
Approach
Dependency
Performance
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.
Syntax to Rename a Column
Example to Rename a Column
SELECT column1, column2, ..., COUNT(*)
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;
ALTER TABLE my_table
CHANGE old_column_name new_column_name VARCHAR(50);
ALTER TABLE table_name
CHANGE old_column_name new_column_name column_definition;

17. Emp_I
d 1 2
3
Emp_Id
1
Employee Table
Employee Table
Emp_Name
Sakshi
Sourav
Pragati
Emp_Name
Sakshi
Address
Delhi
Address
Delhi
Hazaribagh
Nanded
Mobile_No
12345678
9
22336579
6
175468965
Mobile_No
123456789
The result set will have columns named fname and lname instead of first_name and last_name, respectively.
Email
[email protected]
Email
[email protected]
[email protected]
[email protected]
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.
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.
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.
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
Example of Primary Key
Syntax to Give an Alias to a Column
Example of Give an Alias to a Column
30. What is a primary key?
31. What is a Unique Key? Elaborate with an example.
SELECT column_name AS alias_name
FROM table_name;
SELECT first_name AS fname, last_name AS lname
FROM employees;

18. 2
3
Emp_I
d 1 2 3
Emp_I
d 1 2 3
Dept_I
d 101
201
301
Comparison
Basis
Definition
Employee Table
Department Table
Sourav
Pragati
Emp_Name
Sakshi
Sourav
Pragati
Primary Key
Emp_Name
Sakshi
Sourav
Pragati
Read More: Different Types of SQL Keys
Dept_Name
Video
Search_Engine
Content
Address
Delhi
Hazaribagh
Nanded
Hazaribagh
Nanded
Address
Delhi
Hazaribagh
Nanded
A primary key is a unique identifier for each record in a table.
Mobile_No
12345678
9
22336579
6
17546896
5
22336579
6
17546896
5
Mobile_No
12345678
9
22336579
6
17546896
5
Foreign Key
Designation
Video_Maker
SEO
Writer
Email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Email
[email protected]
[email protected]
[email protected]
Dept_I
d 101
201
301
A foreign key establishes a relationship between
tables by referencing the primary key of another table.
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.
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
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.
32. Describe foreign key in MySQL.
33. Differentiate Primary Key and Foreign Key
Example of Foreign Key

19. Basic
Deletion
Indexing
Insertion
NULL
Count
Duplication
Temporary
table
Relationship
In the following way, we can do this:
Read More: Differences between Primary Key and Foreign Key
We can do this using the DISTINCT statement in combination with SELECT or creating a unique key for that table.
1.
Ensures uniqueness and data integrity within a single table. Establishes relationships and maintains referential
integrity between tables.
The foreign key column can accept a NULL value
A table can have more than one foreign key.
Can contain duplicate foreign key values, reflecting
multiple records associated with the same reference.
The primary key column value can never be NULL.
A table can have only one primary key.
No duplicate primary key values are allowed within the table.
Primary keys are automatically indexed to enhance data retrieval
speed.
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.
Foreign keys can be indexed but are not automatically
indexed.
The foreign key value can be removed from the table
without bothering that it refers to the primary key of
another table.
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.
Foreign keys can also be applied to temporary tables
to establish relationships.
Foreign keys establish relationships and connect data
between related tables.
Primary keys can be applied to temporary tables.
Primary keys define the basis for establishing relationships with
other tables.
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?
36. How to prevent duplicate records when making a query?
34. What is a transaction? What are the different types of transactions in SQL Server?
Example
SELECT DISTINCT column1, column2
ALTER TABLE Employees DROP PRIMARY KEY;
ALTER TABLE table_name DROP PRIMARY KEY;

20. 2.
Read More: Different Types of SQL Joins
MySQL follows the client-server architecture. The client communicates with the server over the network using MySQL protocol.
 . 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.
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.
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.
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.
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.
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.
MySQL Interview Questions and Answers for Experienced
37. What is a join in MySQL?
38. Describe the architecture of MySQL.
FROM table_name;
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

21. The below query will return five random rows from a table in MySQL
The above query selects all employees with EmployeeName starting with "a".
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().
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).
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.
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.
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.
40. What is an access control list?
41. Explain the LIKE clause in MySQL.
39. Write a query to select random rows from a table.
Syntax
Example
Example
SELECT * FROM Employees
SELECT * FROM table_name
ORDER BY RAND()
LIMIT 5;
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
SELECT * FROM Employees
WHERE EmployeeName LIKE 'a%';

22. The above query selects all employees with EmployeeName starting with "a".
The above query selects all employees with EmployeeName having "r" in the second position.
The above query selects all employees with EmployeeName that starts with "a" and ends with "o".
Below is a simple example showing the use of joins where the tables "tblDept" and "tblEmp" are being joined based on the DeptId column.
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.
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.
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.
43. Discuss Equi Join.
42. What is normalization? What are the different normal forms?
Inner Join Example
Syntax of Inner Join
WHERE EmployeeName LIKE '%a';
SELECT * FROM Employees
WHERE EmployeeName LIKE '_r%';
SELECT * FROM Employees
WHERE EmployeeName LIKE 'a%o';
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

23. Inner Join Output
tblEmp.Name
Ram
Raju
Soya
Sam
Function Name
CONCAT()
SUBSTR()
LENGTH()
REPLACE()
INSTR()
tblEmp.DeptID
1
2
2
3
In the join condition, you can also use other operators like <,>,<>.
tblDept.Name
HR IT
IT
ADMIN
Description
joins two or more string values appending the second string to the end of the first one
returns a part of a string satisfying the provided start and end points
returns the length of a string, including the blank spaces
replaces all occurrences of a defined substring in a provided string with another substring
returns the numeric position of a defined substring in a provided string
Character manipulation functions represent a subset of character functions, and they're used to modify the text data.
tblDept.DeptID
1
2
2
3
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
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.
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)
44. What is Scaling in MySQL?
46. What is MySQL Workbench?
45. Can you define all the keys in a database table?
47. What are character manipulation functions? Give some examples.
SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;

24. LPAD() and RPAD()
TRIM()
The below query finds the 3rd highest value in the salary column of a table named employees.
return the padding of the left-side/right-side character for right-justified/left-justified value
removes all the defined characters, white spaces, from the left, right, or both ends of a provided string
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.
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.
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.
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.
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.
49. How to create a new user in MySQL?
50. How can you optimize a MySQL query?
51. What is the use of the DELIMITER command in MySQL?
48. How to find the nth highest value in a column of a table?
Syntax
Syntax
Syntax
Example
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 2, 1;
SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT n-1, 1;
CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';

25. 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
DELIMITER ;
DELIMITER //
CREATE PROCEDURE my_procedure()
BEGIN
-- SQL statements here;
END//