MySQL interview questions and answers

Home >> MySQL interview questions and answers >>

  MySQL interview questions and answers part - 3

This article will give you an overview of MySQL questions and answers that may be helpful for you in an interview or online test.

Question: How many columns can be used for creating Index?
 Maximum of 16 indexed columns can be created for any standard table.

Question: What is the different between NOW() and CURRENT_DATE()?

NOW() command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

Question: What are the objects can be created using CREATE statement?
Following objects are created using CREATE statement:

  •     DATABASE
  •     EVENT
  •     FUNCTION
  •     INDEX
  •     PROCEDURE
  •     TABLE
  •     TRIGGER
  •     USER
  •     VIEW

Question: How many TRIGGERS are allowed in MySql table
SIX triggers are allowed in MySql table. They are as follows

  •     BEFORE INSERT
  •     AFTER INSERT
  •     BEFORE UPDATE
  •     AFTER UPDATE
  •     BEFORE DELETE
  •     AFTER DELETE

Question: What are the nonstandard string types?
Following are Non-Standard string types:

  •     TINYTEXT
  •     TEXT
  •     MEDIUMTEXT
  •     LONGTEXT    

Question: What are all the Common SQL Function?
CONCAT(A, B) Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) Formats the number X to D significant digits.

CURRDATE(), CURRTIME() – Returns the current date or time.

NOW() – Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.

DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) – Determines the difference between two times.

FROMDAYS(INT) – Converts an integer number of days into a date value.

Question: What is required to create MYSQL database?
To create MySQL databse the first component which has to be present is a database server on which the queries of database will run and software tool through which you can access the applications. It also requires PHP scripts for communicating with the database using SQL commands.

Question: What do you understand by MYSQL terminal?
MySQL terminal is used as a command line interface in many operating system. It provides a way to access the database and other resources using the SQL commands that are interpreted by the MySQL database server.    
    
Question: Why phpMyAdmin is used for MYSQL?
PhpMyAdmin is a very popular and easy to use GUI tool that can allow SQL commands to be run to create database, create tables, insert data and retrieve it. It provides a web based interface to the user for the ease of use. 
    
Question: In which language MySQL is written?
MySQL is written in C and C++ and its SQL parser is written in yacc.

Question: What does " i_am_a_dummy flag" do in MySQL?
The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.  

Question: What are the MySQL Data Types?
1. Numeric Data Types
2. Date and Time Types
3. String Types

1. Numeric Data Types
INT - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.

TINYINT - A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.

SMALLINT - A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.

MEDIUMINT - A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

BIGINT - A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

FLOAT - A floating-point number that cannot be unsigned. This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.

DOUBLE - A double precision floating-point number that cannot be unsigned. This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE.

DECIMAL - An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. NUMERIC is a synonym for DECIMAL.

2. Date and Time Types
DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31.

DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.

TIMESTAMP - The TIMESTAMP data type is used for values that contain both date and time parts.

TIME - Stores the time in HH:MM:SS format.

YEAR(M) - Stores a year in 2-digit or 4-digit format. The default length is 4.

 3. String Types
CHAR(M) - A fixed-length string between 1 and 255 characters in length.

VARCHAR(M) - A variable-length string between 1 and 255 characters in length.

BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. 

TEXT also hold large amounts of data, the difference between that the data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.

MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL).

Question: What is the difference between datetime and timestamp?
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Another major difference between these two data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime data type values are unchanged in relation to time zone.

Question: What storage engines are used in MySQL?
InnoDB:

  • The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. 
  • InnoDB row-level locking and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.
  • InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. 
  • To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

MyISAM:

  • These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

Memory:

  • Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. 
  • Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory.

CSV:

  • Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format.
  • CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

Archive:

  • These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
  • Storage: Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression
  • Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows.

Blackhole:

  • The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set.
  • These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.

NDB (also known as NDBCLUSTER):

  • This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

Merge:

  • Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

Federated:

  • Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

Question: What is different between InnoDB and MyISAM?

InnoDB:

  • The default storage engine in MySQL 5.7.
  • InnoDB supports Row-level Locking.
  • InnoDB designed for maximum performance when processing high volume of data.
  • InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS.
  • InnoDB stores its tables and indexes in a tablespace.
  • InnoDB supports transaction. You can commit and rollback with InnoDB.
  • InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

MyISAM:

  • MYISAM supports Table-level Locking.
  • MyISAM designed for need of speed.
  • MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS.
  • MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI).
  • MYISAM not supports transaction.
  • MYISAM supports fulltext search.
  • You can use MyISAM, if the table is more static with lots of select and less update and delete.

Write your comment now