Skip to main content

Common MySql Interview Questions and Answers For Experienced/Freshers

Some Common MySql Interview Questions and Answers For Experienced + Freshers Which mostly asked my interviewer during interview session, As you know MySql is highly popular relational database and good compatible with open source languages like PHP.

Following list of basic Mysql question and answer surely help developers for getting new jobs.

MySql Interview Questions and Answers

Question: What is MySQL?
MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.

Question: 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

Question: In which language MySQL is written?
MySQL is written in C and C++ and its SQL parser is written in yacc.

Question: What are the technical features of MySQL?
MySQL has the following technical features:-
* Flexible structure
* High performance
* Manageable and easy to use
* Replication and high availability
* Security and storage management

Question: What is maximum length of column name, table name and database name?

column name can be upto 64 chars, table name can be upto 64 and database name can be upto 64 chars.

Question: How to start and stop MYSql service?
Start mysql service:
service mysqld start

Stop mysql service:
service mysqld stop

Question: What is the default port for MySQL Server?
The default port for MySQL server is 3306

Question: What is the difference between MySQL and SQL?
SQL is known as standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

Question: How will you export tables as an XML file in MySQL?
MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML

Question: Differentiate between FLOAT and DOUBLE in MYSql?
* Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
* Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

Question: What are the Types of storage engine in MySQL?
Following are the types of storage engine in MYSql
* Heap
* Merge

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

Question: What is the difference between CHAR and VARCHAR?
* CHAR and VARCHAR types are different in storage and retrieval.
* When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
* CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.

Question: What is DDL, DML and DCL?
DDL (Data Definition Language) deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL.
DML (Data Manipulation Language) deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT etc. DCL (Data Control Language) includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Question: How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.

Question: What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.

Question: How do you return the a hundred items starting from 20th position?
SELECT item_name FROM items LIMIT 20, 100.

Where the first number in LIMIT is the offset, the second is the number.

Question: Give string types available for column in MYSql?
Following are the string types in MYSql

Question: What are the disadvantages of MySQL?
* MySQL is not so efficient for large scale databases.
* It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
* Transactions are not handled very efficiently.

Question: How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.

Question: How to get current MySQL version?

Question: What is the difference between primary key and candidate key?
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

Question: What is the query to display current date and time?

-- Display only current date 

Question: What is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

Question: How can we run batch mode in mysql?
mysql ; mysql mysql.out

Question: What is MySQL data directory?
MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

Question: What is the use of mysql_close()?
it can be used to close connection opened by mysql_connect() function.

Question: How many Triggers are possible in MySQL?
Following are the possible triggers in MYSql.
* Before Insert
* After Insert
* Before Update
* After Update
* Before Delete
* After Delete

Question: What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values and that can be used during table creation.

Question: Define REGEXP?
REGEXP is a pattern match in which matches pattern anywhere in the search value.
See REGEXP eg: How to search for exact matched word using MySql Query

Question: How do you get the number of rows in MYSql?


Popular posts from this blog

Symbols Used in Excel Formula | The purpose of Symbols in Excel Formula

Following symbols are used in Excel Formula. They will perform different actions in Excel Formulas and Functions. Each of these special characters have used for different purpose in Excel. Symbol Name Description = Equal to Every Excel Formula begins with Equal to symbol (=). Example: = B1+B6 () Parentheses All Arguments of the Excel Functions specified between the Parentheses. Example: =COUNTIF ( B1:B5,5 ) () Parentheses Expressions specified in the Parentheses will be evaluated first. Parentheses changes the order of the evaluation in Excel Formula. Example:   =25+ ( 35*2 ) +5 * Asterisk Wild card operator to to denote all values in a List. Example:   =COUNTIF(B1:B5,” * “) , Comma List of the Arguments of a Function Separated by Comma in Excel Formula. Example:   =COUNTIF(B1:B5 , “>” &C1) & Ampersand Concatenate Operator to connect two strings into one in Excel Formula. Example:   =”Total: “ & SUM(C2:C25) $ Dollar Makes Cell Reference as Absolute in Excel Formula. Exam

How to create a Barcode Using PHP Barcode 128 Generator

A barcode is an optical, machine-readable, representation of data, the data usually describes something about the object that carries the barcode.We will use PHP to generate Barcode in this tutorial. In this script, we are using coding which will generate barcodes in barcode format Code 128 . First, we will create index.php which will ask for the user input for which Barcode has to be created PHP Barcode Generator <fieldset><legend>Detail Informations</legend><form action="createbarcode.php" method="post"><b>Enter Your Code </b><input name="barcode" type="text" /><input type="submit" value="Create Barcode" /></form></fieldset> Now we will create createbarcode.php which will call function from Barcode code128 class for creating barcode <? php include('barcode128.php'); // include php barcode 128 class // design our barcode display echo

How to change a MySQL database’s table prefix

Changing a database table prefix is easy and here’s the simple step-by-step guide! For WordPress installations, it’s essential! How to change a prefix 1. In your text editor, change database_name, old_prefix_ and new_prefix_ to the required values: SET @database = "database_name"; SET @old_prefix = "old_prefix_"; SET @new_prefix = "new_prefix_"; SELECT concat( "RENAME TABLE ", TABLE_NAME, " TO ", replace(TABLE_NAME, @old_prefix, @new_prefix), ';' ) AS "SQL"  FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database; 2. Run the query in cPanel or PHPMyAdmin on your WordPress database 3. The output will be a series of SQL queries that will rename the tables for you 4. Run the output 5. Done!   How to add a prefix    If your database doesn’t have a prefix at all, follow the steps above but use the below query that’s been slightly modified fo