MySQL Interview Questions and Answers
MYSQL is one of the most successful and leading pathways toward data administration careers. You will be amazed to know that this database service holds a total of 46.83% share in the relational database market. So to make your career and prepare for interviews in this field would be a good choice. Today we will help with these interviews by providing you with some MYSQL Interview Questions and Answers. MYSQL is one of the most popular database technologies used in the database industry. Popular companies like Microsoft, Amazon, Oracle, and IBM are using this technology on a vast basis to manage and store their data. According to one survey, nowadays more than 90% of organizations are using OSDBMS i.e. Open Source Database Management System. But to handle this database, professionals must know the MYSQL programming language.
Quick Facts About MySQL | |
---|---|
What is the latest version of MySQL? | 8.0.22 released in 19th October 2020 |
When was MySQL first released? | 23 May 1995 |
Who developed MySQL? | Oracle Corporation. |
What language does MySQL use? | C and C++ language |
About License | GNU Public License |
Official Website | https://www.mysql.com |
MySQL Query Interview Questions
DELETE | DROP | TRUNCATE |
---|---|---|
It removes Some or All rows from a table. | It removes a table from the database. | It removes all rows from a table. |
A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query. | It does not require a WHERE clause. | It does not require a WHERE clause |
The DELETE command is Data Manipulation Language Command | The DROP command is Data Definition Language Command. | The TRUNCATE command is a Data Definition Language Command. |
The DELETE command is more sluggish as compared to the DROP, and the TRUNCATE commands as it removes the rows according to the condition. | The DROP Command offers better performance than the DELETE Command but not as in comparison to the Truncate Command since the DROP command erases all tables from databases immediately after the rows have been deleted. | The TRUNCATE command performs better that the DROP or DELETE because it removes all records from the table with no condition. |
The Integrity Constraints remain same for the DELETE command. | The Integrity Constraints disappear with this DROP command. | The Integrity Constraints are not removed from the TRUNCATE command. |
DELETE operation can be rolled back. | DROP operation cannot be rolled back. | TRUNCATE operation cannot be rolled back. |
DELETE FROM TableName WHERE condition; |
DROP TABLE table_name; |
TRUNCATE TABLE table_name; |
id | first_name | middle_name | last_name | phone |
---|---|---|---|---|
101 | Ram | Krit | Singh | 9873393635 |
102 | Lalit | Kumar | Verma | 9873393636 |
103 | Shyam | Singh | Verma | 9873393637 |
SELECT CONCAT(first_name, ' ', middle_name, '', last_name) as name FROM `customers`;
These are most commonly used storage engines in MySQL are MyISAM and InnoDB.
Difference between MyISAM and InnoDB
- MyISAM no longer supports transactions, however, InnoDB supports transactions.
- MyISAM helps Table-level Locking, however, InnoDB supports Row-level Locking.
- MyISAM helps full-text search, however InnoDB does not.
- MyISAM is designed for the need for speed but InnoDB is designed for the most performance.
- MyISAM does now not aid overseas keys, but InnoDB helps foreign keys.
- We can use commit and rollback with InnoDB however not in MyISAM.
- MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation, and Durability) however InnoDB supports the ACID property.
- In the InnoDB table, the AUTO_INCREMENT field is a section of the index but is now not in MyISAM.
- MyISAM stores its tables, data, and indexes in the disk area with the usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.
Primary Key | Unique Key |
---|---|
A table can hold only one primary key | It can be more than one unique key in one table |
A Primary Key cannot be NULL. | A Unique key can have NULL. |
SELECT COUNT(*) FROM customers WHERE created_at BETWEEN '2022-03-24 17:32:05' AND '2022-03-25 17:32:05';
- Don't use "sp_" while creating a stored procedure.
- Don't use "*" while you are using the SELECT command. Also avoid unnecessary columns in SELECT clause.
- You can use table aliases while writing queries.
- Avoid the usage of the wildcard (%) at the starting of a predicate.
- DISTINCT and UNION need to be used solely if it is necessary.
SELECT * FROM customers WHERE name LIKE 'A%';
id | name | salary | |
---|---|---|---|
101 | Umesh Singh | [email protected] | 20000 |
102 | Sonu Singh | [email protected] | 10000 |
103 | Avinash | [email protected] | 7000 |
104 | Manish | [email protected] | 60000 |
105 | Rekha | [email protected] | 5000 |
106 | Shiv | [email protected] | 20000 |
SELECT name, email, min(salary) as minSalary, max(salary) as maxSalary from employee;
SELECT name, salary FROM Employee AS emp1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee emp2 WHERE emp2.salary > emp1.salary);
Table: customers
id | name | |
---|---|---|
101 | Umesh Singh | umesh.singh |
102 | Sonu Singh | [email protected] |
103 | Avinash | [email protected] |
104 | Manish | bestinterviewquestion.com |
106 | Shiv | [email protected] |
SELECT * FROM `customers` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';
SELECT id, name, email, salary, IF(salary > 20000, 'Good', 'Average') AS type FROM `customers`;
A database index is the structure of a fact that improves the velocity of operations in a table. It can be created the usage of one or greater columns, presenting the foundation for each fast random lookups and efficient ordering of getting admission to records. Indexes are used to locate rows with specific column values quickly.
CREATE INDEX [index name] ON [table name]([column name]);
Employee
id | name | salary | |
---|---|---|---|
101 | Umesh Singh | [email protected] | 20000 |
102 | Sonu Singh | [email protected] | 10000 |
103 | Avinash | [email protected] | 7000 |
104 | Manish | [email protected] | 60000 |
105 | Rekha | [email protected] | 5000 |
106 | Shiv | [email protected] | 20000 |
For this, we can use the between operator with a where clause.
SELECT id, name, salary FROM Employee WHERE salary BETWEEN 5000 AND 25000;
NOW | CURRENT_DATE |
---|---|
NOW() give you the current date time in the format 'YYYY-MM_DD HH:MM: SS' | CURRENT_DATE() will only give you the current date in format "YYYY-MM_DD" |
Comparisons operators are used to comparing one expression to another value or expression. It is just like = , < , > , => , =<, <>
The standard way to perform case insensitive queries in SQL is to use the SQL upper or lower functions like the following:
select * from users where upper(first_name) = 'AJAY';
OR
select * from users where lower(first_name) = 'ajay';
The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.
1. DDL
In MySQL, DDL is the short form for Data Definition Language, which is used in database schemas and descriptions while deciding how data should reside in the database.
Here’s a list of DDL Queries:
- CREATE
- ALTER
- DROP
- TRUNCATE
- COMMENT
- RENAME
2. DML
DML is a short form for Data Manipulation Language which is used in data manipulation and mostly includes common SQL statements to store, modify, retrieve, delete and update data in a database.
Here is the list of DML Queries:
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
- CALL
- EXPLAIN PLAN
- LOCK TABLE
3. DCL
DCL is a short form for Data Control Language including commands which are concerned with User rights, permissions and other controls within the database system.
Here’s a list of queries for DCL:
- GRANT
- REVOKE
MySQL -u username -p database_name < file.sql
Here username is your database username like "root," database_name is your database name, file.sql is your SQL file name with complete path.
It is used to concatenate two or more strings.
SELECT CONCAT('BestInterview', ' ', 'Question') AS 'Name';
Table: Employee
id | name | |
---|---|---|
101 | Umesh Singh | [email protected] |
102 | Sonu Singh | [email protected] |
103 | Avinash | [email protected] |
104 | Manish | [email protected] |
105 | Rekha | [email protected] |
106 | Shiv | [email protected] |
107 | Manish | [email protected] |
108 | Sonu Singh | [email protected] |
The following SQL query can be used to obtain unique or different values for a column in MySQL Table. There are different values that can be assigned to one or more columns. You must separate the column names with a comma.
Syntax
SELECT DISTINCT(column_name) FROM your_table_name;
Example
SELECT DISTINCT(email) FROM Employee;
The default port is 3306. We can change it in /etc/MySQL/my.conf
there is a port variable. We can update this port according to our need
IS NULL
checks to see if the cell is empty but IS NOT NULL
checks to see if the cell is not empty.
Example :
SELECT id FROM users WHERE 'user_type' IS NOT NULL;
SELECT id FROM users WHERE 'user_type' NOT IS NULL;
Note: This is very essential MySQL query interview questions.
In MySQL, standards-based drivers for JDBC, ODBC, and .Net are provided in order to enable developers in building database applications with their language of choice.
Available Drivers in MySQL:
- PHP Driver
- JDBC Driver
- ODBC Driver
- C WRAPPER
- PYTHON Driver
- PERL Driver
- RUBY Driver
- CAP11PHP Driver
- Ado.net5.mxj
JDBC, ODBC, and .Net drivers are necessary for MySQL as they enable the developers for building their database applications.
Primary Key | Candidate Key |
---|---|
It is a column that uniquely identifies a record. In Mysql, only one candidate key can behave like Primary Key. | It can be any column that can qualify as a unique key in the database. In MySQL, there can be multiple candidate keys in one table. Each candidate key can behave like as a primary key. |
In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table.
It can be performed when you run one of the following MySQL like INSERT, UPDATE and DELETE occurred in a table. Its activation time can be BEFORE or AFTER.
Types of Triggers in MySQL
- Before Insert: This is done before inserting data into the table.
- After Insert: This is after data has been entered into the table.
- Before update: This is activated prior to the updating of the data in the table.
- After update: This function is activated upon the updating of the table's data.
- Before delete: This is done before any data are removed from the table.
- After delete: This function is activated when the data has been deleted from the table.
What is the purpose of triggers in MySQL?
- It is easy to set up triggers.
- Triggers allow us to validate data before it is inserted or updated.
- Triggers allow us to keep track of records, such as audit trails for tables.
- Triggers increase the performance of SQL queries as it doesn't need to compile every time the query executes.
- Triggers can be used to reduce client-side code, which saves time and effort.
- It allows us to scale our applications across multiple platforms.
- SQL triggers are an alternative method to verify the integrity of data.
- Triggers are an alternative method to perform the task.
Syntax
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--trigger code // You can write here your code
END;
delimiter //
CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;
delimiter ;
Mysql is not case sensitive. Its case-sensitivity relies on the underlying operating system as OS determines the case sensitivity of tables names and database. In windows, database and table names are not case sensitive but in the case of UNIX, it is case sensitive in nature. Especially on the UNIX host, database accepts the upper case and lower-case table names.
SELECT id, name FROM users WHERE phone IS NULL;
To insert new records into a table, use the INSERT INTO statement. You can write the INSERT INTO sentence in one of two ways.
- You will need to specify both column names and values that you wish to insert:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
- You don't need to include the column names in your SQL query if you are adding values to all columns in the table. But, ensure that the order of your values matches the order of the columns in the table. The INSERT INTO syntax is as follows:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example
INSERT INTO Customers (name, email, salary) VALUES ('MySQL Query Interview Questions', '[email protected]', 15000);
In MySQL, the time period cardinality refers to the specialty of facts values that can be put into columns. It is a type of property that influences the potential to search, cluster and kind data.
Cardinality can be of two sorts which are as follows
- Low Cardinality − All values for a column have to be the same.
- High Cardinality − All values for a column ought to be unique.
Binary data can be stored in Mysql in different ways such as
- The short length of binary data can be stored in the form of string as a VARCHAR
- A BLOB data type allows users to store an arbitrary amount of binary data.
Regex is an operational and powerful pattern that can help the users for the implementation of a powerful search utility function for database systems. Moreover, it is an operator which is used when we require matching regular expressions. In addition to this, it also supports a wider range of meta-characters which allow flexibility and more control over the database structure when performing pattern matching.
Match beginning of string(^):
SELECT name FROM users WHERE name REGEXP '^bestinterviewquestion';
UPDATE `your_table_name` SET new_field=old_field
LENGTH() | CHAR_LENGTH() |
---|---|
It returns the total length of the string which is measured in bytes. | It returns the total length of the strings measured in characters. |
In MySQL, an ENUM is actually a string object whose value is selected from the list of permitted values pre-defined during the time of a column creation. It is used because it provides compact data storage in addition to readable queries and output.
Here’s the difference between an ENUM and a SET
ENUM | SET |
---|---|
The value should be one listed in the column definition or internal numeric equivalent. | Must be an empty string consisting of values listed in column |
mysql_fetch_array() | mysql_fetch_row() | mysql_fetch_object() |
---|---|---|
This returns an array of strings corresponding to a fetched row, or FALSE if there are no rows. | It returns with a numerical array of strings corresponding to a fetched row, or FALSE if there are no rows. | This returns with an object, or a FALSE value if there are no rows. |
The returned array type depends on how result_type is defined | Here, the row is returned as an array. | This fetches a result row as an object type. |
Yes, the primary key can be dropped in MySQL with the use of the “ALTER TABLE” statement.
Syntax:
ALTER TABLE table_name DROP PRIMARY KEY
Images in MySQL can be stored as blobs. For saving them: All the database images are converted into the blobs first. Then, they will get inserted into the database, and later on, it will get stored into the disk.
To write an optimized query in MySQL, below are some guidelines:
- Functions should not be used in predicates.
- At the beginning of predicates, do not use the wildcard symbols such as %.
- Use only needed columns in the SELECT clause.
- Always use the inner join option.
- Use of the “Order by” clause is necessary for SQL, in case of assuming the sorted results.
In general slow query, the log is basically used in Mysql for the determination of which database queries will take a long duration to run. Moreover, a slow query log in MySQL simplifies that operations in the context of efficient and time-consuming queries.
In order to enable the slow query log the command- Get global slow_query_log – ‘ON’; is used
There are a number of unique features that are incorporated in Mysql 8 include-
- Unicode 9.0 support
- Window functions
- Recursive SQL syntax statements
- Support Native JSON data
- Support for document store Functionality
It is a table that points to a table in another MySQL database instance. It can be seen as a view of this remote database. Other RDBMS have the same concepts for example database links.
The maximum size of the HEAP table can be controlled by the MySQL config variable called max_heap_table_size.
It is a language but not a Programming Language. It is a relational database management system.
- Data Security
- It is used to retrieve large amounts of records from a database quickly and efficiently.
- On-Demand Scalability
- High Performance
- Excellent Uptime
- Reduced Total Cost of Ownership
Storage engines are Mysql components, that can handle the operations for different table types to store and manage information in a database.
- InnoDB
- MyISAM
- MEMORY
- MERGE
- EXAMPLE
- CSV
- ARCHIVE etc
ALTER TABLE EnterTableName ENGINE = EnterEngineName;
SELECT id, GROUP_CONCAT(department SEPARATOR ' ') AS department FROM employee group by id;
Between operator is used to select a range of data between two values. It can be texts, numbers, and dates, etc.
Syntax: Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2
IN operator is used to check for a value in the given set of values.
Syntax: Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)
You can use this query in your PHPMyAdmin SELECT version();
The MySQL aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.
MySQL provides many aggregate functions that are listed below.
- AVG()
- COUNT()
- SUM()
- MIN()
- MAX() etc
Function | Procedure |
---|---|
The function returns the single value which is anyhow mandatory. | The procedure returns zero or N values. |
They only work with a statement: Select | They can work with multiple statements: Insert, Update, Delete, Select. |
In function, transactions are not possible | In the procedure, transactions are possible |
Error handling is not possible | With try-catch, error handling is possible. |
In Mysql, joins are used to retrieve data from multiple tables. It is performed whenever two or more tables are joined.
There are three types of MySQL joins:-
- MySQL INNER JOIN
SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;
- MySQL LEFT JOIN
SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;
- MySQL RIGHT JOIN
SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;
- CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
- CHAR field is a fixed length, but VARCHAR is a variable length field.
- CHAR uses static memory allocation, but VARCHAR uses dynamic memory allocation.
SELECT std_id, COUNT(std_id) as cnt FROM Student GROUP by std_id having cnt > 1
SELECT id, name, department FROM Employee where MOD(id,2) = 1
The stored procedure is like a subprogram in a typical computing language which is stored in the database. A stored procedure contains the name, list of parameters, and the SQL statements. All the relational database system works as pillars for stored procedures.
In this example, we are creating a simple procedure called job_data, when this procedure will get executed, all the data from "jobs" tables will get displayed.
DELIMITER //
CREATE PROCEDURE GetAllPages()
BEGIN
SELECT * FROM pages WHERE title LIKE '%MySQL Interview Questions%';
END //
DELIMITER ;
SELECT * FROM Student where MOD(id,2) = 0
SELECT * FROM 'TableName' WHERE 'status' = 1 LIMIT 10
SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;
Using INTERSECT
SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary
WHERE term is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.
WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.
These are the set of rules applied to columns on the table. It is used to bound the type of data that can go into a table.
Types of constraints
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE:
- Not NULL
select count(*) from TABLE_NAME where 'status' = 1;
HEAP tables are in memory. No TEXT or BLOB fields are allowed inside HEAP tables. They are typically used for high-speed transient storage.
In MySQL, a temporary table is a distinct type of table that approves you to save a transient end result set, in which you can reuse countless instances in a single session. A brief table is created with the aid of the use of the CREATE TEMPORARY TABLE
statement.
It is the best way to upload large CSV files through the LOAD DATA command.
Syntax:
LOAD DATA LOCAL INFILE "your file name" INTO TABLE table name FIELDS TERMINATED by ’,’ LINES TERMINATED BY ’\n’
The degree of a table is the number of columns in the given table. It is also called as Arity. It is the range of entities inside each entity type that can be linked via a given relationship type.
Here’s a code script to convert string data to UTF 8 in your database:
header('Content-Type: text/plain; charset=utf-8');
$databasesToConvert = [ 'database1' */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];
CONVERT(CAST(CONVERT(`{$column}` USING english) AS binary) USING utf8)";
$query = "UPDATE `{$table}` SET " . join( ', ', $converts );
echo "\n", $query, "\n";
echo "\n";
Timestamp in MySQL
Timestamp in MySQL is used when users want to convert the value from the current time zone to UTC zone for the sake of storage or UTC time zone to the current time zone for retrieval.
DateTime in MySQL
DateTime in MySQL is used to insert values ‘yyyy-mm-dd’ in the table where yyyy is the year, mm is the month and dd is the date. This keyword is used to retrieve or to display DATETIME values in Mysql.
The view is basically a virtual table which is used to view certain and preferable rows or columns of the table. In practice, users can add a number of SQL functions, JOIN and WHERE clause to view the present data of the table.
In MySQL, the CREATE VIEW statement is used to create a new view inside the database.
Here’s the basic syntax of the statement:
CREATE VIEW productList AS SELECT qty, price, totalprice AS value FROM product;
Conclusion
In MYSQL Database, data is stored in tabular form and that is why we also call it a Relational Database Management System. With the help of it, users can develop an infinite number of applications but it is mainly used for web applications. Talking about platforms, it can run on various platforms like LINUX, UNIX, and WINDOWS and is reliable, fast, and very easy to use. We hope these questions will help you to clear your core concepts regarding MYSQL. Let us help you with some short and important tips which might help you with your interviews-
- MYSQL is a programming language, so always avoid YES and NO answers.
- If you don’t know the answer, simply say I don't know rather than pretending you know.
- Always carry a pen and notepad with you.
- If you are stuck somewhere don’t panic, just stay calm and think out loud.
- Go with the expectation that you will reject a lot, as “Failure is the key to Success”.