Top 50 MySQL Interview Questions and Answers

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.

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

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?

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 Name	Description
   TINYINT	Very Small Integer
   SMALLINT	Small Integer
   MEDIUMINT	Medium-sized Integer
   INT	Standard Integer
   BIGINT	Large Integer
   DECIMAL	Fixed-point number
   FLOAT	Single-precision floating-point number
   DOUBLE	Double-precision floating-point number
   BIT	Bit-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 Name	Description
   CHAR	fixed-length nonbinary(character) string
   VARCHAR	variable-length nonbinary string
   BINARY	fixed-length binary string
   VARBINARY	variable-length binary string
   TINYBLOB	Very small BLOB(binary large object)
   BLOB	Small BLOB
   MEDIUMBLOB	Medium-sized BLOB
   LONGBLOB	Large BLOB
   TINYTEXT	A very small nonbinary string
   TEXT	Small nonbinary string
   MEDIUMTEXT	Medium-sized nonbinary string
   LONGTEXT	Large nonbinary string
   ENUM	An enumeration; each column value is assigned, and one enumeration member
   SET	A 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 Name	Meaning
   DATE	A date value, in ' CCYY-MM-DD ' Format
   TIME	A Time value, in ' hh : mm :ss ' format
   DATETIME	Date and time value, in ' CCYY-MM-DD hh : mm :ss ' format
   TIMESTAMP	A timestamp value, in ' CCYY-MM-DD hh : mm :ss ' format
   YEAR	A 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
   GEOMETRY	The base data type for all spatial data types.
   POINT	A single location in space
   LINESTRING	A sequence of points that form a line
   POLYGON	A 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.

13. How char data type is different from varchar?

14. What is a schema?

15. What is an SQL comment?

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.

1. Arithmetic Operators

   Operator	Description
   +	Performs addition
   -	Performs subtraction
   *	Performs multiplication
   /	Performs division
   %	Perform modulus

2. Comparison Operators

   Operator	Description
   =	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

   Operator	Description
   +=	Add equals
   -=	Subtract equals
   *=	Multiply equals
   /=	Divide equals
   %=	Modulo equals
   &=	Bitwise AND equals
   ^-=	Bitwise exclusive equals
   |*=	Bitwise OR equals

4. Logical Operators

   Operator	Description
   ALL	TRUE if all of the subquery values meet the condition
   AND	TRUE if all the conditions separated by AND is TRUE
   ANY	TRUE if any of the subquery values meet the condition
   BETWEEN	TRUE if the operand is within the range of comparisons
   EXISTS	TRUE if the subquery returns one or more records
   IN	TRUE if the operand is equal to one of a list of expressions
   LIKE	TRUE if the operand matches a pattern
   NOT	Displays a record if the condition(s) is NOT TRUE
   OR	TRUE if any of the conditions separated by OR is TRUE
   SOME	TRUE if any of the subquery values meet the condition

5. Bitwise Operators

   Operator	Description
   &	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.

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 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?

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

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

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

Department Table

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

Read More: Different Types of SQL Keys

33. Differentiate Primary Key and Foreign Key

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.

   

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.

   

   

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.

   

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.

   

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.

   

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.

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

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

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.

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