In this post i am sharing some of the frequently asked mysql interview questions for freshers and experienced candidates.I have already written Top 20 SQL interview questions in my previous post go though this link to see also this post.
1) What is MySql?
Ans: MySQL is a multithreaded,multi-user SQL Database Management System. This is the most popular and widely used open source database. It is mainly used for eCommerce, Online transaction processing and embeded database applications.
2) In which language MySQL is written?
Ans: MySQL is written in C and C++ and its SQL parser is written in yacc.
3) What are the technical specification of MySQL?
Ans: MySQL has the following technical specifications
4) 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.
5) Could tell the advantage of MySQL over Oracle?
Ans: MySQL is a free,fast,reliable,open source Relational Database while Oracle is expensive.In terms of installation process MySQL takes only 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB. MySQL is great for database enabled websites while Oracle is made for enterprises. MySQL is portable.
6) Any Disadvantages of MySQL?
Ans: Yes, there are few disadvantages they are MySQL is not so efficient for large scale databases. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0. Transactions also not handled efficiently.
7)What is difference between MySQL_connect and MySQL_pconnect?
Ans: There are few differences between MySQL_connect and MySQL_pconnect
MySQL_connect: It opens a new connection to the database. Every time you need to open and close the database connection depending on the request.
MySQL_pconnect: In this p represent 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
8) What should we keep in mind about security alerts while using MySQL?
Ans: You should install antivirus and configure the OS firewall. You must keep in mind that never use the MySQL Server as the UNIX root user. Change root username and password restrict or disable remote access.
9) How to change a password for an existing user via Mysqladmin?
Ans: Simply write the following commond
Mysqladmin -u root -p password new password
10) What is REGEXP?
Ans: REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.
11) Difference between NOW() and CURRENT_DATE()?
Ans: NOW() command is used to show current year,month,date with hours,minutes and seconds whereas CURRENT_DATE() shows the current year with month and date only.
12) Can you write a query to display top 10 rows?
Ans: SELECT * FROM table_name LIMIT 0,10;
13) What is Save point in MySQL?
Ans: A defined point in any transaction is known as savepoint. It is a statement in MySQL which is used to set a named transaction save point with a name of identifier.
14) What is SQLyog?
Ans: SQLyog program is the most popular GUT tool for admin. It is the most popular MySQL manager and admin tool.It combines the features of MySQL administrator,phpMyadmin and other MySQL front ends and MySQL GUI tools.
15) What is Trigger In MySQL?
Ans: A Trigger or data Trigger is a stored program executed automatically to respond to a specific event for example,insert ,update and delete occurred in a table. It is mainly useful to automate some database operations such as logging and auditing etc..
16) How many Triggers are possible in MySQL?
Ans: There are only six Triggers allowed to use in MySQL database.
1)Before insert
2)After insert
3)Before update
4)After update
5)Before Delete
6)After Delete
17) What is heap table in MySQL?
Ans: Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL,you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They don't allow BLOB or TEXT fields.
18) What does i_am_a_dummy flag do in MySQL?
Ans: The i_am_a_dummy flag enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present
19)How many columns can be used for creating index?
Ans: For any standard table you should be created maximum of 16 columns.
20) Write a Query to display current date and time?
Ans: SELECT NOW();
SEE ALSO:
TOP 20 SQL Query interview Questions
Basic SQL Interview Questions
SQL SUB QUERY WITH EXAMPLES
VIEWS IN SQL WITH EXAMPLE
1) What is MySql?
Ans: MySQL is a multithreaded,multi-user SQL Database Management System. This is the most popular and widely used open source database. It is mainly used for eCommerce, Online transaction processing and embeded database applications.
2) In which language MySQL is written?
Ans: MySQL is written in C and C++ and its SQL parser is written in yacc.
3) What are the technical specification of MySQL?
Ans: MySQL has the following technical specifications
- Flexible structure
- Multi threaded
- Manageable and easy to use
- Enterprise Backup
- High performance
- JSON support
- Security
- Storage Management
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.
5) Could tell the advantage of MySQL over Oracle?
Ans: MySQL is a free,fast,reliable,open source Relational Database while Oracle is expensive.In terms of installation process MySQL takes only 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB. MySQL is great for database enabled websites while Oracle is made for enterprises. MySQL is portable.
6) Any Disadvantages of MySQL?
Ans: Yes, there are few disadvantages they are MySQL is not so efficient for large scale databases. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0. Transactions also not handled efficiently.
7)What is difference between MySQL_connect and MySQL_pconnect?
Ans: There are few differences between MySQL_connect and MySQL_pconnect
MySQL_connect: It opens a new connection to the database. Every time you need to open and close the database connection depending on the request.
MySQL_pconnect: In this p represent 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
8) What should we keep in mind about security alerts while using MySQL?
Ans: You should install antivirus and configure the OS firewall. You must keep in mind that never use the MySQL Server as the UNIX root user. Change root username and password restrict or disable remote access.
9) How to change a password for an existing user via Mysqladmin?
Ans: Simply write the following commond
Mysqladmin -u root -p password new password
10) What is REGEXP?
Ans: REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.
11) Difference between NOW() and CURRENT_DATE()?
Ans: NOW() command is used to show current year,month,date with hours,minutes and seconds whereas CURRENT_DATE() shows the current year with month and date only.
12) Can you write a query to display top 10 rows?
Ans: SELECT * FROM table_name LIMIT 0,10;
13) What is Save point in MySQL?
Ans: A defined point in any transaction is known as savepoint. It is a statement in MySQL which is used to set a named transaction save point with a name of identifier.
14) What is SQLyog?
Ans: SQLyog program is the most popular GUT tool for admin. It is the most popular MySQL manager and admin tool.It combines the features of MySQL administrator,phpMyadmin and other MySQL front ends and MySQL GUI tools.
15) What is Trigger In MySQL?
Ans: A Trigger or data Trigger is a stored program executed automatically to respond to a specific event for example,insert ,update and delete occurred in a table. It is mainly useful to automate some database operations such as logging and auditing etc..
16) How many Triggers are possible in MySQL?
Ans: There are only six Triggers allowed to use in MySQL database.
1)Before insert
2)After insert
3)Before update
4)After update
5)Before Delete
6)After Delete
17) What is heap table in MySQL?
Ans: Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL,you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They don't allow BLOB or TEXT fields.
18) What does i_am_a_dummy flag do in MySQL?
Ans: The i_am_a_dummy flag enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present
19)How many columns can be used for creating index?
Ans: For any standard table you should be created maximum of 16 columns.
20) Write a Query to display current date and time?
Ans: SELECT NOW();
SEE ALSO:
TOP 20 SQL Query interview Questions
Basic SQL Interview Questions
SQL SUB QUERY WITH EXAMPLES
VIEWS IN SQL WITH EXAMPLE
No comments:
Post a Comment