MYSQL Interview Questions

MySQL is a database management system for web servers. It can grow with the website as it is highly scalable. Most of the websites today are powered by MySQL.

MySQL database server is stable, fast and very easy to use. This software can be downloaded as freeware. 

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are the same for Latin characters, but they are different for Unicode and other encodings.

REGEXP is a pattern match in which matches pattern anywhere in the search value 

Federated tables allow access to the schedules located on other databases on the servers

Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTOINCREMENT

Yes, MYSQLi is the object version of MYSQL, and it interfaces in PHP

An expression which consists of a temporary set of results defined in a SQL statement is said to be a common table expression. (CTE) 

The storage models in OLAP are MOLAP, HOLAP and ROLAP

MySQL is written in C, and C++ language, and its SQL parser is written in YACC language.

MySQL is a client-server based application that includes:

• A multithreaded SQL Server supports various client programs and libraries.
• It includes different backends.
• It includes a wide range of programming interfaces and administrative tools.

MySQL is a database server that is fast, reliable, and easy to use. It can be downloaded from the internet from a single click.

Consider enrolling for a MySQL self-lerning program for in-depth understanding of MYSql and its related functionalities.

These are available in memory for high-speed storage temporarily. It does not allow BLOB or text fields; only comparison operators are allowed. Heap tables and indexes do not support an auto-increment function and should be NOT NULL.

It is 3306.

SELECT COUNT (user_id) FROM users;

As unique integers representing the powers of two, due to storage optimizations.

mysqladmin -u root -p password "newpassword"

SELECT DISTINCT user_firstname FROM users;

SELECT VERSION();

A LIKE pattern match, which succeeds only if the pattern matches the entire value.

SELECT * FROM pet WHERE name REGEXP "^b";

SELECT * FROM pet WHERE name LIKE "%b";

The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

mysql> SELECT * FROM tablename;

SELECT USER();

ALTER TABLE name_file ENGINE innodb;

CONCAT (string1, string2, string3)

SELECT MONTH(january_timestamp) from tablename;

% corresponds to 0 or more characters, _ is exactly one character.

SELECT CURRENT_DATE();

SHOW DATABASES;

On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are

• PHP Driver
• ODBC Driver
• JDBC Driver
• ado.net5.mxj
• CAPI1PHP DRIVER
• PERL DRIVER
• PYTHON DRIVER
• RUBY DRIVER
• C WRAPPER

mysql < batch-file >;
mysql < batch-file > mysql.out

Considered as a pointer to point to one row in a set of rows, a Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

To display the maximum salary in SQL, you can use the inbuilt function called MAX().

Following are Non-Standard string types:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

SIX triggers are allowed in MySql table. They are as follows:

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

Following objects are created using CREATE statement:

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

Maximum of 16 indexed columns can be created for any standard table.

In MySql, top 50 rows are displayed by using this following query:

SELECT * FROM
LIMIT 0,50;

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Total 5 types of tables are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

Each MyISAM table is stored on disk in three formats:

• The ‘.frm’ file stores the table definition
• The data file has a ‘.MYD’ (MYData) extension
• The index file has a ‘.MYI’ (MYIndex) extension

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

It compress the MyISAM tables, which reduces their disk or memory usage.

Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.