MySQL Interview Questions

MySQL Interview Questions

MySQL is a multi-threaded or multi-user database management system.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) which is based on structured query language.

It is supported by most of the popular operating systems, such as Windows, Linux, etc.

The default port number of MySQL is 3306

MySQL supports wide ranges of operating systems most famous of those include Windows, Linux & UNIX

Developer(s) : Oracle Corporation

Initial release : 23 May 1995

Latest Stable release: 8.0.17 / 22 July 2019

Repository : github.com/mysql/mysql-server

Why MySQL is used?

The MySQL database server is very fast, reliable, open source and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

What is the purpose of using a HEAP table?

HEAP tables are present in memory and they are used for high speed storage on temporary basis. 

The table which uses a hashed index and stores in the memory is called the HEAP table. It works as a temporary table and it uses the indexes that make it faster than another table type.

What are the different tables present in MySQL?

There are many tables that remain present by default. But, INNODB is the default database engine used in MySQL. MySQL prior to version 5.5 was MyISAM

There are five types of tables that are present:

  • INNODB
  • MyISAM
  • Heap
  • Merge
  • ISAM
  • Memory
  • CSV
  • Merge
  • Archive

What is a storage engine? What are the differences between InnoDB and MyISAM engines?

One of the major components of the MySQL server is the storage engine for doing different types of database operations. Each database table created is based on the specific storage engine.

MySQL supports two types of storage engines i.e transactional and non-transactional

InnoDB is the default storage engine of MySQL which is transactional. MyISAM storage engine is a non-transactional storage engine.

The differences between InnoDB and MyISAM storage engines are discussed below:

  • MyISAM supports the FULLTEXT index but InnoDB doesn’t support the FULLTEXT index.
  • MyISAM is faster and InnoDB is slower.
  • InnoDB supports ACID (Atomicity, Consistency, Isolation, and Durability) property but MyISAM doesn’t.
  • InnoDB supports row-level locking and MyISAM supports table-level locking.
  • InnoDB is suitable for large database and MyISAM is suitable for a small database.

What is Database White Box Testing?

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

What is Database Black Box Testing?

Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis.

What is a Cursor

The cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

What is Normalization and list the different types of normalization?

Normalization is the process of organizing data to avoid duplication and redundancy. There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.

  • First Normal Form (1NF) – No repeating groups within rows
  • Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
  • Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

Table Indexes:

SHOW INDEX FROM tableName

Mysql Version: SELECT VERSION()

ACID :  Atomicity, Consistency, Isolation, and Durability that can be defined as properties of a transaction.

LAST_INSERT_ID

Leave a Reply

Your email address will not be published. Required fields are marked *