MySQL Interview Questions and Answers

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.

For this, we can use the between operator with a where clause.

SELECT id, name, salary FROM Employee WHERE salary BETWEEN 5000 AND 25000;

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.

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;