MySQL Interview Questions – Set 11

by

How can we run batch mode in MySQL?

To perform batch mode in MySQL, we use the following command:

mysql;
mysql mysql.out;

In which language MySQL has been written?

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

How to install MySQL?

Installing MySQL on our system allows us to safely create, drop, and test web applications without affecting our live website’s data. There are many ways to use MySQL on our system, but the best way is to install it manually. The manual installation allows us to learn more about the system and provides more control over the database. To see the installation steps of MySQL in Windows goes to the below link:

https://www.javatpoint.com/how-to-install-mysql

Manual installation of MySQL has several benefits:

  • Backing up, reinstalling, or moving databases from one location to another can be achieved in a second.
  • It provides more control to how and when MySQL server starts and closes.
  • We can install MySQL anywhere, like in a portable USB drive.

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

Write a query to retrieve a hundred books starting from 20th.

SELECT book_title FROM books LIMIT 20, 100;

How to change the database name in MySQL?

Sometimes we need to change or rename the database name because of its non-meaningful name. To rename the database name, we need first to create a new database into the MySQL server. Next, MySQL provides the mysqldump shell command to create a dumped copy of the selected database and then import all the data into the newly created database. The following is the syntax of using mysqldump command:

mysqldump -u username -p “password” -R oldDbName > oldDbName.sql
Now, use the below command to import the data into the newly created database:

mysql -u username -p”password” newDbName < oldDbName.sql

How to execute a stored procedure in MySQL?

We can execute a stored procedure in MySQL by simply CALL query. This query takes the name of the stored procedure and any parameters we need to pass to it. The following is the basic syntax to execute a stored procedure:

CALL stored_procedure_name (argument_list);
Let’s understand it with this example:

CALL Product_Pricing (@pricelow, @pricehigh);
Here, a stored procedure named Product_Pricing calculates and returns the lowest and highest product prices.

How to display the nth highest salary from a table in a MySQL query?

Let us take a table named the employee.

To find Nth highest salary is:

select distinct(salary)from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:

select distinct(salary)from employee order by salary desc limit 2,1

How to join two tables in MySQL?

We can connect two or more tables in MySQL using the JOIN clause. MySQL allows various types of JOIN clauses. These clauses connect multiple tables and return only those records that match the same value and property in all tables. The following are the four easy ways to join two or more tables in MySQL:

  • Inner Join
  • Left Join
  • Right Join
  • Cross Join

Write a query to display the current date and time?

If you want to display the current date and time, use –

SELECT NOW();

If you want to display the current date only, use:

SELECT CURRENT_DATE();