Top Answers to MySQL Interview Questions
|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|
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.
SQL Server is free and anyone can download and use it. The application uses SQL (Structured Query Language), and it is easy to use.
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.
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.
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!
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.
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!
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!
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.
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.
The syntax for concatenating tables is MySQL:
CONCAT (string 1, string 2, string 3)
The maximum limit of indexed columns that can be created for any table is 16.
In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR.
The syntax for getting the current version of MySQL:
SELECT VERSION ();
Yes. MySQLi is the object-oriented version of MySQL, and it interfaces in PHP.
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.
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!
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.
Yes. The syntax for using MySQL with Linux operating system is as follows:
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.
Following TRIGGERS are allowed in MySQL:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
- 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”;
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’.
A user can take an incremental backup in MySQL using Percona XtraBackup.
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.
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.
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.
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.
The storage models in OLAP are MOLAP, ROLAP, and HOLAP.
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.
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.
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.
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.
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!
The logical architecture of MySQL is made of ‘connection manager’, ‘query optimizer’, and ‘pluggable engines’.