Top Answers to MySQL Interview Questions
1. Compare MySQL vs SQL Server.
|Programmed in||C and C++||Mainly C++, but some parts in C|
|Platforms||Supports many platforms||Supports only Linux and Windows|
|Syntax||Complex Syntax||Simpler and easy-to-use syntax|
2. What is SQL Server?
SQL Server is one of the database management systems (DBMS) and is designed by Microsoft. DBMS are computer software applications with the capability of interacting with users, various other applications, and databases. The objective of SQL Server is capturing and analyzing data and managing the definition, querying, creation, updating, and administration of the database.
Watch this SQL Tutorial for Beginners video:
3. How and why use SQL Server?
SQL Server is free and anyone can download and use it. The application uses SQL (Structured Query Language), and it is easy to use.
4. What are the features of MySQL?
MySQL provides cross-platform support, a wide range of interfaces for application programming, and has many stored procedures like triggers and cursors that help in managing the database.
5. What is the Traditional Network Library for a system?
In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using the network stack, and data can be sent without affecting the performance. Servers set up named pipes to listen to requests. The client process needs to know the specific pipe name to send the request.
6. What is the default port for MySQL Server?
The default port for MySQL Server is 3306. Another standard default port is 1433 in TCP/IP for SQL Server.
Learn all about SQL through this SQL Certification Course!
7. What do DDL, DML, and DCL stand for?
DDL is the abbreviation for Data Definition Language dealing with database schemas, as well as the description of how data resides in the database. An example of this is the CREATE TABLE command. DML denotes Data Manipulation Language which includes commands such as SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE, etc.
8. What is a join in MySQL?
In MySQL, joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table. There are four types of joins in MySQL. Inner join returns rows if there is at least one match in both tables. Left join returns all the rows from the left table even if there is no match in the right table. Right join returns all the rows from the right table even if no matches exist in the left table. Full join would return rows when there is at least one match in the tables.
Master SQL concepts in depth from this SQL Tutorial now!
9. What are the common MySQL functions?
Common MySQL functions are as follows:
- NOWO: The function for returning the current date and time as a single value
- CURRDATEO: The function for returning the current date or time
- CONCAT (X, Y): The function to concatenate two string values creating a single string output
- DATEDIFF (X, Y): The function to determine the difference between two dates
Check out Intellipaat’s blog to get a fair understanding of SQL Optimization Techniques!
10. What is the difference between CHAR and VARCHAR?
When a table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1–255. The VARCHAR command is used to adjust the column and table lengths as required.
11. What are Heap Tables?
Basically, Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.
12. What is the syntax for concatenating tables in MySQL?
The syntax for concatenating tables is MySQL:
CONCAT (string 1, string 2, string 3)
13. What is the limit of indexed columns that can be created for a table?
The maximum limit of indexed columns that can be created for any table is 16.
14. What are the different types of strings used in database columns in MySQL?
In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR.
15. How can a user get the current SQL version?
The syntax for getting the current version of MySQL:
SELECT VERSION ();
16. Is there an object-oriented version of MySQL library functions?
Yes. MySQLi is the object-oriented version of MySQL, and it interfaces in PHP.
17. What is the storage engine used for MySQL?
Storage tables are named as table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities, and functions.
18. What is the difference between the primary key and the candidate key?
The primary key in MySQL is used to identify every row of a table in a unique manner. For one table, there is only one primary key. The candidate keys can be used to reference the foreign keys. One of the candidate keys is the primary key.
Want to get certified in SQL? Go through our blog on SQL Server Certification and be informed!
19. What are the different types of tables in MySQL?
MyISAM is the default table that is based on the sequential access method.
- Heap is the table that is used for fast data access, but the data will be lost if the table or the system crashes.
- InnoDB is the table that supports transactions using the COMMIT and ROLLBACK commands.
- BDB can support transactions similar to InnoDB, but the execution is slower.
20. Can you use MySQL with Linux operating system?
Yes. The syntax for using MySQL with Linux operating system is as follows:
21. What is the use of ENUM in MySQL?
The use of ENUM will limit the values that can go into a table. For instance, a user can create a table giving specific month values and other month values would not enter into the table.
22. What are the TRIGGERS that can be used in MySQL tables?
Following TRIGGERS are allowed in MySQL:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
23. What is the difference between LIKE and REGEXP operators in MySQL?
- LIKE is denoted using the ‘%’ sign. For example:
SELECT * FROM user WHERE user name LIKE “%NAME”
- On the other hand, the use of REGEXP is as follows:
SELECT * FROM user WHERE username REGEXP “^NAME”;
24. How to use the MySQL slow query log?
Information that is provided on the slow query log could be huge in size. The query could also be listed over a thousand times. In order to summarize the slow query log in an informative manner, one can use the third-party tool ‘pt-query-digest’.
25. How can one take an incremental backup in MySQL?
A user can take an incremental backup in MySQL using Percona XtraBackup.
26. How can you change the root password if it is lost?
In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in a normal mode.
27. How to resolve the problem of the data disk that is full?
When the data disk is full and overloaded, the way out is to create and soft link and move the .frm and the .idb files into that link location.
28. What is the difference between the DELETE TABLE and TRUNCATE TABLE commands in MySQL?
Basically, DELETE TABLE is a logged operation, and every row deleted is logged. Therefore, the process is usually slow. TRUNCATE TABLE also deletes rows in a table, but it will not log any of the rows deleted. The process is faster here in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement without a WHERE clause.
29. What are the types of joins in MySQL?
There are four types of joins in MySQL. Inner join returns the rows if there is at least one match in two tables. Left join returns all the rows from the left table even if there is no match in the right table. Right join returns all the rows from the right table even if no matches exist in the left table. Full join would return rows when there is at least one match in the tables.
30.What are the storage models of OLAP?
The storage models in OLAP are MOLAP, ROLAP, and HOLAP.
31. How to define the testing of network layers in MySQL?
For this, it is necessary to review the layered architecture and determine hardware and software configuration dependencies with respect to the application put to test.
32. What is the difference between primary key and unique key?
While both are used to enforce the uniqueness of the column defined, the primary key would create a clustered index, whereas the unique key would create a non-clustered index on the column. The primary key does not allow ‘NULL’, but the unique key does.
33. What is meant by transaction? What are ACID properties?
Transaction is a logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.
34. How can one restart SQL Server in the single user or the minimal configuration modes?
The command line SQLSERVER.EXE used with ‘–m’ will restart SQL Server in the single user mode and the same with ‘–f’ will restart it in the minimal configuration mode.
35. What is the difference between BLOB and TEXT?
BLOBs are binary large object holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.
If you have any doubts or queries related to SQL, get them clarified from SQL experts on our SQL Community!
36. What is the basic MySQL architecture?
The logical architecture of MySQL is made of ‘connection manager’, ‘query optimizer’, and ‘pluggable engines’.