What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?
TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.
How do you login to MySql using Unix shell?
We can login through this command:
| 1 | # [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password> |
What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
MySQL Interview Question
How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.
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.
What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
Advance MySQL Interview Question
How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.
How can you see all indexes defined for a table?
Indexes are defined for the table by:
| 1 | SHOW INDEX FROM <tablename>; |
What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
MySQL Interview Question
What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
How can we get the number of rows affected by query?
Number of rows can be obtained by
| 1 | SELECT COUNT (user_id) FROM users; |
Is Mysql query is case sensitive?
No.
| 123 | SELECT VERSION(), CURRENT_DATE; SeLect version(), current_date; seleCt vErSiOn(), current_DATE; |
All these examples are same. It is not case sensitive.
Advance MySQL Interview Question
What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
- TINYBLOB
- BLOB
- MEDIUMBLOB and
- LONGBLOB
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
- TINYTEXT
- TEXT
- MEDIUMTEXT and
- LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.
What is the difference between mysql_fetch_array and mysql_fetch_object?
Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.
How can we run batch mode in mysql?
Following commands are used to run in batch mode:
| 12 | mysql ;mysql mysql.out |
MySQL Interview Question
Where MyISAM table will be stored and also give their formats of storage?
Each MyISAM table is stored on disk in three formats:
- The ‘.frm’ file stores the table definition
- The data file has a ‘.MYD’ (MYData) extension
- The index file has a ‘.MYI’ (MYIndex) extension
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
| 1 | SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a; |
How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.
Advance MySQL Interview Question
How to display top 50 rows?
In MySql, top 50 rows are displayed by using this following query:
| 12 | SELECT * FROMLIMIT 0,50; |
How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
What are the objects can be created using CREATE statement?
Following objects are created using CREATE statement:
- DATABASE
- EVENT
- FUNCTION
- INDEX
- PROCEDURE
- TABLE
- TRIGGER
- USER
- VIEW
MySQL Interview Question
What are the nonstandard string types?
Following are Non-Standard string types:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What are all the Common SQL Function?
CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.
FORMAT(X, D) – Formats the number X to D significant digits.
CURRDATE(), CURRTIME() – Returns the current date or time.
NOW() – Returns the current date and time as one value.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age
SUBTIMES(A, B) – Determines the difference between two times.
FROMDAYS(INT) – Converts an integer number of days into a date value.
Can you tell what are the different set operations available in MySQL?
The various set operations available in MySQL are as follows:
- UNION – This operation returns all the distinct rows selected by a query
- UNION ALL – This operation returns all the rows selected by a query and also includes all duplicate rows.
- MINUS – This operation returns all the distinct rows selected by the first query but does not select the rows selected by the second query.
- INTERSECT – This operation returns all the distinct rows selected by both queries.
Advance MySQL Interview Question
What is Database White Box Testing?
The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.
What is Database Black Box Testing?
Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis.
What is CTE?
An expression which consists of a temporary set of results defined in a SQL statement is said to be a Common Table Expression(CTE).
MySQL Interview Question
What is a Cursor?
Considered as a pointer to point to one row in a set of rows, a Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.
How can you test for NULL values in a database?
A NULL value is a field with no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.
Refer below for Syntax of IS NULL and IS NOT NULL.
| 12 | SELECT column_names FROM table_name WHERE column_name IS NULL;SELECT column_names FROM table_name WHERE column_name IS NOT NULL; |
Can you tell how can you display the Maximum salary in SQL?
To display the maximum salary in SQL, you can use the inbuilt function called MAX().
Advance MySQL Interview Question
What is the difference between the NVL function, IFNULL function, and the ISNULL function?
The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value. The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function
For example, let us say we have a column(column_3) which has NULL values.
So, if you run the below statement, the output you would get is a NULL value.
| 1 | SELECT column_1 * (column_2 + column_3) FROM Example_Table |
Now, to overcome this, you can use the above three functions as follows:
| 123 | SELECT column_1 * (column_2 + NVL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + IFNULL(column_3,0)) FROM Example_TableSELECT column_1 * (column_2 + ISNULL(column_3,0)) FROM Example_Table |
What is the difference between GUI Testing and Database Testing?
| GUI Testing | Database Testing |
| Also known as User Interface Testing of Front-end Testing. | Also known as Back-End Testing or Data Testing. |
| Deals with items that interact with users. | Deals with items that are hidden from users. |
| Testers need not know SQL. | Testers need to know SQL. |
| GUI Testing focuses on the outlook of the application | Database Testing focuses on the integrity of data in the front end with the data present in the back end |
How To Display Nth Highest Salary From A Table In A Mysql Query?
Consider the table named “Employee”.
Now, to find the Nth salary consider the below statement.
Explore Curriculum
| 1 | SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1 |
So, if you want to find out the 7th largest salary, consider the below query.
| 1 | SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 6,1 |
MySQL Interview Question
What is the command used to create a database using PHP and MySQL?
The command used to create a database using both PHP and MySQL is mysql_create_db(“Database Name”).
Can you tell the Difference Between Mysql_fetch_object And Mysql_fetch_array?
Both of them are similar but vary with a single difference. Mysql_fetch_object return as object and Mysql_fetch_array returns an array. This means that you cannot access the data by their offsets but can only access through its fields names.
What are the ways in which you can retrieve data in the result set of MySQL using PHP?
The different ways in which you can retrieve data in the result set of MySQL using PHP are as follows:
- mysql_fetch_object: This constant fetches a result row as an object.
- mysql_fetch_array: This constant fetches a result row as an associative array, numeric array or as both.
- mysql_fetch_row: This constant gives us a result row as an enumerated array.
- mysql_fetch_assoc: This constant gives us a result row as an associative array.
Advance MySQL Interview Question
Can you tell how many values can Set the function of MySQL to consider?
MySQL’s Set function can take a maximum of 64 values, but can also consider 0 values.
What are the functions used to encrypt and decrypt the data present in MySQL?
The function used to encrypt the data is AES_ENCRYPT() and the function used to decrypt the data is AES_DECRYPT().
How can you increase the performance of MySQL SELECT query?
The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set. The SELECT statement can be either individually used or can be used with other statements such as ORDER BY, GROUP BY, and HAVING clause.
To increase the performance of a MySQL SELECT query, you can use the LIMIT clause to limit MySQL from further search in a table, after collecting the required number of records. Apart from this, we can also use the LEFT JOIN or the RIGHT JOIN to retrieve data from two or more tables.
MySQL Interview Question
How can you take the backup and restore a MySQL database using PHP?
MySQL comes with a utility mysqldump to provide the database backup and restore. The command you can use for backup and restore are as follows respectively.
| 1234 | //To take the backup of databasemysqldump database > backup -file.sql;//To restore the databasemysqldump database < backup -file.sql; |
You can also use the phpMyAdmin user interface to backup your database. If you wish to backup, the database you just have to click on the “export” link on the phpMyAdmin main page.
How can you handle the –secure-file-priv in MySQL?
–secure-file-priv option limits the MySQL Server from loading the directories using the LOAD DATA INFILE.
If you wish to see the directory that has been configured then you may use the SHOW VARIABLES LIKE “secure_file_priv”;
You have mainly two options to tackle:
- Either move your file to the directory specified by the secure-file-priv.
- Or you can disable secure-file-priv. You cannot disable this later on, and you have to remove it from the start itself.
State the differences between MongoDB and MySQL.
| MongoDB | MYSQL |
| An open source database that stores JSON like documents which vary in structure. | An open source relational database management system which stores relational data. |
| Each and every individual record are stored as documents. | Each and every individual record are stored as rows in a table. |
| Documents from a particular class or a group are stored in a collection. | A similar type of records are stored in a table. |
Advance MySQL Interview Question
What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN:
| 1 | SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50; |
Example of IN:
| 1 | SELECT * FROM students where ROLL_NO IN (8,15,25); |
What are the different types of Collation Sensitivity?
Following are the different types of collation sensitivity:
- Case Sensitivity
- Kana Sensitivity
- Width Sensitivity
- Accent Sensitivity
What are the features of MySQL?
MySQL has several useful features that make it a popular database management software.
Some important features of MySQL are mentioned below:
- It is reliable and easy to use too.
- It is a suitable database software for both large and small applications.
- Anyone can install and use it at no cost.
- It is supported by many well-known programming languages, such as PHP, Java, C++, PERL, etc.
- It supports standard SQL (Structured Query Language).
- The open-source license of MySQL is customizable. Hence, a developer can modify it according to the requirements of the application.
MySQL Interview Question
What are the advantages and disadvantages of using MySQL?
There are several advantages of MySQL which are making it a more popular database system now.
Some significant advantages and disadvantages of MySQL are mentioned below.
Advantages:
- It is well-known for its reliable and secure database management system. Transactional tasks of the website can be done more securely by using this software.
- It supports different types of storage engines to store the data and it works faster for this feature.
- It can handle millions of queries with a high-speed transactional process.
- It supports many advanced level database features, such as multi-level transactions, data integrity, deadlock identification, etc.
- Maintenance and debugging processes are easier for this software.
Disadvantages:
- It is hard to make MySQL scalable.
- It is not suitable for a very large type of database.
- The uses of stored routines and triggers are limited to MySQL.
Which MySQL function is used to concatenate string?
CONCAT() function is used to combine two or more string data. The use of this function is here with an example.
Example:
The following()SELECT query with CONCAT function will combine five words, ‘Welcome ‘, ‘to’, ‘SoftwareTestingHelp’,’.’ and ‘com’.
SELECT CONCAT('Welcome ',to ','SoftwareTestingHelp','.',com'); |
CONCAT() function can be used on any table as well. The following SELECT query will show the output by combining two fields, brand and type of items table.
SELECT CONCAT(brand,'=>',type) from items; |
What is an index? How can an index be declared in MySQL?
An index is a data structure of a MySQL table that is used to speed up the queries.
It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table.
Example:
username and email fields are set as the index in the following create table statement.
CREATE TABLE users( username VARCHAR(50) PRIMARY KEY, email VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, INDEX (username, email)); |
The following command will show the index key information of the ‘users’ table.
SHOW INDEXES FROM users; |
Advance MySQL Interview Question
