Introduction
MySQL is the essential tool for developers to communicate with databases. It uses Structured Query Language (SQL) to perform all the operations like retrieve, manage, and manipulate data from the database. MySQL is commonly used with PHP and Apache Web servers to create dynamic websites. Mastering SQL can open up the door for exciting opportunities. In this article, I have compiled a list of 50 basic MySQL interview questions that will help you prepare for your upcoming interview.
Don't miss out on your chance to work with the best
Apply for top global job opportunities today!
Top 50 Basic MySQL Interview Questions
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS) used for a wide variety of applications, including data warehousing, e-commerce, and logging applications.
2. How do we create a new database in MySQL?
To create a database, we use the following query:
CREATE DATABASE DBname; 3. What is the query to create a new table in MySQL?
The following is the SQL query that we use to create a new table:
CREATE TABLE table_name (
column1 datatype1,
column2 datatype2,
...
); 4. What are the DROP and TRUCNATE commands used for?
DROP is used to remove a table from the database, once it removes the table it cannot be rolled back. But if we use the TRUNCATE command, all the rows will get removed from the table.
Must Read: JSON vs SQL Overview
5. What are the steps to install MySQL in Windows?
The following are the steps to install the MySQL in Windows:
- First, visit the MySQL official page and download the MySQL Community Edition installer for Windows
- Once done, run the installer and choose the “Setup Type” according to your requirements follow the installation wizard, and configure the MySQL server as per your preferences.
- When the installation process is completed, set your MySQL root password along with the user’s credentials.
- After successfully setting the root, MySQL will start installing automatically.
6. What is the difference between a clustered index and a non- clustered index?
| Clustered Index | Non-Clustered Index |
|---|---|
| It is used for easy retrieval of data from the database. | It does not alter but creates a separate object within a table that again points back to the original rows. |
| Comparatively faster than the non-clustered index. | relatively slower |
| One table can only have one clustered index. | One table can have many non-clustered indexes. |
7. What is the role of a stored procedure in MySQL?
A stored procedure is useful when we need to perform a task repeatedly and complex task. It allows us to store and reuse the code, whenever necessary we can call it in the code.
8. What are the first three normal forms of normalization?
Each normal form depends on the previous one. The following are the first three normal forms:
- First Normal Form (1NF): It does not repeat groups within rows
- Second Normal Form (2NF): Every supporting column value is dependent on the primary key.
- Third Normal Form (3NF): It depends only on the primary key.
9. Which SQL command do we use to insert values into a table?
The following is the SQL command:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...); 10. What is the difference between SQL and MySQL?
SQL is the Structured Query Language that uses sets of commands or queries to create, manage, and manipulate data in the relational database. MySQL is the Relational Management System developed by Oracle Corporation for storing databases.
11. Write the SQL query to retrieve all the column data from a table.
To retrieve all the data from a column we use, the following MySQL query
SELECT * FROM tablename; 12. What is the role of Trigger in SQL?
Trigger in SQL is a special type of stored procedure that allows you to execute a batch of code using any query executed against a specific table.
13. What are the three operators available in SQL?
The following are the three types of operators:
- Arithmetic Operators
- Logical Operators
- Comparison Operators
14. What is the primary key in MySQL?
A primary key is a unique identifier that contains unique values. In MySQL table, there is always one primary key to identify each row in the table.
15. What is the difference between relationships and entities in MySQL?
Entities refer to any real-world example such as a place, the name of a person, or a thing that can be stored in a database. For instance, a database table that stores details of an employee in a row and column represents one kind of entity. Whereas relationships are the way to link these entities with one another in the database. For example, an employee’s name can be linked with the department and contact details.
Also Read: What is an SQL Database?
16. What is the SQL query to retrieve only the specific columns from a table?
To retrieve the specific columns from a table, we need to specify the column number and then give a table name, the following the SQL query:
SELECT column1, column2
FROM table_name; 17. Why do we use JOIN in SQL?
A JOIN clause is used to combine two or more two tables and then merge them and retrieve data from there. There are four types of joins in SQL those are:
- Inner Join
- Right Join
- Left Join
- Full Join
18. Why do we use NULL values in the table?
A NULL value is used to represent an unknown value, assigned or not applicable, and unavailable.
19. When are HEAP tables used?
HEAP tables are used for high-speed temporary storage. When the MySQL server shuts down, all data stored in the HEAP tables gets lost.
20. What is the query to fetch data from a table with a salary over 50k?
The following is the MySQL query to use to get a salary greater than 50K:
SELECT * FROM table_name WHERE salary > 50k; 21. What is the difference between a table and a field in MySQL?
Tables contain structured data in an organized way in a row and columns. A table can be a Student table which stores the data of all students in rows and columns, or it could be an Employee table that consists of all the employee details. For example, Table: StudentDetails or EmployeesDetails. Whereas the field represents a single attribute associated with the table. It could be a date, name, or id. For example, Field for StudenDetails must have these fields Std ID, Std Name, and Std Course.
22. Why do we use foreign keys?
Foreign key helps to maintain referential integrity by enforcing a link between the data in two tables. It also prevents the destruction of links between the child and parent tables.
23. Why do we need to use SQL functions?
The following are the reasons that SQL functions are used:
- To modify any data items in the table
- To manipulate the output
- To format dates and numbers
- To convert the data types
- To perform calculations on the data
24. Differentiate Between TRUNCATE and DELETE Commands.
| DELETE | TRUNCATE |
|---|---|
| It deletes the row from the table using the WHERE Clause based on the specified condition. | It removes all rows at once without any specifying conditions. |
| It is comparatively slower than TRUNCATE. | It is considered faster than DELETE. |
| It allows using rollback. | There is no option for rollbacks. |
| It falls under DML command. | It falls under the DDL command. |
25. What does ACID mean in RDBMS?
ACID stands for Atomicity, Consistency, Isolation, and Durability, where Atomicity refers to transactions that are completely done or failed. It means if one part of any transaction fails, the entire transaction fails, and the database is as it is. Consistency makes sure that all the data must meet the validation rules. Isolation controls the concurrency. Lastly, Durability guarantees the success of a transaction even if there is power loss, crash, or any sort of system error. Overall, this all ensures reliability whenever transactions are processed in a database system.
26. What are cross-join and natural join?
The cross join which is also known as the Cartesian product is two tables whereas the natural join is used for all the columns that have the same name and data types in both tables.
27. How do we join two student tables with a common column ID?
Let’s assume two students are enrolled in an AI course and they share a common column i.e., course_id, the following is the query for joining two tables:
select * from students
inner join courses
on students.course_id=courses.course_id; 28. What is a subquery?
A subquery is used inside another query to retrieve data or information back from the database. It is of two types:
- Correlated subquery: This type of query depends on another table. It selects the data from a table referenced in the outer query.
- Non-correlated subquery: It is an independent query where the output of the subquery is substituted in the main query.
29. What is collation? And what are the different types of collation sensitivity?
Collation is a set of rules on how data is stored and compared. Character data uses a correct character sequence by specifying the case sensitivity, character width, etc. The following are the types of collation sensitivity:
- Case Sensitivity
- Kana Sensitivity
- Width Sensitivity
- Accent Sensitivity
30. Why do we need to use constraints?
Whenever we need to specify the limit on the data type constraints are used. We can specify it while altering the statement of the table or creating it.
31. In what case do we use the DISTINCT keyword?
We use the DISTINCT keyword while selecting the unique records in the table. The following is the SQL query that will display the unique student ID from the table Student:
Select DISTINCT studentID from Student 32. What is the key difference between the having clause and the where clause?
WHERE filters record before aggregating in GROUP BY, whereas HAVING filters after aggregation.
33. What is the difference between primary keys and unique keys?
| Primary Keys | Unique Keys |
|---|---|
| It is used to create a relationship with another table through foreign keys. | It identifies a single row uniquely and columns are unique across all rows. |
| One table can have only one primary key. | Whereas a table can have multiple unique keys. |
| Primary ensures that no other rows have the same primary key | It allows you to store multiple values in one table. |
34. Explain Inner Join, Left Join, Right Join, and Full Join.
- Inner Join: It returns all the rows from multiple tables when its condition is satisfied.
- Left Join: As the name suggests, it returns all the rows from the left table and selects the matching rows from the right table when the join condition is met.
- Right Join: This is just opposite to Left Join which returns all the rows from the right table and similarly selects the matching rows from the left table.
- Full Join: It returns all the rows from the left-hand side table and all the rows from the right-hand side table.
35. What is the difference between the UNION ALL and the UNION operator?
Both operators are used to combine the result set of two or more SELECT statements. But the difference between them is that the UNION operator removes duplicate rows between the various SELECT statements and UNION ALL does not remove duplicate rows.
37. What is the LIKE operator used for?
LIKE operator is used to specify the pattern in a column.
select * from students where studentname like ‘a%’
By using this (%) sign, it can match more characters or zero. And using (_) sign, it can match only one character.
select * from student where studentname like ‘abc_’
38. What is the difference between schema and cursor in MySQL?
A schema is a collection of database objects, tables, views, indexes, procedures, functions, and triggers. A cursor is an object that retrieves data from a result one at a time.
39. Write the SQL command to filter the names that begin with A in the Student table.
The following is the command to get all the student’s names with an A
SELECT * FROM Table_name WHERE StdName like 'A%'
40. What is the purpose of using relationships in MySQL?
Relationships are used to make links between entities or to connect between the tables in a database. The following are the types of relationships:
- One-to-One Relationship.
- One-to-Many Relationship.
- Many-to-One Relationship.
- Self-Referencing Relationship
41. What does MySQL connector maven mean?
MySQL connector maven also known as MySQL Connector/J is a JDBC driver that allows Java applications to interact with a MySQL database. It provides methods and classes for executing SQL statements, querying data, and managing database connections.
42. Explain the HAVING clause and WHERE clause.
HAVING clause is used with a SELECT statement and GROUP BY clause
It operates like a WHERE clause if it is not used by GROUP BY. However, WHERE Clause can be used with each row before they are a part of the GROUP BY function in a query.
43. Why do we use Char and Varchar2 in SQL?
We use Char for fixed-length strings let say we have assigned char(20) this char can string up to 20 characters but not more than that. Whereas, Varchar2 can store variable length of character strings. For example, varchar2(10) can store variables between 6,8,2.
44. What is a View? And What is it used for?
A view is a virtual table that contains rows and columns, just like a real table, due to which takes less storage space. It is used for the following reasons:
- To restrict access to data.
- Break down the complex queries into simple ones.
- To make sure that data is independent.
- To provide different views for the same data.
45. What is the SQL query to fetch common records from two tables?
For this, we need to use INTERSECT, the following is the query:
Select employeeID from employee INTERSECT Select EmployeeID from Dept
46. What is the key difference BETWEEN the operator and the IN-condition operator?
- BETWEEN Operator: Displays rows based on a range of values SELECT * FROM Students where ROLL_NO BETWEEN AND 60;
- IN Condition Operator: Checks values in a specific set of values.
SELECT * FROM students where ROLL_NO IN (5,10, 17);
47. What are some of the commonly used Group functions?
The most common group functions are AVG, COUNT, MAX, MIN, SUM, and VARIANCE.
48. Define Data Integrity.
Data Integrity refers to maintaining the accuracy and consistency of a database. So that data won’t get lost or corrupted in the future.
49. What is the SQL query to display the current date?
SQL query to display the current data is the following:
SELECT CURRENT_DATE() + 1;
Must Read: What is PostgresSQL?
50. What is Normalization? List some of the advantages of it.
In RDBMS, normalization is the process of organizing the data in such a way that helps to prevent redundancy. The following are the advantages of normalization:
- It promotes better organization of the database.
- More than one table can be stored in smaller rows.
- It doesn’t allow duplicate data to occur.
- It allows efficient data access with high flexibility for queries.
- It is a fast way to find any information and modification is on the fly.
- To implement security is pretty straightforward.
Conclusion
SQL skills are important for database management or data analytics roles. Having a solid grasp of these basic skills will be an asset and will also help you develop critical problem-solving skills. Remember to confidently answer these MySQL interview questions to crack the first round of interviews. I hope this interview question is helpful.
Take control of your career and land your dream job
Sign up with us now and start applying for the best opportunities!
FAQs
To become a MySQL developer, you need to have a degree in computer science. If you’re from a different background and want to switch to a developer role, then you can get a professional certification in MySQL from a well-known institute. It can help you to provide hands-on experience training and ace MySQL interviews.
The role of a MySQL developer is to monitor, test, and maintain the database.
Practice coding skills as much as you can and try to prepare all the possible MySQL interview questions and answers. Research the organization you’re sitting for the interview.
Yes, it does. A basic understanding of any database helps to relate to the code and allows you to grasp the concept quickly. It takes some time to be familiar with the syntax, command, and MySQL architecture. But it does take a year to master it within 3-6 months will do.
