Today we're having a huge collection of MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. ( MySQL interview questions to help both the freshers and experienced people searching for job. These questions are literally important regarding interview process. You might be asked one of the in the future or similar to one of them. Go through them and submit your own questions asked in the interview.
1. What Is MySQL?
Source)
Some of the top reasons are given below. You can have a complete list of technical features of MySQL from
- Scalability and Flexibility
- High Performance
- High Availability
- Web and Data Warehouse Strengths
- Robust Transactional Support
- Comprehensive Application Development
- Strong Data Protection
- Management Ease
- Lowest Total Cost of Ownership
- Open Source Freedom and 24 x 7 Support
here.
4. What is the default port for MySQL Server?
The default port for MySQL server is 3306.
Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data type like "BLOB" or "TEXT". They use indexes which make them faster.
6. Difference Between Float And Double?
They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. MySQL uses four bytes for single-precision values and eight bytes for double-precision values. (
Source)
7. CHAR_LENGTH And LENGTH?
CHAR_LENGTH, as the name suggests, returns the number of characters / character count. The LENGTH returns the number of bytes / bytes count. To count the Latin characters, both lengths are the same. To count Unicode and other encodings, the lengths are different.
8. What are the advantages of MySQL over Oracle?
9. What Is BLOB?
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 .
10. How Many Triggers Are Allowed In MySQL Table?
Six triggers are allowed in Mysql table:
12. Difference Between BLOB And Text?
BLOB is used for storing binary data while Text is used to store large string.
13. How are MySQL timestamps seen to a user?
In a readable format : YYYY-MM-DD HH:MM:SS.
14. How would you get the current date in Mysql?
By using command SELECT CURRENT_DATE();
15. How one can concatenate strings in MySQL?
By using CONCAT (string1, string2, string3)
16. How do you control the max size of a HEAP table?
Using MySQL config variable called max_heap_table_size.
17. How would you enter Characters as HEX Numbers?
Enter HEX numbers with single quotes and a prefix of (X).
18. You need to show all the indexes defined in a table say 'user' of Database say 'Mysql'. How will you achieve this?
19. How will you export tables as an XML file in MySQL?
Using command mysql -u USER_NAME -xml -e 'SELECT * FROM table_name' > table_name.xml
20. How will you get current date in MySQL?
21. You want to see only certain rows from a result set from the beginning or end of a result set. How will you do it?
Using Command mysql> SELECT * FROM name LIMIT 1; (To Show 1 Record) (Source)
22. Login In MySQL With Unix Shell?
# [mysql dir]/bin/mysql -h hostname -u root -p pass
23. Create a database on the mysql server with unix shell.
mysql> create database databasename;
24. How you will list or view all databases from the mysql server?
26. See all the tables from a database of mysql server.
27. How to see table's field formats or description of table .
28. Delete a database from mysql server.
29. How you will get Sum of column?
31. Show all data from a table.
32. How to returns the columns and column information pertaining to the designated table?
33. Show certain selected rows with the value "xyz".
mysql> SELECT * FROM tablename WHERE fieldname = "xyz";
34. How you will show unique records?
mysql> SELECT DISTINCT columnname FROM tablename;
35. Show selected records sorted in an ascending or descending order?
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;
36. How to Return total number of rows?
37. How to Change a users password from unix shell?
[mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
38. How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server?
39. Restore database from backup.
[mysql dir]/bin/mysql -u username -password databasename < /tmp/databasename.sql.
40. How to dump a table from a database?
[mysql dir]/bin/mysqldump -c -u username -password databasename tablename > /tmp/databasename.tablename.sql
41. How to dump one database for backup?
[mysql dir]/bin/mysqldump -u username -password -databases databasename >/tmp/databasename.sql
42. How to dump all databases for backup. Backup file is sql commands to recreate all db's?
[mysql dir]/bin/mysqldump -u root -ppassword -opt >/tmp/alldatabases.sql
43. How to Load a CSV file into a table?
mysql> LOAD DATA INFILE '/tmp/name.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);
44. How to Update database permissions?
45. List the objects that can be created using CREATE statement?
Below objects can be created using CREATE statement:
46. How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). (Source: Wikipedia)
48. What are the drivers in MySQL?
The SQLyog program is Software which provide GUI tool for MySQL.
So this was all about mysql interview questions and answers. If you've faced any interview then submit your interview questions below in the comment section. For any doubt or query feel free to leave a comment.
