Category Archives: MySQL

Articles and Interview Questions,Tips related to MySQL – Interview Sortout

Choose between MongoDB and MySQL?

As there is a huge surge in the on-demand application market, businesses are looking to build feature-rich applications across platforms. Database management of such high functioning application is complex and tedious. With large coding structures and programming to incorporate all the changes and updates that will be integrated at various stages of app development.

There are two major DBMS in the market, which are open-sourced, free and most popular too. They are MongoDB and MySQL. MongoDB is developed by MongoDB inc. and MySQL is developed by Oracle. It is important to know them very well when it comes to choosing the right DBMS.

MongoDB: An Introduction

MongoDB was conceptualised in 2007 and launched in 2010 by MongoDB inc. It employs key-value pairs known as a document store. These document stores are created and maintained in BSON files, which are a modified version of JSON files. The use of modified JSON files allows the exchange of data between web applications and human-readable servers. There is no schema implementation, providing flexibility in the design of code structure.

It allows hierarchal relationships representation and changes in records onboard. With NoSQL or un-structured query language, MongoDB provides facilities of auto-sharding and embedding and mirroring.

MySQL: An Introduction

Originally, designed as MySQL AB by Oracle, this DBMS is a relational database management system or RDBMS. MySQL employs the storage of data in vertical columns and horizontal rows. It uses structured query language for data access and transfers through commands like SELECT, INSERT, UPDATE, DELETE.

Related information is stored in different tables reducing the possibility of data duplication. MySQL uses pr-defined schema designs, which can’t be changed.

Performance Comparison

MongoDB has a single most important edge over MySQL and that is its ability to handle large unstructured data. It is magically faster because it allows users to query in a different manner that is more sensitive to workload. It also allows more RAM and CPU slice for queries to run faster.

In the MongoDB database, one can partition and shard the database with the In-built sharding feature. You can specify a shard key and sharding is enabled, which partitions the data evenly according to the shard key. MongoDB also possess an auto replica feature which allows replicating of data and imparting them primary and secondary role according to the scenarios at any point, in the entire process, It also improves the flow rate of operations by providing built-in special functions that help in finding specific data from specific locations with accuracy to boost up the overall process.

MongoDB uses security features, which include authentication, auditing, and authorization and you can also use Transport Layer Security (TLS) and Secure Sockets Layer (SSL) for encryption purposes. This ensures that it is only accessible and readable by the intended client.

While MySQL authenticates a user and facilitates it with user privileges on a particular database such as CREATE, SELECT, INSERT, UPDATE, and in doing so, it fails to explain why a given user is denied specific access.

When to use MongoDB?

MongoDB can be used when you need to build a cloud-based application for multi-platforms. When there is a need for instant and fast data recovery, totally automated. When you need to reduce your schema migration cost without a database administrator.

When to use MySQL?

MySQL can be used if your database is not scaled yet and want a low maintenance setup. If you want high performance on a limited budget and with a fixed schema. There will be no major changes in the near future and there would be high transactions. The most important parameter is data security as MySQL is a totally secure database.

Scalability Comparison

Scaling is done in two ways, vertically and horizontally. Vertical scaling includes adding more powerful RAM and CPU processing power to the same machine. While horizontal scaling is to add more machines to your pool adding RAM power and CPU processing power by unilateral distribution.

MySQL databases are vertically scalable, which means that you can increase the load on a single server by increasing things like CPU, RAM or SSD. MongoDB databases are horizontally scalable. You can handle more traffic by sharding, or adding more servers in your MongoDB database.

Spread the love

What is a transaction in Database?

Database transaction interview questions covered in this section:
What is a Transaction?
What are the different states of Transaction?
What are the main properties of Transaction?
What are the advantages of a Transaction?
Do all storage engines support transaction?
What are the ACID properties of a database system?

Transaction

An independent and logical unit of work that is performed to complete read and write operations on a database and in an RDBMS is known as Transaction. A Transaction may contain several sub-tasks and after a complete transaction, the overall data remain consistent.

Let’s take an example of a Banking System where Money is transferred from one account to another.

Steps :
Step 1: Account A has amount = a
Step 2: Account B has an amount = b
Step 3: The total amount that A and B have combined = (a+b)
Step 4: Now A transfers 1000 rupees to B.
Step 5: So After transaction A has amount = a-1000
Step 6: and After transaction B has amount = b+1000
and after transaction combined amount that A and B have is
= (a-1000) + (b+1000)
= (a+b) and this the amount total that A and B have before the transaction.

States of Transaction

A transaction in its life cycle may have in different states at different time. There are the following states that a transaction may have:

  1. Active
  2. Partially Committed
  3. Failed
  4. Aborted
  5. Committed

Active

When a transaction starts it is in Active state. The very initial stage of a transaction.

Partially committed

After initialization operation starts and the state is known as partially committed. The partial word is added because the operation is not yet completed and the transaction does not reflect the data manipulation results.

Failed

A transaction is failed by many reasons like any checks made by the database system, fails OR Database systems fails or system crashed. In such cases transaction is called failed and can not proceed further.

Aborted

When any checks fail then It is necessary to abort the transaction and kill it. In this phase, the changes made by write queries undone and database is backed to initial state.

Committed

Committed is a state when all the operations in a transaction completed and unit of the task done completely. After commit, changes can not be undone and can be reflected in the result.

ACID Properties

A single independent logical unit in a database or a transaction need to maintain consistency during processing. So there are some standard and norms that need to follow by these transactions and these are the mandatory features of any transactions. These features are known as ACID properties. ACID means a system must maintain Atomicity, Consistency, Isolation, and Durability.

Atomicity

There is no intermediate state between all and none. A transaction is an atomic unit and either it’s executed all the operations or none of them executed that means either the whole transactions take place or none of them happened. If some of them executed and the transaction fails then in partially committed condition then Roll Back occurs and all the parts done operations, undone.

Consistency

Let’s say that the amount of data must be consistent before and after any transaction. This is very important to maintain the integrity of data during a transaction. An example given above can explain the transaction.

Isolation

A transaction is an independent thread of all the other transactions. Failure of success of any transaction does not affect the presence of any other transaction. There may be many transactions are executing at a time. While multiple transactions are executing on a system the integrity of data is maintained.

Durability

After successful completion of any transaction, the data is saved to disk and should be persistent. The changes become permanent after a transaction committed. Even after system failures and several restarts.

Spread the love

What is the Storage Engine in MySQL

Following Question Based on MySQL Storage Engine covered in this Section:

  1. What is storage engine?
  2. How many types of storage engines are there in MySQL?
  3. What are the features of Different type of storage engines?
  4. Why Memory is faster than MyISAM and InnoDB?
  5. Which one is the Transaction Compatible Engine?
  6. Which are the storage engine in MySQL , do not use Indexing?
  7. What are the main difference between in MyISAM and InnoDB?
  8. Which version of MySQL support Transaction?

MySQL Storage Engine

Storage Engine in MySQL is the bottom most layer and stores data in various formats. Based on their techniques and compatibility for different types of application we choose which one would be suitable. MySQL has number of storage engines. Few of them are transactional and few are non-transactional. Here is the list of popular storage engines in MySQL:

  1. MyISAM
  2. InnoDB
  3. Memory(HEAP)
  4. Archive
  5. CSV
  6. MERGE

MyISAM

This is a non-transactional storage engine and a table can hold up to 256 TB data. Before MySQL 5.5 MyISAM was the default storage engine, but after Introduction of transaction support InnoDB is the default storage Engine in MySQL. Data in MyISAM is compressed and optimized for quick access. Earlier it was known as ISAM. It can repair the corrupted tables itself on startup.

InnoDB

InnoDB is the most widely used storage engine now a days and used as a default storage engine in MySQL. It is transaction safe and compatible to ACID properties of database transactions. It supports all the DML, DDL and DCL operations. It can hold up-to 64 TB data in a table. It also uses compression techniques to store data.

MEMORY

Memory Engines store data in Memory. It uses the hash index techniques to store data and hence the access of data is comparatively faster than MyISAM and InnoDB. In Memory data is stored in the form of HEAP tables.

Archive

The engine is used for archiving the records. It can be used to archive the huge amount of data and records. It stores data in compressed format and when an access request is come it serves the data after decompressing the archives. We can not have Indexing in Archive engines so we have to scan all the data to get the particular records.

CSV

CSV is a well-known spreadsheet format. It is also used for Non-SQL based systems to store and retrieve data. Data is stored CSV format or comma separated formats. This is also a non-indexing technique. We can not have a NULL value in CSV. It also required to read full table to get a record. Hence it is slower than InnoDB and MyISAM.

MERGE

MERGE is storage engine that uses MyISAM tables as component and merged them to store as a single table. MERGE tables use Indexes of component tables, they do not have their own indexes. When we run DROP command then It does not drop tables in real, but the merger splits.

Spread the love

TOP 10 : MySQL Database Interview Questions For Experienced

What are the storage engines used by MySQL?
Storage Engines supported by MySQL are as follows:
  1. InnoDB
  2. MyISAM
  3. Memory
  4. CSV
  5. Merge
  6. Archive
  7. Federated
  8. Blackhole

Which one is the default storage Engine in MySQL?
The default engine is InnoDB.

What is a database engine?
Database Engine is the basic building blocks of storage systems , used by Database Management Systems to run CRUD (INSERT,SELECT,UPDATE,DELETE)instructions.

What is difference between InnoDB and MyISAM?
InnoDB is featured with ACID propety of transactions, but MyISAM does not support transaction. MyISAM supports FULLTEXT search index where as InnoDB does not.

What are HEAP tables in MySQL?
HEAP tables are the existing tables in memory.They also known as memory tables and use Indexes instead of BLOB and TEXt.

What are Stored Procedures in MySQL?
Stored Procedure is Batch of SQL statements and saved in database. It also accepts parameters and can be triggered explicitly. It improves the traffic control and gives a modular structure to implement maintainability.

CREATE PROCEDURE show_employee

AS

    SELECT FirstName, LastName

    FROM employee;

EXEC show_employee;

What is Trigger?
Auto executable sets of SQL statements stored in database are known as Triggers. They are fired when some event CRUD event occurs.When we do some CRUD operations on table the trigger linked with that table fires. Trigger is behaves like a stored procedure but it invoked automatically. It adds a layer of security in database.

What are differences between Trigger and Stored Procedure?

We can execute procedure explicitly whenever we would like , but trigger can only be invoked on an event fire.We can call a Procedure inside another Procedure but we can not invoke a trigger directly inside another trigger. We can use trigger for transaction purpose but we can not use procedure for transactions.

What are the CONSTRAINTs in MySQL?
MySQL CONSTRAINTs used to define the rules for data restrictions. MySQL provides various types of constraints as:
NOT NULL– does not allow NULL value
UNIQUE – does not allow duplicate entry in column
PRIMARY KEY – uniquely identify a rows in a table
FOREIGN KEY – Primary Key of another table is being as a reference in another table
CHECK – Check for the valid data to be saved in the database
DEFAULT – set the default value for the column, in case of no value passed to SQL statement.
We can declare MySQL CONSTRAINT at the time of table creation or we can alter table to apply CONSTRAINT.

What do you mean by index in a table?
MySQL Indexing is the indexing of data as keys to make the accessibility fast and easy. Rather than searching all over the data indexing allows to get directly to the indexed values. It Eliminates the unnecessary iteration because the indexes are stored in the form of B.Tree.

Spread the love

Basic MySQL Interview Questions and Answers

What is MySQL?
MySQL is a Relational Database Management System marketed and distributed by Oracle as an open source software. However, enterprise edition is also available with support systems.

What is Relational Database?
Yes, we can say a database as a Relational database, because tables in the database are related to each other.

What is the use of the DISTINCT phrase in MySQL?
DISTINCT eliminates the duplicate records in the selection and return unique values. We can use it also with aggregate functions like AVG, SUM, MAX, MIN etc.

What is SELF JOIN?
Join a table to itself is called SELF JOIN. We must use an alias of the table to join itself. There is no any phrase like SELF JOIN.

What is LEFT JOIN?
LEFT JOIN gets the column of the LEFT table if the relation exists with the next table.

What is CROSS JOIN?
CROSS JOIN is a cartesian product of two tables and does not require any join condition. CROSS JOIN used to JOIN two table having no relations.If table A has 100 rows and Table B has 200 rows then we will get 100×200 = 20000 rows in CROSS JOIN.

What is INNER JOIN?
INNER JOIN gets all the records that are matched or common between the tables followed by the condition.

What is FULL JOIN?
FULL JOINS returns the records from both the tables. It is the result of LEFT JOIN and RIGHT JOIN at the same time.

What is RIGHT JOIN?
RIGHT JOIN results in the column of the RIGHT table if the relation exists with the next table.


What is OUTER JOIN?
LEFT JOIN, RIGHT JOIN and FULL JOIN are OUTER joins.

What are aggregate functions in MySQL?
The function that performs a calculation on records and returns a resulting single value is known n as an aggregate function. We have many aggregate functions in MySQL. As : AVG,SUM,MIN,MAX,COUNT.

What is the difference between DISTINCT and GROUP BY?
DISTINCT can return NULL values because it considers NULL as a value and selects one NULL from many, however, GROUP BY does not consider NULL values as a record. GROUP BY sorts the records where DISTINCT does not.

What is VIEW MySQL?
VIEW is an imaginary table holding the real records, based on query statements. It fetches data from real tables and displays always updated values. We can do all the MySQL operations on it.  It helps to achieve security and reusability while implementing database operations. We can hide sensitive data from end users while limiting the access of original tables.
For example, we can have a VIEW for all the employee who is older than 40 years.

CREATE VIEW older_than_fourty AS SELECT * FROM employee WHERE age > 40 ;
Spread the love

DBMS Interview Questions and Answers

What is DBMS?
A database management system (DBMS) is system software. The DBMS is used for creating databases and manipulating data and records in an organized way.

What is RDBMS?
RDBMS stands for Relational Database Management System. it is based on relational blocks. RDBMS replaces the traditional file system to store the data. All modern systems follow the concept of RDBMS. for example MS SQL Server, Oracle, MySQL etc.

What is Relational Database?
A relational database is a collection of tables and the tables are related to each other. Data is organized in the form of a matrix of rows and columns. The columns are Fields and Rows are the records.

What do you mean by Schema?

The Schema is the structure of Database System.

What is Database Model?
The database model is a conceptual model to represent the flow of data processing and manipulation techniques.It displays the Blueprint of a database.

What is ER-Model?
The entity-relationship model (or ER model) is a graphical representation of Database Model.  ER model uses different types of graphical shapes to represent the different types of the element of database systems.

What is Metadata?
Metadata is the information about other data stored.

What is data dictionary?
The data dictionary is the collection of Metadata. All the metadata are stored in data dictionary.

What is tuple?
A tuple is a row or a record in a table.

What is the unique key?
The unique key is a column of a table that is used to identify a column. It defines a column uniquely.No two rows can have the same unique key. It is used as a constraint to make records unique.

What is a foreign key?
A foreign key is a unique key of a table can is used as a reference in another table. A column in a table can be used to identify records in another table, then It will be a foreign key.

What is a composite key?
A composite key is a combination of keys that functions like a unique key. When two or more columns are required to Identify a record uniquely, then It will be called as a Composite key.

What is Index?
The index is a key that is used to search records in the database. An index makes searching and parsing of data easy. It can be used for sorting or grouping purpose in queries. It improves the runtime of queries.

What is the procedure?
We can say a procedure is a function defined in PL/SQL. It is the collection of multiple Structured Query Language. We cam implement modules in SQL using procedures.

What is a transaction?
A transaction is an independent thread in execution. It can be described on the basis of ACID properties.

Explain ACID properties?

ACID properties are associated with a database transaction. ACID stands for Atomicity, Consistency, Isolation, and Durability. A transaction must maintain these states during its execution.

What is aggregation?
Aggregating the values of many rows to produce an efficient result is known as aggregation in Database System. There are many functions to complete these tasks as SUM, COUNT, AVG etc.

What is normalization?
Normalization is a technique to process over a grouped records organized in tuples.

What are the database languages?
Database systems provide some standard communication that can be used to operate the systems. We can read, update, alter and store data in tables using these languages. For example, SQL is a database Language.

What is DDL – the Data Definition Language?
The database language for creating the structure is known as DDL or Data Definition Language. CREATE, ALTER, and DROP is the DDL parts.

What is DML – the data Manipulation Language?
DML is a tool to read and amend the data stored in the database. SELECT, INSERT, UPDATE are the part of DML.

Spread the love

Most Common PHP-MySQL Interview Questions and Answers

There are some basic PHP Interview questions, asked in every interview often. They must be known to every interview aspirants.

Chances of asking these questions by Interviewer is very high because they just test your basic concepts of PHP. They highlight your understanding and command of the language. They are helpful to nail the first impression on the interviewer when you are in the interview.

An Interviewer expects that you should know the answers to these questions. After these questions, you will be asked to answer some hard-core questions and concepts.

What we have given here is experienced. these questions give you the pattern of often asked questions on Interviews along with their answers.

We try to give genuine answers, by verifying them from www.php.net, www.w3schools.com, and other popular tutorials site. After knowing the pattern It will be easy for you to explore more questions from various sources.

What is PHP?
PHP is a server-side scripting language. It is lightweight as well as enriched with OOPs features. PHP is freely available and has lots of frameworks, CMS to built robust and secure applications.

Explain these functions :
echo( ),print( ),print_r( ),die( ),exit( ),var_dump( )
var_dump($var):- Displays information about a variable.
echo() or echo: This is not actually a function.This doesn’t return a value after it takes parameter.
print() pritn : This is also not a function ,accepts always single parameter and returns 1.
print_r():-This prints the content and indexes of arrays and objects.
exit:-It terminates the current script with a message.
die(): This is identical to exit;

What are the differences amongst include(), require(),require_once(), require(),include()?
include(): Includes an external script into current script. On failure it emits Warning and allows script to continue, it does not terminate the execution of the script.
include_once(): On failure emits an compile E_COMPILE_ERROR  error. It terminates the script execution.

What is the difference between in_array() and is_array()?
Both of these function returns a boolean type.
in_array() :
If a value finds within an array it returns TRUE otherwise returns false.
is_array() :
It checks whether the type of variable is an array or not. It returns true if an array is passed into it. It can be applied to any type of array.

What is the difference between count() and sizeof()?
Literally we can say there is no difference between these two terms. Both of these have Identical behaviors.
count():- It counts all elements of an array or object.
sizeof():-Identical to count(), can say Alias.

What is the difference between json_encode() and json_decode()?
json_encode() converts a PHP array or an object into json string, and json_decode() convers an JSON string into PHP array or into PHP object.

Where do we store Session and Cookie?
The session is stored on the server whereas cookies are stored on the client machine in browsers.

How we use foreach loop in PHP?
Foreach loop is used to iterate an array or an object in PHP. For example

<?php 
foreach ($array as $key =>; $value){
echo "$value is at $key place";
}
?>

What are the differences amongst trim(),ltrim(),rtrim()?

  • trim():-It removes white spaces from both the sides of a string.
  • ltrim():-It removes white space from the left side of a string.
  • rtrim():-It removes white space from the right side of a string.

What are the constants , how we define them in PHP?
A constant is a name assigned to a value. It is like Identifiers but cannot change during script execution. We can define them usually in UPPER_CASE underscored string without $. For example: define(“CURRENT_YEAR”, “2017”);


What is $_GLOBAL?
$_GLOBAL Is a superglobal array and holds all the variables that are global and can be accessed from anywhere in the script. $_POST,$_GET,$_REQUEST,$_FILE,$_SESSION,$_COOKIE all are superglobal arrays available in PHP. We can call them anywhere in the script.


What is the difference between MySQL and mysqli?
MySQLi is the extension of MySQL and It is an improved version of MySQL. MySQLi supports OOPS features and compatible with PDO.


What unlink() function do in PHP?
It is used to delete a file or directory from its location.

What are the basic differences amongst Warning, Fatal Error, and Notice?
Fatal error results in the termination of the script, no further execution of the script is possible when it happens. Whereas Notice does not terminate the script, rather it notifies about some tolerable mistake and script after that line is able to execute. Warnings are the deprecated and bearable things in scripts. This doesn’t affect the result usually.

Which function is used to send email in PHP?
The mail() function is used to send emails in PHP. Learn more about The PHP mail() Function.

What is the difference between array_diff() and array_filter()?
array_diff() returns the array of values of difference of two arrays and array_filter() returns the arrays of values and filter null values or specific values. The function array_filter() also used a callback function but it is optional.


What is the difference between array_serach() and in_array()?
The method array_search() returns the key if the value is found in the array and the function in_array() return TRUE if it will find the values in the array. In case of a failure, both functions return FALSE.


What strlen() do?
strlen() is a PHP library function and it returns the length of string passed to it.


How substr() works?
substr() is a PHP library function and it returns the part of string passed with parameters to it. It accepts starting point, length of returning string and input string as parameters.


How str_replace() works?
It is used to replace a sub-string with another substring in an input string.

 

Spread the love