After writing interview questions for beginers, i am going to write some interview questions for experienced guys. Here i am picking up some questions which generally asked in interviews:
1- 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.
2- 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.
3- 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.
4- 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.
5- 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.
6- 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
7- 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.
8- What is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.
9- What is UNION?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
10- How To Get a List of Indexes of an Existing Table?
If you want to see the index you have just created for an existing table, you can use the “SHOW INDEX FROM tableName” command to get a list of all indexes in a given table.
11- How To Drop an Existing Index in MySQL?
If you don’t need an existing index any more, you should delete it with the “DROP INDEX indexName ON tableName” statement. Here is an example SQL script:
mysqi> DROP INDEX tip_subject ON tip;
12- Explain federated tables?
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
13- What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
14- How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(datel, date2) function as shown below:
SELECT DATEDI FF( DATE(‘1997-02-28’), DATE(‘1997-03-01’)) FROM DUAL;
15- What is SERIAL data type in MySQL?
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
16- What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
17- How To Drop an Existing View in MySQL?
If you have an existing view, and you dont want it anymore, you can delete it by using the “DROP VIEW viewName” statement as shown in the following script:
mysql> DROP VIEW faqComment;
18- How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
19- How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
If you don’t use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.
You can also find beginer level MySQL interview questions here.
You can also check MySQL Database Backup and Restore commands
