MySQL Interview Questions – Set 12

by

What is the difference between the heap table and the temporary table?

Heap tables:

Heap tables are found in memory that is used for high-speed storage temporarily. 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 transient data. Sometimes it is beneficial in cases to hold temporary data. The temporary table is deleted after the 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).

How to import a CSV file in MySQL?

MySQL allows us to import the CSV (comma separated values) file into a database or table. A CSV is a plain text file that contains the list of data and can be saved in a tabular format. MySQL provides the LOAD DATA INFILE statement to import a CSV file. This statement is used to read a text file and import it into a database table very quickly. The full syntax to import a CSV file is given below:

LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv’
INTO TABLE tablename
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘rn’
IGNORE 1 ROWS;

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;
}

What are federated tables?

Federated tables are tables that point to the tables located on other databases on some other server.

What does “i_am_a_dummy flag” do in MySQL?

The “i_am_a_dummy flag” enables the MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.

How to find the second highest salary in MySQL?

MySQL uses the LIMIT keyword, which can be used to limit the result set. It will allow us to get the first few rows, last few rows, or range of rows. It can also be used to find the second, third, or nth highest salary. It ensures that you have use order by clause to sort the result set first and then print the output that provides accurate results. The following query is used to get the second highest salary in MySQL:

SELECT salary
FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;
There are some other ways to find the second highest salary in MySQL, which are given below:

This statement uses subquery and IN clause to get the second highest salary:

SELECT MAX(salary)
FROM employees
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);
This query uses subquery and < operator to return the second highest salary:

SELECT MAX(salary) From employees
WHERE salary < ( SELECT Max(salary) FROM employees);

What are the technical specifications 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
  • Drivers
  • Graphical Tools
  • MySQL Enterprise Monitor
  • MySQL Enterprise Security
  • JSON Support
  • Replication & High-Availability
  • Manageability and Ease of Use
  • OLTP and Transactions
  • Geo-Spatial Support

What is the usage of the “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.

How to get the current date in MySQL?

To get current date, use the following syntax:

SELECT CURRENT_DATE();

What is the difference between primary key and candidate key?

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.