11
OctTop 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.
SQL | MySQL |
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). |
Read More: Difference Between SQL and MySQL |
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
- 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:
- CREATE
- ALTER
- DROP
- TRUNCATE
- 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:
- INSERT
- UPDATE
- DELETE
- 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.
- 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:
- SAVEPOINT
- ROLLBACK
- COMMIT
- 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:
- GRANT
- REVOKE
Read More: Basics of SQL Commands
4. What are the frequently used MySQL functions?
Function Name | Functionality |
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 |
NOWO | Returns the current date and time as a single value |
CURRDATEO | Returns 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 MySQL returns the resultant record/records.
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:
- 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;
- 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);
- 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);
- 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;
- 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;
- 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;
- 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
- 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);
- 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);
- 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);
- 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);
- 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:
- 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) );
- 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 );
- 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 );
- 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:
- MyISAM
- Heap
- Merge
- INNO DB
- 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 Index | Non-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?
CHAR | VARCHAR |
CHAR datatype is used to store character strings of fixed length | VARCHAR 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 length | 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 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 character | VARCHAR takes 1 byte for each character and some extra bytes for holding length information |
Better performance than VARCHAR | Performance is not good as compared to CHAR |
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.
- Arithmetic Operators
Operator Description + Performs addition - Performs subtraction * Performs multiplication / Performs division % Perform modulus - 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 - Compound Operators
Operator Description += Add equals -= Subtract equals *= Multiply equals /= Divide equals %= Modulo equals &= Bitwise AND equals ^-= Bitwise exclusive equals |*= Bitwise OR equals - 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 - 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:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- 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:
- AFTER INSERT ON employees specify that the trigger should activate after an insert operation on the employees table.
- FOR EACH ROW indicates that the trigger will be executed for each row affected by the insert operation.
- NEW.salary refers to the salary value of the newly inserted row.
- 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:
- 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.
- 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.
- 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:
- students: Stores information about students.
- courses: Stores information about courses.
- 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?
Parameters | Nested Query | Correlated Query |
Definition | A 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. |
Approach | Bottom-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. |
Dependency | Inner query execution is not dependent on Outer query. | The inner query is dependent on the Outer query. |
Performance | Performs 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_Id | Emp_Name | Address | Mobile_No | |
1 | Sakshi | Delhi | 123456789 | abc@xyz.com |
2 | Sourav | Hazaribagh | 223365796 | jkl@gmail.com |
3 | Pragati | Nanded | 175468965 | ghi@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_Id | Emp_Name | Address | Mobile_No | |
1 | Sakshi | Delhi | 123456789 | abc@xyz.com |
2 | Sourav | Hazaribagh | 223365796 | jkl@gmail.com |
3 | Pragati | Nanded | 175468965 | ghi@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 the 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_Id | Emp_Name | Address | Mobile_No | |
1 | Sakshi | Delhi | 123456789 | abc@xyz.com |
2 | Sourav | Hazaribagh | 223365796 | jkl@gmail.com |
3 | Pragati | Nanded | 175468965 | ghi@yahoo.com |
Department Table
Dept_Id | Dept_Name | Designation |
101 | Video | Video_Maker |
201 | Search_Engine | SEO |
301 | Content | Writer |
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_Id | Emp_Name | Address | Mobile_No | Dept_Id | |
1 | Sakshi | Delhi | 123456789 | abc@xyz.com | 101 |
2 | Sourav | Hazaribagh | 223365796 | jkl@gmail.com | 201 |
3 | Pragati | Nanded | 175468965 | ghi@yahoo.com | 301 |
Read More: Different Types of SQL Keys
33. Differentiate Primary Key and Foreign Key
Comparison Basis | Primary Key | Foreign Key |
Definition | A 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. |
Basic | Ensures uniqueness and data integrity within a single table. | Establishes relationships and maintains referential integrity between tables. |
NULL | The 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. |
Relationship | Primary 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:
- Implicit Transaction
- 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.
SELECT DISTINCT column1, column2 FROM table_name;
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
- 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.
- 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.
- 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.
- 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.
- 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.
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.
- The top layer contains the services most network-based client/server tools or servers need such as connection handling, authentication, security, and so forth.
- 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.
- 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.
- Reduce data redundancy
- Reduce data dependency
- Reduce data duplication
- 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:
- Community Edition (Open Source, GPL)
- Standard Edition (Commercial)
- 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 Name | Description |
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
Download this PDF Now - MySQL Interview Questions and Answers PDF By ScholarHat |