Intermediate Level

What are the difference between MongoDB and MySQL?

MongoDB - The representation of data in MongoDB is totally different from Mysql, as in MongoDB
data is represented in the collection of JSON documents. MongoDB is an object oriented database.

MySQL - The representation of Mysql database is totally different form Mongodb, as in Mysql
data is stored in the form of tables (Row and columns).Mysql is a structured query database.

How to store binary data in MySQL?

Binary data can be stored in Mysql in different ways such as
• The short length of binary data can be stored in the form of string as
a VARCHAR
• A BLOB data type allows users to store an arbitrary amount of binary
data.

What is regex in MySQL?

Regex is an operational and powerful pattern that can help the users for
the implementation of a powerful search utility function for database
systems. Moreover, it is an operator which is used when we require
matching regular expressions. In addition to this, it also supports a wider
range of meta-characters which allow flexibility and more control over
the database structure when performing pattern matching.

Example:
Match beginning of string(^):
SELECT name FROM users WHERE name REGEXP
'^bestinterviewquestion';

How to make a copy values from one column to another in Mysql?

UPDATE `your_table_name` SET new_field=old_field

What are the difference between char_length and length in MySQL?

LENGTH() - It returns the total length of the string which is measured in bytes.
CHAR_LENGTH() - It returns the total length of the strings measured in characters.

What is the use of enum in MySQL? How it is different from set?

In MySQL, an ENUM is actually a string object whose value is selected
from the list of permitted values pre-defined during the time of a column
creation. It is used because it provides compact data storage in addition
to readable queries and output.
Here’s the difference between an ENUM and a SET

ENUM - The value should be one listed in the column definition or internal numeric equivalent.
SET - Must be an empty string consisting of values listed in column

Can a primary key be dropped in MySQL? If yes, how?

Yes, the primary key can be dropped in MySQL with the use of the
“ALTER TABLE” statement.
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY

How to save images in MySQL?

Images in MySQL can be stored as blobs. For saving them: All the
database images are converted into the blobs first. Then, they will get
inserted into the database, and later on, it will get stored into the disk.

How to write an optimized query in MySQL?

To write an optimized query in MySQL, below are some guidelines:
• Functions should not be used in predicates.
• At the beginning of predicates, do not use the wildcard symbols such as %.
• Use only needed columns in the SELECT clause.
• Always use the inner join option.
• Use of the “Order by” clause is necessary for SQL, in case of assuming the sorted results.

What is slow query log in MySQL?

In general slow query, the log is basically used in Mysql for the
determination of which database queries will take a long duration to run.
Moreover, a slow query log in MySQL simplifies that operations in the
context of efficient and time-consuming queries.
In order to enable the slow query log the command- Get global
slow_query_log – ‘ON’; is used

What's new in MySQL 8?

There are a number of unique features that are incorporated in Mysql 8
include-
• Unicode 9.0 support
• Window functions
• Recursive SQL syntax statements
• Support Native JSON data
• Support for document store Functionality

What is federated tables in MySQL?

It is a table that points to a table in another MySQL database instance. It
can be seen as a view of this remote database. Other RDBMS have the
same concepts for example database links.

How to control the max size of a HEAP table?

The maximum size of the HEAP table can be controlled by the MySQL
config variable called max_heap_table_size

What are the advantages of MySQL?

• Data Security
• It is used to retrieve large amounts of records from a database quickly and efficiently.
• On-Demand Scalability
• High Performance
• Excellent Uptime
• Reduced Total Cost of Ownership


Explore the Data Science Course in Pune
Watch the video on SQL Interview Question Set - 3