Explore topic-wise InterviewSolutions in .

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges GRANTED to and revoked from them.

A user account can be granted roles, which grants to the account the privileges associated with each role. This enables assignment of sets of privileges to accounts and PROVIDES a convenient alternative to granting individual privileges, both for conceptualizing desired privilege assignments and implementing them.

CREATE ROLE and DROP ROLE ENABLE roles to be created and removed.

GRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.

SHOW GRANTS DISPLAYS privilege and role assignments for user accounts and roles.

1. INNER JOIN: Returns rows when there is a MATCH in both joined tables
2. LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.
3. RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.
4. FULL JOIN: It combines the results of both left and right outer joins.

For INNER joins,  the order doesn't matter. The queries will return the same results, as long as we change your selects from SELECT * to SELECT a.*, b.*, c.*.

For (LEFT, RIGHT or FULL) OUTER joins, YES, the order matters.

First, outer joins are not COMMUTATIVE, so a LEFT JOIN b is not the same as b LEFT JOIN a

MySQL supports two kinds of aliases which are known as column ALIAS and table alias. SOMETIMES the names of columns are so technical that make the query’s OUTPUT very DIFFICULT to understand. To give a column a descriptive name, you use a column alias.

To give a column an alias, you use the AS keyword followed by the alias. If the alias contains space, you must quote it.You can use an alias to give a table a different name. You assign a table an alias by using the AS keyword as the following syntax

The alias for the table is called table alias. Like the column alias, the AS keyword is optional so you can omit it.

>CREATE TABLE myset (col SET('a', 'B', 'c', 'd'));

And INSERTING the values 

what will be the output of this query?

For a value containing more than one SET element, it does not matter what ORDER the elements are listed in when you insert the value. It also does not matter how MANY times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table CREATION time.

SELECT orderNumber,        requiredDate FROM orders WHERE requiredDate BETWEEN '2018-01-01' AND '2018-01-31';

The query SELECTS orders WHOSE required dates are in January 2018. The data type of the requireDate COLUMN is DATE, therefore, MySQL has to convert the literal strings: '2018-01-01' and '2018-01-31'into TIMESTAMP values before EVALUATING the WHERE condition.

A stored ROUTINE is either a PROCEDURE or a function. Stored ROUTINES are created with the CREATE PROCEDURE and CREATE FUNCTION statements 

A SET is a string object that can have zero or more values, each of which MUST be CHOSEN from a list of permitted values specified when the table is created. SET column values that CONSIST of multiple set members are specified with members separated by commas (,). A CONSEQUENCE of this is that SET member values should not themselves CONTAIN commas.

A character set is a set of symbols and encodings. A collation is a set of RULES for comparing characters in a character set
To display the available character SETS run the below query

MySQL is capable of doing these things 

1. Store strings using a variety of character sets.

2. Compare strings using a variety of COLLATIONS.

3. Mix strings with different character sets or collations in the same SERVER, the same database, or even the same table.

4. Enable specification of character set and collation at any level.

Latin1 is the default character set in MySQL

A DATABASE engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, UPDATE and delete (CRUD) data from a database.

The disabled_storage_engines configuration OPTION defines which storage engines cannot be used to create tables or tablespaces. By default, disabled_storage_engines is empty (no engines disabled), but it can be SET to a comma-separated list of one or more engines.

PDO is PHP Data Objects. 

PDO will WORK on 12 different database SYSTEMS, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO MAKES the process easy. You only have to change the CONNECTION string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

CREATE TABLE [IF NOT EXISTS] table_name(     column_list ) ENGINE=storage_engine CREATE TABLE `test`.`car` ( `id` INT(12) NULL AUTO_INCREMENT , `NAME` VARCHAR(20) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

SHOW create table statement shows the CREATE TABLE statement that CREATES the named table.

Why we use IGNORE keyword query in MySQL?
There are certain CONDITION when we need to insert some duplicate records in table in presence of unique INDEX. But when we TRY this we will get some error. So to discard this we use the "INSERT IGNORE" COMMAND instead of "INSERT". And MSSQL will discard the record if it is duplicate. And below is the syntax for IGNORE Keyword in MySQL:-

INSERT IGNORE INTO Table (field1, field2, field3)
-> VALUES( 'value1', 'value2', 'value3');

What do you understand by i_am_a_dump flag in MySQL?
i_am_a_dump is one of flag which supports MySQL ENGINE when clause "Where" is MISSING. And this will refuses to PERFORM any UPDATE or delete statements.

What is the DEFAULT PORT for MYSQL?
Default port of MySQL is 3306.

How to enable slow query log MySQL?
As the NAME suggest slow query log this will slow down the PERFORMANCE of query or overall server performance. And this can be huge in size. Below are five steps to enable slow query logs in python
(1)In first step we need to do login in server by using SSH.
(2)In second step on command line we need to use below command:-
mysql -U root -p
(3)In third step we need to type MySQL root PASSWORD.
(4)In fourth step to enable slow query log we need to run below command as mySQL prompt:-
SET GLOBAL slow_query_log = 'ON'
(5)We have some additional option when we using slow query log.

What is BLOB and its types in MYSQL?
BLOB is LARGE binary OBJECT which can have a variable amount of data. Below are the some of very important aspects of BLOB.
(1)BLOB is the FAMILY of column type intended as high-capacity binary storage.
(2)Actually BLOB column is of 4 types
(i)TINYBLOB
(II)BLOB
(iii)MEDIUMBLOB
(iv)LONGBLOB
(3)Four of above BLOB types are similar the only difference is the amount of information data each can store.

What are scrollable CURSOR and how it is DIFFERENT from standard cursor in MYSQL?
Scrollable cursor is very much FLEXIBLE type of cursor which when placed in a table. And this cursor can be moved backward, forward or start of the table. But when we say about the standard cursor this above facility or we can say feature is not available. And normal cursor will not allow use to MOVE down and up of the table.

What is ROLE of tee command in MySQL?
In MySQL tee command is used to mainly used for logging process. And after USING tee command it will start logging and send output to the console and TEXT FILE. Below is the syntax to use tee command:-

mysql> tee /tmp/logfile.out;

Default time to log a query in slow query log and can we CHANGE that in MySQL?
YES, we can change the default time to log a query in MySQL. By default it will take more then 10 seconds to run. We can change this interval. And below is the command to change the time:-
SET GLOBAL long_query_time = n;
In above command we can PUT number in place of n to set the time to log query in slow query log.

How do we stop Slow Query Log in MYSQL?
To disable the Slow Query Log we need to RUN below COMMAND on MySQl:-
SET GLOBAL slow_query_log = 'OFF';

What are the common MySQL functions
Below are the four main common MySQL functions
(1)DATEDIFF(X,Y):-Method to GET difference between two dates.
(2)CONCATE(X,Y):-Method to CONCATENATES two string VALUES creating single string output.
(3)CURRDATEO:-Method to for returning the current date or time.
(4)NOWO:-Method for returning current date and time as single value.

How do we RETURN 100 records from table starts from 25th record in MySQL?
Here ONE of the benefits of LIMIT keyword is that we can just provide range of record starts from and upto how MANY records. And QUERY is given below:-

SELECT column_name from table_name LIMIT 25,100

Here in above query first number after LIMIT is the offset and the second is the number of records.

Below are the FIVE TYPES of table in MySQL
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM

Who was started and owned MySQL?
MySQL is one of the most famous and POPULAR free open-source database software. And it comes under GNU General Public LICENSE. In starting it was owned and sponsored by a Swedish COMPANY name MySQL AB. But now it has been owned by Sun Microsystems ( ORACLE Corporation). Now it is responsible for managing and DEVELOPING the database.

What is heap table and other NAME of these table in MYSQL?
In MySQL TABLES that present in memory is known as HEAP tables. But when we create Heap table in MySQL we need to SPECIFY the type as HEAP. And these tables are commonly known as memory tables. Heap tables are used for high-speed storage on a temporary basis. They do not ALLOW BLOB or TEXT fields.

How to view all DATABASES available on to the MySQL SERVER?
Below is the command which is used to view all the databases available on to the server:-
mysql > SHOW DATABASES;

Way to clear screen in MySQL in Window OPERATING system?
Before version 8 we need to apply some manual method to clear screen in windows of MySQL. To clear screen we need to do exit from MySQL command line tool then again OPEN the MySQL command line tool.
But after the version 8 a new command comes into picture to clear the command line screen that is GIVEN below:-
mysql < SYSTEM CLS;

What are different tables and DEFAULT database PRESENT in MySQL?
There are many tables that remains present in database by default. And the default database name is "MyISAM" in MySQL. And there are five types of tables that are present:-
(1)MyISAM
(2)Heap
(3)Merge
(4)INNO DB
(5)ISAM

Difference between char_length and Length in MySQL?
Both of these are mainly to COUNT the length but DIFFERNCIATE is when we use Latin characters both are same. But these are not same when we use Unicode and some other ENCODING. In naturally char_length will return the CHARACTER count and Length will return the BYTE count.

Can we USE "ORDER BY" in delete STATEMENT in MySQL Query?
Yes we can do that when we donot NEED to delete ROW by and particular row is. We use this when we need to delete records from timestamp below is the example:-

DELETE FROM table_name ORDER BY timestamp LIMIT 1