MYSQLI INTERVIEW QUESTIONS ANSWERS

MySQL is an open source Database Management System which is built, supported and distributed by MySQL AB (now acquired by Oracle)

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 deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT and Update etc.
Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

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.

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

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;

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

MySQL database software is a client or server system which includes

  • Multithreaded SQL server supporting various client programs and libraries
  • Different backend
  • Wide range of application programming interfaces and
  • Administrative tools.

HEAP tables are present in memory and they are used for high speed storage on temporary

basis.

• BLOB or TEXT fields are not allowed

• Only comparison operators can be used =, <,>, = >,=<

• AUTO_INCREMENT is not supported by HEAP tables

• Indexes should be NOT NULL

  • MySQL is open source software which is available at any time and has no cost involved.
  • MySQL is portable
  • GUI with command prompt.
  • Administration is supported using MySQL Query Browser

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.

MySQL supports the following types of joins:

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

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

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

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

SELECT CURRENT_DATE();

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

TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

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.

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

SELECT DISTINCT columnname FROM tablename;

SELECT COUNT(*) FROM tablename;

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

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.

Following are the differences between mysql_fetch_array and mysql_fetch_object:

mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.

mysql_fetch_object –  Returns a result row as object from database.

In MySql, top 50 rows are displayed by using this following query:

Maximum of 16 indexed columns can be created for any standard table.

Following objects are created using CREATE statement:

  • DATABASE
  • EVENT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • TABLE
  • TRIGGER
  • USER
  • VIEW

SIX triggers are allowed in MySql table. They are as follows:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE and
  • AFTER DELETE

Leave a Reply