«

»

May 13

MySQL Interview Questions and Answers

Q1. What’s MySQL ?
Ans. MySQL is an relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. MySQL is noted mainly for its speed, reliability, and flexibility. MySQL Provide CREATE and ALTER TABLE which is called DDL(Data Definition Language), SELECT, INSERT, UPDATE and DELETE which is called DML(Data Manipulation Language and GRANT which is called DCL(Data Control Language ). GRANT is concerns with rights, permissions and other controls of the database system.  MySQL database is very fast, reliable, scalable and easy to use. MySQL is open source and available under GPL  (GNU General Public License).

Q2. how do you find out the unique values? If the value in the column is repeatable.
Ans.
SELECT DISTINCT(name) from tblname. DISTINCT use for getting unique value from table in MySQL. We can also find the total number of value using SELECT COUNT(DISTINCT(name)) from tblname.

Q3. How do you return the a hundred aticle starting from 25th?
Ans.
SELECT article_title FROM article LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

Q4. How do I find out all databases starting with ‘tech’ to which I have access to?
Ans.
SHOW DATABASES LIKE ‘tech%’;

Q5. How do you concatenate strings in MySQL?
Ans.
CONCAT (string1, string2, string3)

Q6. How do you get a portion of a string?
Ans.
SELECT SUBSTR(title, 1, 10) from tblname

Q7. What’s 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.

Q8. How do you convert a string to UTF-8?
Ans.
SELECT (tblname USING utf8);

Q9. What’s the difference between Unix timestamps and MySQL timestamps?
Ans.
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.

Q10. What are ENUMs used for in MySQL?
Ans.
We can use ENUM for limit the possible value that go into the table. Example CREATE Table userstatus status(ENUM,’Active’,’Deactive’,’Suspended’).

Q11. How do you add three minutes to a date?
Ans.
ADDDATE(datetime, INTERVAL 3 MINUTE)

Q12. How do you convert between Unix timestamps and MySQL timestamps?
Ans.
UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

Q13. How do you start and stop MySQL on Windows?
Ans.
net start MySQL, net stop MySQL

Q14. How do you start MySQL on Linux?
Ans.
/etc/init.d/mysql start

Q15. How would you delete a column?
Ans.
ALTER TABLE tblname DROP user_id.

Q16. What’s the default port for MySQL Server?
Ans.
3306

Q17. Explain the difference between MyISAM Static and MyISAM Dynamic. ?
Ans. In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

Q18. What does myisamchk do?
Ans. It compressed the MyISAM tables, which reduces their disk usage.

Q19. Explain advantages of InnoDB over MyISAM? 
Ans. Row-level locking, transactions, foreign key constraints and crash recovery.

Q20. What is difference between MyISAM and InnoDB? 
Ans. Myisam

  1. Myisam support table lavel locking
  2. MyISAM designed for need of speed
  3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
  4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

INNODB

  1. InnoDB supports Row-level Locking
  2. InnoDB designed for maximum performance when processing high volume of data
  3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
  4. InnoDB stores its tables and indexes in a tablespace
  5. InnoDB supports transaction. You can commit and rollback with InnoDB

 Q20. What is difference between Mysql_connect and mysql_pconnect?

Ans. Difference between mysql_connect and mysql_pconnect.

  1. Mysql_pconnect try to find connection which is already open with same host, username and password. If found then this will return and not new connection. But Mysql_connect not check for open connection and it will open new connection every time and when execution of script is end then it will close automatically.
  2. In Mysql_pconnect, the connection will not closed when script end the execution it will still open for future use. But in Mysql_connect, the connection will closed when script end the execution and not available for use in future.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>