Skip to main content

MySQL Interview Questions and Answers

What Is MySQL?
MySQL is an Oracle backed open source relational database management system.

Its name is combinations of “My”, the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael and the first internal release was on 23 May 1995.

MySQL is an important component of an open source enterprise stack called LAMP.


MySQL runs on virtually all platforms, including Linux, UNIX, and Windows.

MySQL can also be run on cloud computing platforms such as Microsoft Azure, Amazon EC2, and Oracle Cloud Infrastructure.
Now MySQL Server 8.0 was announced in April 2018.

MySQL Server 8.0 contains the following -
1.      NoSQL Document Store
2.      Atomic and crash safe DDL sentences
3.      JSON Extended syntax
4.      Introduce New Functions - JSON table functions, improved sorting, and partial updates

How MySQL Works?
MySQL is based on a client-server model.

MySQL server is available as a separate program for use in a client server networked environment and as a library that can be linked into separate applications.

What Are the Features of MySQL?
1.      MySQL provides cross-platform support.
2.      Different backend
3.      Multithreaded SQL server supporting various client programs and libraries
4.      MySQL has many stored procedures like triggers and cursors that helps in managing the database
5.      Administrative tools

What Is Default Port Number of MySQL?
The default port number for MySQL Server is 3306 and the TCP/IP standard default port is 1433 in for SQL Server.

What Is the Maximum Length of a Table name, Database name, and Fieldname in MySQL?
The following table describes the maximum length for each type of identifier which is -
1.      Database 64 bytes
2.      Table 64 bytes
3.      Column 64 bytes
4.      Index 64 bytes
5.      Alias 255 bytes

Is there an Object Oriented Version of MySQL library functions?
MySQLi is an object oriented version of MySQL and it interfaces in PHP.

What Are the Limitations of MySQL?
The Limitations of Joins -
The maximum number of tables that can be referenced in a single join is 61.

Limitations of Innodb Storage Engine -
1.      A table cannot contain more than 1000 columns.
2.      The maximum number of columns per index is 16.
3.      The maximum table space size is 4 Mia database pages (64 Tbyte).

Limitations of the MyISAM Storage Engine -
1.      Large files up to 63-bit file length are supported.
2.      There is a limitation of 264 rows in a MyISAM table.
3.      The maximum key length is 1000 bytes.

Limitations of MySQL CLUSTER -
1.      Max attributes/columns in an index are 32.
2.      Max number of attributes (columns and indexes) in a table is 128.
3.      Max number of table is 1792.
4.      Max size in bytes of a row is 8052 byte, excluding blobs which are stored separately.
5.      Max number of nodes in a cluster is 255 in CGE.
6.      Max number of metadata objects is 20320.
7.      Max attribute name length is 31 characters.
8.      Max database + table name length is 122 characters.


The Advantages of Using MySQL -
1.      MySQL is Open-Source, free, fast, reliable, and relational database.
2.      MySQL is Easy To Use
3.      MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
4.      MySQL is Incredibly Inexpensive
5.      MySQL is an Industry Standard

What Are The Disadvantages Of Using MySQL?

The Disadvantages of Using MySQL -
1.      Some Stability Issues - MySQL is not so efficient for large scale databases.
2.      Poor Performance
3.      Transactions are not handled very efficiently
4.      Developers have some Of Limitations as like Joins, CLUSTER, and Inodb storage engine.

What Is mysqlcheck do?
The mysqlcheck is a client program which used to check the integrity of database tables.

What Is mysqldump?
The mysqldump is a client program which used to create logical backups of database.

In which language MySQL Is Written?
The MySQL is written in C and C++ and The SQL parser is written in yacc.

How Do You Change a Password for an Existing user via mysqladmin?
The mysqladmin -u root -p password “Your-new-password”

What Are the Different Types of Tables in MySQL?
There are three different types of tables in MySQL -
1.      HEAP
2.      InoDB
3.      BDB

What Is BLOB in MySQL?
The BLOB is an acronym stands for a large binary object and its used to hold a variable amount of data.

There are 4 Types of BLOB -
1.      BLOB
2.      TINYBLOB
3.      MEDIUMBLOB
4.      LONGBLOB

What Is TEXT in MySQL?
The TEXT is a case-insensitive BLOB and the TEXT values are non-binary strings (character string).

There are 4 Types of TEXT -
1.      TEXT
2.      TINYTEXT
3.      MEDIUMTEXT
4.      LONGTEXT

What Are HEAP Tables?
The HEAP tables are in-memory and used for high speed temporary storages.
The HEAP tables do not support AUTO INCREMENT, TEXT, and BLOB fields.

What Does Tee Command do in MySQL?
The “tee” followed by a filename turns on MySQL logging to a specified file.

What Are Advantages of InnoDB over MyISAM?

The Advantages of InnoDB over MyISAM are -
1.      Row Level Locking
2.      Transactions
3.      Foreign Key Constraints
4.      Crash Recovery

How To Get the Current SQL version?
The SELECT VERSION (); query is used for get the current SQL version.

How To Get the Current Date in MySQL?
The SELECT CURRENT_DATE(); query is used for get the current date in MySQL.

What Is a Trigger in MySQL?
A trigger is a set of codes that executes in response to some events.

How To Resolve the Problem of Data Disk that is Full?
When the data disk is full and overloaded the way out is to create and soft link and move the .frm as well as the .idb files into that link location.

What Is the Difference Between mysql_fetch_array and mysql_fetch_object?
The mysql_fetch_array() is used  to returns a result row as an associated array.
Teh mysql_fetch_object() is used to  returns a result row as object from database.

How Many Triggers Are Possible in MySQL?
There Are Only Six Triggers allowed using in MySQL database i.e.
1.      Before Insert
2.      After Insert
3.      Before Update
4.      After Update
5.      Before Delete
6.      After Delete

I hope You Enjoyed. Thank you Very Much for your Time.
By Anil Singh | Rating of this article (*****)

Popular posts from this blog

List of Countries, Nationalities and their Code In Excel File

Download JSON file for this List - Click on JSON file    Countries List, Nationalities and Code Excel ID Country Country Code Nationality Person 1 UNITED KINGDOM GB British a Briton 2 ARGENTINA AR Argentinian an Argentinian 3 AUSTRALIA AU Australian an Australian 4 BAHAMAS BS Bahamian a Bahamian 5 BELGIUM BE Belgian a Belgian 6 BRAZIL BR Brazilian a Brazilian 7 CANADA CA Canadian a Canadian 8 CHINA CN Chinese a Chinese 9 COLOMBIA CO Colombian a Colombian 10 CUBA CU Cuban a Cuban 11 DOMINICAN REPUBLIC DO Dominican a Dominican 12 ECUADOR EC Ecuadorean an Ecuadorean 13 EL SALVADOR

39 Best Object Oriented JavaScript Interview Questions and Answers

Most Popular 37 Key Questions for JavaScript Interviews. What is Object in JavaScript? What is the Prototype object in JavaScript and how it is used? What is "this"? What is its value? Explain why "self" is needed instead of "this". What is a Closure and why are they so useful to us? Explain how to write class methods vs. instance methods. Can you explain the difference between == and ===? Can you explain the difference between call and apply? Explain why Asynchronous code is important in JavaScript? Can you please tell me a story about JavaScript performance problems? Tell me your JavaScript Naming Convention? How do you define a class and its constructor? What is Hoisted in JavaScript? What is function overloadin

React | Encryption and Decryption Data/Text using CryptoJs

To encrypt and decrypt data, simply use encrypt () and decrypt () function from an instance of crypto-js. Node.js (Install) Requirements: 1.       Node.js 2.       npm (Node.js package manager) 3.       npm install crypto-js npm   install   crypto - js Usage - Step 1 - Import var   CryptoJS  =  require ( "crypto-js" ); Step 2 - Encrypt    // Encrypt    var   ciphertext  =  CryptoJS . AES . encrypt ( JSON . stringify ( data ),  'my-secret-key@123' ). toString (); Step 3 -Decrypt    // Decrypt    var   bytes  =  CryptoJS . AES . decrypt ( ciphertext ,  'my-secret-key@123' );    var   decryptedData  =  JSON . parse ( bytes . toString ( CryptoJS . enc . Utf8 )); As an Example,   import   React   from   'react' ; import   './App.css' ; //Including all libraries, for access to extra methods. var   CryptoJS  =  require ( "crypto-js" ); function   App () {    var   data

25 Best Vue.js 2 Interview Questions and Answers

What Is Vue.js? The Vue.js is a progressive JavaScript framework and used to building the interactive user interfaces and also it’s focused on the view layer only (front end). The Vue.js is easy to integrate with other libraries and others existing projects. Vue.js is very popular for Single Page Applications developments. The Vue.js is lighter, smaller in size and so faster. It also supports the MVVM ( Model-View-ViewModel ) pattern. The Vue.js is supporting to multiple Components and libraries like - ü   Tables and data grids ü   Notifications ü   Loader ü   Calendar ü   Display time, date and age ü   Progress Bar ü   Tooltip ü   Overlay ü   Icons ü   Menu ü   Charts ü   Map ü   Pdf viewer ü   And so on The Vue.js was developed by “ Evan You ”, an Ex Google software engineer. The latest version is Vue.js 2. The Vue.js 2 is very similar to Angular because Evan You was inspired by Angular and the Vue.js 2 components looks like -

Encryption and Decryption Data/Password in Angular

You can use crypto.js to encrypt data. We have used 'crypto-js'.   Follow the below steps, Steps 1 –  Install CryptoJS using below NPM commands in your project directory npm install crypto-js --save npm install @types/crypto-js –save After installing both above commands it looks like  – NPM Command  1 ->   npm install crypto-js --save NPM Command  2 ->   npm install @types/crypto-js --save Steps 2  - Add the script path in “ angular.json ” file. "scripts" : [                "../node_modules/crypto-js/crypto-js.js"               ] Steps 3 –  Create a service class “ EncrDecrService ” for  encrypts and decrypts get/set methods . Import “ CryptoJS ” in the service for using  encrypt and decrypt get/set methods . import  {  Injectable  }  from   '@angular/core' ; import   *   as   CryptoJS   from   'crypto-js' ; @ Injectable ({    providedIn:   'root' }) export   class   EncrDecrS