Q-1: What is difference between where and having clauses

Having

• HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
• HAVING clause select rows after grouping
• MAX,MIN,AVG,COUNT,SUM:(SUM and AVG functions only work on numeric data.)

Where

• Where clause select rows before grouping

Q-2: How to store data of flash

Ans: blob

Q-3: Can i join two tables from different databases ?

Ans: yes,

 select * from db1.table1 JOIN db2.table ON t1.id=t2.id;

Q-4: Which query is fast Insert or Update

Update is faster, In Update query we updating existing value without touching index etc.

Q-5: What is index in mysql and Advantages and disadvantage of index ?

A database index is a data structure that improves the speed of data retrieval operations on a database.

Advantages of indexes :

1. Speed up the SELECT query.
2. If index is set to fill-text index, then we can search against large string values.

Disadvantages of indexes:

1. They decrease performance on inserts, updates, and deletes because on each operation the indexes must also be updated.
2. Speed up UPDATE if the WHERE condition has an indexed field.
3. Indexes take extra disk space to save index.

Q-6: Diffrence between DELETE, TRUNCATE and DROP in MySQL ?

DELETE

1. DELETE is a Data Manipulation Language (DML) Command.
2. We can use where condition for remove specific rows(records).
3. DELETE is slower than TRUNCATE. it's maintain logs.
4. Rollback is possible in Delete command.
5. No triggers will be fired.

TRUNCATE

1. TRUNCATE is a Data Definition Language ( DDL) Command.
2. TRUNCATE is used to remove all rows from the Table.
3. Truncate is faster compared to delete, it does not maintain transaction log.
4. Rollback is not possible (autocommit).
5. No triggers will be fired.

DROP

1. DROP is a Data Definition Language( DDL) Command.
2. DROP command removes all records,indexes,structure and privileges permanently.
3. No DML triggers will be fired.
4. Rollback is not possible.

Q-7: Diffrence between UNION and UNION All ?

Ans:UNION
UNION is used to combine the results of two or more SELECT statements. However it will eliminate duplicate rows from its result set. In UNION number of columns and data type must be same in both the tables, on which UNION operation is being applied.
UNION ALL
UNION ALL show all records including duplicate

Q-8: What is the difference between CHAR and VARCHAR ?

CHAR

1. Used to store character string value of fixed length.
2. The maximum no. of characters the data type can hold is 255 characters.
3. It's 20% faster than VARCHAR.
4. Uses static memory allocation.
5. When CHAR values are stored, they are right-padded with trailing spaces to make up the specified length.

VARCHAR

1. Used to store variable length alphanumeric data.
2. It's slower than CHAR.
3. Uses dynamic memory allocation.
4. VARCHAR values aren't padded during storage.Trailing spaces are retained when data is inserted into the field.

Q-1: What is result of below query ?

Ans:

Select * from users where 1=1 //execute and fetch all rows
Select * from users where 1=2 //execute and but not fetch any rows
Select * from users where 'Vishal'=name //execute and fetch those recods find the name vishal
Select * from users where 'kamal'=abcde(abcde is not column name) // error will show

Q-2: How to use second index forcefully in MySQL ?

Ans:

SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name;

Q-3: How to get total number of records of table in MySQL?

Ans:

 1.SELECT COUNT(*) FROM test;

Q-4: How to get total number of records of table in MySQL without count ?

Ans:

 SELECT TABLE_ROWS from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employee';

Q-5: How to copy structure and data from one table to another table in MySQL?

Ans:

 //only structure 
CREATE TABLE new_table LIKE old_table;

//structure as well as data
CREATE TABLE new_table SELECT * FROM old_table;
CREATE TABLE new_table AS SELECT * FROM old_table;

//only data
INSERT INTO new_table SELECT * FROM old_table; 

Q-6: How to find greater than average salary records in MySQL?

Ans:

SELECT salary from emp WHERE salary > (SELECT AVG(salary) FROM emp

Q-7: How to find third heighest salary in MySQL?

Ans: With the use of limit n-1,1; we can get the heighest salary.

SELECT * FROM `test` order by amount desc limit 2,1;

Q-8: How to find third heighest salary in MySQL without limit?

Ans: With the use of WHERE ( n ) , we can get the heighest salary

SELECT * FROM `emp` a WHERE (3) = (SELECT count(salary) from emp b where a.salary<=b.salary)

Q-9: How to get duplicate records in MySQL query?

Ans:

SELECT * FROM `test` GROUP BY name having count(name)>1;
//OR
SELECT name,count(name) as t FROM `test` GROUP BY name having t>1;

Find the duplicate recods by multiple columns.

SELECT ColumnA, ColumnB, ColumnC, count(*) as total from table 
group by ColumnA, ColumnB, ColumnC having total > 1;

Q-10: How to delete duplicate records in MySql?

Ans:

#(lowest id will keep)
1.DELETE n1 from test n1 , test n2 where n1.`id`>n2.id AND n1.`name`=n2.`name`; 
#(max id will keep)
2.DELETE n1 from test n1 , test n2 where n1.`id` < n2.id AND n1.`name`=n2.`name`;