Top MySQL Interview Questions and Answer

MySQL is an open-source relational database management system. As with other relational databases, MySQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL.

 

Notes:

1. MySQL is a relational database management system
2. MySQL is open-source
3. MySQL is free
4. MySQL is ideal for both small and large applications
5. MySQL is very fast, reliable, scalable, and easy to use
6. MySQL is cross-platform
7. MySQL is compliant with the ANSI SQL standard
8. MySQL was first released in 1995
9. MySQL is developed, distributed, and supported by Oracle Corporation
10. MySQL is named after co-founder Monty Widenius's daughter: My

Top 8 Advantages of MySQL

1. Ease of use: MySQL is known for being easy to use and set up, making it a popular choice for developers who are new to database management.
2. It is a cost-effective solution, as it is available as open-source software and can be downloaded and used for free.
3. MySQL is highly scalable and can handle a large amount of data efficiently.
4. It has a large user community, which means a lot of support and resources are available online, including documentation, forums, and tutorials.
5. MySQL is highly customizable and can be easily integrated with other applications and platforms.
6. It is a secure database management system with data encryption and user authentication to protect data from unauthorized access.
7. MySQL has a high performance and is able to handle a large number of concurrent connections, making it suitable for use in high-traffic web applications.
8. It is a reliable database management system, with a proven track record of stability and uptime.

There are three main ways you can interact with MySQL: 

• using a command line

• via a web interface

• through a programming language

A database query is either an action query or a select query. A select query is one that retrieves data from a database. An action query asks for additional operations on data, such as insertion, updating, deleting or other forms of data manipulation.

This doesn't mean that users just type in random requests. For a database to understand demands, it must receive a query based on the predefined code. That code is a query language.

Some common MySQL commands are:

• CREATE – To create Table

• INSERT – To insert data

• JOIN – To join tables

• DELETE – To delete a row from a table

• ALTER – To alter database or table

• BACKUP – to back up a table

• DROP – To delete a database or table

• CREATE INDEX – to add indexing to a column in a table

• GRANT – To change user privileges

• TRUNCATE – Empty a table

• EXIT – to exit

CREATE DATABASE vlogslogs;

Note :

If you get an error such as ERROR 1044 (42000): Access denied for user 'micah'@'localhost' to database 'vlogslogs' when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator

The following query can be used to create a table:

CREATE TABLE IF NOT EXISTS  `qualification` (
  `pki_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `hindi_name` varchar(64) DEFAULT NULL,
  `category` tinyint(2) DEFAULT NULL,
  `status` tinyint(2) NOT NULL,
  PRIMARY KEY (`pki_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

 

 

NSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

The DROP command is used to remove a column from a database.

 

Alter table 'history' drop column title;

In MySQL, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. You can achieve fast searches with the help of an index. Indexes speed up performance by either ordering the data on disk so it's quicker to find your result or, telling the SQL engine where to go to find your data.

Example: Adding indexes to the history table:

ALTER TABLE history ADD INDEX(author(10));

ALTER TABLE history ADD INDEX(title(10));

ALTER TABLE history ADD INDEX(category(5));

ALTER TABLE history ADD INDEX(year);

DESCRIBE history

We use the DELETE statement to remove records from a table.

The syntax is as follows:

DELETE FROM table_name WHERE column_name

The SHOW DATABASES command allows the user to view all databases on the MySQL server host. 

mysql> SHOW DATABASES;

here are numeric data types for integer, fixed-point, floating-point, and bit values in MySQL. Except for BIT, the other numeric data types can be signed or unsigned.

Examples: 

INT - Standard Integer

TINYINT - Very Small Integer

SMALLINT - Small Integer

MEDIUMINT - Medium-sized Integer

BIGINT - Large Integer

DECIMAL - Fixed-point number

FLOAT - Single-precision floating-point number

DOUBLE - Double-precision floating-point number

BIT - Bit-field

The string data types in MySQL are:
CHAR
VARCHAR
BINARY
VARBINARY
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET
NULL

 

MySQL provides temporal data types for date and time, as well as a combination of date and time. These are:

DATE - A date value in CCYY-MM-DD Format

TIME - A Time value in hh : mm :ss format

DATETIME - Date and time value in CCYY-MM-DD hh : mm :ss format

TIMESTAMP - A timestamp value in CCYY-MM-DD hh : mm :ss format

YEAR - A year value in CCYY or YY format

BLOB is an acronym that stands for a binary large object. It is used to hold a variable amount of data.
There are four types of BLOB:

1. TINYBLOB

2. BLOB

3. MEDIUMBLOB

4. LONGBLOB

 

A BLOB can hold a very large amount of data. For example - documents, images, and even videos. You could store your complete novel as a file in a BLOB if needed.

mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

In MySQL, a view consists of a set of rows that is returned if a particular query is executed. This is also known as a ‘virtual table’. Views make it easy to retrieve the way of making the query available via an alias.
The advantages of views are:

• Simplicity

• Security

• Maintainability

Creating a view is accomplished with the CREATE VIEW statement. As an example:

CREATE

   [OR REPLACE]

   [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED }]

   [DEFINER = { user | CURRENT_USER }]

   [SQL SECURITY { DEFINER | INVOKER }]

   VIEW view_name [(column_list)]

   AS select_statement

   [WITH [CASCADED | LOCAL] CHECK OPTION]

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event.
Triggers have many purposes, including:

• Audit Trails

• Validation

• Referential integrity enforcement

SIX triggers are available in MySQL table.

1. BEFORE INSERT

2. AFTER INSERT

3. BEFORE UPDATE

4. AFTER UPDATE

5. BEFORE DELETE

6. AFTER DELETE

The server, mysqld, is the hub of a MySQL installation; it performs all manipulation of databases and tables.

The default port for MySQL Server is 3306. Another standard default port is 1433 in TCP/IP for SQL Server.

• 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.

CHAR and VARCHAR are common strings we use to specify the type of data in MySQL.

CHAR is a fixed-length data type that always stores the same number of characters, while VARCHAR is a variable-length data type that can store a variable number of characters.

 

For example, VARCHAR(10) can store any length of characters up to 10. A CHAR string always uses the same amount of memory irrespective of what we store. VARCHAR is more efficient in using the storage space as it allocates memory dynamically.

Select version();

MySQL is a relational database management system that uses SQL.

SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way. SQL does not support any connector

 

SQL is a programming language you can use to store and process information in a relational database. MySQL is the most widely adopted open-source relational database. It serves as the primary relational data store for many popular websites, applications, and commercial products. Both technologies work together so that you can store and process data in structured tables with rows and columns. MySQL is the underlying technology that stores the data, while SQL is the language you use to read, modify, and delete that data.

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

‘_’ corresponds to only one character but ‘%’ corresponds to zero or more characters in the LIKE statement.

FLOAT stores floating-point numbers with accuracy up to 8 places and allocate 4 bytes. On the other hand, DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes.

 WHERE statement is used to filter rows but HAVING statement is used to filter groups.
  GROUP BY is not used with WHERE. HAVING clause is used with GROUP BY.

 

The MySQL DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values.

The CURRENT_DATE() method in MySQL returns only the date. The NOW() methods return the date and the time of the server. Because of this additional property, the NOW() method is preferred over the CURRENT_DATE() method.

A join is an SQL operation performed to establish a connection between two or more database tables based on matching columns, thereby creating a relationship between the tables. Most complex queries in an SQL database management system involve join commands.

The four common ways to join two or more tables in MySQL are:

1. Inner Join
2. Left Join
3. Right Join
4. Cross Join
5. Left Join

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE

CREATE TABLE TableName(
    pki_id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (ID)
);

SQL PRIMARY KEY on ALTER TABLE

ALTER TABLE TableName
ADD PRIMARY KEY (pki_id);

 

To remove Primary Key from a table, you can use the ALTER TABLE statement. The syntax to delete a primary key from a table is the following:

ALTER TABLE TableName DROP PRIMARY KEY;

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 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.

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.

InnoDB is a storage engine for MySQL.

 

Each InnoDB table arranges the data on the disk in order to optimize queries with a primary key. This makes access a little slower but also much more secure. Data selection is relatively faster, but inserting and updating take longer. As such, InnoDB is best suited to large databases and in particular those that contain lots of relational data.

 

InnoDB A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB also supports FOREIGN KEY referential-integrity constraints.

 

InnoDB is newer and more modern than other MySQL storage engines. However, it’s also more complex and requires a much more powerful database environment.

The main difference between UNION and UNION ALL is that: UNION: only keeps unique records. UNION ALL: keeps all records, including duplicates.

 

UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both the table, while UNION ALL returns all the records from both the tables.

MySQL Timestamp is a time-based MySQL data type containing date and time together.

 

The MYSQL TIMESTAMP() function is converts the date or datetime expression as a datetime value and returns the result in the form o f a string.

 

The MySQL TIMESTAMP is a transient data type that contains a mixture of date and time. It is exactly 19 characters long. The structure of a TIMESTAMP field is as follows:

# MySQL Timestamp

YYYY-MM-DD HH:MM:SS