TOP INTERVIEW QUESTIONS AND ANSWERS FOR MySql Developers

  1. Why MySQL is used?

MySQL database server is reliable, fast and very easy to use.  This software can be downloaded as freeware and can be downloaded from the internet.

  1. What are the features of MySQL?

MySQL gives cross-stage bolster, extensive variety of interfaces for application programming and has many stored techniques like triggers and cursors that assistance in dealing with the database.

  1. Differentiate between FLOAT and DOUBLE? 
  • Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes’ data.
  • Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes’ data.
  1. What is the default port for MySQL Server?

3306 is the default port for MySQL Server. Another one is port 1433 in TCP/IP for SQL Server.

  1. What do DDL, DML, and DCL stand for?
  • DDL – Data Definition Language
  • DML -Data Manipulation Language
  • DCL – Data Control Language
  1. What are meant by Joins in MySQL?

In MySQL the Joins are utilized to inquiry information from at least two tables. The question is made utilizing relationship between specific sections existing in the table. There are four sorts of Joins in MySQL. Inner Join returns the lines if there is at least one match in both the tables. Left Join returns every one of the lines shape the left table regardless of the possibility that there is no match in the right table. Right Join returns all the rows from the right table regardless of the possibility that no matches exist in left table. Full Join would return rows when there is at least one match in the tables.

  1. What are the regular MySQL functions?

Basic MySQL functions are as per the following:

  • NOWO –For returning current date and time as single esteem.
  • CURRDATEO – For returning the present date or time.
  • CONCAT (X, Y) –To concatenates two string esteems making single string output.
  • DATEDIFF (X, Y) – Function to determine difference two dates.
  1. Differentiate CHAR and VARCHAR?

CHAR is used to define the fixed length of the table and columns while creating the table. The length value could be in the range of 1-255. VARCHAR is used to adjust the column and table length as required.

  1. What are HEAP Tables?

Basically HEAP tables are in-memory and used for high speed temporary storages. But TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.

  1. What is the syntax for concatenating tables in MySQL?

The syntax for concatenating tables is MySQL is CONCAT (string 1, string 2, string 3)

MySQL-Developer

  1. What are the limits for using columns to create the Index?

The maximum limits of indexed columns that could be created for any table is 16.

  1. What are the different types of strings in Database columns in MySQL?

Different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEX, ENUM, and CHAR.

  1. How end user gets to know current SQL version?

The syntax for getting the current version of MySQL is SELECT VERSION ();

  1. Is there an object oriented version of MySQL library functions?

MySQLi is the object oriented version of MySQL and it interfaces in PHP.

  1. What is the storage engine for MySQL?

Storage tables are named as table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities and functions.

  1. What is the difference between primary key and candidate key?

Primary key in MySQL is use to identify every row of a table in unique manner. For one table there is only one primary key. One of the candidate keys is the primary key and the candidate keys can be used to reference the foreign keys.

  1. What are the different types of tables in MySQL?

MYISAM, InnoDB, CSV, MEMORY, Aria, SEQUENCE, etc

  1. Can you use MySQL with LINUX operating system?

Yes, the syntax for using MySQL with LINUX operating system is /etc/init.d/mysqlstart

  1. What is the use of ENUM in MySQL?

ENUM is used to limit the values that can go into a table. For instance; the user can create a table giving specific month values and other month values would not enter into the table.

  1. What are the TRIGGERS that can be used in MySQL tables?

The following TRIGGERS are allowed in MySQL:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE
  1. What is the difference between LIKE and REGEXP operators in MySQL?

LIKE is denoted using the % sign. For example: SELECT * FROM user WHERE user name LIKE “%NAME”. On the other hand the use of REGEXP is as follows: SELECT * FROM user WHERE username REGEXP “^NAME”;

  1. How to use the MySQL slow query log?

Information that is provided on the slow query log could be huge in size. The query could also be listed over thousand times. “pt-qury-digest” is used to summarize the slow query log in an informative manner.

  1. How can one take incremental backup in MySQL?

Percona xtrabackup is used for incremental backup in MySQL.

  1. How can you change the root password if the root password is lost?

In a case if a password is lost the user should begin the DB with – skip-grants-table and then change the password. Thereafter with the new password the user should restart the DB in normal mode.

  1. Differentiate DELETE TABLE and TRUNCATE TABLE commands in MySQL?

Basically DELETE TABLE is logged operation and every row deleted is logged. Hence the process is usually slow. TRUNCATE TABLE also deletes rows in a table but it will not log any of the rows deleted.  The process is faster in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement using no WHERE clause.

  1. Explain the various types of joins in MySQL?

There are following types of Joins in MySQL.

  • Inner Join- It returns the rows if there is at least one match in both the tables.
  • Left Join- It returns all the rows form the left table even if there is no match in the right table.
  • Right Join- it returns all the rows from the right table even if no matches exist in left table.
  • Full Join- It is used to return the rows when there is at least one match in the tables.
  1. What are the storage models of OLAP?

The storage models of OLAP:

  • MOLAP
  • ROLAP
  • HOLAP
  1. How to define testing of network layers in MySQL?

For this it is essential reviewing the layered architecture and defining hardware and software configuration dependencies in respect of the application put to test.

  1. What is the difference between primary key and unique key?

While both are used to implement uniqueness of the column defined but primary key would create a assembled index whereas unique key would create non-assembled index on the column. Primary key does not allow ‘NULL’ but unique key allows it.

  1. What is meant by transaction and ACID properties?

Transaction is consistent unit of work where either all or none of the means ought to be performed. ACID is the condensing for Atomicity, Consistency, Isolation, and Durability that are properties of any exchange.

 

Save

Save

Next
Previous

Add a Comment

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