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 the difference between DBMS and RDBMS?
RDBMS (relational database management system) applications store data in a tabular form.
. DBMS stores 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 the 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 a 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 a 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 the 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 the 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 the difference between FLOAT and DOUBLE?
FLOAT and DOUBLE both represent 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 values whereas a DOUBLE is for double-precision values and a DOUBLE allows greater accuracy than a FLOAT.
Float saves floating-point numbers up to eight places and DOUBLE saves floating-point numbers up to 18 places.
How many types of SQL joins?
MySQL supports the following types of joins:
- Cross join
- Inner join
- Left join
- Right join
How to find a second maximum salary?
SELECT max(salary) FROM tblname WHERE salary <( SELECT max(salary) FROM tblname )
What is the 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 roll back.
4 : We can not use the where clause in truncate but in delete, we can use conditions using the where clause
What is the difference between the PRIMARY key and UNIQUE Key in SQL?
1: UNIQUE key can be a NULL value but the 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, the 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 the difference between mysqli_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 the difference between CHAR and VARCHAR data types in SQL?
CHAR is 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 the remaining space is wasted.
CHAR stores only character values whereas VARCHAR stores alphanumeric values
What is the difference between MyISAM and InnoDB storage engines in MySQL?
1 : InnoDB provides us with row-level locking while MyISAM provides us table-level locking.
2 : InnoDB offers foreign key constraints wherein MyISAM does not have foreign key constraints.
3 : InnoDB does not have a full-text search whereas MyISAM provides us full-text search.
How to get the current date in MySQL?
SELECT CURRENT_DATE();
What type of storage engine does 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 do fetch records sorted in an ascending (asc) or descending (desc)?
SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
SELECT col1,col2 FROM tablename ORDER BY col2 ASC;
How to get the 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 a hundred items starting from the 10th position?
SELECT item_name FROM items LIMIT 10, 100.
What is the difference between the primary key and the candidate key?
Every row of a table is identified uniquely by the primary key. There is only one primary key for a table.
The Primary Key is also a candidate key. By common convention, the candidate key can be designated as primary and 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 difference between NOW() and CURRENT_DATE()?
NOW () is used to show the current year, month, date, hours, minutes, and seconds.
CURRENT_DATE() shows the current year, month, and date only.
What is the command to check the table is exist?
CHECK TABLE table_name;
Write an SQL query to find names of employees starting with ‘A’?
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
What is a 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 a temporary basis. They don’t allow BLOB or TEXT fields.
