MySQL Interview questions for experienced

1.Find duplicate columns from given table.?
SELECT
    col,
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

e.g:
SELECT
    email,
    COUNT(email)
FROM
    contacts
GROUP BY email
HAVING COUNT(email) > 1;

2.Find duplicate values in multiple columns
SELECT
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...

FROM
    table_name
GROUP BY
    col1,
    col2, ...
HAVING
       (COUNT(col1) > 1) AND
       (COUNT(col2) > 1) AND
       ...

3.get nth highest salary
SELECT Salary FROM Worker ORDER BY Salary DESC LIMIT n-1,1;

4.show last record from table without limit
Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID) from Worker);

5.fetch first row of table.
Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID) from Worker);

6.employees having the highest salary in each department.
SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew
Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT
 and TempNew.TotalSalary=t.Salary;

7. fetch three max salaries from a table.
SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

8.nth max salary
SELECT distinct Salary from worker a WHERE n >= (SELECT count(distinct Salary) from worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;


9.primary key

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

10.Foreign Key

• A FOREIGN KEY is a key used to link two tables together.
• A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
• The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

11.SQL Injection

• SQL injection is a code injection technique that might destroy your database.
• SQL injection is one of the most common web hacking techniques.
• SQL injection is the placement of malicious code in SQL statements, via web page input

12.composite primary key.
A primary key having two or more attributes is called composite key. It is a combination of two or more columns.

13.How to Improve mysql performance?

1. Optimize Your Database
a. Normalize Tables (1NF,2NF,3NF,4NF)
b. Use Optimal Data Types
c. Avoid Null Values
d. Avoid Too Many Columns

2. Optimize Joins
reduce join statements in queries.

3. Index All Columns Used in ‘where’, ‘order by’, and ‘group by’ Clauses
4. Use Full-Text Searches
5. Optimize Like Statements With Union Clause
6. MySQL Query Caching

7.Add Indexing for the required columns
Indexing is a way of sorting a number of records on multiple fields. Creating an index on a field in a table creates another data structure which holds the field value, and a pointer to the record it relates to. This index structure is then sorted, allowing Binary Searches to be performed on it.


14.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.

15.What is a join in MySQL?
In MySQL, joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table. There are four types of joins in MySQL.

• Left Join => Left join returns all the rows from the left table even if there is no match in the right table.
• Right Join => Right join returns all the rows from the right table even if no matches exist in the left table.
• Inner Join => It returns rows if there is atleast one match in both the tables.
• full outer join => Full join would return rows when there is at least one match in the tables.

16. What is the difference between CHAR and VARCHAR?

• A CHAR field is a fixed length, and VARCHAR is a variable length field.
• This means that the storage requirements are different - a CHAR always takes the same amount of space regardless of what you store, whereas the storage requirements for a VARCHAR vary depending on the specific string stored.

17.What are Heap Tables?
Basically, Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.

18. What is the limit of indexed columns that can be created for a table?
The maximum limit of indexed columns that can be created for any table is 16.

19.What are the different types of strings used in database columns in MySQL?
In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR.

20.Get current sql version.
SELECT VERSION ();

21.What is the storage engine used for MySQL?
Storage tables are named as table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities, and functions.

22.Candidate key > 

The candidate keys can be used to reference the foreign keys.

23.What are the different types of tables in MySQL?

1. MyISAM is the default table that is based on the sequential access method.
2. Heap is the table that is used for fast data access, but the data will be lost if the table or the system crashes.
3. InnoDB is the table that supports transactions using the COMMIT and ROLLBACK commands.
4. BDB can support transactions similar to InnoDB, but the execution is slower.

24.. 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

 25.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”;

26.What is meant by transaction? What are ACID properties?
Transaction is a logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.

27.How Are Enums And Sets Represented Internally?
As unique integers representing the powers of two, due to storage optimizations.