MySQL is the most widespread open-source relational database management system (RDBMS), frequently used with PHP. It is fast, trustworthy, and easy to handle on the web and server. MySQL is the world’s most famous open-source database software and the preferred choice for business-critical applications by giants like Yahoo, Suzuki, and NASA.
MySQL uses standard programming to create, modify, and extract data from a relational database. Data is stored in tables that include rows and columns. Users can interact straight with MySQL or use it to implement applications that need the capability of a relational database. MySQL roles range from MySQL Developer, Database Administrator, MySQL Database Engineer, and more.
Below is a list of 35 most common MySQL interview questions
1. What is SQL Server?
SQL Server is a database management system (DBMS) designed by Microsoft. DBMSs are computer software applications capable of interacting with users, various other applications, and databases. The purpose of SQL Server is to capture and analyze data and manage database definition, querying, creation, updating, and management.
2. What are the features of MySQL?
MySQL provides cross-platform support, various application programming interfaces, and many stored procedures such as triggers and cursors to help manage the database.
3. What are the advantages and disadvantages of using MySQL?
There are various advantages and disadvantages to using MySQL. Some of them are listed below:
Advantages
MySQL helps in secure database management. By using it, we can perform database transactions securely.
It is fast and efficient compared to other database management systems because it supports different storage modules.
Because transaction processing is high, MySQL can perform millions of queries.
In addition, some of the features that make MySQL unique are deadlock identification, multi-transaction execution, efficient processing, and easy administration.
Disadvantages
Scalability in MySQL is a redundant task.
MySQL is mainly used for large databases.
There are software instability issues.
4. What is the basic architecture of MySQL?
MySQL’s logical architecture consists of a “connection manager”, a “query optimizer,” and “pluggable engines”.
5. What is a traditional network library for a system?
Named pipes on Windows or POSIX systems provide interprocess communication methods to connect different processes on the same computer. The need to use a network stack is eliminated, and data can be sent without affecting performance. Servers set up named channels to listen for requests. The client function needs to know the specific channel name to send the request.
6. What is the full form of DDL in Oracle DB?
DDL is one of the types of SQL, which stands for Data Definition Language. Other types of SQL are DML, DCL, and TCL.
7. For which Oracle database objects are DDL statements used?
DDL contains statements like CREATE, ALTER, and ANALYZE that are used to CREATE TABLES and display stored routines and packages in the database schema.
8. What is the basic unit of storage in an Oracle database that holds data?
A table is the basic unit of physical data storage in an Oracle database.
9. Define the view in the DDL command in SQL query.
A view is a query that acts as a window for formatting data contained in one or more tables. Views do not include physical data, only the query created at runtime.
10. What storage modules are used in MySQL?
Storage engines are tabular types, and data is stored in files using various techniques.
The technique includes:
Storage mechanism
Locking levels
Indexing
Abilities and Functions.
11. What are the drivers in MySQL?
The following drivers are available in MySQL:
PHP driver
JDBC driver
ODBC driver
C PACKAGING
PYTHON driver
PERL driver
Driver RUBY
CAP11PHP driver
Ado.net5.mxj
12. What does TIMESTAMP do with the UPDATE CURRENT_TIMESTAMP data type?
The TIMESTAMP column is updated to zero when the table is created. The UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to the current time whenever there is a change in other table areas.
13. What is the dissimilarity between primary and candidate keys?
Each table row is uniquely identified by a primary key. There is only one primary key for a table.
A primary key is also a candidate key. A candidate key can be specified directly and used for any foreign key references by standard convention.
14. How can you change the root password if it is lost?
In cases where the password is lost, the user should start the DB with skip-grants-table and then change the password. Then the user with the new password should restart the DB in normal mode.
15. How to solve the data disk full problem?
When the data disk is full and congested, the workaround is to create a weak link and move the .frm and .idb files to that link.
16. What is the difference between the DELETE TABLE and TRUNCATE TABLE MySQL commands?
Primarily, DELETE TABLE is a logged process, and every deleted row is logged. Therefore, the process is usually slow. TRUNCATE TABLE also deletes rows in the table but does not log any deleted rows. The approach here is comparatively faster. TRUNCATE TABLE can be undone and is functionally similar to DELETE without the WHERE clause.
17. What are the connection types in MySQL?
There are four kinds of joins in MySQL. An inner join returns rows if at least one match exists in the two tables. A left join returns all rows from the left table, even if there is no conflict in the right table. On the right, the join returns all rows from the right table, even if there are no matches in the left table. A full join returns rows if at least one match exists in the tables.
18. What are the OLAP storage models?
The storage models in OLAP are MOLAP, ROLAP, and HOLAP.
19. How to define network layer testing in MySQL?
To define this, it is necessary to review the layered architecture and determine the dependencies of the hardware and software configuration with respect to the application under test.
20. How to perform an incremental backup in MySQL?
Users can perform incremental backup in MySQL using Percona XtraBackup.
21. What is a transaction? What are the properties of ACID?
A transaction is a logical unit of work where all or none of the steps should be performed. ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties of any transaction.
22. How to restart SQL Server in single user or minimal configuration mode?
The SQLSERVER.EXE command line used with ‘m’ restarts SQL Server in single user mode, and the same with ‘f’ restarts it in minimal configuration mode.
23. What is the difference between BLOB and TEXT?
BLOBs are large binary objects containing massive data. The four types of blobs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is an upper and lower case BLOB. The four types of TEXT are SMALL TEXT, TEXT, MEDIUM TEXT, and LONG TEXT.
24. What is scaling?
Scalability in MySQL is the ability to handle the load in terms of:
Amount of data
number of users
User Activity
Size of related datasets
25. What is Sharding?
Sharding divides large tables into smaller parts or fragments spread over many servers. Speeds up queries, maintenance, and other tasks.
26. What is Transaction Storage Engines?
The InnoDB storage engine allows users to use the MySQL transaction facility.
27. Write a SQL query to retrieve all employees who are also managers from the EmployeeDetails table.
Ans. Here we need to use Self-Join because the requirement requires us to parse the EmployeeDetails table as two tables. We will use different aliases ‘E’ and ‘M’ for the same EmployeeDetails table.
SELECT DISTINCT E.FullName
FROM Employee Details E
INNER JOIN Employee Details M
ON E.EmpID = M.ManagerID;
28. Write a SQL query to retrieve duplicate records from EmployeeDetails (without considering the primary key EmpId).
Ans. To find duplicate records from a table, we can use GROUP BY on all fields and then use the HAVING clause to return only those whose count is greater than 1, i.e., rows with duplicate records.
SELECT FullName, ManagerId, DateOfJoining, City, COUNT(*)
From employee details
GROUP BY FullName, ManagerId, DateOfJoining, City
HAVINGCOUNT(*) > 1;
29. Write a SQL query to remove duplicates from a table without using a temporary table.
Ans. Here we can use delete with alias and inner join. We check all matching records for equality and then delete the row with the higher EmpId.
DELETE E1 FROM E1 Employee Details
INNER JOIN Employee Details E2
WHERE E1.EmpId > E2.EmpId
AND E1.FullName = E2.FullName
And E1.ManagerId = E2.ManagerId
AND E1.DateOfJoining = E2.DateOfJoining
AND E1.City = E2.City;
30. How many columns can be used to create an index?
A maximum of 16 indexed columns can be created for any standard table.
31. What is the difference between NOW() and CURRENT_DATE()?
The NOW() command displays the current year, month, and date with hours, minutes, and seconds.
CURRENT_DATE() only displays the current year, month, and date.
32. What objects can be created using the CREATE statement?
The following objects are created using the CREATE statement:
DATABASE
EVENT
FUNCTION
INDEX
METHOD
TABLE
TRIGGER
USER
VIEW
33. How many TRIGGERS are allowed in a MySql table?
SIX triggers are enabled on a MySql table. They are as follows:
BEFORE INSERTING
AFTER INSERTING
BEFORE UPDATING
AFTER UPDATE
BEFORE DELETING a
AFTER DELETE
34. What are non-standard string types?
The following are non-standard string types:
SMALL TEXT
TEXT
MIDDLE TEXT
LYRICS
35. What is TIMESTAMP data type?
Timestamping in SQL Server helps with row versioning. Row versioning is a type of concurrency that allows a value to persist until it is committed to the database. It shows the instant time of any event. It consists of the date and time of the event. A timestamp also helps to back up data during a transaction failure.
While we insert, update or delete a record, the date and time are automatically inserted.
“ Timestamp format: YYYY-MM-DD HH:MM:SS
“ Timestamp range: “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07” UTC
Conclusion
In this blog, we have seen 35 most frequently asked MySQL interview questions. We hope this helps you in your learning.
Advanced Certification in Applied Data Science, Machine Learning & IoT By E&ICT Academy, IIT Guwahati
9 months
Certification in Data Science and Machine Learning With Python By The
IoT Academy Noida
180 Hrs
