A set of basic MySQL Interview Questions suitable for Fresher Interview and Mid Level Jobs. MySQL is an Important part of Backend Technologies and an Open Source DBMS. It is also the most popular DBMS. MySQL Provides, DDL, DML, Transactions and all the features that a DBMS should have.
Let’s start with the questions-
What is MySQL?
MySQL is a Relational Database Management System marketed and distributed by Oracle as an open source software. However, enterprise edition is also available with support systems.
What is Relational Database?
Yes, we can say a database as a Relational database, because tables in the database are related to each other.
What is the use of the DISTINCT phrase in MySQL?
DISTINCT eliminates the duplicate records in the selection and return unique values. We can use it also with aggregate functions like AVG, SUM, MAX, MIN etc.
What is SELF JOIN?
Join a table to itself is called SELF JOIN. We must use an alias of the table to join itself. There is no any phrase like SELF JOIN.
What is LEFT JOIN?
LEFT JOIN gets the column of the LEFT table if the relation exists with the next table.
What is CROSS JOIN?
CROSS JOIN is a Cartesian product of two tables and does not require any join condition. CROSS JOIN used to JOIN two table having no relations.If table A has 100 rows and Table B has 200 rows then we will get 100×200 = 20000 rows in CROSS JOIN.
What is INNER JOIN?
INNER JOIN gets all the records that are matched or common between the tables followed by the condition.
What is FULL JOIN?
FULL JOINS returns the records from both the tables. It is the result of LEFT JOIN and RIGHT JOIN at the same time.
What is RIGHT JOIN?
RIGHT JOIN results in the column of the RIGHT table if the relation exists with the next table.
What is OUTER JOIN?
LEFT JOIN, RIGHT JOIN and FULL JOIN are OUTER joins.
What are aggregate functions in MySQL?
The function that performs a calculation on records and returns a resulting single value is known n as an aggregate function. We have many aggregate functions in MySQL. As : AVG,SUM,MIN,MAX,COUNT.
What is the difference between DISTINCT and GROUP BY?
DISTINCT can return NULL values because it considers NULL as a value and selects one NULL from many, however, GROUP BY does not consider NULL values as a record. GROUP BY sorts the records where DISTINCT does not.
What is VIEW MySQL?
VIEW is an imaginary table holding the real records, based on query statements. It fetches data from real tables and displays always updated values.
We can do all the MySQL operations on it. It helps to achieve security and re-usability while implementing database operations.
We can hide sensitive data from end users while limiting the access of original tables.
For example, we can have a VIEW for all the employee who is older than 40 years.
CREATE VIEW older_than_fourty AS SELECT * FROM employee WHERE age > 40 ;