Intermediate Level

What is MySQL?

MySQL is a multithreaded, multi-user SQL database management system
which has more than 11 million installations. It is the world's second
most popular and widely-used open source database. It is interesting
how MySQL name was given to this query language. The term My is
coined by the name of the daughter of co-founder Michael Widenius's
daughter, and SQL is the short form of Structured Query Language. Using
MySQL is free of cost for the developer, but enterprises have to pay a
license fee to Oracle.
Formerly MySQL was initially owned by a for-profit firm MySQL AB, then
Sun Microsystems bought it, and then Oracle bought Sun Microsystems,
so Oracle currently owns MySQL.


MySQL is an Oracle-supported Relational Database Management System
(RDBMS) based on structured query language. MySQL supports a wide
range of operating systems, most famous of those include Windows,
Linux & UNIX. Although it is possible to develop a wide range of
applications with MySQL, it is only used for web applications & online
publishing. It is a fundamental part of an open-source enterprise known
as Lamp.

What are the technical specifications of MySQL?

MySQL has the following technical specifications -
o Flexible structure
o High performance
o Manageable and easy to use
o Replication and high availability
o Security and storage management
o Drivers
o Graphical Tools
o MySQL Enterprise Monitor
o MySQL Enterprise Security
o JSON Support
o Replication & High-Availability
o Manageability and Ease of Use
o OLTP and Transactions
o Geo-Spatial Support

What is the difference between the database and the table?

There is a major difference between a database and a table. The differences are as follows:
o Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
o Tables are used to group the data in relation to each other and create a dataset. This dataset will be used in the database. The data stored in the table in any form is a part of the database, but the
reverse is not true.
o A database is a collection of organized data and features used to access them, whereas the table is a collection of rows and columns used to store the data.

Why do we use the MySQL database server?

First of all, the MYSQL server is free to use for developers and small
enterprises.
MySQL server is open source.
MySQL's community is tremendous and supportive; hence any help
regarding MySQL is resolved as soon as possible.
MySQL has very stable versions available, as MySQL has been in the
market for a long time. All bugs arising in the previous builds have been
continuously removed, and a very stable version is provided after every
update.
he MySQL database server is very fast, reliable, and easy to use. You can
easily use and modify the software. MySQL software can be downloaded
free of cost from the internet.
What are the different tables present in MySQL?
There are many tables that remain present by default. But, MyISAM is the
default database engine used in MySQL. There are five types of tables
that are present:
o MyISAM
o Heap
o Merge
o INNO DB
o ISAM

What are the difference between and MyISAM and InnoDB?

These are most commonly used storage engines in MySQL are MyISAM
and InnoDB.
Difference between MyISAM and InnoDB
o MyISAM no longer supports transactions, however, InnoDB supports
transactions.
o MyISAM helps Table-level Locking, however, InnoDB supports Rowlevel Locking.
o MyISAM helps full-text search, however InnoDB does not.
o MyISAM is designed for the need for speed but InnoDB is designed for
the most performance.
o MyISAM does now not aid overseas keys, but InnoDB helps foreign
keys.
o We can use commit and rollback with InnoDB however not in
MyISAM.
o MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation,
and Durability) however InnoDB supports the ACID property.
o In the InnoDB table, the AUTO_INCREMENT field is a section of the
index but is now not in MyISAM.
o MyISAM stores its tables, data, and indexes in the disk area with the
usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.

What is the difference between delete, drop and truncate?

TRUNCATE
o It removes all rows from a table.
o It does not require a WHERE clause.
o Truncate cannot be used with indexed views.
o It is performance wise faster.
DELETE
o It removes Some or All rows from a table.
o A WHERE clause is used to remove particular rows according to the
matched condition. All rows will be deleted when we did not use
Where condition in Query.
o It removes rows one by at a time.
o It can be used with indexed views.
DROP
o It removes a table from the database.
o All table's rows, indexes, and privileges will also be removed when we
used this command.
o The operation cannot be rolled back.

Is MySQL case-sensitive?

Mysql is not case sensitive. Its case-sensitivity relies on the underlying
operating system as OS determines the case sensitivity of tables names
and database. In windows, database and table names are not case
sensitive but in the case of UNIX it is case sensitive in nature. Especially
on the UNIX host, database accepts the upper case and lower-case table
names.

What are the different ways to optimize a MySQL query?

- Don't use "sp_" while creating a stored procedure.
- Don't use "*" while you are using the SELECT command. Also avoid
unnecessary columns in SELECT clause.
- You can use table aliases while writing queries.
- Avoid the usage of the wildcard (%) at the starting of a predicate.
- DISTINCT and UNION need to be used solely if it is necessary.

What is indexing and how do you create an index in MySQL?

A database index is the structure of a fact that improves the velocity of
operations in a table. It can be created the usage of one or greater
columns, presenting the foundation for each fast random lookups and efficient ordering of getting admission to records. Indexes are used to
locate rows with specific column values quickly.
Example: CREATE INDEX [index name] ON [table “” not found /]

What is cardinality in MySQL?

In MySQL, the time period cardinality refers to the specialty of facts
values that can be put into columns. It is a type of property that
influences the potential to search, cluster and kind data.
Cardinality can be of two sorts which are as follows
Low Cardinality − All values for a column have to be the same.
High Cardinality − All values for a column ought to be unique

How to create case insensitive query in MySQL?

The standard way to perform case insensitive queries in SQL is to use the
SQL upper or lower functions like the following:
select * from users where upper(first_name) = 'AJAY';
OR
select * from users where lower(first_name) = 'ajay';
The method is to make the field you are searching as uppercase or
lowercase then also make the search string uppercase or lowercase as
per the SQL function.

What is DDL, DML and DCL in MySQL?

1. DDL
In MySQL, DDL is the short form for Data Definition Language, which is
used in database schemas and descriptions while deciding how data
should reside in the database.
Here’s a list of DDL Queries:
- CREATE
- ALTER
- DROP
- TRUNCATE
- COMMENT
- RENAME
2. DML
DML is a short form for Data Manipulation Language which is used in
data manipulation and mostly includes common SQL statements to store,
modify, retrieve, delete and update data in a database.
Here is the list of DML Queries:
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
- CALL
- EXPLAIN PLAN
- LOCK TABLE
3. DCL
DCL is a short form for Data Control Language including commands
which are concerned with User rights, permissions and other controls
within the database system.

Here’s a list of queries for DCL:
- GRANT
- REVOKE

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