MySQL Interview Questions
Q.General Information About MySQL
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.
MySQL is free software. It is licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/.
Q.MYSQL is a database management system.
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
Q.MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases.
Q.MySQL is Open Source Software.
Open source means that it is possible for anyone to use and modify. Anybody can download MySQL from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. MySQL uses the GPL (GNU General Public License) http://www.gnu.org, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL into a commercial application you can buy a commercially licensed version from us.
Q.Why use MySQL?
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
Q.The technical features of MySQL
For advanced technical information, see section 7 MySQL Language Reference. MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and a programming interface. We also provide MySQL as a multi-threaded library which you can link into your application to get a smaller, faster, easier to manage product. MySQL has a lot of contributed software available.
It is very likely that you will find that your favorite application/language already supports MySQL. The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL.
Q.How do you return the a hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
Q.You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10". Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
Q.How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).
Q.How would you delete a column?
ALTER TABLE techpreparation_answers DROP answer_user_id.
Q.How do you offload the time/date handling to MySQL?
SELECT DATE_FORMAT(techpreparation_timestamp, ‘%Y-%m-%d’) from techpreparation_questions; A similar TIME_FORMAT function deals with time.
Q.How do you convert between Unix timestamps and MySQL timestamps?
UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.
Q.What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.
Q.Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
Q.Explain advantages of MyISAM over InnoDB?
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
Q.What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
Q.What does "tee" command do in
"tee" followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
Q.Explain about database design?
Database design is also called as Data modeling. It is used for long-term management of database. This process is used to store information and to keep data for long term. Creating an efficient structure helps you to channelize information into good channels.
Q.Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it?s not readable by others.
Q.what is difference between candidate key and primary key
Any attribute that is uniquely identify a row in a table is candidate key for the table. We select one of the candidate key as Primary key. All candidate keys which are not chosen as primary key are Alternate keys. The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys
Q.Explain the difference between mysql and mysqli interfaces in PHP?
mysqli is the object-oriented version of mysql library functions.
Q.How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"
Q.Explain the difference between BOOL, TINYINT and BIT.
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
Q.Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
TIMESTAMP : The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range and some special properties that make it especially suitable for tracking data modification times. MySQL displays TIMESTAMP values using the same format as DATETIME values; that is, 'YYYY-MM-DD hh:mm:ss'. The TIMESTAMP data type in MySQL is special in that you can cause a TIMESTAMP column to be initialized or updated automatically to the current date and time without explicitly assigning it a value. It's important to know about the automatic initialization and update properties of TIMESTAMP. These properties make TIMESTAMP columns useful for tracking record modification times, but can be a source of confusion if you're not aware of them.
DEFAULT : The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL.
CURRENT_TIMESTAMP : CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW(). NOW() Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. (There is no .uuuuuu part before MySQL 4.1.13.) The value is expressed in the current time zone. E.g. select now();
Q.How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
Q.State two considerations which can improve the performance of MYSQL?
Two considerations which can improve the performance of MYSQL are as follows: -
1) Fixed length fields take up more space than variable length fields but they are a bit faster.
2) Size of the field should be restricted to the smallest possible value based upon the largest input value.
Q.What Is "mysqladmin" in MySQL?
"mysqladmin" is a command-line interface for administrators to perform server administration tasks. It support a number of commonly used commands like:
"mysqladmin shutdown" - Shuts down the server.
"mysqladmin ping" - Checks if the server is alive or not.
"mysqladmin status" - Displays several important server status values.
"mysqladmin version" - Displays version information of the server.
"mysqladmin create databaseName" - Creates a new database.
"mysqladmin drop databaseName" - Drops an existing database.
Q.How To Create a New View in MySQL?
You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:
mysql> CREATE TABLE comment (faqID INTEGER,
message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,
f.description, c.message FROM faq f, comment c
WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM faqComment;
Q.Explain about normalization?
Applying specific rules (normal forms) to the database is the primary process. These rules should be applied in the order specified starting with the first normal form. These rules should be adhered by every database they are
1)Each column should have only one value
2)Repeating columns of data cannot be done.
Q.How we can count duplicate entery in particular table against Primary Key ? What are constraints?
The syntax in the previous answer (where count(*) > 1) is very questionable. suppose you think that you have duplicate employee numbers. there's no need to count them to find out which values were duplicate but the followin SQL will show only the empnos that are duplicate and how many exist in the table:
Select empno, count(*)
from employee
group by empno
having count(*) > 1
Generally speaking aggregate functions (count, sum, avg etc.) go in the HAVING clause. I know some systems allow them in the WHERE clause but you must be very careful in interpreting the result. WHERE COUNT(*) > 1 will absolutely NOT work in DB2 or ORACLE. Sybase and SQLServer is a different animal.
Q.How do you start MySQL on Linux?
/etc/init.d/mysql start
Q.Write a C/C++ program to add a user to MySQL. The user should be permitted to only "INSERT" into the given database.
#include
#include
int main(void) {
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
/* Change me */
char *server = "localhost";
char *user = "root";
char *password = "PASSWORD";
char *database = "mysql";
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn, server,
user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s ", mysql_error(conn));
exit(1);
}
/* send SQL query */
if (mysql_query(conn, "show tables")) {
fprintf(stderr, "%s ", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
/* output table name */
printf("MySQL Tables in mysql database: ");
while ((row = mysql_fetch_row(res)) != NULL)
printf("%s ", row[0]);
/* close connection */
mysql_free_result(res);
mysql_close(conn);
return 0;
}
Q.Explain about MyISAM table?
This feature is a default type for tables. This table is not so much considered for transactions because it is not considered as safe but this kind of table is very fast in execution. The maximum key length is 1024 bytes and 64 keys per table. Size of this table entirely depends upon the operating system.
Q.Explain about the time stamp field?
TIMESTAMP filed occurs when an INSERT and UPDATE field occurs when there is no value specified for the field. There are many behaviors for TIMESTAMP field and it depends upon the version of MYSQL.
Q.How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
Q.How Many SQL DML Commands Are Supported by "mysql"?
There are 4 SQL Data Manipulation Language (DML) commands that are supported by "mysql". They are listed below with short descriptions:
"INSERT INTO tableName ..." - Inserts new data rows into the specified table.
"DELETE FROM tableName ..." - Deletes existing data rows from the specified table.
"UPDATE tableName SET ..." - Updates existing data rows in the specified table.
"SELECT expressionList FROM ..." - Selects rows from tables and returns rows of values resulted from the specified expression list.
Q.How Do You Know If Your MySQL Server Is Alive?
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 mysqlin
>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.
Q.How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
Q.How To Concatenate Two Character Strings?
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('ggl','center','.com') FROM DUAL;
Q.What Is "mysqlcheck"?
"mysqlcheck" is a command-line interface for administrators to check and repair tables. Here are some sample commands supported by "mysqlcheck":
"mysqlcheck databaseName tableName" - Checks the specified table in the specified database.
"mysqlcheck databaseName" - Checks all tables in the specified database.
"mysqlcheck --all-databases" - Checks all tables in all databases.
"mysqlcheck --analyze databaseName tableName" - Analyzes the specified table in the specified database.
"mysqlcheck --repair databaseName tableName" - Repairs the specified table in the specified database.
Q.Collapse/Expand Answer of Question How To Include Numeric Values in SQL statements?
If you want to include a numeric value in your SQL statement, you can enter it directly as shown in the following examples:
SELECT 255 FROM DUAL; -- An integer
255
SELECT -6.34 FROM DUAL; -- A regular number
-6.34
SELECT -32032.6809e+10 FROM DUAL; -- A floating-point value
-3.20326809e+014
Q.How To Enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good examples:
SELECT X'313233' FROM DUAL;
123
SELECT 0x414243 FROM DUAL;
ABC
SELECT 0x46594963656E7465722E636F6D FROM DUAL;
Q.What Happens If NULL Values Are Involved in Expressions?
If NULL values are used in expressions, the resulting values will be NULL values. In other words:
Arithmetic expressions with NULL values result NULL values.
Comparison expressions with NULL values result NULL values.
Logical expressions with NULL values result NULL values.
The tutorial exercise shows you some interesting examples:
SELECT NULL + NULL FROM DUAL;
NULL
SELECT NULL + 7 FROM DUAL;
NULL
SELECT NULL * 7 FROM DUAL;
NULL
SELECT NULL = NULL FROM DUAL;
NULL
SELECT 0 < NULL FROM DUAL;
NULL
SELECT '' > NULL FROM DUAL;
NULL
SELECT NULL AND TRUE FROM DUAL;
NULL
SELECT NULL OR TRUE FROM DUAL;
1
This is contradicting against the rules!
Q.How To Convert Numeric Values to Character Strings?
You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:
SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
4123.45700
How to get rid of the last 2 '0's?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
--Very poor conversion
Q.What Is InnoDB?
InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
Q.How To Use CASE Expression?
There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return one of the predefined values based on a list of conditions. Here is the syntax of both types of CASE expressions:
CASE value WHEN target_value THEN result
WHEN target_value THEN result
WHEN target_value THEN result
...
ELSE result
END
CASE WHEN condition THEN result
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The tutorial exercise below gives two good examples:
SELECT CASE 'Sun' WHEN 'Mon' THEN 'Open'
WHEN "Fri" THEN "Open" ELSE 'Closed' END FROM DUAL;
Closed
SELECT CASE WHEN HOUR(CURRENT_TIME())<9 br="" losed="" then="">WHEN HOUR(CURRENT_TIME())>17 THEN 'Closed'
ELSE 'Open' END FROM DUAL;
Closed
Q.How To Enter Microseconds in SQL Statements?
If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:
SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;
09:26:50.123000
SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;
09:26:50.000123
Q.What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
Q.How To Start MySQL Server?
If you want to start the MySQL server, you can run the "mysqld" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysqld
"mysqld" will run quietly without printing any message in you command window. So you will see nothing after entering the "mysqld" command. You should trust "mysqld" and believe that MySQL server is running ok on your local computer now.
Another way to start the MySQL server is double-click mysqlinmysqld.exe on your file explorer window.
Q.How To Decrement Dates by 1 in MySQL?
If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28
SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28
0
comments
