MySQL DBA Interview Questions and Answers

🔰 MySQL DBA 🔰

Last Updated: 2023-09-23

Can you tell the difference between Mysql_connect And Mysql_pconnect?

Mysql_connect

Used to open a new connection to a database.

You can open and close the database connection based on the request.

Opens a page everytime the page is loaded.

Mysql_pconnect

Used to open a persistent connection in a database

You cannot close the database connection.

There is no need to open and close a connection everytime a page is loaded.

What is the default port for MySQL server?

MySQL Server’s default port is 3306. Apart from this, another standard default port for the SQL Server in TCP/IP is 1433.

Can you tell what are the different set operations available in MySQL?

The various set operations available in MySQL are as follows:

UNION – This operation returns all the distinct rows selected by a query

UNION ALL – This operation returns all the rows selected by a query and also includes all duplicate rows.

MINUS – This operation returns all the distinct rows selected by the first query but does not select the rows selected by the second query.

INTERSECT – This operation returns all the distinct rows selected by both queries.

Can you tell the order of SQL SELECT statement?

The order of SQL SELECT statement is as follows:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

What is Database White Box Testing?

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

What is Database Black Box Testing?

Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis.

What is CTE?

An expression which consists of a temporary set of results defined in a SQL statement is said to be a Common Table Expression(CTE).

What are the different tables present in Mysql?

There are mainly five types of tables present in MySQL. Out of all these database engines, the default database engine used in MySQL is MyISAM. Refer below to know the five types of tables:

MyISAM

Heap

Merge

INNO DB

ISAM

What is a Cursor?

Considered as a pointer to point to one row in a set of rows, a Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

How can you test for NULL values in a database?

A NULL value is a field with no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.

Refer below for Syntax of IS NULL and IS NOT NULL.

SELECT column_names FROM table_name WHERE column_name IS NULL;SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

Can you elaborate on BLOB and TEXT in MySQL?

BLOB

BLOB(Binary Large Object) is used to hold a variable amount of data and holds up to 65,535 bytes of data. The following are the four types of BLOB.

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

TEXT

TEXT is used to store string values and holds up to a maximum length of 65,535 characters. The following are the four types of TEXT

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

Can you tell how can you display the Maximum salary in SQL?

To display the maximum salary in SQL, you can use the inbuilt function called MAX().

What is the difference between the NVL function, IFNULL function, and the ISNULL function?

The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value. The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function

For example, let us say we have a column(column_3) which has NULL values.

So, if you run the below statement, the output you would get is a NULL value.

SELECT column_1 * (column_2 + column_3) FROM Example_Table

Now, to overcome this, you can use the above three functions as follows:

SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table

What is the difference between GUI Testing and Database Testing?

GUI Testing Database Testing

Also known as User Interface Testing of Front-end Testing. Also known as Back-End Testing or Data Testing.

Deals with items that interact with users. Deals with items that are hidden from users.

Testers need not know SQL. Testers need to know SQL.

GUI Testing focuses on the outlook of the application Database Testing focuses on the integrity of data in the front end with the data present in the back end

How To Display Nth Highest Salary From A Table In A Mysql Query?

Consider the table named “Employee”.

Now, to find the Nth salary consider the below statement.

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1

So, if you want to find out the 7th largest salary, consider the below query.

SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 6,1

What is the command used to create a database using PHP and MySQL?

The command used to create a database using both PHP and MySQL is mysql_create_db(“Database Name”).

Can you tell the Difference Between Mysql_fetch_object And Mysql_fetch_array?

Both of them are similar but vary with a single difference. Mysql_fetch_object return as object and Mysql_fetch_array returns an array. This means that you cannot access the data by their offsets but can only access through its fields names.

What are the ways in which you can retrieve data in the result set of MySQL using PHP?

The different ways in which you can retrieve data in the result set of MySQL using PHP are as follows:

mysql_fetch_object: This constant fetches a result row as an object.

mysql_fetch_array: This constant fetches a result row as an associative array, numeric array or as both.

mysql_fetch_row: This constant gives us a result row as an enumerated array.

mysql_fetch_assoc: This constant gives us a result row as an associative array.

Can you tell how many values can Set the function of MySQL to consider?

MySQL’s Set function can take a maximum of 64 values, but can also consider 0 values.

Can you tell the reasons for selecting Lamp(Linux, Apache, MySQL, PHP) instead of any other combination of software programs, servers, and operating system?

The reason behind selecting Lamp stack is very simple. Linux, Apache, MySQL, PHP are open source software. The security of the Linux operating system is much more than Windows. The Apache server is a better server than others in the perspective of functionalities and security. MySQL is one of the most popular open source databases is used with PHP to perform various functionalities.

Subscribe For Free Demo

Can you tell a way to know the number of days between the two given dates in PHP?

You can simply declare the two dates, and then use the strtotime function to subtract both the dates and find the differences between the days in seconds.

Consider the below example.

date1 =’2018-09-15′;

date2 = ‘2018-10-15’;

days = (strtotime($date1) – strtotime($date2)) / (60 * 60 * 24);

Can you tell how to find the number of rows in a resultset using PHP?

You can use the mysql_num_rows function to find the number of rows in a resultset.

Consider the below example.

output = mysql_query(sql, database_name);number_of_rows = mysql_num_rows(output);echo “The number of forws found are equal to: $number_of_rows”;

What are the functions used to encrypt and decrypt the data present in MySQL?

The function used to encrypt the data is AES_ENCRYPT() and the function used to decrypt the data is AES_DECRYPT().

If you wish to encrypt the username and password using PHP, how will you do that?

You can encrypt the username and password using the following functions respectively:

SET USERNAME=USERNAME(“Username”);SET PASSWORD=PASSWORD(”Password”);

How can you increase the performance of MySQL SELECT query?

The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set. The SELECT statement can be either individually used or can be used with other statements such as ORDER BY, GROUP BY, and HAVING clause.

To increase the performance of a MySQL SELECT query, you can use the LIMIT clause to limit MySQL from further search in a table, after collecting the required number of records. Apart from this, we can also use the LEFT JOIN or the RIGHT JOIN to retrieve data from two or more tables.

Can you tell the difference between $message and $$message?

$message and $$message are both PHP variables. $message is used to store the variable data and $$message is used to store the variable of a variable. So basically, data is stored in $message and $$message is used to store the data that can be changed dynamically.

What Is Mysql?

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world's second most popular and widely used open source database.

In Which Language Mysql Is Written?

MySQL is written in C and C++ and its SQL parser is written in yacc.

What Are The Technical Specification Of Mysql?

MySQL has the following technical specifications - Flexible structure High performance Manageable and easy to use Replication and high availability Security and storage management

What Is The Difference Between Mysql And SQL?

SQL is known as a standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe. A PHP script is required to store and retrieve the values inside the database.

What Is The Difference Between Database And Table?

There is a major difference between a database and a table. The differences are as follows: Tables are a way to represent the division of data in a database while, database is a collection of tables and data. Tables are used to 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 the reverse is not true.

Why Do We Use Mysql Database Server?

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

What Are The Different Tables Present In Mysql?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present: MyISAM Heap Merge INNO DB ISAM

What Is The Difference Between Char And Varchar?

A list of differences between CHAR and VARCHAR: CHAR and VARCHAR types are different in storage and retrieval. CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255. When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

What Is The Difference Between Truncate And Delete In Mysql?

The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.

How Many Triggers Are Possible In Mysql?

There are only six Triggers allowed to use in MySQL database. Before Insert After Insert Before Update After Update Before Delete After Delete

What Is Heap Table?

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 do not allow BLOB or TEXT fields.

What Is Blob And Text In Mysql?

BLOB is an acronym stands for a binary large object. It is used to hold a variable amount of data. There are four types of BLOB. TINYBLOB BLOB MEDIUMBLOB LONGBLOB The differences among all these are the maximum length of values they can hold. TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set. There are four types of TEXT. TINYTEXT TEXT MEDIUMTEXT LONGTEXT

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.

What Does " I_am_a_dummy Flag" Do In Mysql?

The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

How To Get The Current Date In Mysql?

To get current date, use the following syntax: SELECT CURRENT_DATE();

What Are The Security Alerts While Using Mysql?

Install antivirus and configure the operating system's firewall. Never use the MySQL Server as the UNIX root user. Change root username and password Restrict or disable remote access.

How To Change A Password For An Existing User Via Mysqladmin?

Mysqladmin -u root -p password "newpassword".

What Is The Difference Between Unix Timestamps And Mysql Timestamps?

Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

How To Display Nth Highest Salary From A Table In A Mysql Query?

Let us take a table named employee. To find Nth highest salary is: 1. select distinct(salary) from employee order by salary desc limit n-1,1 if you want to find 3rd largest salary: 1. select distinct(salary) from employee order by salary desc limit 2,1

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.

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. 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

How Do You Determine The Location Of Mysql Data Directory?

The default location of MySQL data directory in windows is C:mysqldata or C:Program FilesMySQLMySQL Server 5.0 data.

What Is Mysql Data Directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

What Is The Use Of Mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

How Is Myisam Table Stored?

MyISAM table is stored on disk in three formats. '.frm' file : storing the table definition '.MYD' (MYData): data file '.MYI' (MYIndex): index file

What Is The Usage Of Enums In Mysql?

ENUMs are used to limit the possible values that go in the table: For example: CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').

What Are The Advantages Of Myisam Over Innodb?

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.

Define MySql

MySQL, a multi-user SQL Database Management System that contains eleven million plus installations. MySQL DBA is the second most widespread and extensively utilized open-source Database.

List out the technical specifications of MySql

Below are the MySql technical specifications:

Security and Storing management

Stretchy structure

Replication and high accessibility

Best performance

Controllable and user-friendly

What is the purpose of using Mysql Database Server?

The MySQL Database server is quite rapid, consistent, and simple for usage. It is easy for the user to use and make changes to the software.

Provide the various database engines present in MySQL

Below are the different database engines in Mysql:

Federated

INNODB

CSV

Memory

MyISAM

In Mysql Database, how many triggers are probable?

Only six (6) triggers are allowed in Mysql Database for use.

Mention the six (6) triggers allowed in Mysql Database

The six (6) triggers allowed in Mysql Database are given below:

After Update

After Insert

Before Update

After Delete

Before Delete

Before Insert

How many tables are present in Mysql Database?

There are five (5) tables in Mysql Database

Mention the different tables available in Mysql Database

Five (5) tables available in Mysql Database are provided below:

Heap

MyISAM

Merge

ISAM

INNO DB

Which is the default database engine utilized in Mysql Database?

MyISAM is said to be the default database engine utilized in Mysql Database.

Define ‘Delete’ in Mysql Database

‘DELETE’is for deleting data from a table. This command is used to delete only the rows of data from a table.

Define ‘Truncate’ in Mysql Database

‘Truncate’ is to delete every row from a table permanently. Hence, this is said to be a dangerous command.

Define ‘Heap’ table

Heap tables are the ones that are existing in memory. In Mysql, while creating a heap table, users must specify the type as ‘HEAP’. Also, these tables are usually named as Memory tables. Heap tables are used for large speed storage in a momentary manner.

What are the fields not allowed in Heap table?

TEXT or BLOB fields are not allowed in Heap table.

Define BLOB

BLOB (Binary Large Object) is for holding a variable quantity of data.

What are the types of BLOB?

Four types of BLOBs are given below:

TINYBLOB

LONGBLOB

MEDIUMBLOB

BLOB

Explain TEXT inMysql DBA

TEXT, a case-insensitive BLOB and the text values are always non-binary strings, having a character set and values stockpiled and related depending on the character set collation.

How many TEXT types are available in Mysql DBA?

There are four (4) types of TEXT available in Mysql DBA

TINYTEXT

LONGTEXT

MEDIUMTEXT

TEXT

Mention any one disadvantage of Mysql DBA

Mysql DBA does not support STORED PROCEDURES and COMMIT functions which have a lesser version of 5.0.

What is the command to retrieve the current date in Mysql DBA

Below is the syntax to retrieve the current date in Mysql DB

SELECT CURRENT_DATE();

List out the security alerts when using Mysql

Below are the security alerts while using Mysql

Confine or deactivate the remote access

Install antivirus and configure the operating security system

Modify the origin username and password

MySQL Server is never used as the UNIX root user

Mention the Mysql DBA Default Port Number

3306 is the default port number of Mysql DBA

Provide the command required to view the table content in Mysql DBA

SELECT command is the one to view the table content in MySQL

Mention the advantage of Mysql_close()

Mysql_close() is used to close connection which are opened by mysql_connect().

What is the benefit of Enums In Mysql?

The purpose of ENUMs is to restrict and limit the probable values that get inside the table.

Give an example for Enums in Mysql

CREATE TABLE weeks (week ENUM ‘Monday’, ‘Tuesday’, ‘Wednesday’); INSERT weeks VALUES (‘Thursday’).

Describe Sqlyog

SQLyog program is the top-most GUI tool for admin. Also, Sqlyog is the widespread MySQL manager. MySQL administrator, PHPMyAdmin and MySQL GUI tools and others MySQL front ends are combined by Sqlyog.

What is the command to verify whether Mysql is running or not?

The command “service mysqld status” in RedHat and “service MySQL status” helps in verifying whether Mysql is running or not.

What is the command to stop/start the service in Mysql?

The command “service mysqld start” is to start MySql service and “service mysqld stop” to stop the service.

Mention the finest installation procedure for MySQL

RPM Installation, Binary Installation, and Source Code compilation are the best installation methods in Mysql.

What is the best RAID level suitable for Mysql?

RAID 10 is the best RAID level suitable for Mysql

Give the different types of logs available in Mysql DBA

Below are the different types of logs:

Error Log

General Log

Slow Query Log

Binary Log

What is the command to check the Mysql server uptime?

SHOW GLOBAL STATUS LIKE ‘UPTIME’ is the command to check Mysql server uptime

Provide the drawbacks of using big Query cache size?

Query cache pushes an extra load on the database. It forces the DB to perform the task on nullifying the queries from the cache.

Give the optimum size of InnoDB buffer cache

Optimum size of InnoDB buffer cache must be 70-80% of the existing memory.

How do you take incremental backup in Mysql DBA?

To take increment backup in Mysql DBA, use Using percona xtrabackup.

What is the process to perform if you find the data disk full?

In-case of data disk is full, create a soft link and try to move the .idb and .frm files to the linked place.

How many types of backup are available in Mysql DBA?

There are three main types of backup in Mysql DBA which are Cold backups, Logical backups, and Hot backups.

Explain Cold backup

Cold backups is used to shut down the database server and taking a backup of all the data files by creating a copy of those files to another directory. The whole data dir including log files, binlogs, and /etc/my.cnf config file is also backed up.

Explain Logical Backup

Logical backups used a tool called mysqldump tool. This tool locks the tables while it runs to uphold the reliability of modifying data and can result in downtime. The subsequent dump file contains CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements for database build.

Explain Hot Backup

Hot backups permits to back up the physical database data files when the server is up and running. This kind of backup process can be achieved using xtrabackup tool which is available from Percona.

What is the primary key?

Primary Key is to exclusively recognize each row of the table and there can be only one primary Key in a table.

For creating an index, how many columns can be used?

Maximum of 16 columns can be used for creating index.

Describe Database White Box Testing

The Database Whitebox Testing handles data model, schema, and referential integrity rules. In addition, it also handles logical view with database uniformity, triggers, and ACID properties.

Describe Database Black Box Testing

Database Black Box Testing handles data storing & retrieving and data mapping. Also, this kind of testing is also used for techniques such as Boundary Value Analysis and Equivalence Partitioning.

Define CTE

CTE (Common Table Expression) is the one consisting of a momentary set of outcomes demarcated in a SQL statement.

If you want to display the maximum salary, what is the function to be used?

You can use the function MAX() to display the maximum salary.

Explain CSV tables

CSV is an abbreviation of Comma-Separated Values. CSV table is the one to store data in tabular format and plain text. CSV naturally holds only one record per line.

Describe Mysql_connect

Using Mysql_connect, user can open and close the database connection depending on the appeal.

Describe Mysql_pconnect

Mysql_pconnect is used to open a determined connection in a database and not possible to close such a connection.

Define ACID

ACID is the acronym of Atomicity, Consistency, Isolation, and Durability. ACID Property is the top-most vital segment of the database.

What are the different types of MySQL functions?

Strings functions

Numeric functions

Date functions

Aggregate functions

Write a query to create, insert, update and delete?

Create Query: CREATE DATABASE DatabaseName;

Insert Query : INSERT INTO table_name (tablecolumn1, tablecolumn1,

tablecolumn1,…)VALUES (value1, value2, value3,…);

Update Query: UPDATE table_name SET tablecolumn1=value, tablecolumn2=value2,…

WHERE this_column=this_value;

Delete Query: DELETE FROM table_name WHERE this_column = this_value;

What is the syntax to connect the mysql?

$connection = new mysqli($localhost, $username, $password);

What is the syntax for concatenating tables in MySQL?

concat(‘value1’, ‘ ’ , ‘value2’);

What is the difference between mysql_fetch_array and mysql_fetch_object?

Mysql_fetch_object is the function which helps to retrieve the values as an object from

the database, whereas Mysql_fetch_array will return the values as an array.

How Do You Get The Number Of Rows Affected By Query?

It can be got by using the Count();

Name any five combination queries that we use in MySQL?

Orderby

Limit

Between

Group by

What is the query used to combine two tables and retrieve the value?

Join query is used to combine two tables

What are the ways in which you can retrieve data in the result set of MySQL using PHP?

We can retrieve data in four ways. They are as follows.

mysql_fetch_array

mysql_fetch_assoc

mysql_fetch_object

mysql_fetch_row

What is the MySQL default port number?

3306

What is the query to display the top 10 rows?

It can be displayed by using a limit function in the select query.

Write a query to count the number of rows of a table in MySQL

Select COUNT(*) FROM tableName

What is the use of the Primary key? Where we use it.?

The primary key is used to uniquely identify the table records. Mostly we use a primary key

for IDs.

What are the different types of joins?

There are four types of joins,

Inner join

Outer join

Left join

Right join

Explain the differences between delete, drop and truncate?

Delete: Delete the row value where conditions meet.

Drop: remove the table or database completely.

Truncate: Remove all the rows in the table in time.

How to sort the value while retrieving records from the table?

The value can be sorted by using the OrderBy keyword in the select query.

What is the use of Now()?

It is used to return the current date and time.

Which MySQL function is used to concatenate string?

concat(‘value1’, ‘ ’ , ‘value2’);

What is the query used to add and remove any column of a table?

ALTER query is used to add and remove the column in the table.

List the Sailent features of MySQL?

It is easy to use and reliable

It is an open-source

It mainly supports all programming languages

What would be the default port number of MySQL

Default port number is 3306

What do you understand by the term myisamchk?

It is a database utility tool which is used to get some information about MyISAM database tables

Give the main difference between VARCHAR and CHAR data types?

Both are used to store string data under the field of a table

Is it possible to add or remove any column from a table?

Yes. It is possible by using the add column or alter column we can do it.

Define Index?

The index is defined as a data structure of a MySQL table and can speed up with queries.

Explain about the view in MySQL?

It works as a virtual table used to store query and returns a result.

What Are The Differences Between Mysql_fetch_array(), Mysql_fetch_object(), Mysql_fetch_row()?

Mysql_fetch_object is used to retrieve the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names. For example: Using mysql_fetch_object field can be accessed as $result->name. Using mysql_fetch_array field can be accessed as $result->. Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function. Example: 1.$result = mysql_query("SELECT * from students"); 2.while($row = mysql_fetch_row($result)) 3.{ 4.Some statement; 5.}

How Do You Backup A Database In Mysql?

It is easy to backing up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.

What Is Sqlyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

Write a program using the SELECT statement, While Loop.

You can write a program to select the students details from the student table and use the loop to just print the name of students.

example_query = mysql_query(“SELECT * FROM ‘students’ WHERE ‘student_id’ = ‘1’;”);while(output = mysql_fetch_array(example_query)){echo output[‘Students_Name’];}

How can you take the backup and restore a MySQL database using PHP?

MySQL comes with a utility mysqldump to provide the database backup and restore. The command you can use for backup and restore are as follows respectively.

//To take the backup of databasemysqldump database > backup -file.sql;//To restore the databasemysqldump database < backup -file.sql;

You can also use the phpMyAdmin user interface to backup your database. If you wish to backup, the database you just have to click on the “export” link on the phpMyAdmin main page.

Can you tell the difference between ereg_replace() and eregi_replace()?

ereg_replace and eregi_repalce() are regular expressions used to replace the matching characters. The only difference between these functions are eregi_replace() function ignores the case distinction when it matches alphabetic characters.

How to copy data from one server to another using PHP?

You can use the following three options:

Option 1: You can use the PHP Copy to move files from server to server. Refer to the syntax below:

/* Copy the file from source url to server */$copy = copy( $remote_file_url, $local_file );

Option 2: You can use the PHP FTP to move files from server to server. Refer to the syntax below.

/* Download $remote_file and save to $local_file */ftp_get($connect_it,$local_file,$remote_file,FTP_BINARY)

Option 3: You can use the ZIP and UNZIP Files option in PHP.

Can you tell few best practices to be followed for optimization in SQL?

The best practices to be followed for SQL optimizations depend on the individual to individual, but the following list consists of the most popular practices that are advised to follow. Refer below.

Try avoiding prefixing your stored procedure names with “sp_”.

It is recommended to use the column list in INSERT statements.

Preferably use the ANSI-Standard JOIN Clauses rather than the Old style clauses.

While using SELECT statement, avoid using * queries.

Do not use double quotes in T-SQL code.

Try avoiding to use column numbers in the ORDER BY clause.

Try using table aliases if your SQL statement involves more than a single source.

Can you tell what are various ways to create an index?

The various options to create an index are as follows:

You can create an index using the T-SQL statements.

You can use the SQL Server Management Studio. In this, you can browse to the table you need to create an index and then right click on the Indexes node. Over here you have to choose the New Index option.

You can indirectly identify the index by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statement.

What is the difference between a Heap table and Temporary table?

Heap Table Temporary Table

Heap Table exists in the memory A temporary table is valid only during the session.

Heap Tables are shared among a various number of clients. Temporary tables are not shared among the clients.

Temporary tables need a special privilege to create tables. Heap Tables are storage engines which do not need special privileges.

Why do you think it is advised to not to use GUID and CHARACTER columns as Clustered Index arrays?

GUID columns affect the clustered index sorting performance as the nature of the random GUID value generated is larger than the integer data types.

CHARACTER columns affect the sorting performance of the character data types, larger-size values, non-increasing values, and non-static values which often tend to change. These values cannot be compared as binary values, as the characters comparison mechanism depends on the used collection.

How can you handle the –secure-file-priv in MySQL?

–secure-file-priv option limits the MySQL Server from loading the directories using the LOAD DATA INFILE.

If you wish to see the directory that has been configured then you may use the SHOW VARIABLES LIKE “secure_file_priv”;

You have mainly two options to tackle:

Either move your file to the directory specified by the secure-file-priv.

Or you can disable secure-file-priv. You cannot disable this later on, and you have to remove it from the start itself.

What is the difference between B-Tree and Hash Indexes?

B-Tree Hash Indexes

A B-Tree index can be used for column comparisons like =, >, <, >=, <= or BETWEEN operators. A Hash-Index can be only used for equality comparisons that use =, >=, <=.

B-Tree can be used to search the next entry in the order. Hash Index cannot be used to search for the next entry in the order.

Any leftmost prefix of the key can be used to find the rows. Only whole keys are used to find a row.

Where is the MyISAM table stored?

Each and every MyISAM Table is stored on disk in the following three files:

.frm file – Stores the table definition.

.MYD file – A data file has an MYData extension.

.MYI index file – The index file has an MYIndex extension.

State the differences between MongoDB and MySQL.

MongoDB MYSQL

An open source database that stores JSON like documents which vary in structure. An open source relational database management system which stores relational data.

Each and every individual record are stored as documents. Each and every individual record are stored as rows in a table.

Documents from a particular class or a group are stored in a collection. A similar type of records are stored in a table.

Identify what is wrong in the below query.

SELECT EmployeeID, AVG(Salary)FROM EmployeeDetails WHERE AVG(Salary) > 75GROUP BY EmployeeID

What is Normalization and list the different types of normalization?

Normalization is the process of organizing data to avoid duplication and redundancy. There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.

First Normal Form (1NF) – No repeating groups within rows

Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.

Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

Consider you have a composite index of three columns. Now, you have to provide the value of two columns in the WHERE clause of a SELECT query. Do you think Index can be used for the operation?

Usage of index completely depends on if you consider the primary index or not. Consider you have a student table. Now, suppose if an Index is present on StudentID, StudentFirstName, and StudentLastName then you can consider a query as follows:

SELECT * FROM StudentDetails WHERE StudentID=3 and StudentFirstName=’Jatin’

Suppose you have to collect the first name, middle name and the last name of students from the below table. But, you observe that there few missing values either in the first name, middle name and the last name columns. How will you return the first non-null values?

StudentID FirstName MiddleName LastName

1 Rohit Kumar NULL

2 Sakshi Chowdhary NULL

3 NULL Yash Singhania

4 Akash NULL Kumar

5 Avinash NULL Daksh

You can use the COALESCE function to return the first non-null value from the table. Consider the below query.

SELECT StudentID, COALESCE(FirstName, MiddleName, LastName) as Name FROM StudentDetails;

Consider a scenario where you have two to three tables with thousand tuples in each of them. Now, if you have to perform a JOIN operation between them will you choose to perform filtering of rows or transforming of rows first.

The answer to this question is quite logical. If you have three tables with thousands of tuples in each of them, then you are first supposed to filter the rows in those tables and then transform the table. This would be beneficiary as if you transform the table, then the number of columns may increase reducing the performance. Due to such performance issues, a lot of memory will be used and the output will appear on your screen after quite a long wait of time.

How can you validate emails using a single query?

To validate emails you can use the regular expressions function (REGEXP_LIKE). Consider the below query.

SELECTEmailFROMEmployeewhere NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’);

Consider a scenario where you have to send an email to a client from the SQL database. How do you think you can achieve this task?

To send an email from the database, you can use the stored procedures. Follow the below procedure to send the emails:

Configure your SQL Server Mail account.

Enable the database mail.

Write a script to send an email. Refer below for the script.

USE [YourDB]EXEC msdb.dbo.sp_send_dbmail@recipients = ‘abc@example.com; def@example.com;xyz@example.com’@body = ‘ Sample Body Text’,@subject = ‘Example Email’ ;

Consider you have the following three tables which have to be linked together.

Department(Ssn, EmployeeName, EmployeeAge..)EmployeeContactDetails(Ssn, DepartmentID,desc,Ord)EmployeeAddress(Ssn,DepartmentID, desc, Ord)

The problem statement is to select all the departments from the Department table, with the “desc” field from the EmployeeContactDetails and EmployeeAddress where Ord=1. Now, you have to solve this problem statement with a single query.

To solve this problem statement you can use the JOINS concept. You simply have to perform a JOIN on the Department.Ssn and the DepartmentID in the other tables.

Now, if you are sure that the Ssn exists in all the three considered tables, then you can use the INNER JOIN. Also, if you are not sure that you have matching rows, then you can use the LEFT JOIN. Consider the below query.

SELECT d.Ssn,d.EmployeeName,c.desc ContactDetailsDesc,a.desc AddressDetailsDescfrom Department dinner join EmployeeContactDetails con d.id = c.DepartmentIDinner join address aon d.id = a.DepartmentIDwhere d.EmployeeName = ‘abc’and c.ord = 1and a.ord = 1

If you are assigned a task, to find the information of PROCEDURES. What are the basic commands that you will use to do so?

To check the procedures, you can consider the following query.

SELECT * FROM SampleSourceWHERE Type=’PROCEDURE’AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

To find the procedures columns information, you can consider the following query.

SELECT OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;

Can you tell which of the following WHERE clauses is faster?

WHERE col * 4 < 16

WHERE col < 16 / 4

If we compare both the statements, then the second WHERE clause would be comparatively faster than the first one. That is because, for the first statement, MYSQL would retrieve the value of ‘col’ for each and every row, multiplied by four. After that, it would compare the result to 16. Also, in the first case no Index can be used, and hence it makes it further slow.

What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

Example of IN:

SELECT * FROM students where ROLL_NO IN (8,15,25);

What are the different types of Collation Sensitivity?

Following are the different types of collation sensitivity:

Case Sensitivity

Kana Sensitivity

Width Sensitivity

Accent Sensitivity

Course Curriculum

Enroll in Mysql DBA Training & Build Your Skills to Next Level

Instructor-led Sessions Real-life Case StudiesAssignments

In Which Language Mysql Is Written?

MySQL is written in C and C++ and its SQL parser is written in yacc.

What Are The Technical Specification Of Mysql?

MySQL has the following technical specifications –

Flexible structure

High performance

Manageable and easy to use

Replication and high availability

Security and storage management

What Is The Difference Between Mysql And SQL?

SQL is known as a standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

What Is The Difference Between Database And Table?

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

Tables are a way to represent the division of data in a database while, database is a collection of tables and data.

Tables are used to 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 the reverse is not true.

Why Do We Use Mysql Database Server?

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

What Is The Difference Between Char And Varchar?

A list of differences between CHAR and VARCHAR:

CHAR and VARCHAR types are different in storage and retrieval.

CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.

When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

What Is The Difference Between Truncate And Delete In Mysql?

How Many Triggers Are Possible In Mysql?

There are only six Triggers allowed to use in MySQL database.

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

What Is Heap Table?

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.

What Does ” I_am_a_dummy Flag” Do In Mysql?

The ” i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

How To Get The Current Date In Mysql?

To get current date, use the following syntax:

SELECT CURRENT_DATE();

What Are The Security Alerts While Using Mysql?

Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change root username and password

Restrict or disable remote access.

How To Change A Password For An Existing User Via Mysqladmin?

Mysqladmin -u root -p password “newpassword”.

What Is The Difference Between Unix Timestamps And Mysql Timestamps?

Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers!

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.

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

How Do You Determine The Location Of Mysql Data Directory?

The default location of MySQL data directory in windows is C:mysqldata or C:Program FilesMySQLMySQL Server 5.0 data.

What Is Mysql Data Directory?

What Is The Use Of Mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

How Is Myisam Table Stored?

MyISAM table is stored on disk in three formats.

‘.frm’ file : storing the table definition

‘.MYD’ (MYData): data file

‘.MYI’ (MYIndex): index file

What Is The Usage Of Enums In Mysql?

ENUMs are used to limit the possible values that go in the table:

For example: CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).

What Are The Advantages Of Myisam Over Innodb?

What Are The Differences Between Mysql_fetch_array(), Mysql_fetch_object(), Mysql_fetch_row()?

Mysql_fetch_object is used to retrieve the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

$result = mysql_query(“SELECT * from students”);

while($row = mysql_fetch_row($result))

{

Some statement;

}

How Do You Backup A Database In Mysql?

What Is Sqlyog?

Define REGEXP?

REGEXP is a pattern match in which matches pattern anywhere in the search value.

Give string types available for column?

The string types are:

SET

BLOB

ENUM

CHAR

TEXT

VARCHAR

What storage engines are used in MySQL?

Storage engines are called table types and data is stored in files using various techniques.

Technique involves:

Storage mechanism

Locking levels

Indexing

Capabilities and functions.

What are the drivers in MySQL?

Following are the drivers available in MySQL:

PHP Driver

JDBC Driver

ODBC Driver

C WRAPPER

PYTHON Driver

PERL Driver

RUBY Driver

CAP11PHP Driver

Ado.net5.mxj

What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

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.

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.

How do you login to MySql using Unix shell?

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u -p

What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

How do you control the max size of a HEAP table?

Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption.