and Windows

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

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog -
My Books - Book 1 and Book 2

MySQL Interview Questions and Answers MySQL Interview Questions and Answers Reviewed by Anil Singh on 7:02 AM Rating: (5) Powered by Blogger.