Introduction
MySQL is a powerful relational database management system (RDBMS). It lets you store massive data for big e-commerce or heavy business activities. If you are an intermediate candidate who have an upcoming interview, then you must prepare well for MySQL interview questions . This article consists of 50 intermediate MySQL interview questions and answers to help you test your skills and boost your confidence.
Must Read: 50 BASIC MYSQL INTERVIEW QUESTIONS Top 50 Intermediate MySQL Interview Questions
1. What are the ways to insert the NULL values?
First, by omitting a column from the column list, and second, by specifying the NULL keywords in the VALUES clause.
2. Which MySQL query do we use to find the second-highest salary from the employee table?
We use the LIMIT clause MySQL to find the second-highest salary:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; 3. Explain the difference between RDBMS, DBMS, and Database.
| Database management systems (DBMS) | Relational database management system (RDBMS) | Database |
|---|---|---|
| It is software used to store, retrieve, and run queries on data. Examples of the DBMS are MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, Oracle Database, and Microsoft Access. | It is a type of DBMS relational model that stores a large amount of data in a table. An example of RDBMS is a SQL server. | It is a collection of data organized in a structured way. It can be a relational database, NoSQL database, or hierarchical database. |
Don't miss out on your chance to work with the best
Apply for top global job opportunities today!
4. What do you mean by self-referencing the foreign key? Write the MySQL query showing self-referencing the foreign key.
A foreign key stored in the table on its own is called a self-referencing one.
CREATE TABLE 'Student'
'name' VARCHAR(30) NOT NULL,
'student_id' CHAR(10) NOT NULL,
'classteacher_id' CHAR(9) NOT NULL,
'Marks' decimal(15, 7) NULL,
PRIMARY KEY ('student_id')
FOREIGN KEY ('classteacher_id') REFERENCE student(student_id) ON DELETE CASCADE 5. What is the function to calculate the average salary?
The AVG function is what we use to calculate the average salary.
SELECT AVG(salary)
FROM employees; 6. What are the four categories of SQL queries?
The four categories are:
Data Definition Language (DDL): It includes the basic SQL commands such as CREATE which creates the databases, tables, view, and schema. and define the structure, columns, data types, and constraints of the object.
- DROP: It permanently deletes a database object.
- ALTER: It allows modification of the structure of existing database objects, by adding or dropping columns in a table.
- TRUNCATE: It deletes all data from a table while keeping its structure unchanged.
- ADD COLUMN: Adds any column to the table schema
Data Manipulation Language (DML): It is used to manipulate the data in the database. The following are the SQL commands that come under DML:
- SELECT INTO: It selects data from one table and then inserts it into another.
- INSERT: It inserts data or records into a table.
- UPDATE: It updates the value of any record in the database.
- DELETE: It deletes records from a table.
Data Control Language (DCL): It manages access rights and permission control. GRANT and REVOKE are the commands that fall under this category where GRANT gives access to database objects and REVOKE withdraws permission from database objects.
Transaction Control Language (TCL): It manages the transactions in a database and makes the changes made by the DML statements. The following are the SQL commands:
- COMMIT: Once the transaction is made it cannot be retrieved.
- ROLLBACK: When an error occurs, it reverts the steps in a transaction.
- SAVEPOINT: It sets a savepoint in the transaction so that rollback can be executed.
- SET TRANSACTION: It sets the characteristics of the transaction.
Must Read: JSON VS. SQL: WHAT’S THE DIFFERENCE? 7. How to delete data from a table in MySQL?
DELETE FROM table_name
WHERE column_name = value_name 8. When do we use the GROUP_CONCAT function and SQL CASE statement?
Whenever we want to concatenate values from multiple rows into a single string, we use the GROUP_CONCAT function. Whereas SQL CASE statement is used to apply the conditional logic in SQL queries.
9. What is the command to log into a MySQL account when using a UNIX shell?
The following is the command:
#[mysql dir]/bin/mysql – hostname –u –p 10. What is the difference between OLTP and OLAP?
| OLTP | OLAP |
|---|---|
| It stands for online transaction processing. | It stands for online analytical processing. |
| It lets a group of people execute massive amounts of database transactions in real time. | It is an online database query response system used for complex queries and data analysis. |
11. Which SQL query do we use to identify the duplicate rows?
To identify the duplicate rows, we use the GROUP BY clause and HAVING:
SELECT column1, column2, COUNT(*) as count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1; 12. What is JOIN?
JOIN combines a row based on a related column between two or more tables. In simple words, it merges two tables and retrieves data from it.
13. Write a MySQL query to perform backup and restore.
For backup, the following is the MySQL query:
mysqldump -u username -p database_name > backup.sql
To restore, the following is the MySQL query:
mysql -u username -p database_name < backup.sql14. Which statement do you use to handle errors in stored procedures?
We use the DECLARE CONTINUE HANDLER statement to handle errors in stored procedures. This statement allows us to specify a handler that can easily deal with specific conditions and exceptions.
15. Write a query to update the column in a NULL row.
UPDATE table_name SET column_name = NULL WHERE condition; 16. Which are the SQL clauses used in conjunction with a SELECT query?
WHERE, ORDER BY, GROUP BY, and HAVING are the clauses that are often used with SELECT query.
SELECT *
FROM myDB.students
WHERE graduation_year = 2024
ORDER BY studentID DESC;
here we use WHERE and ORDER BY with SELECT query.
SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != "LONDON"
GROUP BY country
HAVING COUNT(studentID) > 10;
here we use GROUP BY and HAVING with SELECT query.17. What is the use of the ‘AS’ keyword in MySQL?
The ‘AS’ keyword in MySQL renames a column or table in the result set of a SELECT query.
Also Read: DIFFERENCE BETWEEN SQL VS. NOSQL DATABASE 18. Which SQL query is used to fetch N random records?
For this, we use two MySQL queries i.e., ORDER BY and LIMIT
SELECT column1, column2
FROM table_name
ORDER BY RANDOM()
LIMIT N; 19. What is SQL? And What is it used for?
Structured Query Language (SQL) is a programming language tool used to manage, store, manipulate, retrieve, and analyze data in relational databases. It is widely used by businesses of all sizes including finance, healthcare, data analysis, data science, and e-commerce to manage and analyze large amounts of data and extract insights from data.
20. Write an MySQL query to add a user.
CREATE USER 'test user' IDENTIFIED BY 'sample password';21. How do you calculate the running total?
We use the SUM function and OVER clause:
SELECT date, value,
SUM(value) OVER (ORDER BY date) AS running_total
FROM data; 22. Why is the primary key important in a relational database?
Primary keys help to keep the unique identifier for each row in the table. It is important for a relational database to retrieve and manipulate data efficiently. Another thing is that with the help of the primary key, we can quickly locate specific information and perform operations. Lastly, it maintains data integrity and prevents data duplication and inconsistencies.
23. Write a SQL query to find the missing numbers in a range.
WITH RECURSIVE MissingNumbers AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM MissingNumbers
WHERE number < 100
)
SELECT number
FROM MissingNumbers
LEFT JOIN table_name ON MissingNumbers.number = table_name.column_name
WHERE table_name.column_name IS NULL; 24. Write a command to view the database.
SQL command to view MySQL database is the following:
mysql> SHOW DATABASES; 25. How does the foreign key differ from the primary key? Explain.
A primary key is used to uniquely identify each record in a table, while a foreign key is used to connect between two tables. Let’s assume there are two tables: the Student table and the Marks table. The student table might have a primary key column called StudentID, while the Marks table has a foreign key column called StudentID. This StudentID, we can use to link the two tables to know the marks of the students.
26. Write the MySQL query to display product categories as columns and the total sales for each month.
SELECT *
FROM (
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, product_category, sales_amount
FROM sales
) AS pivoted
PIVOT (
SUM(sales_amount)
FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_sales;27. What is the importance of normalization?
Normalization breaks down complex data into simpler and manageable chunks to avoid data duplication and inconsistencies. Breaking down the table into smaller tables makes it easier to add specific purposes and relationships with each other. This further ensures that each table contains only one type of data and improves database performance. Normalization is crucial to maintain the database design, flexibility, and reduce data redundancy.
28. What is the difference between SQL and MySQL?
| SQL | MySQL |
|---|---|
| SQL is a standard language to extract, delete, and access data from a database. | MySQL is an open-source relational database management system that supports all types of application development. |
| One must understand the basics of SQL before using it. | One can easily download and install MySQL. |
| It was developed by Microsoft Corporation to query and operate a database system. | It is developed by MySQL AB, but it is owned by Oracle Corporation. |
29. How many rows of data MySQL can store?
MySQL row can store a maximum of 65,535 bytes, using the InnoDB and MyISAM storage engines. Even if the storage engine is capable is supporting the rows larger than the limit it can hold up to 65,535 bytes.
30. Show the highest salary from the employee's table and their department.
SELECT department, employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees; 31. How do you handle slow queries in MySQL?
- First set the slow_query_log variable to “ON” and specify the long_query_time threshold to identify queries that take longer than a certain amount of time.
- Use the EXPLAIN statement to display how MySQL executes a query, including the tables and indexes it accesses and the order in which it performs operations.
- Modify the by adding indexes, optimizing joins, or modifying the WHERE clause to reduce the number of rows returned. This will optimize the query and improve performance.
- Adjust the configuration by increasing the size of the buffer pool, or altering query cache size, or the thread cache.
- Lastly, monitor the CPU and memory usage, disk I/O performance, and network bandwidth.
32. Write a query using a recursive CTE to fetch all employees and their managers in a hierarchical structure.
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, employee_name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.employee_name, e.manager_id
FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy; 33. What is the difference between MyISAM and InnoDB?
| InnoDB | MyISAM |
|---|---|
| It is a transactional storage engine. | It is an on-transactional storage engine. |
| It supports ACID properties | It supports transactions, row-level locking, and foreign keys. |
| It is suitable for applications that require transactions and data integrity. | Comparatively faster and simpler. |
34. What are the different types of indexes in SQL?
The following are the three types of indexes:
Unique Index: Once the column is uniquely indexed It does not allow any field to have duplicate values.
Clustered Index: A table can have only one table. The clustered index reorders the values of the table and searches based on key values.
Non-Clustered Index: It maintains a logical order of the data. A table can have many nonclustered indexes.
35. What is the query to analyze the change in sales over time?
To analyze the changes in sales over time, we use LEAD() and LAG (), the following is the proper SQL query:
SELECT order_date, total_sales,
LAG(total_sales) OVER (ORDER BY order_date) AS previous_sales,
LEAD(total_sales) OVER (ORDER BY order_date) AS next_sales
FROM orders; Read MOre: SQL vs. JSON: Key Difference 36. What is the SHOW STATUS command used for?
The ‘SHOW STATUS’ command provides the server an information about the operational state, statistics on the operations performed, and counters for various operation types.
37. Create a database in MySQL?
First, connect to the MySQL server using the command-line client and run this command mysql -u root -p in your terminal. It will ask for your root password.
Once you’re connected create a new database using this statement: CREATE DATABASE database_name
After that Replace the database name with any name that you like to keep for the new database.
CREATE DATABASE mydatabase;
Now, you can verify the database that you have using this statement SHOW DATABASES statement:
SHOW DATABASES;
To exit MySQL, simply press the Ctrl + D and to use the newly created database use this statement: USE mydatabase;
38. Where do we use the COUNT() function?
The COUNT() function is an aggregate function that can be used with a single column, multiple columns, or with a wildcard (*). It is used with the where clause to return the rows with matching criteria.
39. How do we calculate the total sales for each product category from the sales table?
SELECT product_category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_category; 40. What is schema in MySQL?
A schema is a collection of database objects, tables, views, indexes, procedures, functions, and triggers.
41. What is the query to add columns in MySQL?
ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];42. How to install MySQL for MacOS?
For macOS:
- Download the MySQL Community Edition installer for macOS from the official MySQL website.
- Open the downloaded .dmg file and follow the installation instructions.
- During installation, be sure to note the root password as it will be required to connect to your MySQL server.
- After completing the installation, you can manage the MySQL server using the MySQL Workbench, the command line, or macOS preferences
43. Write the MySQL query to fetch the customer's names from the customer tables and their orders from the orders tables.
The following is the SQL query to retrieve the names of customers along with their appropriate orders:
SELECT customers.customer_name, orders.order_id
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id 44. What is the difference between these two operators ‘!=’ and ‘<>’?
These operators signify inequality in MySQL and are interchangeable. These function in the same way as well as return the same results.
45. Write a MySQL query to delete the duplicate from the employee table.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) AS rn
FROM emp
)
DELETE FROM cte WHERE rn > 1; 46. Which sets of set operators MySQL supports?
It supports UNION, UNION ALL, INTERSECT, and MINUS to combine the results of two or more SELECT statements.
47. Write a MySQL query to copy the structure of the table without copying data.
We use the CREATE TABLE LIKE statement that will create a new table based on the structure of an existing table so we don’t have the copy the data in MySQL. MySQL query for this is the following:
CREATE TABLE new_table LIKE old_table;
48. What do you mean by Full-text search in MySQL?
It is a technique to search a text in a collection of documents stored in a database. MySQL comes with the built-in capabilities for performing full-text search operations.
Must Read: What is PostgresSQL?
49. Why do we use EXCEPT Operator? Write its syntax?
EXCEPT Operator is used to return the rows from the first result set. It is order-sensitive like Left Join and Right Join. The following is the syntax for EXCEPT Operator:
SELECT
column_1, column_2
FROM
table_1
EXCEPT
SELECT
column_1, column_2
FROM
table2; 50. What is the difference between PostgreSQL and MySQL?
| PostgreSQL | MySQL |
|---|---|
| It is used for complex queries and data-based reporting where the data structure doesn’t change frequently. | It is a client-server architecture used in web applications. |
| It supports ACID-compliant transactions and uses Multiversion Concurrency Control (MVCC) to handle concurrent transactions. | It uses InnoDB for transactions and MyISAM for speed. InnoDB supports ACID transactions and row-level locking. |
Final Words
MySQL’s widespread adoption has created numerous career opportunities for professionals in the industry. It has been the backbone of numerous websites, applications, and businesses for more than a decade. Over the years, it has undergone numerous changes and adapted to the changing needs. It plays a crucial role in the data-driven world we live in today.
Take control of your career and land your dream job
Sign up with us now and start applying for the best opportunities!
FAQs
MS SQL is a Microsoft SQL Server and MySQL is a structured query language. Both are relational database management systems. MySQL is widely used in web development along with PHP whereas MS SQL is a commercial product with more advanced features, and better integration with Microsoft technologies, and is commonly used in enterprise environments.
MS SQL certification helps to broaden the knowledge and skills in working with SQL databases. Taking a Microsoft SQL server certification helps to get hands-on experience in database design, querying, administration, and optimization. Taking the SQL certification courses can open your job prospects, may get you a raise in salary, and provide a competitive edge in the job market for professionals.
It is used to group rows of more than one column. It is used along with aggregate functions like SUM, COUNT, and AVG to perform calculations on groups of rows rather than individual rows.
Advanced topics such as database normalization, query optimization techniques, stored procedures, triggers, and indexing strategies are asked mostly of experienced candidates. Mysql interview questions and answers for experienced users may also be asked to demonstrate their problem-solving skills through real-world scenarios.
