Loading…

MySQL Interview Questions and Answers

By Admin
Last update: 25 Nov 2020, 72 Questions

In this computer-driven world, when everything is stored on machines, it is important to have database management to store & handle it effectively. Then MySQL comes into the picture. It is the famous open-source Relational Database Management System which makes data handling and storing easy and saves a lot of time. Backed by Oracle, it provides access to multiple users. If you want to learn more about it, then you should keep yourself posted with MySQL interview questions. And you would find the best ones here on our platform. We cover all the latest and important concepts and questions of MySQL.

Quick Facts About MySQL
What is the latest version of MySQL? 8.0.22 released in 19th October 2020
Who developed MySQL? Oracle Corporation.
What language does MySQL use? C and C++ language
About License GNU Public License
Official Website https://www.mysql.com

Most Frequently Asked MySQL Interview Questions

Here in this article, we will be listing frequently asked MySQL Interview Questions and Answers with the belief that they will be helpful for you to gain higher marks. Also, to let you know that this article has been written under the guidance of industry professionals and covered all the current competencies.

1. How would you select all the users whose phone is null MySQL?

SELECT id, name FROM users WHERE phone IS NULL;

3 0
2. How to create a trigger in MySQL? Explain with example

In MySQL, a trigger corresponds to a set of actions running automatically when a particular change operation like SQL INSERT, UPDATE, or DELETE query is performed on a table.

You can either write command in MySQL like this:

Example

                                                    

CREATE TRIGGER data_backup BEFORE UPDATE ON users
   FOR EACH ROW
   BEGIN
     IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
     ELSEIF NEW.amount > 100 THEN
       SET NEW.amount = 100;
     END IF;
   END;//

3 0
3. What are the drivers in MySQL?

In MySQL, standards-based drivers for JDBC, ODBC, and .Net are provided in order to enable developers in building database applications with their language of choice.

Following is a list of drivers available in MySQL:

  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxj
4 2
4. What is the difference between IS NULL & IS NOT NULL?

IS NULL checks to see if the cell is empty but IS NOT NULL checks to see if the cell is not empty.

Example :

SELECT id FROM users WHERE 'user_type' IS NOT NULL;

SELECT id FROM users WHERE 'user_type' NOT IS NULL;

Note: This is very essential MySQL query interview questions.

7 0
5. What is the use of CONCAT() in Mysql?

It is used to concatenate two or more strings.

Example

                                                    

SELECT CONCAT('BestInterview', ' ', 'Question') AS 'Name';

4 0
6. List some comparisons operators used in Mysql?

Comparisons operators are used to comparing one expression to another value or expression. It is just like = , < , > , => , =<, <>

3 0
7. What are the difference between NOW and CURRENT_DATE in MySQL?
NOW CURRENT_DATE
NOW() give you the current date time in the format 'YYYY-MM_DD HH:MM: SS' CURRENT_DATE() will only give you the current date in format "YYYY-MM_DD"
9 0
8. How to get Nth highest salary from table in a MySQL?

SELECT * FROM EmployeeTable1 AS Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.amount)) FROM EmployeeTable2 AS Emp2 WHERE Emp2.amount > Emp1.amount)

9 6
9. What is TRIGGERS and how it can be used in MySQL?

In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. It's activation time can be BEFORE or AFTER

Example

                                                    

mysql> delimiter //

mysql> CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;

mysql> delimiter ;

12 2
10. Explain the difference between primary key and candidate key in Mysql?
Primary Key Candidate Key
It is a column that uniquely identifies a record. In Mysql, only one candidate key can behave like Primary Key. It can be any column that can qualify as a unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave like as a primary key.
11 0
11. How do I import database through command line?

MySQL -u username -p database_name < file.sql
Here username is your database username like "root," database_name is your database name, file.sql is your SQL file name with complete path.

11 0
12. What is the default port for MySQL and how it can change?

The default port is 3306. We can change it in /etc/MySQL/my.conf there is a port variable. We can update this port according to our need

12 1
13. What is the difference between the primary and unique key in Mysql?
Primary Key Unique Key
A table can hold only one primary key It can be more than one unique key in one table
A Primary Key cannot be NULL. A Unique key can have NULL.
18 1
14. How to create case insensitive query in MySQL?

The standard way to perform case insensitive queries in SQL is to use the SQL upper or lower functions like the following:

select * from users where upper(first_name) = 'AJAY';

OR

select * from users where lower(first_name) = 'ajay';

The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.

17 1
15. Is MySQL case-sensitive?

Mysql is not case sensitive. Its case-sensitivity relies on the underlying operating system as OS determines the case sensitivity of tables names and database. In windows, database and table names are not case sensitive but in the case of UNIX, it is case sensitive in nature. Especially on the UNIX host, database accepts the upper case and lower-case table names.

31 3
16. What are the different ways to optimize a MySQL query?
  • Don't use "sp_" while creating a stored procedure.
  • Don't use "*" while you are using the SELECT command. Also avoid unnecessary columns in SELECT clause.
  • You can use table aliases while writing queries.
  • Avoid the usage of the wildcard (%) at the starting of a predicate.
  • DISTINCT and UNION need to be used solely if it is necessary.
24 4
17. What is cardinality in MySQL?

In MySQL, the time period cardinality refers to the specialty of facts values that can be put into columns. It is a type of property that influences the potential to search, cluster and kind data.

Cardinality can be of two sorts which are as follows
  • Low Cardinality − All values for a column have to be the same.
  • High Cardinality − All values for a column ought to be unique.
16 1
18. What are the difference between and MyISAM and InnoDB?

These are most commonly used storage engine in MySQL are MyISAM and InnoDB.

Difference between MyISAM and InnoDB are given below:-
  • MyISAM does no longer support transactions, however InnoDB supports transactions.
  • MyISAM helps Table-level Locking, however InnoDB supports Row-level Locking.
  • MyISAM helps full-text search, however InnoDB does not.
  • MyISAM designed for the need for speed but InnoDB designed for most performance.
  • MyISAM does now not aid overseas keys, but InnoDB helps foreign keys.
  • We can use commit and rollback with InnoDB however not in MyISAM.
  • MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation, and Durability) however InnoDB supports the ACID property.
  • In the InnoDB table, the AUTO_INCREMENT field is a section of the index but now not in MyISAM.
  • MyISAM stores its tables, data, and indexes in disk area the usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.
18 0
19. What is the difference between delete, drop and truncate?
TRUNCATE
  • It removes all rows from a table.
  • It does not require a WHERE clause.
  • Truncate cannot be used with indexed views.
  • It is performance wise faster.
DELETE
  • It removes Some or All rows from a table.
  • A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query.
  • It removes rows one by at a time.
  • It can be used with indexed views.
DROP
  • It removes a table from the database.
  • All table's rows, indexes, and privileges will also be removed when we used this command.
  • The operation cannot be rolled back.
26 0
20. Write a query to fetch duplicate records from a table using MySQL?

SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;

30 3
21. What is the difference between MyISAM dynamic and MyISAM static in MySQL?
MyISAM Dynamic MyISAM static
This is a table storage format which is basically used when some columns in the existing tables use variable data types such as VARCHAR, BLOB, TEXT, VARBINARY, etc.) This is also a table storage format which is basically used when all columns in the existing table’s uses fixed width data types.
2 0
22. What are the difference between MongoDB and MySQL?
MongoDB MySQL
The representation of data in MongoDB is totally different from Mysql, as in MongoDB data is represented in the collection of JSON documents.
MongoDB is an object-oriented database
The representation of Mysql database is totally different form Mongodb, as in Mysql data is stored in the form of tables (Row and columns).
Mysql is a structured query database.
Related Article: SQL Interview Questions
1 0
23. How to store binary data in MySQL?

Binary data can be stored in Mysql in different ways such as

  • The short length of binary data can be stored in the form of string as a VARCHAR
  • A BLOB data type allows users to store an arbitrary amount of binary data.
2 0
24. How to delete the repeated records from a table?

DELETE FROM Admin WHERE id IN(SELECT * FROM(SELECT id FROM Admin GROUP BY name HAVING COUNT(*) > 1) AS A)

0 0
25. What is regex in MySQL?

Regex is an operational and powerful pattern that can help the users for the implementation of a powerful search utility function for database systems. Moreover, it is an operator which is used when we require matching regular expressions. In addition to this, it also supports a wider range of meta-characters which allow flexibility and more control over the database structure when performing pattern matching.

Example

                                                    

Match beginning of string(^):

SELECT name FROM users WHERE name REGEXP '^bestinterviewquestion';

0 0
26. How to make a copy values from one column to another in Mysql?

UPDATE `your_table_name` SET new_field=old_field

2 0
27. What are the difference between char_length and length in MySQL?
LENGTH() CHAR_LENGTH()
It returns the total length of the string which is measured in bytes. It returns the total length of the strings measured in characters.
1 0
28. What is the use of enum in MySQL? How it is different from set?

In MySQL, an ENUM is actually a string object whose value is selected from the list of permitted values pre-defined during the time of a column creation. It is used because it provides compact data storage in addition to readable queries and output.

Here’s the difference between an ENUM and a SET

ENUM SET
The value should be one listed in the column definition or internal numeric equivalent. Must be an empty string consisting of values listed in column
1 0
29. What are the differences between mysql_fetch_array () mysql_fetch_object () mysql_fetch_row ()?
mysql_fetch_array() mysql_fetch_row() mysql_fetch_object()
This returns an array of strings corresponding to a fetched row, or FALSE if there are no rows. It returns with a numerical array of strings corresponding to a fetched row, or FALSE if there are no rows. This returns with an object, or a FALSE value if there are no rows.
The returned array type depends on how result_type is defined Here, the row is returned as an array. This fetches a result row as an object type.
3 0
30. How to write optimized query in MySQL?

The optimized query can be written with the use of the following clause- ‘where’, ‘order-by’ and group-by’. Apart from theses clause like statements with union clause is also used for the optimization of the queries.

4 1
31. What is slow query log in MySQL?

In general slow query, the log is basically used in Mysql for the determination of which database queries will take a long duration to run. Moreover, a slow query log in MySQL simplifies that operations in the context of efficient and time-consuming queries.

In order to enable the slow query log the command- Get global slow_query_log – ‘ON’; is used

2 0
32. What's new in MySQL 8?

There are a number of unique features that are incorporated in Mysql 8 include-

  • Unicode 9.0 support
  • Window functions
  • Recursive SQL syntax statements
  • Support Native JSON data
  • Support for document store Functionality
0 0
33. What is federated tables in MySQL?

It is a table that points to a table in another MySQL database instance. It can be seen as a view of this remote database. Other RDBMS have the same concepts for example database links.

0 0
34. How to control the max size of a HEAP table?

The maximum size of the HEAP table can be controlled by the MySQL config variable called max_heap_table_size.

1 0
35. What is MySQL and why it is used?

MySQL is one of the widely used open source database platforms in the world today. The data in a MySQL database is stored in the form of tables, which consist of many columns and rows. It is a reliable, stable and robust platform with advanced performance and security features.

MySQL is used for a wide range of purposes like data warehousing, e-commerce, web applications, and logging applications.

6 1
36. Is Mysql is a programming language?

It is a language but not a Programming Language. It is a relational database management system.

4 0
37. What are the advantages of Mysql?
  • Data Security
  • It is used to retrieve large amounts of records from a database quickly and efficiently.
  • On-Demand Scalability
  • High Performance
  • Excellent Uptime
  • Reduced Total Cost of Ownership
3 0
38. What are Storage Engines in Mysql?

Storage engines are Mysql components, that can handle the operations for different table types to store and manage information in a database.

  • InnoDB
  • MyISAM
  • MEMORY
  • MERGE
  • EXAMPLE
  • CSV
  • ARCHIVE etc
4 1
39. How to change the database engine in Mysql?

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

2 0
41. How to use GROUP_CONCAT with different SEPARATOR in MySQL?

SELECT id, GROUP_CONCAT(department SEPARATOR ' ') AS department FROM employee group by id;

0 0
42. List data types in Mysql? Explain

Mysql supports various data types. Some most common used data types are given below:-

  • CHAR
  • VARCHAR
  • TINYTEXT
  • TEXT
  • INT
  • FLOAT
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIME
  • ENUM
  • DECIMAL
  • TINYINT
  • LONGTEXT
  • MEDIUMTEXT etc
0 0
43. What is the difference between BETWEEN and IN operators in Mysql?

Between operator is used to select a range of data between two values. It can be texts, numbers, and dates, etc.

Syntax: Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2

IN operator is used to check for a value in the given set of values.

Syntax: Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)

0 0
44. How to get a version of Mysql?

You can use this query in your phpmyadmin SELECT version();

1 0
45. What are aggregate functions in MySQL?

The MySQL aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.

MySQL provides many aggregate functions that are listed below.

  • AVG()
  • COUNT()
  • SUM()
  • MIN()
  • MAX() etc
0 0
46. Explain the difference between procedure and function in MySQL?

Functions are generally used for computations, but procedures are usually used for executing business logic.

The function returns only 1 value, but the procedure can return multiple values.

The function can be called directly by SQL statement like select functionName from dual, but procedures cannot be called directly.

The function can be used in the SQL Query directly, but a procedure can not be used in a SQL query.

1 0
47. In Mysql, what is joins? Explain

In Mysql, joins are used to retrieve data from multiple tables. It is performed whenever two or more tables are joined.

There are three types of MySQL joins:-

  • MySQL INNER JOIN
    SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;
  • MySQL LEFT JOIN
    SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;
  • MySQL RIGHT JOIN
    SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;
2 0
48. What is the difference between CHAR and VARCHAR in MySQL?
  • CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
  • CHAR field is a fixed length, but VARCHAR is a variable length field.
  • CHAR uses static memory allocation, but VARCHAR uses dynamic memory allocation.
2 0
50. How we can create index in mysql?

You can use
CREATE INDEX [index name] ON [table name]([column name]);

0 0
51. Write a query to find duplicate rows In table?

SELECT std_id, COUNT(std_id) as cnt FROM Student GROUP by std_id having cnt > 1

0 0
52. How to display odd rows in Employee table in Mysql?

SELECT id, name, department FROM Employee where MOD(id,2) = 1

2 0
53. What are procedures in Mysql? Explain

It is a procedure stored in a database which can be called by the database engine and connected programming languages. A procedure has a name, a parameter list, and SQL statement, etc. It introduced in MySQL 5.

0 0
54. Write a query to display even rows in student table using MySQL?

SELECT * FROM Student where MOD(id,2) = 0

1 0
55. How to display top 10 rows in Mysql?

SELECT * FROM 'TableName' WHERE 'status' = 1 LIMIT 10

1 0
56. Write a query to fetch common records between two tables using MySQL?
Using INTERSECT

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

 

1 0
57. What is the difference between having and where clause in Mysql? Explain

WHERE term is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.

WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.

1 0
58. What is constraints? Also explain the different types of constraints?

These are the set of rules applied to columns on the table. It is used to bound the type of data that can go into a table.

Types of constraints
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE:
  • Not NULL
1 0
59. How we can get distinct columns values in MySql?

SELECT DISTINCT name FROM Users;

// Here name is the field & Users is the Table name

0 0
61. How can we get total number of records by query in Mysql?

select count(*) from TABLE_NAME where 'status' = 1;

0 0
62. Explain the different types of Collation Sensitivity in MySQL?
  • Kana Sensitivity
  • Case Sensitivity
  • Accent Sensitivity
  • Width Sensitivity etc
0 0
63. What is heap table in MySQL?

HEAP tables are in memory. No TEXT or BLOB fields are allowed inside HEAP tables. They are typically used for high-speed transient storage.

1 0
64. What is temporary table in MySQL?

In MySQL, a temporary table is a distinct type of table that approves you to save a transient end result set, in which you can reuse countless instances in a single session. A brief table is created with the aid of the use of the CREATE TEMPORARY TABLE statement.

0 0
65. How to upload a large file through phpmyadmin in MySQL?

It is the best way to upload large CSV files through the LOAD DATA command.

Syntax:

LOAD DATA LOCAL INFILE "your file name"

INTO TABLE table name

FIELDS TERMINATED by ’,’

LINES TERMINATED BY ’\n’

2 0
66. What is indexing in MySQL?

A database index is the structure of a fact that improves the velocity of operations in a table. It can be created the usage of one or greater columns, presenting the foundation for each fast random lookups and efficient ordering of getting admission to records. Indexes are used to locate rows with specific column values quickly.

5 0
67. What is the degree of a table in MySQL?

The degree of a table is the number of columns in the given table. It is also called as Arity. It is the range of entities inside each entity type that can be linked via a given relationship type.

1 0
68. What is DDL, DML and DCL in MySQL?

1. DDL

In MySQL, DDL is the short form for Data Definition Language, which is used in database schemas and descriptions while deciding how data should reside in the database.

Here’s a list of DDL Queries:
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • COMMENT
  • RENAME

2. DML

DML is a short form for Data Manipulation Language which is used in data manipulation and mostly includes common SQL statements to store, modify, retrieve, delete and update data in a database.

Here is the list of DML Queries:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • MERGE
  • CALL
  • EXPLAIN PLAN
  • LOCK TABLE

3. DCL

DCL is a short form for Data Control Language including commands which are concerned with User rights, permissions and other controls within the database system.

Here’s a list of queries for DCL:
  • GRANT
  • REVOKE
10 0
69. How to convert a string to utf 8 in MySQL?

Here’s a code script to convert string data to UTF 8 in your database:

header('Content-Type: text/plain; charset=utf-8');
$databasesToConvert = [ 'database1' */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];
CONVERT(CAST(CONVERT(`{$column}` USING english) AS binary) USING utf8)";
$query = "UPDATE `{$table}` SET " . join( ', ', $converts );
echo "\n", $query, "\n";
echo "\n";

 

3 1
70. What is the difference between timestamp and datetime in MySQL?
Timestamp in MySQL

Timestamp in MySQL is used when users want to convert the value from the current time zone to UTC zone for the sake of storage or UTC time zone to the current time zone for retrieval.

DateTime in MySQL

DateTime in MySQL is used to insert values ‘yyyy-mm-dd’ in the table where yyyy is the year, mm is the month and dd is the date. This keyword is used to retrieve or to display DATETIME values in Mysql.

1 0
71. What is view in MySQL?

The view is basically a virtual table which is used to view certain and preferable rows or columns of the table. In practice, users can add a number of SQL functions, JOIN and WHERE clause to view the present data of the table.

3 0
72. How do you create view in MySQL?

In MySQL, the CREATE VIEW statement is used to create a new view inside the database.

Here’s the basic syntax of the statement:

CREATE VIEW productList AS SELECT qty, price, totalprice AS value FROM product;

3 0

The data in a MySQL database is stored in the form of tables, which consist of many columns and rows. It is a reliable, stable and robust platform with advanced performance and security features.

Development History of MySQL

The first version of MySQL was released in May 1995 by a Swedish company MySQL AB.

To get into complete detail of MySQL interviews, we are mentioning some of the essential MySQL Interview Questions and Answers for experienced. Go through the issues as they will help you.

Advantages of MySQL
  • Data Security
  • On-Demand Scalability
  • High Performance
  • Excellent Uptime
  • Lowered TCO (Total Cost of Ownership)