Mysql basic interview questions

MySQL Interview Questions & Answers part 1

By  Parveen Kumar

 

183

 

0

Share on Facebook

 

Tweet on Twitter

   

Q: – What is REPLCAE statement, and how do I use it?

The REPLACE statement is the same as using an INSERT INTO command. The syntax is pretty much the same. The difference between an INSERT statement and a REPLACE statement is that MySQL will delete the old record and replace it with the new values in a REPLACE statement, hence the name REPLACE.

Q: –MySQL has a lot of neat functions. What if I need one that isn't there?

MySQL is so flexible that it allows you to create your own functions. These user-defined functions act the same way that MySQL's own intrinsic functions operate. It is also possible to recompile your functions into the application so that you will always have them, no matter how many times you install.

Q: –Do all unique keys have to be primary keys?

No. MySQL permits only one primary key per table, but there may be a number of unique keys. Both unique keys and primary keys can speed up the selecting of data with a WHERE clause, but a column should be chosen as the primary key if this is the column by which you want to join the table with other tables.

Q: – How many databases can one MySQL RDBMS contain?

Because MySQL uses the file system of the operating system, there really is no limit to the number of databases contained within a single MySQL RDBMS. The size of the database is limited by the operating system. The database tables can only be as big as the OS's file system will allow.

Q: –I want to sort the values of my ENUM and SET columns. How do I do this?

The sort order depends on the order in which the values were inserted. ENUM and SET types are not case sensitive. The value that is inserted reverts to the value that you used when you created the ENUM or SET.

Q: –What can I do with the contents of a mysqldump file?

This file is a complete replica of your database in SQL format. You can do a lot of things with this data. You could re-create your database in Microsoft SQL Server or Sybase by simply cutting and pasting the contents of the file. You could also restore your database by using the dump file and the batching ability of the mysql program.

Q: – What are features of MYSQL ?

MySQL is a full-featured relational database management system. It is very stable and has proven itself over time. MySQL has been in production for over 10 years.

– MySQL is a multithreaded server. Multithreaded means that every time someone establishes a
connection with the server, the server program creates a thread or process to handle that client's
requests. This makes for an extremely fast server. In effect, every client who connects to a MySQL
server gets his or her own thread.

– MySQL is also fully ANSI SQL92-compliant. It adheres to all the standards set forth by the American National Standards Institute.

– another feature of MySQL is its portability—it has been ported to almost every platform. This means that you don't have to change your main platform to take advantage of MySQL. And if you do want to switch, there is probably a MySQL port for your new platform.

– MySQL also has many different application programming interfaces (APIs). They include APIs for Perl, TCL, Python, C/C++, Java (JDBC), and ODBC.

Q: –What do I do if I forget the MySQL root password?

First log in to the system as the same person who is running the mysqld
daemon (probably root). Kill the process, using the kill command.
Restart MySQL with the following arguments:
bin/mysqld Skip-grant
USE mysql;
UPDATE user SET password = password('newpassword') WHERE User = 'root';
Exit
bin/mysqladmin reload

The next time you log in, you will use your new password

Q: –Where is the data stored in a MySQL database?

MySQL uses files to store data. These files are under the data/databasename directory, where databasename is the name of the database. There are three file types: .ISM, .FRM, and .ISD. The .FRM file contain the table schema. The .ISD is the file that actually holds the data. The .ISM file is the file that provides quick access between the two of them.

Q: – Explain the terms "mysqlimport", "mysqldump", "mysqladmin" and "mysqlcheck"?

mysqlimport for importing data files, mysqldump for making backups, mysqladmin for server administration, and mysqlcheck for checking the integrity of the database files.

Q: –How you will determine the options which are used by mysql?

#mysql –help

Q: –How you will determine the version of MySQL?

mysql –version

Q: – How you will connect to the server at a specific IP address with username and password?

mysql –host=10.168.1.33 –user=NAME –password=PASSWORD

Q: –What do you think about this command "mysql> STATUS;"

It will display information about the current connection to the server, as well as status information about the server itself.

Q: –Have you used this command "mysql> HELP contents;"?

Yes, You can get server-side help from this command.

mysql> HELP contents;

You asked for help about help category: "Contents"

For more information, type 'help <item>', where <item> is one of

the following categories:

Administration
Column Types
Data Definition
Functions
Geographic features
Transactions

Q: – What is MySQL Query Browser?

The MySQL Query Browser is a graphical tool designed to provide a user friendly environment in which to construct and execute SQL statements.

Q: –Explain "AUTO_INCREMENT" attribute?

AUTO_INCREMENT attribute may be added to an integer column definition to create a column for which MySQL automatically generates a new sequence number each time you create a new row. There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL.

Q: –What this command "mysqladmin status variables" will do?

This command will display a brief status message, followed by the list of server system variables.