MYSQL Interview Questions

  • 1
    What is MySQL?

    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.

  • 2
    Why is MYSQL used?

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

  • 3
    What is the difference between CHAR_ LENGTH and LENGTH?

    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.

  • 4
    Define REGEXP?

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

  • 5
    What are federated tables?

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

  • 6
    What are heap tables?

    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

  • 7
    Is there an object-oriented version of MYSQL library functions?

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

  • 8
    What is CTE?

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

  • 9
    What are the storage models of OLAP?

    The storage models in OLAP are MOLAP, HOLAP and ROLAP

  • 10
    In which language MySQL is written?

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

  • 11
    What are the technical specifications of MySQL?

    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.
  • 12
    Why is MySQL so popular?

    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.

  • 13
    What do you understand about Heap Tables?

    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.

  • 14
    Name the default port for the MySQL Server.

    It is 3306.

  • 15
    How do you get the number of rows affected by query?

    SELECT COUNT (user_id) FROM users;

  • 16
    How are ENUMs and SETs represented internally?

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

  • 17
    How do you change a password for an existing user via mysqladmin?

    mysqladmin -u root -p password "newpassword"

  • 18
    If the value in the column is repeatable, how do you find out the unique values?

    SELECT DISTINCT user_firstname FROM users;

  • 19
    How do you get the current version of mysql?

    SELECT VERSION();

  • 20
    What is the LIKE?

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

  • 21
    Differentiate the LIKE and REGEXP operators?

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

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

  • 22
    What are the String types are available for a column?

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

  • 23
    How we get Sum of column?

    mysql> SELECT * FROM tablename;
    
  • 24
    How do you get current user in mysql?

    SELECT USER();
    
  • 25
    How would you change a table to InnoDB?

    ALTER TABLE name_file ENGINE innodb;

  • 26
    How do you concatenate strings in MySQL?

    CONCAT (string1, string2, string3)

  • 27
    How do you get the month from a timestamp?

    SELECT MONTH(january_timestamp) from tablename;

  • 28
    What do % and _ mean inside LIKE statement?

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

  • 29
    How do you get the current date in Mysql?

    SELECT CURRENT_DATE();
    
  • 30
    How do you display the list of database in mysql?

    SHOW DATABASES;
    
  • 31
    What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?

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

  • 32
    How many drivers in Mysql?

    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
  • 33
    How do you run batch mode in mysql?

    mysql < batch-file >;
    mysql < batch-file > mysql.out
    
  • 34
    What is a Cursor?

    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.

  • 35
    Can you tell how can you display the Maximum salary in SQL?

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

  • 36
    What are the nonstandard string types?

    Following are Non-Standard string types:

    • TINYTEXT
    • TEXT
    • MEDIUMTEXT
    • LONGTEXT
  • 37
    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 and
    • AFTER DELETE
  • 38
    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
  • 39
    How many columns can be used for creating Index?

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

  • 40
    How to display top 50 rows?

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

    SELECT * FROM
    LIMIT 0,50;
    
  • 41
    How to enter Characters as HEX Numbers?

    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.

  • 42
    How MySQL Optimizes DISTINCT?

    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;
    
  • 43
    What is InnoDB?

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

  • 44
    What is ISAM?

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

  • 45
    What are the different tables present in MySQL?

    Total 5 types of tables are present:

    • MyISAM
    • Heap
    • Merge
    • INNO DB
    • ISAM
  • 46
     Where MyISAM table will be stored and also give their formats of storage?

    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
  • 47
    What are the column comparisons operators?

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

  • 48
    How do you login to MySql using Unix shell?

    We can login through this command:

    # [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
    
  • 49
     What does myisamchk do?

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

  • 50
    How do you control the max size of a HEAP table?

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