IBM MySQL Most Frequently Asked Latest Interview Questions Answers

Why phpMyAdmin is used for MYSQL?

PhpMyAdmin is a very popular and easy to use GUI tool that can allow SQL commands to be run to create database, create tables, insert data and retrieve it. It provides a web based interface to the user for the ease of use. phpMyAdmin allows user to manage everything from one place and no other installation is required in the computer after this.

Write a query to create a database and a table?

MySQL comes up with some default database that can be used as a base to create a new one. The command that is used to create a new database is as follows:

CREATE DATABASE SQL command

The command has to be written in MySQL terminal. This command will create a new database and then you can create new tables and include data in it.

How can you make a database as your current database?

After making a database the first thing which has to be done is to create a table inside the database to test the new database that is being created. The command which is used to do that is:

USE aliendatabase;

This command allows us to make a database which is not a current database as my current. I have to just use the USE variable and the name of the database and it will become active for use.

What is the difference between a database and a table?

There is a major difference between a database and a table. The differences are as follows:

1. Tables are a way to represent the division of data in a database. Whereas, database is a collection of tables and data.
2. Tables group the data in relation with each other and create a dataset; this dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but opposite is not true.

IBM MySQL Most Frequently Asked Latest Interview Questions Answers

Is the syntax correct? Explain the meaning of the syntax given below

$dbc = mysqli_connect('data.aliensabductedme.com', 'owen', 'aliensrool', 'aliendatabase');

Yes the syntax is correct and this query is getting used to connect the database with a PHP script. PHP uses three functions to communicate with MySQL database. The three functions are:

mysqli_connect(), mysqli_query(), and mysqli_close().

mysqli_connect() and mysqli_query() are used to connect to mysql database and issue a query to the database. mysqli_query() is also used to retrieve the string or any other data from the database. Mysqli_close() is used to close a connection with the database.

How to connect a PHP script with the MySQL database?

PHP script can be connected with MySQL database by using the function called as mysqli_connect() function. You need to provide the information related to your location, username and password to the server to get permission to interact with the MySQL database server. When database name will be asked provide the name and it will connect you to the database.

What is the difference between truncate and delete?

Delete command is used to delete data from a table for example Remove emails where we write a script to delete the customer’s data. It deletes the rows of data from a table. The syntax of it as follows:

DELETE FROM table_name

Whereas, truncate is very dangerous command and should be used carefully as it deletes every row from a table. The syntax of it as follows:

TRUNCATE TABLE "table_name"

How important is to list the column names when doing an INSERT?

It is not important to list the column names when doing using an INSERT command as you can provide the column information and values in the table in the same order in which they appear in the table structure. It is safer and convenient way to specify the column names as it will keep the count of the column you are visiting.

Where’s database data actually stored? Is there a way to see the files which are stored?

Database data is usually get stored in the computer hard-disk and you can manage the data by the database program like MySQL and phpAdmin. The files can be seen but database files remain in binary format so it can be opened and read but, you have to put extra effort to understand it. SQL is given for the purpose of interacting with the database and read the database and retrieve the information out of it.

Why to use CHAR instead of VARCHAR in the database?

CHAR is much more accurate and efficient to use. CHAR doesn’t have to keep a count of the variable length. It is more efficient when you have to use it for a text column which is of an exact length. Char is used for the data which are fixed, but VARCHAR is used for data like password, which are variable.

What Is Mysql Default Port Number?

MySQL default port number is 3306.

What Is Regexp?

REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.

How Many Columns Can You Create For An Index?

You can create maximum of 16 indexed columns for a standard table.

What Is The Difference Between Now() And Current_date()?

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

What Is A Trigger In Mysql?

A trigger is a set of codes that executes in response to some events.

What Is The Difference Between Heap Table And Temporary Table?

Heap tables:

Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
Temporary tables:

The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.
Main differences:

The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

What Is The Difference Between Float And Double?

FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.

What Are The Advantages Of Mysql In Comparison To Oracle?

MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
MySQL is great for database enabled websites while Oracle is made for enterprises.
MySQL is portable.

What Are The Disadvantages Of Mysql?

MySQL is not so efficient for large scale databases.
It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
Transactions are not handled very efficiently.

What Is The Difference Between Mysql_connect And Mysql_pconnect?

Mysql_connect:

It opens a new connection to the database.
Every time you need to open and close database connection, depending on the request.
Opens page every time when it loaded.
Mysql_pconnect:

In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection.
the database connection can not be closed.
it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

Which Command Is Used To View The Content Of The Table In Mysql?

The SELECT command is used to view the content of the table in MySQL.

What Is The Usage Of I-am-a-dummy Flag In Mysql?

In MySQL, the i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

What Is The Usage Of Regular Expressions In Mysql?

In MySQL, regular expressions are used in queries for searching a pattern in a string.

* Matches 0 more instances of the string preceding it.
+ matches 1 more instances of the string preceding it.
? Matches 0 or 1 instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.
"." Can be used to match any single character. "|" can be used to match either of the two strings
REGEXP can be used to match the input characters with the database.
Example:
The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name
From employee
Where employee_name REGEXP '1000'
Order by employee_name.