What are roles in MySQL and briefly explain any three?

One of the most frequently posed MYSQL query interview questions, be ready for it.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 the 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.CREATE ROLE ‘admin’, ‘devops’;DROP ROLE ‘admin’, ‘devops’;GRANT and REVOKE enable privilege assignment and revocation for user accounts and roles.GRANT ALL ON db1.* TO ‘admin’@’localhost’;REVOKE INSERT ON *.* FROM ‘admin’@’localhost’;SHOW GRANTS displays privilege and role assignments for user accounts and roles.SHOW GRANTS FOR 'admin'@'localhost';

Top Database Interview Questions and Answers for 2024

Q: Explain create table statement and its difference with show create table

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.

Q: How to change the data type of requireDate field in query?

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.SELECT orderNumber, requiredDate FROM orders WHERE requiredDate BETWEEN CAST('2018-01-01' AS DATETIME) AND CAST('2018-01-31' AS DATETIME);

Q: Creating a table with query

>CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));And inserting the values >INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');what will be the output of this query?>SELECT col FROM myset; | col | +------+ | a,d | | a,d | | a,d | | a,d | | a,d |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.

All Courses

Introduction

MySQL is a tool used to manage databases and servers, where SQL (Structured Query Language) is a standardized computer programming language that is used to maintain relational databases and perform data manipulation as well. Whether you are a beginner or an intermediate or an experienced MySQL professional, these questions will help you increase your confidence and knowledge of MySQL. SQL skills are very crucial in most job roles. This guide will give you a better understanding of the concepts related to SQL, Oracle, Database, and Servers. With MySQL interview questions, you can be confident to be prepared well for the upcoming interview.

MySQL Interview Questions and Answers

Beginner

1. Explain create table statement and its difference with show create table

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.

2. What is PDO and the reasons to prefer it over MySQLi?

This is a frequently asked question in MYSQL interview questions for freshers.

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.

3. What is a storage engine.How to prevent the use of a particular storage engine?

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.

4. What is character set in MySQL. What is the default character set in MySQL

Expect to come across this popular question in MYSQL questions for interview.

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:

>SHOW CHARACTER SET;

MySQL is capable of doing these things

Store strings using a variety of character sets.
Compare strings using a variety of collations.
Mix strings with different character sets or collations in the same server, the same database, or even the same table.
Enable specification of character set and collation at any level.

Latin1 is the default character set in MySQL.

5. What is datatype set

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.

Advanced

1. What is a routine and how is it created

A stored routine is either a procedure or a function. Stored routines are created with the CREATE PROCEDURE and CREATE FUNCTION statements

DELIMITER //
CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER ;

2. How to change the data type of requireDate field in query?

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.

SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN  CAST('2018-01-01' AS DATETIME)
                        AND CAST('2018-01-31' AS DATETIME);

3. Creating a table with query

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

And inserting the values

>INSERT INTO myset (col) VALUES
 ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

what will be the output of this query?

>SELECT col FROM myset;

| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |

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.

4. What are MySQL aliases?

It's no surprise that this one pops up often in MYSQL basic interview questions.

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

SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;

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.

table_name AS table_alias

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

5. What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

A common question in MYSQL interview questions for experienced, don't miss this one.

INNER JOIN: Returns rows when there is a match in both joined tables
LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.
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

Want to Know More?

Full Name*

Email*

+91

Phone Number*

United States +1

India +91

Canada +1

Australia +61

Singapore +65

New Zealand +64

Germany +49

United Arab Emirates +971

Hong Kong +852

Ireland +353

Afghanistan +93

Aland Islands +358

Albania +355

Algeria +213

AmericanSamoa +1684

Andorra +376

Angola +244

Anguilla +1264

Antarctica +672

Antigua and Barbuda +1268

Argentina +54

Armenia +374

Aruba +297

Ascension Island +247

Austria +43

Azerbaijan +994

Bahamas +1242

Bahrain +973

Bangladesh +880

Barbados +1246

Belarus +375

Belgium +32

Belize +501

Benin +229

Bermuda +1441

Bhutan +975

Bolivia +591

Bosnia and Herzegovina +387

Botswana +267

Brazil +55

British Indian Ocean Territory +246

Brunei Darussalam +673

Bulgaria +359

Burkina Faso +226

Burundi +257

Cambodia +855

Cameroon +237

Cape Verde +238

Cayman Islands +1345

Central African Republic +236

Chad +235

Chile +56

China +86

Christmas Island +61

Cocos (Keeling) Islands +61

Colombia +57

Comoros +269

Congo +242

Cook Islands +682

Costa Rica +506

Cote d'Ivoire +225

Croatia +385

Cuba +53

Cyprus +357

Czech Republic +420

Democratic Republic of the Congo +243

Denmark +45

Djibouti +253

Dominica +1767

Dominican Republic +1849

Ecuador +593

Egypt +20

El Salvador +503

Equatorial Guinea +240

Eritrea +291

Estonia +372

Eswatini +268

Ethiopia +251

Falkland Islands (Malvinas) +500

Faroe Islands +298

Fiji +679

Finland +358

France +33

French Guiana +594

French Polynesia +689

Gabon +241

Gambia +220

Georgia +995

Ghana +233

Gibraltar +350

Greece +30

Greenland +299

Grenada +1473

Guadeloupe +590

Guam +1671

Guatemala +502

Guernsey +44

Guinea +224

Guinea-Bissau +245

Guyana +592

Haiti +509

Holy See (Vatican City State) +379

Honduras +504

Hungary +36

Iceland +354

Indonesia +62

Iran +98

Iraq +964

Isle of Man +44

Israel +972

Italy +39

Jamaica +1876

Japan +81

Jersey +44

Jordan +962

Kazakhstan +77

Kenya +254

Kiribati +686

Korea, Democratic People's Republic of Korea +850

Korea, Republic of South Korea +82

Kosovo +383

Kyrgyzstan +996

Laos +856

Latvia +371

Lebanon +961

Lesotho +266

Liberia +231

Libya +218

Liechtenstein +423

Lithuania +370

Luxembourg +352

Macau +853

Madagascar +261

Malawi +265

Malaysia +60

Maldives +960

Mali +223

Malta +356

Marshall Islands +692

Martinique +596

Mauritania +222

Mauritius +230

Mayotte +262

Mexico +52

Micronesia, Federated States of Micronesia +691

Moldova +373

Monaco +377

Mongolia +976

Montenegro +382

Montserrat +1664

Morocco +212

Mozambique +258

Myanmar +95

Namibia +264

Nauru +674

Nepal +977

Netherlands +31

New Caledonia +687

Nicaragua +505

Niger +227

Nigeria +234

Niue +683

Norfolk Island +672

North Macedonia +389

Northern Mariana Islands +1670

Norway +47

Oman +968

Pakistan +92

Palau +680

Palestine +970

Papua New Guinea +675

Paraguay +595

Peru +51

Philippines +63

Pitcairn +872

Poland +48

Portugal +351

Puerto Rico +1939

Qatar +974

Reunion +262

Romania +40

Russia +7

Rwanda +250

Saint Barthelemy +590

Saint Helena, Ascension and Tristan Da Cunha +290

Saint Kitts and Nevis +1869

Saint Lucia +1758

Saint Martin +590

Saint Pierre and Miquelon +508

Saint Vincent and the Grenadines +1784

Samoa +685

San Marino +378

Sao Tome and Principe +239

Saudi Arabia +966

Senegal +221

Serbia +381

Seychelles +248

Sierra Leone +232

Sint Maarten +1721

Slovakia +421

Slovenia +386

Solomon Islands +677

Somalia +252

South Africa +27

South Georgia and the South Sandwich Islands +500

South Sudan +211

Spain +34

Sri Lanka +94

Sudan +249

Suriname +597

Svalbard and Jan Mayen +47

Sweden +46

Switzerland +41

Syrian Arab Republic +963

Taiwan +886

Tajikistan +992

Tanzania, United Republic of Tanzania +255

Thailand +66

Timor-Leste +670

Togo +228

Tokelau +690

Tonga +676

Trinidad and Tobago +1868

Tunisia +216

Turkey +90

Turkmenistan +993

Turks and Caicos Islands +1649

Tuvalu +688

Uganda +256

Ukraine +380

United Kingdom +44

Uruguay +598

Uzbekistan +998

Vanuatu +678

Venezuela, Bolivarian Republic of Venezuela +58

Vietnam +84

Virgin Islands, British +1284

Virgin Islands, U.S. +1340

Wallis and Futuna +681

Yemen +967

Zambia +260

Zimbabwe +263

By Signing up, you agree to ourTerms & Conditionsand ourPrivacy and Policy

Description

SQL (Structured Query Language) is a standardized computer programming language which is used to maintain relational databases and perform data manipulation as well. It is used to query, insert, update and modify data.

SQL skills are very crucial in most of the job roles. The following set of MySQL interview questions covers the common MySQL interview questions asked. It will guide you to get a better understanding of the concepts related to SQL, Oracle, Database and Server.

Prepare well with the following MySQL interview questions for freshers as well as experienced to crack your interview. These questions discuss the basic as well as the advanced concepts in MySQL, opening a gateway to your dream job! Start your preparation today!

Recommended Courses

Learners Enrolled For

Got more questions? We've got answers.

Book Your Free Counselling Session Today.

MySQL