MySQL interview Questions and Answers For Experienced

Question: What's MySQL ?
Answer: MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.

Question: How many columns we can update in single mysql query?
Answer:we can update 64 columns in single mysql query.

Question: What is maximum length of column name, table name and database name?
Answer: column name can be upto 64 chars, table name can be upto 64 and database name can be upto 64 chars.

Question: How will you export tables as an XML file in MySQL?Answer: MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.

Question: What is DDL, DML and DCL ? Answer: Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL.

DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT etc.

Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Question: How do you get the number of rows?
Answer: SELECT COUNT (user_id) FROM users

would only return the number of user_id’s.

Question: What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?
Answer: Mysql_fetch_object returns the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names. E.g. using mysql_fetch_object field can be accessed as $result->name and using mysql_fetch_array field can be accessed as $result->[name]. mysql_fetch_row($result):- where $result is the result resource returned from a successful query executed using the mysql_query() function.

Question: If the value in the column is repeatable, how do you find out the unique values? Answer: Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

Question: How do you return the a hundred books starting from 25th? Answer: SELECT book_title FROM books LIMIT 25, 100.

The first number in LIMIT is the offset, the second is the number.

Question: How to create case insensitive query in mysql?
Answer: use binary for case insensitive query. For Example
select * from users where name= binary 'bob';

Question: What is name of collation, for case sensitive?
Answer: latin1_general_cs.
When you want to search data as case sensitive, use latin1_general_cs

Question: How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

Question: How would you select all the users, whose phone number is null? Answer: SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

Question: How do you find out which auto increment was assigned on the last insert?
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.

Question: On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? Answer: What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

Question: When would you use ORDER BY in DELETE statement? Answer: When you’re not deleting by row ID. Such as in DELETE FROM com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table com_questions.

Question: How can you see all indexes defined for a table?
SHOW INDEX FROM questions

Question: How would you delete a column?
ALTER TABLE answers DROP answer_user_id.

Question: How would you change a table to InnoDB?
ALTER TABLE questions ENGINE innodb;

Question: When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column

Question: How do I find out all databases starting with ‘users’ to which I have access to?
SHOW DATABASES LIKE ‘users%’;

Question: How do you concatenate strings in MySQL?
CONCAT (string1, string2, string3)

Question: How do you get a portion of a string?
SELECT SUBSTR(name, 1, 10) from questions;

Question: What’s the difference between CHAR_LENGTH and LENGTH?
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.

Question: How do you convert a string to UTF-8?

SELECT (question USING utf8);

Question: What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.

Question: What does + mean in REGEXP?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

Question: How to get the second highest salary of an employee from a employee table?

You can do this in two ways1) select salary from employee order by salary desc limit 1,1
2) select max(salary) from employee where max(salary)<(select max(salary) from employee)

Question: How do you get the month from a timestamp?
SELECT MONTH(timestamp) from questions;

Question: What are the techniques for query optimization?
Following are the some techniques of query optimization
1) Use Indexes
2) User limit (Don't pull extra records)
3) Use only required fields (Don't pull extra fields which you are not going to use)

Question: How do you offload the time/date handling to MySQL?
SELECT DATE_FORMAT(timestamp, ‘%Y-%m-%d’) from questions; A similar TIME_FORMAT function deals with time.

Question: Get Highest salary from each department with employee name?
select e.name, e.salary, e.department from employee as e INNER join(select max(salary) as m_s, department from employee as me group by department) on e.salary=me.m_s and e.department=me.department.

Question: What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions in MySql are used in queries for searching a pattern in a string.
* Matches 0 more instances of the string preceding it.
+ matches 1 more instances of the string preceding it.
? Matches 0 or 1instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.

REGEXP can be used to match the input characters with the database.

Question: What’s the difference between Unix timestamps and MySQL timestamps?
Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

Question: What is difference between mysql_connect and mysql_pconnect?

Mysql_connect:

- Opens a new connection to the database
- The database connection can be closed
- Opens the page every time the page is loaded.

Mysql_pconnect:

- Opens a persistent connection to the database.
- The database connection can not be closed.
- The page need not be opened every time the page is loaded.

Question: What are ENUMs used for in MySQL?
You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

Question: How to get Second Highest salary of an employee?

   select salary from employee order by salary desc limit 1,1

Question: What are the data type supported by mysql?

Type	Size	Description
CHAR[Length]	Length bytes	A fixed-length field from 0 to 255 characters long.
VARCHAR(Length)	String length + 1 bytes	A fixed-length field from 0 to 255 characters long.
TINYTEXT	String length + 1 bytes	A string with a maximum length of 255 characters
TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters.
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters.
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters.
TINYINT[Length]	1 byte	Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT[Length]	2 bytes	Range of -32,768 to 32,767 or 0 to 65,535 unsigned.
MEDIUMINT[Length]	3 bytes	Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned

INT[Length]	4 bytes	Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
BIGINT[Length]	8 bytes	Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
FLOAT	4 bytes	A small number with a floating decimal point.
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point.
DECIMAL[Length, Decimals]	Length +1 bytes or Length + 2 bytes	A DOUBLE stored as a string, allowing for a fixed decimal point.
DATE	3 bytes	In the format YYYY-MM-DD
DATETIME	8 bytes	In the format YYYY-MM-DD HH:MM:SS.
TIMESTAMP	4 bytes	In the format YYYYMMDDHHMMSS; acceptable range ends in the year 2037.
TIME	3 bytes	In the format of HH:MM:SS.
ENUM	1 or 2 bytes	Short for enumeration, that is, each column can have one of several possible values.
SET	1, 2, 3, 4, or 8 bytes	Like ENUM except that each column can have more than one of several possible values.

Question:How to get current MySQL version?

SELECT VERSION();

Question: What are the drivers in MySQL?

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver

Question: How do you login to MySql using Unix shell?

mysql -h hostname -u username -p password

After pressing enter key, It will prompt for password, Now again type the password.

Question: What does myisamchk do?
myisamchk compress the MyISAM tables, which reduces their disk or memory usage.

Question: What are federated tables?
federated tables which allow access to the tables located on other databases on other servers.

Question:How can we get the number of rows affected by query?

SELECT COUNT (user_id) FROM users;

Question: How to display top slary of 5 employeees?

SELECT * FROM users order by salary desc limit 0,5

Web Technology Experts Notes

Saturday, 30 June 2012

MySQL interview Questions and Answers For Experienced