Top MySQL Interview Questions and Answers by Shiksha Online

MySQL is an open-source relational database management system (RDBMS) by Oracle. It runs across platforms like Linux, UNIX, and Windows, and allows users to manage relational databases. Opportunities are extensive in the field of MySQL, and companies are ready to pay the professionals with the right skill sets and knowledge.

MySQL won the DB-Engines award in the year 2019 among 350 monitored database systems. According to Gartner’s State OpenSource DBMS Market Research Report, more than 70% of new in-house applications will be developed on Open Source Database Management Systems (OSDBMS) till 2022, and as per the survey more than 89% of the organizations already use an OSDBMS.

We are here to help you crack your job interview and curated some of the most popular MySQL interview questions. Go through them to succeed in your interview.

Most important MySQL Interview Questions and Answers by Shiksha Online

Q1. What is RDBMS?

Ans. Relational Database Management System or RDBMS is based on the relational database model and is among the most popular database management systems.

Q2. What is collation sensitivity?

Ans. Collation sensitivity defines the rules to sort and compare the strings of character data, based on correct character sequence, case-sensitivity, character width, and accent marks, among others.

Q3. What are the different types of collation sensitivity?

Ans. There are four types of collation sensitivity, which include –

• Accent sensitivity
• Case sensitivity
• Kana sensitivity
• Width sensitivity

Q4. What is COALESCE?

Ans. COALESCE returns the first non-NULL expression within its arguments from more than one column in the arguments. The syntax for COALESCE is –

COALESCE (expression 1, expression 2, … expression n)

Q5. What is “scheduled job” or “scheduled task”?

Ans. Scheduled job or task allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.

Q6. Can you name different types of SQL commands?

Ans. Yes. SQL commands are divided into following –

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Data Control Language (DCI)
• Transaction Control Language (TCL)

Q7. Name different DDL commands in MySQL.

Ans. DDL commands include –

• CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
• DROP – Used to delete objects
• ALTER – Used to change database structures
• TRUNCATE – Used to erase all records from a table, excluding its database structure
• COMMENT – Used to add comments to the data dictionary
• RENAME – Used to rename a database object

Q8. Name different DML commands in MySQL.

Ans. DML commands include –

• SELECT – Used to select specific database data
• INSERT – Used to insert new records into a table
• UPDATE – Used to update existing records
• DELETE – Used to delete existing records from a table
• MERGE – Used to UPSERT operation (insert or update)
• CALL – Used to call a PL/SQL or Java subprogram
• EXPLAIN PLAN – Used to interpret data access path
•  LOCK TABLE – Used to control concurrency

Q9. Name different DCL commands in MySQL.

Ans. DCL commands are –

• GRANT – Used to provide user access privileges to the database
• DENY – Used to deny permissions to users
•  REVOKE – Used to withdraw user access by using the GRANT command

Q10. Name different TCL commands in SQL.

Ans. DCL commands include –

• COMMIT – Used to commit a transaction
• ROLLBACK – Used to roll back a transaction
• SAVEPOINT – Used to roll back the transaction within groups
• SET TRANSACTION – Used to specify transaction characteristics

Q11. What are the different types of Database Relationship in SQL?

Ans. There are three types of Database Relationship –

• One-to-one – Both tables can have only one record
• One-to-many – The single record in the first table can be related to one or more records in the second table
• Many-to-many – Each record in both the tables can be related to any number of records

Q12. What is Normalization?

Ans. Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.

Q13. What are the different types of Normalization?

Ans. There are six different types of Normalization –

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)

Q14. What is Denormalization?

Ans. Denormalization is a database optimization technique for increasing a database infrastructure performance by adding redundant data to one or more tables. 

Q15. Can you implement ‘AND’ with NOT (!) Operator?

Ans. Yes, we can use the AND operator when (=) is used, and the operator OR can be used when (!=) is used. 

Q16. What is the difference between a clustered and non-clustered index?

Ans. Clustered Index – A clustered index is used to order the rows in a table. It has leaf nodes consisting of data pages. A table can possess only one clustered index.

Non-clustered index – A non-clustered index stores the data and indices at different places. It also has leaf nodes that contain index rows. A table can possess numerous non-clustered indexes.

Q17. What is ERD?

Ans. ERD or Entity Relationship Diagram is a visual representation of the database structures and shows a relationship between the tables. ERD or Enhanced Entity Relationship Diagram is the advanced version of ERD and has added functionalities.

Q18. How will you find the unique values, if a value in the column is repeatable?

Ans. To find the unique values when the value in the column is repeatable, we can use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users. We can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

Q19. How do you return a hundred books starting from 15th?

Ans. SELECT book_title FROM books LIMIT 15, 100. The first number in LIMIT is the offset, and the second is the number.

Q20. What will the query select all teams that lost either 1, 3, 5 or 7 games?

Ans. We will use-

SELECT team_name FROM teams WHERE team_lost IN (1, 3, 5, 7)

Q21. How will you delete a column?

Ans. We can delete a column by –

ALTER TABLE techpreparation_answers DROP answer_user_id.

Q22. What is the meaning of this query – Select User_name, User_isp From Users Left Join Isps Using (user_id)?

Ans. It means SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

Q23. How will you see all indexes defined for a table?

Ans. By using:

SHOW INDEX FROM techpreparation_questions;

Q24. How would you change a table to InnoDB?

Ans. By using:

ALTER TABLE techpreparation_questions ENGINE InnoDB;

Q25. Name the default port for MySQL server.

Ans. The default port for MySQL server is 3306.

1. Data Science Certification
2. Cloud Computing Certification
3. ML & Artificial Intelligence certification
4. IT & Development Certification
5. Marketing & Sales certification
6. Finance & Accounting certification
7. Human Resource Certification
8. Leadership & Management certification
9. Logistics & Supply Chain Management Certification

Q26. What is the possible way to add five minutes to a date?

Ans. By using:

 ADDDATE(techpreparation_publication_date, INTERVAL 5 MINUTE)

Q27. What is the possible way to convert between Unix timestamps and Mysql timestamps?

Ans. Example:

• UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp
• FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp

Q28. How do you implement Enums and sets internally in MySQL? 

Ans. To implement an ENUM column, use the given syntax: 

CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );

Q29. What is the procedure to start and stop Mysql on Windows?

Ans. We can start and stop Mysql on Windowsnet by using start MySQL, net stop MySQL.

Q30. What is the use of tee command in Mysql?

Ans. Tee followed by a filename turns on MySQL logging to a specified file. It can be paused by a command note.

Q31. Is it possible to save your connection settings to a conf file?

Ans. Yes, it is possible, and you can name it ~/.my.conf. You can also change the permissions on the file to 600 so that it’s not readable by others.

Q32. How to convert numeric values to character strings?

Ans. We can convert numeric values to character strings by using the CAST(value AS CHAR) function, as shown in the following examples:

SELECT CAST(4123.45700 AS CHAR) FROM DUAL;

4123.45700

Q33. Use mysqldump to create a copy of the database?

Ans. mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

Q34. What are federated tables?

Ans. Federated tables allow access to the tables situated on other databases on other servers in MySQL 5.0

Q35. Mention the groups of data types?

Ans. There are three groups of data types in MySQL, as listed below:

• String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.
• Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.
• Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.

Q36. What is the procedure to concatenate two character strings?

Ans. To concatenate various character strings into one, you can use the CONCAT() function. Example:

SELECT CONCAT(’Naukri’,’ Learning’) FROM DUAL;

Shiksha Online

SELECT CONCAT(‘Learner’,’Thing’) FROM DUAL;

Learner Thing

Q37. What is the procedure to change the database engine in Mysql?

Ans. By using:

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

Q38. What is the default storage engine in MySQL?

Ans. InnoDB is the default storage engine in MySQL.

Q39. What syntax can we use to get a version of MySQL?

Ans. By using the given query in your phpmyadmin-

SELECT version();

Q40. What syntax is used to create an index in MySQL?

Ans. By using-

CREATE INDEX [index name] ON [table name]([column name]);

Hope this article Most important MySQL Interview Questions and Answers by Shiksha Online will help you with your future interview.

Download PDF

If you have recently completed a professional course/certification, click here to submit a review.