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
* MyISAM
* Heap
* Merge
* INNO DB
* ISAM


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
* SET
* BLOB
* ENUM
* CHAR
* TEXT
* VARCHAR


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?
SELECT 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?
SELECT NOW(); 

-- Display only current date 
SELECT 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?
SELECT COUNT (id) FROM items


Comments

Popular posts from this blog

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

Document Expired or Webpage has expired on back button

If you have a dinamic website and want to allow your visitors to use the back button after they sent a form with the post method, the best combination I found was: <?php header("Expires: Sat, 01 Jan 2000 00:00:00 GMT"); header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT"); header("Cache-Control: post-check=0, pre-check=0",false); session_cache_limiter("must-revalidate"); // and after you start the sessionsession_start(); ?> I try some combinations using header("Cache-Control: no-cache, must-revalidate"), but when clicking the back button, the last changes in the form back to their previous states. The combination above works fine with IE 6.x. I didn't test this with other browsers. When I try something like session_cache_limiter("nocache, must-revalidate") it doesn't work. The page only updates when I used the browser's refresh button. In dynamic web sites this is not g

How to Enable IMAP PHP in xampp

The imap extension comes as standard with the PHP installation. You just need to enable it in your php.ini   Enable IMAP in XAMPP: You need to configure your php.ini file to enable IMAP extension Search for the line ;extension=php_imap.dll and remove semicolon(;) and restart your xampp. The line should look like as mentioned below. extension=php_imap.dll Note : New php version does not have dll anymore. The default php.ini should already contain a line to load the extension but commented out: ;extension=imap Remove semicolon from above like below extension=imap Enable IMAP in Linux: If you are using LAMP server, First install IMAP using the command on terminal $ sudo apt-get install php5-imap To enable IMAP, run the following command. sudo phpenmod imap Restart apache server with below command sudo service apache2 restart