MySQL Theoretical Interview Questions

Below is a list of the most often asked MySQL interview questions and answers.

1) Which programming language was MySQL written in?

Ans: MySQL is written in C and C++ and  yacc-based SQL parser is written in yacc.

2) What are MySQL’s technical specifications?

Ans: MySQL’s technical specifications are as follows:

• Structure that is adaptable
• Exceptional performance
• Simple to use and manage
• High availability and replication
• Management of security and storage
• Drivers
• Graphical Instruments
• MySQL Enterprise Monitor is a tool that allows you to keep track
• Security for MySQL Enterprise
• Support for JSON
• High-availability and replication
• Ease of Use and Manageability
• Transactions and OLTP
• Geo-Spatial Assistance

3) What is the difference between MySQL and SQL?

Ans: The standard query language is known as SQL. It’s used to communicate with databases like MySQL. MySQL is a database that stores and protects numerous sorts of data.

The values in the database must be stored and retrieved using a PHP script.

MySQL is a software or application, whereas SQL is a computer language.

SQL is used to create database management systems, whereas MySQL is used to handle data, store it, delete it, and alter it.

4) How do you tell the difference between a database and a table?

Ans: Tables are a manner of representing the division of data in a database, which is made up of tables and data.

Tables are used to organise and generate a dataset by grouping data in relation to one another. The database will make use of this dataset. The data in the table is a part of the database in any form, but the opposite is not true.

A table is a collection of rows and columns used to store data, whereas a database is a collection of organised data and features used to retrieve it.

5) What exactly is the distinction between a database and a table?

Ans: There is a significant distinction between a database and a table. The following are the distinctions:

Tables are a manner of representing the division of data in a database, which is made up of tables and data.

Tables are used to organise and generate a dataset by grouping data in relation to one another. The database will make use of this dataset. The data in the table is a part of the database in any form, but the opposite is not true.

A table is a collection of rows and columns used to store data, whereas a database is a collection of organised data and features used to retrieve it.

6) What is the purpose of using the MySQL database server?

Ans: To begin with, developers and small businesses can utilise the MYSQL server for free.

• MySQL is a database server that is free and open-source.
• MySQL’s community is enormous and supportive; as a result, any MySQL-related issues are fixed as quickly as possible.
• Because MySQL has been on the market for a long time, it has relatively stable versions. All flaws found in earlier releases have been consistently fixed, and each upgrade provides a very stable version.

7) How do you set up MySQL?

Ans: MySQL is one of the most extensively used relational database management systems in the industry today. It has multi-user access and a number of storage engines to choose from. Oracle Corporation is its supporter.

Download the community server edition software from MySQL’s official website.

You’ll notice a button to select an operating system, such as Windows.

And then follow the steps mentioned on the website.

8) How to check out the MySQL version with command line?

Ans: mysql -v

Download the community server edition software from MySQL’s official website.

9) What is the procedure for changing the MySQL password?

Ans: Using the below statement in a new notepad file, we may change the MySQL root password and save it with a suitable name

Step 1: ALTER USER ‘root ‘@’localhost’ IDENTIFIED BY ‘NewPassword’;

Step 2: Then go to below location and save the file there:

CD C:\Program Files\MySQL\MySQL Server 8.0\bin

Step 3: To update the password, type the following statement:

mysqld –init-file=C:\\mysql-notepadfile.txt

Step 4: Finally, we can use this new password to log onto the MySQL server as root. To ensure the password change, delete the

C:myswl-init.txt file after starting the MySQL server.

10) How to alter the name of a table in MySQL?

Ans: Our table names aren’t always meaningful. In that scenario, the table name must be changed or renamed. To rename one or more tables in the current database, MySQL provides the following syntax:

mysql> RENAME old_table TO new_table;

SQL Scenario-based Interview Questions

1) How to find the second highest-paying-employee-salary through sql query?

Ans: Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

2) Query to find the table’s First Record?

Ans: Select * from Employee where Rownum =1;

3)How to find and delete duplicate records?

Ans: Select a.* from Employee a where rowid !=
(select max(rowid) from Employee b where  a.Employee_num =b.Employee_num;

4) How to find all details about the Constraints which were created earlier?

Ans: SELECT * From User_Constraints;

SELECT * FROM User_Cons_Columns

5) The query to find out the manager’s name of employee?

Ans: Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

6) Find out how many managers are there in the company?

Ans: Select count(*) from emp where job=”MANAGER;

7) List the emps those who joined in company before 15^th of the month?

Ans: select * from emp where to_char(hiredate,’DD’) < ‘15’;

8) List the emps whose names contains ‘A’

Ans: select * from emp where ename like ‘%A%’;

9) List the no. of emps in each department where the no is more than 3

Ans: Select deptno,count(*) from emo group by deptno having count(*) < 3;

10) List the emps whose sal is equal to the average of max and minimum

Ans: Select * from emp where sal=(select (max(sal)+min(sal)/2 from emp);