MySql Interview Questions

Ques 1. Explain what Is MySQL?

Ans. MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. MySQL has the following main features:

• Works on many different platforms.
• APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available.
•  Fully multi-threaded using kernel threads. It can easily use multiple CPUs if they are available.
•  Provides transactional and non-transactional storage engines.
•  Uses very fast B-tree disk tables (MyISAM) with index compression.
•  A very fast thread-based memory allocation system.
•  Very fast joins using an optimized one-sweep multi-join.
•  In-memory hash tables, which are used as temporary tables.
•  SQL functions are implemented using a highly optimized class library and should be as fast as possible. Usually there is no memory allocation at all after query initialization.
•  The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available.

Ques 2. How Do You Know If Your MySQL Server Is Alive?

Ans. If you want to know whether your MySQL server is alive, you can use the "mysqladmin" program in a command window as shown in the following tutorial:

>cd mysqlbin >mysqladmin -u root ping mysqld is alive

The "mysqld is alive" message tells you that your MySQL server is running ok. If your MySQL server is not running, you will get a "connect ... failed" message.

Ques 3. How Do You Know the Version of Your MySQL Server?

Ans. If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial:

>cd mysqlbin

>mysqladmin -u root version

mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32

Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB ...

Server version 5.0.24-community

Protocol version 10

Connection localhost via TCP/IP

TCP port 3306

Uptime: 25 min 9 sec

Threads: 1 Questions: 2 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001

The output in the above example tells you that the version number is 5.0.24.

Ques 4. What Is Table?

Ans. A table is a data storage structure with rows and columns.

Ques 5. What Is Column?

Ans. A column defines one piece of data stored in all rows of the table.

Ques 6. What Is Row?

Ans. A row is a unit of data with related data items stored as one item in one column in a table.

Ques 7. What Is Primary Key?

Ans. A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.

Ques 8. What Is Foreign Key?

Ans. A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.

Ques 9. What Is Index?

Ans. An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

Ques 10. What Is View?

Ans. A view is a logical table defined by a query statement.

Ques 11. What Is Join?

Ans. Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.

Ques 12. What Is Union?

Ans. Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

Ques 13. What Is ISAM?

Ans. ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Ques 14. What Is MyISAM?

Ans. MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.

Ques 15. What Is InnoDB?

Ans. InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

Ques 18. What Is Transaction?

Ans. A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Ques 19. What Is Commit?

Ans. Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Ques 20. What Is Rollback?

Ans. Rollback is a way to terminate a transaction with all database changes not saving to the database server.

Ques 21. Explain MySQL Aggregate Functions.

Ans. Aggregate functions in MySQL are a group of functions that are used to operate on a set of values. These functions ignore NULL values unless specified. Functions like AVG(), MIN(), MAX(), COUNT() etc fall under this category. As they operate on a set of values, if no Group by clause is used, it applies to all rows.

Explain the following functions with an example.

AVG()

Returns the average of the parameter passed. Returns 0 if no matching rows found.

Example:

Avg(salary)

COUNT()

Counts the number of NON NULL values of the parameter passed. Returns 0 if no matching rows found.

Example:

Select employee_id, COUNT(*) from table_name;

MAX()

Returns the maximum value of the parameter passed. Returns 0 if no matching rows found.

Example:

Select MAX(employee_salary) from table_name

MIN()

Returns the minimun value of the parameter passed. Returns 0 if no matching rows found.

Example:

Select MIN(employee_salary) from table_name

SUM()

Returns the sum of the parameter passed. Returns NULL if no matching rows found.

Example:

Select SUM(employee_salary) from table_name

MySQL Aggregate Functions - September 30, 2009 at 17:00 pm by Vidya Sagar

Explain MySQL Aggregate Functions.

1. AVG (): Returns the average value of the given argument.
2. BIT_AND (): Returns the result of bitwise AND
3. BIT OR (): Returns the result of bitwise OR
4. BIT XOR (): Returns the result of bitwise XOR
5. COUNT (): Returns number of rows
6. COUNT (DISTINCT): Returns the count of unique values.
7. GROUP_CONCAT (): Returns concatenated string
8. MAX (): Returns the maximum value
9. MIN (): Returns the minimum value
10. STD (): Returns the standard deviation
11. SUM (): Returns the sum of a set of values
12. VAR (): Returns the variance

Ques 22. What is the difference between MySQL_connect and MySQL_pconnect?

Ans.

Mysql_connect:

It opens a new connection to the database.

Every time you need to open and close database connection, depending on the request.

Opens page every time when it loaded.

Mysql_pconnect:

In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection.

the database connection can not be closed.

it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

Ques 23. How to get the current date in MySQL?

Ans.  To get current date, use the following syntax:

SELECT CURRENT_DATE();

Ques 24. What is the difference between Unix timestamps and MySQL timestamps?

Ans. Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

Ques 25. How to display Nth highest salary from a table in a MySQL query?

Ans. Let us take a table named employee.

To find Nth highest salary is:

select distinct(salary) from employee order by salary desc limit n-1,1  

if you want to find 3rd largest salary:

select distinct(salary) from employee order by salary desc limit 2,1  

Ques 26. How many Triggers are possible in MySQL?

Ans. There are only six Triggers allowed to use in MySQL database.

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

Ques 26.  What is a trigger in MySQL?

Ans. A trigger is a set of codes that executes in response to some events.

Ques 27.  What is the difference between FLOAT and DOUBLE?

Ans. FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.

Ques 28.  How to change a password for an existing user via Mysqladmin?

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

Ques 29. What Are String Data Types?

Ans. MySQL supports the following string data types:

·         CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings. Default character set is ASCII.

·         NCHAR(n) same as NATIONAL CHARACTER(n) - Fixed width and " " padded character strings with UTF8 character set.

·         VARCHAR(n) same as CHARACTER VARYING(n) - Variable width character strings. Default character set is ASCII.

·         NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) - Variable width character strings with UTF8 character set.

·         BINARY(n) - Fixed width and 0x00 padded byte strings.

·         VARBINARY(n) same as BINARY VARYING(n) - Variable width byte string.

·         TINYBLOB - BLOB (Binary Large Object) upto 255 bytes.

·         BLOB - BLOB (Binary Large Object) upto 64K bytes.

·         MEDIUMBLOB - BLOB (Binary Large Object) upto 16M bytes.

·         LONGBLOB - BLOB (Binary Large Object) upto 4G bytes.

·         TINYTEXT - CLOB (Binary Large Object) upto 255 characters.

·         TEXT - CLOB (Binary Large Object) upto 64K characters.

·         MEDIUMTEXT - CLOB (Binary Large Object) upto 16M characters.

·         LONGTEXT - CLOB (Binary Large Object) upto 4G characters.

·         ENUM - An enumeration to hold one entry of some pre-defined strings.

·         SET - A set to hold zero or more entries of some pre-defined strings.

Ques 30. How To Concatenate Two Character Strings?

Ans. If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:

SELECT CONCAT('Welcome',' to') FROM DUAL;

Welcome to

SELECT CONCAT('wb','center','.com') FROM DUAL;

WithoutBook.com

Ques 31. How can I move a database from one server to another?
Ans. My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance,
  mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver 
  You can do all databases with

  mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver