Blog

MySQL Interview Question

Image

MySQL is an open source relational database management system (RDBMS). It runs on both the web and the server. MySQL is fast, reliable, and easy to use.

This article provides a list of basic and advanced MySQL questions for both beginners and experienced professionals.

Let's see the list of top frequently asked MySQL Interview Question:

Q1: What Is The Difference Between MySQL And SQL?

SQL is known as the standard query language used to interact with databases like MySQL. MySQL is a database that securely stores various types of data.

Q2: How To Add Columns In MySQL?
ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

ALTER TABLE Students ADD Email varchar(255);
Q3: How To Delete A Table In MySQL?
DROP TABLE table_name;
Q4: Write A Query To Select All Students With Marks Either 10, 9, 5, Or 7 Games.
SELECT * FROM students WHERE marks IN (10, 9, 5, 7);
Q5: What Is The Difference Between PRIMARY KEY And UNIQUE KEY?

PRIMARY KEY

  • Used to serve as a unique identifier for each row in a table.
  • Cannot accept NULL values.
  • Only one primary key.

UNIQUE KEY

  • Uniquely determines a row which isn’t primary key.
  • Can accepts NULL values.
  • More than one unique key.
Q6: What Is JOIN In MYSQL?

The SQL JOIN command is used to join the data in two SQL tables. The JOIN clause is often used when a table has at least one data column in common.

There are different types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Q7: How Would You Write A Query To JOIN Two Tables?
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;
Q8: What Is The Usage Of ENUMs In MySQL?

An ENUM is a String object that is used to specify a predefined set of values and can be used while creating a table.

Create table size(name ENUM('Small', 'Medium','Large'));
Q9: What Is DISTINCT Statement?

The SELECT DISTINCT statement is used to return only distinct(different) values. Within a table, columns often contain many duplicate values, and you may want to list different(unique) values.

SELECT DISTINCT column1, column2 FROM table_name;
Q10: How Can We Get The Number Of Rows Affected By Query?
SELECT DISTINCT column1, column2 FROM table_name;
Q11: How Many TRIGGERS Are Allowed In MySql Table?
  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE and
  • AFTER DELETE
Q12: What Do You Mean By % And _ In The LIKE Statement?

% corresponds to zero or more characters, and _ is exactly one character in the LIKE statement.

Q13: How To Find The Second Highest Salary In MySQL?
SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT Max(salary) FROM employees);
Q14: How To Display The Nth Highest Salary From A Table In A MySQL Query?
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
Q15: What Is The Difference Between TRUNCATE And DELETE In MySQL?
  • TRUNCATE is a DDL command and DELETE is a DML command. /li>
  • You cannot use the WHERE command with TRUNCATE, but you can use it with the DELETE command.
  • TRUNCATE is not available in indexed views, but DELETE is available in indexed views.
  • The DELETE command is used to delete data from a table. It only removes rows of data from the table, but truncate is a very dangerous command and should be used with caution as it permanently removes each row from the table.
Q16: What Is An Index?

An index is a data structure created on one or more columns of a table. In most cases, the index is structured as a B-tree.

Q17: What Are Some Other Types Of Indexes?

Database indexes are defined based on their index attributes. The types of indexing methods are:

  • Primary Indexing
  • Secondary Indexing
  • Clustering Indexing

The primary Indexing is also further divided into two types.

  • Dense Index
  • Sparse Index
Q18: How To Update The Table In MySQL?
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

UPDATE Student SET Name = 'Jack' WHERE ID = 1;
Q19: How To Insert Data In MySQL?
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES  ( value1, value2,...valueN );
Q20: What Is The Difference Between The Heap Table And The Temporary Table?

Heap tables:

  • The heap table found in memory that is temporarily used for high-speed storage. BLOB or TEXT fields are not allowed.
  • The heap table does not support AUTO_INCREMENT.
  • The index cannot be null.

Temporary tables:
Temporary tables are used to hold temporary data. It may be beneficial to retain temporary data. The temporary table is dropped when the current client session ends.

Q21: 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();
Q22: Write A Query To Retrieve A Hundred Books Starting From 20th.
strong book_title FROM books LIMIT 20, 100;