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
|
1 2 3 4 5 6 7 8 |
<?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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?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-
|
1 2 3 |
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:
- Cross join
- Inner join
- Left join
- 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 language) 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
;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.
