MYSQL DBA INTERVIEW QUESTIONS & ANSWERS

MYSQL DBA INTERVIEW QUESTIONS & ANSWERS

Questions: 1 How to do login in mysql with unix shell?
Ans. By below method if password is pass and user name is root

 # [mysql dir]/bin/mysql -h hostname -u root -p pass

Questions : 2 how you will Create a database on the mysql server with unix shell.  

Ans. mysql> create database databasename;

Questions : 3 how to list or view all databases from the mysql server.

Ans. mysql> show databases;

Questions : 4  How Switch (select or use) to a database.
Ans.  mysql> use databasename;

Questions : 5 How To see all the tables from a database of mysql server.
Ans. mysql> show tables;

Questions : 6 How to see table’s field formats or description of table .
Ans.  mysql> describe tablename;

Questions : 7 How to delete a database from mysql server.
Ans.  mysql> drop database databasename;

Questions : 8 How we get Sum of column.
Ans. mysql> SELECT SUM(*) FROM [table name];

Questions : 9 How to delete a table.
Ans. mysql> drop table tablename;

Questions : 10 How you will Show all data from a table.
Ans.   mysql> SELECT * FROM tablename;

Questions : 11 How to returns the columns and column information pertaining to the designated table.
Ans. mysql> show columns from tablename;

Questions : 12 How to Show certain selected rows with the value “abc”.
Ans. mysql> SELECT * FROM tablename WHERE fieldname = “abc”;

Questions : 13 How will Show all records containing the name “sampan” AND the phone number ’9756359898′.
Ans. mysql> SELECT * FROM tablename WHERE name = “sampan” AND phone_number = ’1234567890′;

Questions : 14 How you will Show all records not containing the name “sampan” AND the phone number ’9756359898′ order by the phone_number field.

Ans.  mysql> SELECT * FROM tablename WHERE name != “sampan” AND phone_number = ’9756359898′ order by phone_number;

Questions : 15 How to Show all records starting with the letters ‘sampan’ AND the phone number ’9756359898′.

Ans. mysql> SELECT * FROM tablename WHERE name like “sampan%” AND phone_number = ’9756359898′;

Questions : 16 How to show all records starting with the letters ‘sampan’ AND the phone number ’9756359898′ limit to records 1 through 5.

Ans.   mysql> SELECT * FROM tablename WHERE name like “sampan%” AND phone_number = ’9756359898′ limit 1,5;

Questions : 16 Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with r.

Ans.  mysql> SELECT * FROM tablename WHERE rec RLIKE “^r”;

Questions : 17 How you will Show unique records.

Ans.  mysql> SELECT DISTINCT columnname FROM tablename;

Questions : how we will Show selected records sorted in an ascending (asc) or descending (desc).

Ans.  mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

Questions : 19 how to Return total number of rows.

Ans.   mysql> SELECT COUNT(*) FROM tablename;

Questions : 20 How to Join tables on common columns.

Ans.  mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id.

Questions : 21 How to Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs?

Ans.  # mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,'username’,PASSWORD(‘password’));

mysql> flush privileges;

Questions : 22 How to Change a users password from unix shell.

Ans.: # [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

Questions : 23 How to Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

Ans.  # mysql -u root -p

mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);

mysql> flush privileges;

Questions : 24 How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

Ans.  # /etc/init.d/mysql stop

# mysqld_safe –skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start

Questions : 25 How to Set a root password if there is on root password.

Ans.  # mysqladmin -u root password newpassword

Questions : 26 How to Update a root password.

Ans.  # mysqladmin -u root -p oldpassword newpassword

Questions : 27 How to allow the user “sampan” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

Ans. # mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to sampan@localhost identified by ‘passwd’;

mysql> flush privileges;

Questions : 28 How to give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

Ans.   # mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);

mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

Questions : How To update info already in a table and Delete a row(s) from a table.

Ans.  mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

mysql> DELETE from [table name] where [field name] = ‘whatever’;

Questions : 30 How to Update database permissions/privilages.

Ans.  mysql> flush privileges;

Questions : 31 How to Delete a column and Add a new column to database.

Ans.  mysql> alter table [table name] drop column [column name];

mysql> alter table [table name] add column [new column name] varchar (20);

Questions : 32 Change column name and Make a unique column so we get no dupes.

Ans.  mysql> alter table [table name] change [old column name] [new column name] varchar (50);

mysql> alter table [table name] add unique ([column name]);

Questions : 33 How to make a column bigger and Delete unique from table.

Ans.  mysql> alter table [table name] modify [column name] VARCHAR(3);

mysql> alter table [table name] drop index [colmn name];

Questions : 34 How to Load a CSV file into a table.

Ans.  mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);

Questions : 35 How to dump all databases for backup. Backup file is sql commands to recreate all db’s.

Ans.  # [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Questions : 36 How to dump one database for backup.

Ans.  # [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Questions : 37 How to dump a table from a database.

Ans.  # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Questions : 38 Restore database (or database table) from backup.

Ans.  # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Questions : 39 How to Create Table show Example.

Ans.  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));

Questions : 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?

Ans. 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 ;

 Questions : 41 What’s MySQL?

Ans.     MySQL the most popular Open Source SQL database management system, is developed, distributed, and supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL developers, that builds its business by providing services around the MySQL database management system.

Questions : 42 Why use the MySQL Database Server?

 Ans.    The MySQL Database Server is very fast, reliable, and easy to use. it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything.

Questions : 43 What is the technical features of MySQL Server?

Ans.     The MySQL Database Software is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).

Questions : 44 What are the column comparisons operators?

Ans.     The = , ‹›, ‹=, ‹, ›=, ›,‹‹,››, ‹=›, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements.

Questions : 45 How do you get the number of rows affected by query?

 Ans.    SELECT COUNT (user_id) FROM users;

Questions : 46 What are HEAP tables in MySQL?

Ans.

 •          HEAP tables are in-memory. They are usually used for high-speed temporary storage.

•           No TEXT or BLOB fields are allowed within HEAP tables.

•           You can only use the comparison operators = and ‹=›.

•           HEAP tables do not support AUTO_INCREMENT.

•           Indexes must be NOT NULL.

Questions : 47 How do you return the a hundred books starting from 25th?

Ans.     SELECT book_title FROM books LIMIT 25, 100;

Questions : 48 How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

 Ans.    SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8).

Questions : 49 What is the default port for MySQL Server?

 Ans.    The default port is 3306.

Questions : 50 How would you select all the users, whose phone number is null?

 Ans.    SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

Questions : 51 What are ENUMs used for in MySQL?

 Ans.    You can limit the possible values that go into the table.

CREATE TABLE months (month ENUM ’January’, ’February’, ’March’,); INSERT months VALUES (’April’).

Questions : 52 What are the advantages of Mysql comparing with oracle?

Ans.     MySql is Open source, which can be available any time. Provides Gui with Command Prompt. Supports the administration using MySQL Admin,MySQL Query Browser.Oracle is best database ever in Software development.

Questions : 53 What is the difference between CHAR_LENGTH and LENGTH?

Ans.     The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

Questions : 54 How are ENUMs and SETs represented internally?

 Ans.    As unique integers representing the powers of two, due to storage optimizations.

Questions : 55 How do you change a password for an existing user via mysqladmin?

Ans.     mysqladmin -u root -p password "newpassword"

Questions : 56 If the value in the column is repeatable, how do you find out the unique values?

 Ans.    SELECT DISTINCT user_firstname FROM users;

Questions : 57 Explain the difference between FLOAT, DOUBLE and REAL?

Ans. FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

Questions : 58 How do you get the current version of mysql?

Ans.     SELECT VERSION();

Questions : 59 Is Mysql query has LETTERCASE?

 Ans.    No.

Ex :

SELECT VERSION(), CURRENT_DATE;

select version(), current_date;

SeLeCt vErSiOn(), current_DATE;

Questions : 60 What is the LIKE?

Ans.     A LIKE pattern match, which succeeds only if the pattern matches the entire value.

Questions : 61 Differentiate the LIKE and REGEXP operators?

Ans.     SELECT * FROM pet WHERE name REGEXP "^b";

SELECT * FROM pet WHERE name LIKE "%b";

Questions : 62 What are the String types are available for a column?

 Ans.    The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

Questions : 63 What is the REGEXP?

Ans. A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested.

Questions : 64 What is the difference between CHAR AND VARCHAR?

 Ans.    The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.

The length of a CHAR column is fixed to the length that you declare when you create the table.

The length can be any value between 1 and 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Questions : 65 How quoting and escaping work in SELECT QUERY?

 Ans.    SELECT ‘hello’, ‘“hello”’,‘““hello””’, ‘hel‘‘lo’, ‘\‘hello’.

Questions : 66 What is the difference between BLOB AND TEXT?

Ans.     A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold.

The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

Questions : 67 How we get Sum of column?

 Ans.    mysql> SELECT * FROM tablename;

Questions : 68 How do you get current user in mysql?

 Ans.    SELECT USER();

Questions : 69 How would you change a table to InnoDB?

Ans.     ALTER TABLE name_file ENGINE innodb;

Questions : 70 How do you concatenate strings in MySQL?

 Ans.    CONCAT (string1, string2, string3)

Questions : 77 What is difference between primary key and candidate key?

Ans.

 Primary Key- are used to uniquely identify each row of the table. A table can have only one primary Key.

Candidate Key- primary key is a candidate key. There is no difference. By common convention one candidate key is designated as a “primary” one and that key is used for any foreign key references.

Questions : 78 How do you get the month from a timestamp?

 Ans. SELECT MONTH(january_timestamp) from tablename;

Questions : 79 What do % and _ mean inside LIKE statement?

 Ans. % corresponds to 0 or more characters, _ is exactly one character.

Questions : 80 If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

 Ans. 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

Questions : 81 How do you get the current date in Mysql?

Ans.  SELECT CURRENT_DATE();

Questions : 81 What is the difference between mysql_fetch_array and mysql_fetch_object?

 Ans. mysql_fetch_array(): - returns a result row as a associated array, regular array from database.

mysql_fetch_object: - returns a result row as object from database.

Questions : 82 You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?

Ans.  SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS();

Questions : 83 What does this query mean: SELECT user_name, user_isp FROM users LEFT

Ans. JOIN isps USING (user_id)?

 It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

Questions : 84 How do you display the list of database in mysql?

 Ans. SHOW DATABASES;

Questions : 85 How do you display the structure of the table?

 Ans.  DESCRIBE table_name;

Questions : 86 How do you find out which auto increment was assigned on the last insert?

 Ans. SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

Questions : 87 What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?

 Ans. On initialization places a zero in that column, on future updates puts the current value of the timestamp in.

Questions : 89 How many drivers in Mysql?

Ans.  There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities.They are

•           PHP Driver

•           ODBC Driver

•           JDBC Driver

•           ado.net5.mxj

•           CAPI1PHP DRIVER

•           PERL DRIVER

•           PYTHON DRIVER

•           RUBY DRIVER

•           C WRAPPER

Questions : 90 How do you run batch mode in mysql?

Ans.

 mysql < batch-file >;

mysql < batch-file > mysql.out

Questions : 91 What Storage Engines do you use in MySQL?

Ans. Storage engines used to be called table types.

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employs different storage mechanisms, indexing facilities, locking levels and ultimately provides a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

Questions : 92 Where MyISAM table is stored?
Ans.

            Each MyISAM table is stored on disk in three files.

•           The ‘.frm’ file stores the table definition.

•           The data file has a ‘.MYD’ (MYData) extension.

•           The index file has a ‘.MYI’ (MYIndex) extension

Questions : 93 Define Primary key?

Ans.  MYSQL allows only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself.A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.