Common MySQL Interview Questions

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

-----------add comments to a column ---------

ALTER TABLE tbl_commety_members modify `member_type` TINYINT( 1 ) COMMENT 'id of user'

// to select a column with serial no which doesn't exist in mysql table
SET @rownum:=0; SELECT @rownum:=@rownum+1 AS id, names FROM table_name;

-----------to fromat date in dd/mm/yyyy format--------------------

SELECT remarks, date_FORMAT(payment_date,'%d/%m/%Y') FROM tbl_payment

--------------------------group by----------------------------

select count( as ctr, from tbl_property left join tbl_city on ( where tbl_property.status=1 group by order by ctr desc limit 0,8

-------------------------count max id from union of two table---------------

select certificate_no from (select certificate_no from tbl_austfumigation order by certificate_no desc limit 0,1) as a
select certificate_no from (select certificate_no from tbl_fumigation order by certificate_no desc limit 0,1) as b
order by certificate_no desc limit 0,1

-----------------------Count co of columns in a table------------
SELECT count(*) FROM information_schema.`COLUMNS` C
WHERE table_name = 'tablename'
AND TABLE_SCHEMA = "databasename"

--------------------rearrange columns ---------------
ALTER table tbl_tour MODIFY COLUMN days tinyint(4) AFTER package;

-----------------replace a part of a str ------------


Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE()performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('', 'w', 'Ww');
    -> ''
This function is multi-byte safe.

SET MyDB.MyTable
= REPLACE(MyDB.MyTable.MyField,'OldString','NewString')
WHERE MyDB.MyTable.MyField like '%OldString%';

--------------------load data from csv-------------------------

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

----------- format date time datetime is the name of the field------------------

select tbl_order.*, DATE_FORMAT(datetime,'%d/%m/%Y %h:%m:%s %p') as tt from tbl_order

-------------rearrange column after cretaing table--------------------------

alter table `tbl_maintenance` modify column vehicle_id bigint(20) after id

Post a Comment