MYSQL database is the one of the popular open source database used mainly with PHP and other online web aplications. I am going to give you some MySQL questions with answers which will help you in interview as well as brushup your MySQL sills with fast review of these questions.
1-What are the column comparisons operators?
Comparison operator are ‘=’, ‘<>’, ‘<=’, ‘<‘, ‘>=’, ‘>’, ‘<<‘, ‘<<‘, ‘<=>’, ‘AND’, ‘OR’ and ‘LIKE’. These can be used in column comprarisons to the left of the FROM in SELECT statements.
2- 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 table.
You can only use the comparison operators = and <=>.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.
3- How do you return the a hundered books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100;
4- How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8);
5- What is the default port for MySQL Server?
Default port is 3306
6- How would you select all the users, whose phone number is NULL?
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
7- What are ENUMs used for in MySQL?
You can limit the possible values that go into table.
8- What is difference between CHAR_LENGTH and LENGTH?
The CHAR_LENGTH is for character count and LENGTH is for byte count.
9- How are ENUMs and SETs represented internally?
As unique integers representing the powers of two, sue to storage optimizations.
10- How do you change password of an existing user via mysqladmin?
mysqladmin -u root -p password newpassword
11- If the vlaue in column is repeatable, how do you find out unique values?
SELECT DISTINCT user_firstname FROM users;
12- Explain difference between FLOAT, DOUBLE and REAL?
FLOAT stores floating point numbers with 8 place accuracy and take up 4bytes. DOUBLE store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
13- How do you get current version of MySQL?
SELECT VERSION();
14- Is MySQL query has LETTERCASE?
No, for example
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;
15- What is LIKE?
A LIKE pattern match, which succeeds only if the patterns matches the entire value.
16- Differentiate the LIKE and REGEXP operators?
SELECT * FROM pet WHERE name REGEXP “^b”;
SELECT * FROM pet WHERE name LIKE “%b”;
16- What are the string types avaliable for a column?
The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM and SET.
17- What is the REGEXP?
A REGEXP pattern match succeed if the pattern matches anywhere in the value being tested.
18- What is the difference between CHAR and VARCHAR?
- CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
- CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
- VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
19- What is the difference between BLOB and TEXT?
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.
20- How to determine who you are currently logged in as in a MySQL command prompt?
mysql> select user();
OR mysql> select current_user();
