Loading…

Mysql Interview Questions

Last update: 03 Dec 2019, 64 Questions and Answers

A Quick Overview of Mysql Interview Questions

Mysql Interview Questions

MySQL is one of the widely used open-source database platforms in the world today. According to mysql.com website, this platform is trusted by the world’s 10 top sites that are considered trustworthy and are frequency visited. Overviewing these MySQL Interview Questions for experienced as well as individuals and the other crucial factors mentioned here can be remarkably result oriented for candidates and new learners to this niche.

A Quick Overview of MySQL
What is MySQL? It the most popular Open Source database management system, is developed and supported by Oracle Corporation.
Latest Version 8.0.19
Created By Oracle Corporation
Written in C and C++ language
About License GNU Public License
Official Website https://www.mysql.com

Most Frequently Asked Mysql Interview Questions And Answers With Examples:

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.

5 1
  • Mysql is available free because it is open source, but SQL is not open-source.
  • Mysql offers updatable views, but SQL offers indexed views that are more powerful.
  • Mysql does not support XML, but SQL supports XML.
  • Mysql not supported Auto tuning but SQL can.
  • Mysql did not support User-defined functions, but SQL supported.
  • Mysql support Transaction is very much limited but SQL support extensively and entirely offered.
  • Mysql not offered Stored procedures and full joins but offered in SQL.
  • Mysql does not support the Cursor feature but is there in SQL.
Related Article: SQL Interview Questions
0 3
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.
1 0

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
1 0

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.

0 0

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

1 0
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.
0 0

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

Difference between MyISAM and InnoDB are given below:-

  • MyISAM does not support transactions, but InnoDB supports transactions.
  • MyISAM supports Table-level Locking, but InnoDB supports Row-level Locking.
  • MyISAM supports full-text search, but InnoDB does not.
  • MyISAM designed for the need of speed but InnoDB designed for maximum performance.
  • MyISAM does not support foreign keys, but InnoDB supports foreign keys.
  • We can use commit and rollback with InnoDB but not in MyISAM.
  • MyISAM does not support ACID (Atomicity, Consistency, Isolation, and Durability) but InnoDB supports the ACID property.
  • In InnoDB table, AUTO_INCREMENT field is a part of the index but not in MyISAM.
  • MyISAM stores its tables, data, and indexes in disk space using a separate table name.FRM, table name.MYD and table name.MYI but InnoDB stores its tables and indexes in a tablespace.
0 0

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

0 1

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.

2 0
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.
0 0

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 ;

0 0

SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

0 0
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"
0 0

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

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

It is used to concatenate two or more strings.

Example

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

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;

1 0
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.
0 0
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.
0 0

I order to validate the email addresses in Mysql, below mentaioned query is used –
SELECT * FROM `users` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

0 0

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

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

0 0

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

UPDATE `your_table_name` SET new_field=old_field

1 0

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

0 0

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

It is developed by David Axmark.

0 0

The first version of MySQL was released on 23 May 1995.

0 0

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

2 0
  • 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
2 0

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

0 0

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

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

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

1 0

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

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.

0 0

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;
0 0
  • 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.
1 0

We can use SELECT NOW();

0 0

You can use DESC table_name

0 1

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

0 0

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

0 0

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

0 0

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

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

0 0

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

0 0
Create table std as Select * from Student;
0 0

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

 

0 0
Using INTERSECT

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

 

0 0

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.

0 0

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

SELECT DISTINCT name FROM Users;

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

0 0

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

0 0

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’

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

0 0

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.

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

Review

No Image
Sonu
1 month ago
5

Thanks for useful interview questions answer.

Add Review

Ask Question