Intermediate Level

How do I count the number of records in a MySQL query?

select count(*) from TABLE_NAME where 'status' = 1;

What is heap table in MySQL?

HEAP tables are in memory. No TEXT or BLOB fields are allowed inside
HEAP tables. They are typically used for high-speed transient storage.

What is temporary table in MySQL?

In MySQL, a temporary table is a distinct type of table that approves you
to save a transient end result set, in which you can reuse countless
instances in a single session. A brief table is created with the aid of the
use of the CREATE TEMPORARY TABLE

How to upload a large file through phpmyadmin in MySQL?

It is the best way to upload large CSV files through the LOAD DATA
command.
Syntax:

LOAD DATA LOCAL INFILE "your file name"
INTO TABLE table name
FIELDS TERMINATED by ’,’
LINES TERMINATED BY ’\n’

What is the degree of a table in MySQL?

The degree of a table is the number of columns in the given table. It is
also called as Arity. It is the range of entities inside each entity type that
can be linked via a given relationship type.

What is the difference between timestamp and datetime in MySQL?

Timestamp in MySQL
Timestamp in MySQL is used when users want to convert the value from
the current time zone to UTC zone for the sake of storage or UTC time
zone to the current time zone for retrieval.

DateTime in MySQL
DateTime in MySQL is used to insert values ‘yyyy-mm-dd’ in the table
where yyyy is the year, mm is the month and dd is the date. This
keyword is used to retrieve or to display DATETIME values in Mysql.

What is view in MySQL?

The view is basically a virtual table which is used to view certain and
preferable rows or columns of the table. In practice, users can add a
number of SQL functions, JOIN and WHERE clause to view the present
data of the table.

How do you create view in MySQL?

In MySQL, the CREATE VIEW statement is used to create a new view
inside the database.
Here’s the basic syntax of the statement:
CREATE VIEW productList AS SELECT qty, price, totalprice AS value
FROM product;

Which port is set as default for MySQL Server?

3306 is the default port for MySQL Server. 1433 is another standard
default port used in TCP/IP for SQL Server.

Which types of strings are used in database columns in MySQL?

In MySQL, We can use SET, BLOB, VARCHAR, TEXT, ENUM, and
CHAR type of strings.

What are Heap Tables?

Heap tables are used for high-speed temporary storage. But, TEXT
and BLOB fields are not allowed within them. They do not support
AUTOINCREMENT.

How can the user take an incremental backup in MySQL?

Percona XtraBackup is used to take an incremental backup in
MySQL.

Explain the difference between REGEXP and LIKE operators in
MySQL?

The identical difference between REGEXP and LIKE operators can
be described as mentioned below.
• LIKE is denoted using the ‘^’ sign.
• SELECT * FROM user WHERE username REGEXP “^NAME”;
• LIKE is denoted using the ‘%’ sign. For example:
• SELECT * FROM user WHERE user name LIKE “%NAME.”

How can the user change the root password if it is lost?

When the password of the user is lost, the user should start the
Database with skip-grants-table and then change the password. After
that, the user should restart the DB with the new password in a normal
mode.

Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy
and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18
places and it has eight bytes.

How to get current MySQL version?

SELECT VERSION ();
is used to get the current version of MySQL.

How do you control the max size of a HEAP table?

Maximum size of Heal table can be controlled by MySQL config variable
called max_heap_table_size.

What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character in the
LIKE statement.

What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data.
There are four types of BLOB –
• TINYBLOB
• BLOB
• MEDIUMBLOB and
• LONGBLOB

They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
• TINYTEXT
• TEXT
• MEDIUMTEXT and
• LONGTEXT
They all correspond to the four BLOB types and have the same maximum
lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and
comparison is performed in case-sensitive for BLOB values and caseinsensitive for TEXT values.

What is the difference between mysql_fetch_array and
mysql_fetch_object?

Following are the differences between mysql_fetch_array and
mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a
regular array from database.
mysql_fetch_object – Returns a result row as object from database.

How can we run batch mode in mysql?

Following commands are used to run in batch mode:
mysql ;
mysql mysql.out

Where MyISAM table will be stored and also give their formats of
storage?

Each MyISAM table is stored on disk in three formats:
• The ‘.frm’ file stores the table definition
• The data file has a ‘.MYD’ (MYData) extension
• The index file has a ‘.MYI’ (MYIndex) extension

How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be
combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX
numbers with single quotes and a prefix of (X), or just prefix HEX
numbers with (Ox).
A HEX number string will be automatically converted into a character
string, if the expression context is a string.

How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:
SELECT * FROM
LIMIT 0,50;

How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year, month, date with
hours, minutes and seconds.
CURRENT_DATE() shows current year, month and date only.

What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement:
• DATABASE
• EVENT
• FUNCTION
• INDEX
• PROCEDURE
• TABLE
• TRIGGER
• USER
• VIEW

How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows:
• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE and
• AFTER DELETE

What are the nonstandard string types?

Following are Non-Standard string types:
• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated
with an object. This list is the basis for MySQL server’s security model
and it helps in troubleshooting problems like users not being able to
connect.
MySQL keeps the ACLs (also called grant tables) cached in memory.
When a user tries to authenticate or run a command, MySQL checks the
authentication information and permissions against the ACLs, in a
predetermined order.


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