MySql Interview Questions and Answers – Part 4

31 How to Delete a column and Add a new column to database
mysql> alter table [table name] drop column [column name];
mysql> alter table [table name] add column [new column name] varchar (20);

32 Change column name and Make a unique column so we get no dupes.
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
mysql> alter table [table name] add unique ([column name]);

33 How to make a column bigger and Delete unique from table.
mysql> alter table [table name] modify [column name] VARCHAR(3);
mysql> alter table [table name] drop index [colmn name];

34 How to Load a CSV file into a table
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

35 How to dump all databases for backup. Backup file is sql commands to recreate all db’s.
# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

36 How to dump one database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

37 How to dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

38 Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

39 How to Create Table show Example
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

40 How to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
By below query we will get second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
SELECT DISTINCT(salary) FROM employee order by salary desc limit 1 , 1 ;
(This way we will able to find out 3rd highest , 4th highest salary so on just need to change limit condtion like LIMIT 2,1 for 3rd highest and LIMIT 3,1 for 4th
some one may finding this way useing below query that taken more time as compare to above query SELECT salary FROM employee where salary < (select max(salary) from employe) order by salary DESC limit 1 ;

Sign-up for our email newsletter and get free job alerts, current affairs and GK updates regularly.
Subscribe Here (or)

One Response to “MySql Interview Questions and Answers – Part 4”

  1. Brent says:

    Great post with great info.

Leave a Reply

Your email address will not be published. Required fields are marked *


− 2 = zero

Popup Dialog Box Powered By : XYZScripts.com
  • RSS
  • Facebook
  • Google+
  • Twitter