MySQL Interview Questions
1) What is MySql?
MySql is a multi-threading, multi-user open source SQL database management system. It is typically used for web application development and is often accessed using PHP.
2) In which language MYSQL is written?
MySql is written in C and C++ programming
MySql is a multi-threading, multi-user open source SQL database management system. It is typically used for web application development and is often accessed using PHP.
2) In which language MYSQL is written?
MySql is written in C and C++ programming
3) What is MySql's default port number?
MySql default port number is 3306
4) How do you start MySQL on Linux?
/etc/init.d/mysql start command is used for start MySQL on Linux
5) Explain the difference between MySQL and MySQL interfaces in PHP?
MySQL interfaces are the object-oriented version of MySQL library functions.
6) What does the tee command do in MySQL?
Tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by a command note.
7) How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password “newpassword”
8) How to use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
9) List some MySql advantages and disadvantages?
Advantages of using Mysql in comparison to Oracle:
• MySQL is a free, fast, reliable, open-source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.
• MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
• MySQL is great for database-enabled websites while Oracle is made for enterprises.
• MySQL is portable.
Disadvantages of MySql.
• MySQL is not so efficient for large-scale databases.
• It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
• Transactions are not handled very efficiently.
10) What is mysqlcheck do?
mysqlcheck is a client program that checks the integrity of database tables.
11) What is mysqldump?
mysqldump is a client program that creates logical backups of databases.
12) What mysql -u john -p command do?
mysql -u john -p command will prompt for the password for user john before allowing access to the database management system.
If your database server requires a username and password to gain access to the -u and -p command-line options.
13) Explain Timestamp?
The timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. the timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
14) Command to list all Databases in MySql?
Show Databases; command is used to list all Mysql Databases.
15) What is the “i_am_a_dump” flag in MySql?
“i_am_a_dump” enables the MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.
16) What is the difference between UNIX timestamp and MySQL timestamp?
Both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in the readable format of YYYY-MM-DD HH:MM:SS format.
17) What is the maximum length of a table name, database name, and field name in MySQL?
The following table describes the maximum length for each type of identifier.
• Database 64 bytes
• Table 64 bytes
• Column 64 bytes
• Index 64 bytes
• Alias 255 bytes
There are some restrictions on the characters that may appear in identifiers.
18) How many values can the SET function of MySQL take?
MySQL set can take zero or more values but at the maximum, it can take 64 values.
19) 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.
20) Write the command to import a MySQL file?
Run the below command on the terminal to import a MySQL database from the file
mysql -u username -p database_name < file.sql
21) How to store binary data in MySQL?
If you have to store the short piece of data like password hashes then it is advised to store it in VARCHAR.
If you need to store arbitrary chunks of binary data then BLOB is the desired product to store the data.
22) What is the difference between Group by and order by clause in MYSQL?
Both GROUP and ORDER BY clauses are used for organizing data in MYSQL.
• ORDER BY clause is used to sort the query result by specific columns.
• GROUP BY clause is used to summarize unique combinations of columns values.
23) How to find the second highest salary from a table in MySQL?
You can use subquery and IN clause to find the second highest salary from a table in MySQL
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
24) Explain Triggers and Stored Procedures?
A trigger can be defined as that stored procedure that executes when some particular event happens in SQL such as update, insert, delete, etc. On the other hand, a stored procedure is a piece of code purely defined by the user and is written in several local versions of PL or SQL. A stored procedure may return a value if it is made a function and can be invoked by calling it explicitly.
25) What is the candidate key in Mysql?
In MySQL, a candidate key can be any column or a combination of columns that can qualify as a unique key in the database. There can be multiple candidate keys in one table. Each candidate key can qualify as the primary key. Candidate keys can take null values whereas primary keys can never be null.
No comments:
Post a Comment