Posted by Mysql is an essential part of a programmer who works as a web or backend developer(API) using PHP, Java, NodeJs, Python, etc programming languages.
A list of MySQL interview questions and answers are given below, which are commonly asked during the interview. Based on their experience, candidates can be asked basic to advanced-level MySQL programming interview questions. The following list includes all of the most important MySQL questions for both beginners and experienced developers.
What is a relational database management system(RDBMS)?
A relational database management system (RDBMS) is software that is used to store, manage, query, and retrieve data from a relational database. It organizes data into related rows and columns.
What is the Data Integrity of MySQL?
Data integrity refers to the overall accuracy, completeness, and reliability of data. It indicates that your information is error-free.
What are a primary key, foreign key, and unique key, and their difference?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Because of their importance in relational databases, primary keys are the most fundamental of all keys and constraints. A table can only have one Primary key.
Foreign keys are used to ensure data integrity and to identify the link between tables.
The unique key is the same as the primary with the difference being the existence of null. The unique key field allows one value as a NULL value.
What is the difference between Group By & Having Clause in MySQL?
-
- The WHERE clause can be used in with a SELECT, UPDATE, or DELETE Statement Clause, while the HAVING clause can only be used with a Select statement.
- A WHERE clause is used before the GROUP BY clause in the query, but a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause.
- WHERE Clause can be used without GROUP BY Clause while HAVING Clause cannot be used without GROUP BY Clause.
- The WHERE Clause is used in row operations, whereas the HAVING Clause is used in column operations.
What is the MySQL database engine and how many types?
What are the advantages of using InnoDB?
What are triggers in MySQL?
A Trigger is a code that is associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be helpful for maintaining database integrity.
What is the difference between DELETE and TRUNCATE?
The Delete command deletes data from a table based on the condition we specify with a WHERE clause. Truncate removes all of the rows from a database, leaving no data in the table when we run the truncate command. Read more…
What is JOIN in MySQL and explain how many types?
Data is saved in related tables to reduce duplication of data. The join keyword is used to retrieve data from a relational table. When there is at least one match in both tables, join returns rows.
What is indexing in MySQL?
What is LIKE, DISTINCT, GROUP BY, and ORDER BY command in MySQL?
How can we start a column with 1000 in MySQL or How to change the auto-increment value?
| 1 | ALTER TABLE table_name AUTO_INCREMENT = 1000; |
Find the employee’s record with the highest salary.
To find the employee’s record with the highest salary in MySQL, you can use the following query:
| 1 2 3 4 5 6 | SELECT * FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees ); |
Find nth salary of the employee
To find the nth salary of employees in MySQL, you can use the following query:
| 1 2 3 4 | SELECT salary FROM employees ORDER BY salary DESC LIMIT n-1, 1; |
Replace n with the desired position of the salary you want to retrieve. For example, if you want to find the 5th highest salary, you would replace n with 5.
Change fields from Male to Female or Female to Male | 1 to 0 or 0 to 1 in the field.
Here is an example to change Male to Female and Female to Male in MySql:
| 1 2 3 4 5 | UPDATE table_name SET field_name = CASE field_name WHEN 'Male' THEN 'Female' WHEN 'Female' THEN 'Male' END; |
Here’s an example to change the values of a field from 1 to 0 or 0 to 1 in MySql:
| 1 2 3 4 5 | UPDATE table_name SET field_name = CASE field_name WHEN 1 THEN 0 WHEN 0 THEN 1 END; |
Find Duplicate entries in the MySQL table.
Here is an example to find the duplicate entries:
| 1 2 3 4 | SELECT column1, column2, ..., count(*) FROM table_name GROUP BY column1, column2, ... HAVING count(*) > 1; |
Delete Duplicate Entries in the MySQL table.
| 1 | SELECT *, count(*) as count FROM users group by email having count > 1; |
It will show the old duplicate data
| 1 2 3 | SELECT u1.* FROM users AS u1 INNER JOIN users AS u2 WHERE u1.id < u2.id and u1.email = u2.email |
It will delete the old duplicate data:
| 1 2 3 | DELETE u1.* FROM users AS u1 INNER JOIN users AS u2 WHERE u1.id < u2.id and u1.email = u2.email |
If you want to delete the latest duplicate data based on the email use the below query:
| 1 2 3 | DELETE u1.* FROM users AS u1 INNER JOIN users AS u2 WHERE u1.id > u2.id and u1.email = u2.email |
How to create a function(User Define Function) in SQL?
Timestamp vs DateTime in MySQL
In MySQL,
TIMESTAMP
and
DATETIME
are both data types used to store date and time values. However, there are a few differences between the two:
-
TIMESTAMP:- The
TIMESTAMPdata type stores date and time values in the formatYYYY-MM-DD HH:MM:SS. - It has a range of
'1970-01-01 00:00:01' UTCto'2038-01-19 03:14:07' UTC. - It can store the time zone information.
- It is a compact format and takes up less space in the database compared to the
DATETIMEdata type.
- The
-
DATETIME:- The
DATETIMEdata type stores date and time values in the formatYYYY-MM-DD HH:MM:SS. - It has a range of
'1000-01-01 00:00:00'to'9999-12-31 23:59:59'. - It does not store the time zone information.
- It is more versatile compared to the
TIMESTAMPdata type and can be used for a wider range of use cases.
- The
What will happen If we use it two times ORDER BY in a MySQL query?
What is the difference between char and varchar in MySql?
In MySQL, “char” and “varchar” are also used to store character strings. The main difference between them is the same as in other relational databases:
- “char” is a fixed-length data type and will always occupy the same amount of storage space, regardless of the length of the stored string. The length of a “char” column is specified in parentheses when creating the table, with a maximum length of 255 characters.
- “varchar” is a variable-length data type and will only use the amount of storage space needed to store the actual data. The maximum length of a “varchar” column is specified in parentheses when creating the table, with a maximum length of 65,535 characters.
In MySQL, the actual storage size of a “varchar” column is determined by the length of the string it stores, plus one or two bytes to indicate the length of the string. For example, a “varchar(10)” column will occupy 11 bytes for each stored string.
It is recommended to use “varchar” over “char” whenever possible, as it is more efficient in terms of storage space and can also be faster for queries that involve sorting or comparison of string values.
What is the MySql transaction and why do you use it?
A database transaction is a collection of database actions that must be handled as a whole, which means that either all or none of them are executed. A financial transfer from one account to another is an example. Either both debit and credit operations must be performed, or none of them. ACID (Atomicity, Consistency, Isolation, and Durability) is a set of properties that ensure database transactions are performed reliably.
What is the view in MySql?
A view is a virtual table that is created from the results of a SQL statement. We use the CREATE VIEW syntax to create the view.