MySql Interview questions and answers

What is SQL
SQL stands for Structured Query Language. SQL is is a special-purpose programming language designed for data held in a relational database management system.
It’s originally  based upon relational algebra and tuple relational calculus, It’s consists of a data definition language(DDL) and a data manipulation language(DML).

What is difference between DBMS and RDBMS.

RDBMS (relational database management system) applications store data in a tabular form.
. DBMS store data as files .However there are is tables in DBMS also, but there is no relation between the tables as in RDBMS.
.In DBMS, data is generally stored in either a hierarchical form or a navigational form.
.In RDBMS tables have an identifier called primary key and Data values will be stored in the form of tables. The relationships  between these data  values will be stored in the form of a table as well.  Every value stored in the relational database is accessible.
.RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS.

How to make database connection to mysqli in php

<?php
$con = mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

How to make database connection to PHP Data Object in php

<?php
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','my_user');
define('DB_PASS','my_password');
define('DB_NAME','my_db');
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>

What is join in SQL

Join  are used to fetch data from one or more tables based on relation between column .
Eg-

SELECT tablename1.columnname, tablename1.columnname, tablename2.columnname
FROM tablename2
JOIN tablename1 ON tablename1.columnname=tablename2.columnname;

What is difference between ORDER BY and GROUP BY in SQL

ORDER BY  used to sort the result in ascending or descending order. By default records are sort in ascending
Eg- SELECT column1, column2, …FROM tablename ORDER BY column1… ASC|DESC;
GROUP BY used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Eg- SELECT columnname FROM tablename WHERE condition GROUP BY columnname

What is difference between FLOAT and DOUBLE

FLOAT and DOUBLE both represents approximate numerical data values. MySql uses four bytes for single precision values and eight bytes for double precision values .
A Float is for single-precision value wheres DOUBLE is for double precision values and DOUBLE allows greater accurcy than FLOAT.
Float saves floating point numbers upto eight places and DOUBLE saves floating point numbers upto 18 places.

How many type of sql joins

MySQL supports the following types of joins:

1. Cross join
2. Inner join
3. Left join
4. Right join

How to find second maximum salary

SELECT max(salary) FROM tblname WHERE  salary <( SELECT max(salary) FROM tblname )

What is difference between TRUNCATE and DELETE in mysql

1: DELETE  is a DML(data manipulation lnguage) command whereas truncate is a DDL(data definition language) command.
2 : Truncate is much faster than Delete.
3 : We can not roll back in truncate but in delete we can rollback.
4 : We can not use where clause in truncate but in delete we can use conditions using where clause

What is difference between PRIMARY key and UNIQUE Key in sql

1: UNIQUE key can be a NULL value but PRIMARY key can not  take NULL values.
2:A table can have multiple UNIQUE key but  can only one PRIMARY key.
How to concatenate two fields in MySql
In MySQL CONCAT function is used to concatenate two strings to form a single string
Synatx : SELECT CONCAT(column1,column2)  FROM tblname.
Ex          : SELECT CONCAT(first_name,last_name) FROM employee

How to get current date in MySql

SELECT CURRENT_DATE();

What is difference between mysql_connect and mysql_pconnect

1: While using the mysql_pconnect  the function would try to find a connection that is already open with   same host,username and password ie.(persistant connection) .If connection found an identifier will be returned instead of opening new connection. And with mysql_connect a  new connection is always established .
2:While using mysql_pconnect the connection will not closed after the execution of script for future use and in mysql_connect the connection will automatically close when execution of script ends.
3 :mysql_pconnect uses less resources than mysql_connect.

What is difference between CHAR and VARCHAR data types in sql

CHAR  used to store  fixed length memory storage whereas VARCHAR is used for variable length memory storage .In VARCHAR if we used less space than defined space ,then the remaining space is not wasted , but In CHAR if we use less space than defined space then remaining space is wasted.
CHAR stores only character values  whereas VARCHAR stores alphanumeric values

What is difference between MyISAM and InnoDB storage engines in mysql

1 : InnoDB provides us row level locking while MyISAM provides us table level locking.
2 : InnoDB offers foreign key constraints wheres in MyISAM does not have foreign key constraints.
3 : InnoDB does not have full text search wheres MyISAM provides us full text search.

How to get current date in MySql

SELECT CURRENT_DATE();

What type of storage engine mysql support

Below are some storage engines names that a mysql support.
1 : MyISAM.
2 :InnoDB
3 :Memory
4 :CSV
5 :Merge etc.

How to find unique records in MYSQL

SELECT DISTINCT columnname FROM tablename;

how to fetch records sorted in an ascending (asc) or descending (desc)

SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
ELECT col1,col2 FROM tablename ORDER BY col2 ASC;

How to get total number of rows

SELECT COUNT(*) FROM tablename;

How to Delete a column from a table

alter table [table name] drop column [column name];

How to add a column from a table

alter table [table name] add column [new column name] varchar (20);

How do you return the a hundred items starting from 10th position

SELECT item_name FROM items LIMIT 10, 100.

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.

Which MySQL Datatype should be used for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT.
For versions lower than 5.0.3 you can use bool and boolean which are at the moment aliases of tinyint(1).

What is the different between NOW() and CURRENT_DATE()

NOW () is used to show current year,month,date, hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

What is command to check table is exist

CHECK TABLE table_name;

Write an SQL query to find names of employee start with ‘A’

SELECT * FROM Employees WHERE EmpName like ‘A%’ ;

What is heap table in MySQL

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL,you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They don’t allow BLOB or TEXT fields.