1. What are the features of MySQL?
MYSQL is open-source, has very rich documentation, a wide range of interfaces and functions for application programming, and has many stored procedures like triggers and cursors that help in managing the database. Also, the Query optimizer is very efficient in MySQL.
2. Name the benefits and drawbacks of MySQL?
There are various advantages and drawbacks of using MySQL. a number of them are given below:
- MySQL helps within the secure management of databases. By using it, we will securely execute database transactions.
- It is fast and efficient as compared to other management systems because it supports sorts of storage engines.
- MySQL can execute many queries due to high transaction processing.
- Besides, a number of the features that make MySQL unique are deadlock identification, execution of multiple transactions, efficient processing, and straightforward management.
- Scalability in MySQL may be a redundant task.
- MySQL serves good for giant databases mostly.
- There are problems with the instability of software.
3. What do DDL, DML, and DCL stand for?
DDL is the abbreviation for Data Definition Language dealing with database schemas, as well as the description of how data resides in the database. An example of this is the
CREATE TABLE command. DML denotes Data Manipulation Language which includes commands such as
SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like
GRANT, REVOKE, etc.
4. In which language MySQL has been written?
MySQL is written in C and C++, and its SQL parser is written in yacc.
5. What is the difference between MySQL and SQL?
- SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.
- A PHP script is required to store and retrieve the values inside the database.
- SQL is a computer language, whereas MySQL is a software or an application
- SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting, and modifying data.
6. How to get the current MySQL version?
We will use the below query to get the current version of MySQL
7. What are the TRIGGERS that can be used in MySQL tables?
Following TRIGGERS are allowed in MySQL:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
8. What is the difference between LIKE and REGEXP operators in MySQL?
LIKE is denoted using the ‘
%’ sign. For example:
SELECT * FROM user WHERE user name LIKE “%NAME”
On the other hand, the use of
REGEXP is as follows:
SELECT * FROM user WHERE username REGEXP “^NAME”;
9. What is the difference between the DELETE TABLE and TRUNCATE TABLE commands in MySQL?
DELETE TABLE is a logged operation, and every row deleted is logged. Therefore, the process is usually slow.
TRUNCATE TABLE also deletes rows in a table, but it will not log any of the rows deleted. The process is faster here in comparison.
TRUNCATE TABLE can be rolled back and is functionally similar to the
DELETE statement without a WHERE clause.
10. What are the two methods of doing INNER JOINs in MySQL?
The inner join is also called a natural join. Inner join adds the records to the resultset only if both the tables have the record with the matching condition. In other words, it checks for equality.
We can do an inner join using the keywords INNER JOIN in the from clause of a query. Whenever we have a query where we check the equality of two columns, we are essentially doing an Inner join.
Select * from actor inner join customer using(first_name); Select * from film f INNER join film_category fc ON f.film_id = fc.film_id;
11. What is meant by transaction?
- A Transaction in SQL is a single logical unit of work.
- Transactions can make multiple changes to the database like create tables, delete records, update records etc in the same database transaction.
- The transaction is successful when all the operations in it complete and are committed which means that the changes are committed to memory i.e. made permanent.
- A transaction fails if any of the operations in it fail and cause a rollback. A rollback means that the changes are discarded and are not permanent.
- Commit or Rollback is either implicit or explicit.
- In MySQL, transactions with DDL instructions like
CREATE, ALTER, RENAME, DROPand
TRUNCATEare implicitly committed. The changes once made are permanent and users cannot control this.
- Users can switch off auto-commit for DML instructions like
INSERT, UPDATE, DELETEand
- Transactions follow the ACID properties.
12. What are ACID properties?
All database systems have ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. They ensure that all operations on the data are processed correctly and the database has consistency and reliability.
13. What are the differences between a primary key and a foreign key?
|Primary Key||Foreign Key|
|It helps in the unique identification of data in a database||It helps establish a link between tables|
|There can be only one primary key for a table||There can be more than one foreign key for a table|
|Primary key attributes cannot have duplicate values in a table||Duplicate values are acceptable for a foreign key|
|Null values are not acceptable||Null values are acceptable|
|We can define primary key constraints for temporarily created tables||It cannot be defined for temporary tables|
|The primary key index is automatically created||The index is not created automatically|
14. How do we create a user in a database?
To create a database user we use the create user statement. The Syntax is,
CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ‘password’;
The account_name consists of two parts: username followed by the @ sign followed by the hostname. The hostname is optional.
15. How to drop a user in a database?
To drop a user we use the
DROP USER statement. The Basic syntax for dropping a user is,
DROP USER [IF EXISTS] <account_name>;
IF the user has a hostname we have to include it while deleting the user.
16. What are the three main categories of datatypes MySQL supports?
There are three main categories of datatypes that MySQL allows. They are:
- Number/Numeric datatype
- Date datatype
- String/Text/Character datatype
17. If a database with a name testdb exists and we try to re-create it. What happens?
If we try to create a database that already exists, MySQL throws an error ”Cannot create Database <dbname>; database exists”.
To reduce the severity of the error to a warning, we can use the command
CREATE DATABASE [IF NOT EXISTS] <db-name>
18. What are the privileges needed to create a table?
We need CREATE Privileges to create a table in MySQL.
19. What is the difference between the unique and primary key constraints?
|Primary Key||Unique Key|
|Unique identifier for rows of a table||Unique identifier for rows of a table when the primary key is not present|
|Cannot be NULL||Can be NULL|
|Only one primary key can be present in a table||Multiple Unique Keys can be present in a table|
|present in a table||present in a table|
|Selection using primary key creates clustered index||Selection using a unique key creates a non-clustered index|
20. IS the primary key automatically indexed in MySQL?
Yes, the primary key is automatically indexed in MySQL because the primary key, index, etc get stored into B-trees. All engines including InnoDB as well as MyISAM automatically support the primary key to be indexed.
The primary key is implicitly indexed in InnoDB, MyISAM, and other engines.
21. How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
As we know that a PRIMARY KEY column must have unique values and cannot have null values hence if we will define a column with UNIQUE and NOT NULL constraint both then MySQL considers that column as PRIMARY KEY by default.
22.Can we remove a primary key from a table? IF yes, how?
Yes, we can remove a primary key constraint from a table. We will have to use the Alter table ..Drop statement
ALTER TABLE <table-name> drop primary key;
23. How to add a column to an existing table?
We can use ALTER TABLE and ADD COLUMN command to add a column in an existing table as shown below,
ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];
24. How does multiple column sorting work?
When sorting records using Multiple columns, the records are sorted on the basis of the first column first.
In case there is a conflict i.e. there are more than one records with the same value then the second column is considered. The conflict records are then sorted using the second column sort and so on.
Example: consider the records from a customer table. We are sorting on the basis of the following columns
SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC,FIRST_NAME ASC, address_id desc;
In the 2 highlighted records, The first name and the last name are the same, so there is a conflict, so the records will be sorted on the basis of descending address_id.
25. How to delete columns in MySQL?
We can remove, drop, or delete one or more columns in an existing table using the
ALTER TABLE statement as follows:
ALTER TABLE table_name DROP COLUMN column_name1, column_name2....;
26. What is the best way to insert 10,000 records from one table to another?
If we want to keep the structure the same we can simply do an insert - Select *
Insert into <table1> select * from <table2>;
27. What are the differences between CHAR and VARCHAR data types?
CHAR and VARCHAR data types store string data.
- Stores fixed-length data
- Storage size = maximum size of the char datatype
- Stores small data
- Is faster
- Stores variable-length data.
- Storage size = length of the string inserted
- Stores larger data
- Is slower.
28. Which MySQL function is used to concatenate string?
CONCAT() function can used to combine two or more string data.
SELECT query with
CONCAT() function will combine five words, ‘Welcome’,’to’,’the’,’techieclues’,’website’.
Welcome to the techieclues website
29. What is the difference between delete, drop and truncate?
- It removes all rows from a table.
- It does not require a WHERE clause.
- Truncate cannot be used with indexed views.
- It is performance-wise faster.
- It removes Some or 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 removes rows one by at a time.
- It can be used with indexed views.
- It removes a table from the database.
- All table rows, indexes, and privileges will also be removed when we used this command.
- The operation cannot be rolled back.
30. Write a query to fetch duplicate records from a table using MySQL?
SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;
31. How do I import the database through the command-line client?
MySQL -u [username] -p [database_name] < [database_dump_file].sql
Where username: Any MySQL user. For example, root,
Database_name: Name of the database in MySQL. This should exist,
database_dump_file: This is the dump file that has all the data, the structure of tables, etc for the database.
32. What is TRIGGERS and how it can be used in MySQL?
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
33. What is the difference between NOW and CURRENT_DATE in MySQL?
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"
34. What is a regex for searching for a string with exactly one character between n and t?
35. What is the difference between BETWEEN and IN operators in Mysql?
Between operator is used to selecting a range of data between two values. It can be texts, numbers, and dates, etc.
Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2
IN operator is used to checking for a value in the given set of values.
Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)
36. What are aggregate functions in MySQL?
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 calculations except for the COUNT function.
MySQL provides many aggregate functions that are listed below.
37. What does a group_concat() do?
Group_concat function virtually groups and combines multiple rows into a single row. For example, the below Query to get all the actors for documentary movies.
SELECT F.TITLE,F.DESCRIPTION,F.LENGTH,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME,A.LAST_NAME) SEPARATOR ', ') AS `ACTORS` FROM FILM_ACTOR FA, FILM F ,ACTOR A WHERE F.FILM_ID = FA.FILM_ID AND A.ACTOR_ID = FA.ACTOR_ID AND F.FILM_ID IN ( SELECT FILM_ID FROM FILM_CATEGORY, CATEGORY WHERE CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID AND CATEGORY.CATEGORY_ID = 6) GROUP BY F.FILM_ID;
38. What is the difference between function and procedure in MySQL?
|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.|
39. What do we do if we want to join two tables and allow duplicates too?
We can use a union all operator,
Select <column-names> from <table1> UNION ALL Select <column-names> from <table2>
40. How does COALESCE work?
COALESCE function is sort of like an either-or. In COALESCE, we give three arguments. IF the First Argument is NULL, then MySQL checks the second argument and returns it if not null. If the second argument is also null, then the third value is returned.
Select first_name ,rating, salary, COALESCE(rating,salary,'NA') From employees;
41. Name a few Control flow in-built functions in MySQL?
||Return the corresponding result in the THEN branch if the condition in the WHEN branch is satisfied, otherwise, return the result in the ELSE branch.|
||If checks the condition is given. If true then true-expression is executed else the false condition is executed.|
||It is similar to If but it checks the IF NULL condition specifically.|
||It checks the equality between two arguments and returns NULL if true and returns the first argument if False.|
42. How to get all indexes on a table?
We can use the below query to get all indexes on a table.
SHOW INDEXES FROM <table_name>;
43. What are Invisible Indexes? Can a Primary Key be an invisible index?
An index on a column can be visible or invisible. To create an invisible index, we use the same create index syntax with the word Invisible added. Primary Key indexes cannot be Invisible, MySQL does not allow it. Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required.
44. How to find the second highest salary in MySQL?
MySQL uses the LIMIT keyword, which can be used to limit the result set. It will allow us to get the first few rows, last few rows, or range of rows. It can also be used to find the second, third, or nth highest salary. It ensures that you have to use order by clause to sort the result set first and then print the output that provides accurate results. The following query is used to get the second highest salary in MySQL:
SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;
There are some other ways to find the second highest salary in MySQL, which are given below:
This statement uses subquery and IN clause to get the second highest salary:
SELECT MAX(salary) FROM employees WHERE salary NOT IN ( SELECT Max(salary) FROM employees);
This query uses subquery and < operator to return the second highest salary:
SELECT MAX(salary) From employees WHERE salary < ( SELECT Max(salary) FROM employees);
45. What is the difference between NOW() and CURRENT_DATE()?
NOW() command is used to show the current year, month, date with hours, minutes, and seconds while
CURRENT_DATE() shows the current year with month and date only.
46. How do we Pivot a table in MySQL?
Pivot is not supported by MySQL directly but we can emulate it by using the CASE statement.
An example from the Sakila database is,
select title, description,rental_rate, CASE rating WHEN 'G' THEN 'ALL' WHEN 'PG' THEN 'Parental Guidance' WHEN 'PG-13' THEN 'TEEN Movies' WHEN 'R' THEN 'ADULTS' WHEN 'NC-17' THEN 'ADULTS' END AS 'Audience' From film;
46. What is MySQL Injection?
MySQL injection is a security problem in MySQL. It is one of the most common hacking techniques for any database.
An SQL injection happens when we are trying to receive some sort of user input from the user interface, say for example a username and instead of the username, the user sends a SQL statement. If we execute this SQL statement into our database without the proper checks, it can destroy the database.
Example of SQL injections:
- MySQL Injection based on 1=1
- MySQL injection based on OR “”=””
47. What is a Cursor?
A Cursor is a data structure using which we can traverse over records in a table. It is like a pointer we use to traverse a list of records in a single direction.
48. Explain BLOB and TEXT in MySQL?
BLOB stands for Binary Large Object. It holds variable amounts of data. The MAX amount of data a BLOB can hold is 65,535 bytes. The types of BLOBs are:
TEXT stores string values and can hold up to a maximum of 65,535 characters. The types are:
49. What is meant by a decimal (5,2)?
A decimal data type is used in MySQL to store the fractional data.
The decimal (5,2) means that the total length of the fractional value is 5. The field can contain 3 digits before the decimal point and 2 digits after the decimal point. If a user adds any value larger than the defined length then it will insert 999.99 in the field.
The use of this data type is explained in the following example.
50. What is the function of mysqldump?
MySQL provides a dump facility (mysqldump) to export and import databases. It is a useful utility tool that is used to dump one or more or all databases from the server for backup or transfer to another database server.
# For a single database mysqldump [OPTIONS] db_name [TABLES] # For multiple databases mysqldump [OPTIONS] –databases DB1 [DB2 DB3…] # For all databases mysqldump [OPTIONS] –all-databases
51. What is the difference between UNIX TIMESTAMP and MySQL TIMESTAMP?
UNIX TIMESTAMP and MySQL TIMESTAMP both are used to represent date and time value. UNIX timestamps use 32-bit integers whereas MySQL timestamp uses a more human-readable format.
A UNIX time value is used by the FROM_UNIXTIME function in the SELECT query to get the date and time value in the human-readable format.
SELECT FROM_UNIXTIME (1596222320) AS 'MySQLTIMESTAMP';