MySql Interview Question and Answers
1. What is mysql.
Mysql is an open source RDBMS(Relational Database Management System).It is using SQL (Structured Query Language).Because its open source its free to use ,any one can download it and use it.
2.What are the features of Mysql.
2.Wide range of application programming interfaces.
3. Stored procedures,Triggers,Cursor.
3.What is the default port for MySQL Server
4. Why Mysql Widely used.
Mysql is open source and free to use .Also it is reliable and fast.
5. What is DDL, DML and DCL ?
DDL:- DDL stands for Data Definition Language.Its deals with database schemas and descriptions of how the data should reside in the database.Example:- CREATE TABLE or ALTER TABLE
DML:- DML stands for data manipulation Language.Its deals with SELECT, INSERT, etc.
DCL:- DCL stand for Data Control Language.Its contain commands like GRANT,Revoke etc.
6. Difference between FLOAT, DOUBLE and REAL.
Float:- FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes.
Double:- DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.
Real:- Real contain 8 place accuracy and take 4 bytes.
7. What are the Joins in Mysql.
Joins are used in an MySql statement to query data from two or more tables, based on a relationship between certain columns in these tables.
8. Explain Different Joins in MySql.
The different joins in mysql are:-
Inner Join:- Return rows when there is at least one match in both tables.
Left Join:- Return all rows from the left table, even if there are no matches in the right table.
Right Join:- Return all rows from the right table, even if there are no matches in the left table.
Full Join:- Return rows when there is a match in one of the tables.
9. Is Mysql query is case sensitive.
No,mysql queries are not case sensitive.
10. What are all the Common MySql Function.
NOW():- Returns the current date and time as one value.
CURRDATE() :- Returns the current date or time.
CURRTIME() :- Returns the current date or time.
CONCAT(X, Y) :- Concatenates two string values to create a single string output. Mostly used to combine two or more fields into one single field.
DATEDIFF(X, Y) :- The difference between two dates.
11. Explain DISTINCT Statement in MySql.
In table some column may be contain the duplicates value but you want to show only same value once not again and again.he DISTINCT keyword can be used to return only distinct (different) values not have two same values.
SELECT DISTINCT Column_name FROM table_name;
12. How MySQL Optimizes DISTINCT.
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT table1.a FROM table1,table2 where table1.a=table2.a;
13. Difference between CHAR and VARCHAR.
CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.Varchar get space according to data interred in column.
14. Difference between CHAR_LENGTH and LENGTH
The CHAR_LENGTH for character count and LENGTH for byte count.
15.How get second highest salary from database table.
For getting second highest salary you will use limit.Example as:-
select salary from table order by salary desc limit 1,1;
16. What are HEAP tables.
HEAP tables are in-memory. They are usually used for high-speed temporary storage.
No TEXT or BLOB fields are allowed within HEAP tables.
Only use the comparison operators = and <=>.
HEAP tables do not support AUTO_INCREMENT.
Indexes must be NOT NULL.
17. How do you concatenate strings in MySQL.
CONCAT (string1, string2, string3)
18. How to control the max size of a HEAP table.
19. How many columns can be used for creating Index.
Maximum of 16 indexed columns can be created for any table.
20. Different string types available for column in database.
21. 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.
Thee COUNT(*)s execute slower than in MyISAM due to tablespace complexity
22. Advantages of InnoDB over MyISAM.
Foreign key constraints
23. Where MyISAM table will be stored and also give their formats of storage.
Each MyISAM table is stored on disk in three formats:-
1. The data file has a ‘.MYD’ (MYData) extension.
2. The ‘.frm’ file stores the table definition.
3. The index file has a ‘.MYI’ (MYIndex) extension.
24. How do you get a portion of a string?
SELECT SUBSTR(title, 1, 10) from user.
25. How to get current MySQL version.
Using SELECT VERSION ();
26. Difference between mysql and mysqli interfaces in PHP.
mysqli is the object-oriented version of mysql library functions.
27. What storage engines are used in MySQL.
Storage engines are called table types and data is stored in files using various techniques.
Like Indexing,Locking levels,Capabilities and functions.
28. What do _ and % mean inside LIKE statement?
_ is exactly one character.
% corresponds to 0 or more characters.
29. Difference between primary key and candidate key.
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
30. What are the different table present in MYsql?
MyISAM:- This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.
HEAP : – Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields.
ISAM :- Based on Indexed Sequntial Access Method.
InoDB :- Supports Transactions using COMMIT & ROLLBACK
BDB :- Supports Transactions using COMMIT & ROLLBACK. Slower that others.
31. Difference between mysql_fetch_array and mysql_fetch_object.
mysql_fetch_object :- Fetch a result row as an OBJECT.
mysql_fetch_array :- Fetch a result row as an associative ARRAY, a numeric array, or both
32. How do you start MySQL on Linux.
33. How do you login to MySql using Unix shell.
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
34. How do you change a password for an existing user via mysqladmin.
mysqladmin -u root -p password “newpassword”
35. What does myisamchk do.
It compress the MyISAM tables, which reduces their disk or memory usage.
36. What are federated tables.
Federated tables which allow access to the tables located on other databases on other servers.
37. How can you see all indexes defined for a table.
SHOW INDEX FROM <tablename>;
38. What are ENUMs used for in MySQL.
You can limit the possible values that go into the table.
CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…);
INSERT months VALUES (’April’);
39. How to Use mysqldump to create a copy of the database.
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
40. How can we get the number of rows affected by query.
SELECT COUNT (ID) FROM users;
41. How to display top 100 rows from table.
SELECT * table_name FROM LIMIT 0,100
42. Difference between BLOB AND TEXT.
BLOB:- A BLOB is a binary large object that can hold a variable amount of data.
There are 4 types of BLOB.
Tinyblob ,Blob, Mediumblob and Longblob.
TEXT:- A TEXT is a case-insensitive BLOB.
There are also 4 types of text:-
43. What is ISAM.
ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.
44. What is InnoDB.
lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
45. Difference between NOW() and CURRENT_DATE().
NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
46. How do I find out all databases starting with ‘user’ to which I have access to.
SHOW DATABASES LIKE ‘user%’;
47. How would you change a table to InnoDB.
ALTER TABLE user ENGINE innodb;
48. How do you convert a string to UTF-8.
SELECT (user USING utf8);
49. How would you delete a column.
ALTER TABLE user DROP user_id.
50. How many TRIGGERS are allowed in MySql table.
The various triggers allowed in mysql are:-
51. How do you get the month from a timestamp.
SELECT MONTH(user_timestamp) from user_questions;
52. Difference between the LIKE and REGEXP operators.
Like are expressed using %.
SELECT * FROM user WHERE user_name LIKE "%b";
REGEXP are expressed using ^.
SELECT * FROM user WHERE user_name REGEXP "^b";
53. Difference between Unix timestamps and MySQL timestamps.
Unix timestamps are stored as 32-bit integers.
MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.
54. What is the difference between MyISAM Static and MyISAM Dynamic.
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
MyISAM Static would be easier to restore in case of corruption.