Categories Interview Question

MySQL Interview Question

1. What is MySQL? 

MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)

2. What are the technical features of MySQL? 

MySQL database software is a client or server system which includes

  • Multithreaded SQL server supporting various client programs and libraries
  • Different backend
  • Wide range of application programming interfaces and
  • Administrative tools.

3. Why MySQL is used?

MySQL database server is reliable, fast and very easy to use.  This software can be downloaded as freeware and can be downloaded from the internet.

4. What are Heap tables?

HEAP tables are present in memory and they are used for high speed storage on temporary

basis.

• BLOB or TEXT fields are not allowed

• Only comparison operators can be used =, <,>, = >,=<

• AUTO_INCREMENT is not supported by HEAP tables

• Indexes should be NOT NULL

5. What is the default port for MySQL Server?

The default port for MySQL server is 3306.

6.  What are the advantages of MySQL when compared with Oracle? 

  • MySQL is open source software which is available at any time and has no cost involved.
  • MySQL is portable
  • GUI with command prompt.
  • Administration is supported using MySQL Query Browser

7. Differentiate between FLOAT and DOUBLE? 

Following are differences for FLOAT and DOUBLE:

• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.

• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

 8. Differentiate CHAR_LENGTH and LENGTH?

CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

9. How to represent ENUMs and SETs internally? 

ENUMs and SETs are used to represent powers of two because of storage optimizations.

10. What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values and that can be used during table creation.

 

11. Define REGEXP? 

REGEXP is a pattern match in which  matches pattern anywhere in the search value.

12. Difference between CHAR and VARCHAR? 

Following are the differences between CHAR and VARCHAR:

  • CHAR and VARCHAR types differ in storage and retrieval
  • CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
  • When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

 13. Give string types available for column?

The string types are:

  • SET
  • BLOB
  • ENUM
  • CHAR
  • TEXT
  • VARCHAR

14. How to get current MySQL version?

is used to get the current version of MySQL.

 15. What storage engines are used in MySQL? 

Storage engines are called table types and data is stored in files using various techniques.

Technique involves:

  • Storage mechanism
  • Locking levels
  • Indexing
  • Capabilities and functions.

16. What are the drivers in MySQL?

Following are the drivers available in MySQL:

  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxj

17. 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.

18. What is the 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.

19. How do you login to MySql using Unix shell?

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

20. What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

21. 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.

22. 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.

23. What are federated tables?

Federated tables which allow access to the tables located on other databases on other servers.

24. What, if a table has one column defined as TIMESTAMP?

Timestamp field gets the current timestamp whenever the row gets altered.

25. 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.

26. 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.

27. How can you see all indexes defined for a table?

Indexes are defined for the table by:

SHOW INDEX FROM <tablename>;

28. What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

29. How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

30. What are the column comparisons operators?

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

 31. How can we get the number of rows affected by query?

Number of rows can be obtained by

32.  Is Mysql query is case sensitive?

No.

 

All these examples are same. It is not case sensitive.

33. What is the difference between the LIKE and REGEXP operators?  

LIKE and REGEXP operators are used to express with ^ and %.

 

34. 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.

35. 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.

36. How can we run batch mode in mysql?

Following commands are used to run in batch mode:

 

37. 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

38. What are the different tables present in MySQL?

Total 5 types of tables are present:

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

MyISAM is the default storage engine as of MySQL .

39. 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.

 40. What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

41. How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

42. 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.

43. How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:

 

44. How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

45. What is the different 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. 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

47. How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE and
  • AFTER DELETE

48. What are the nonstandard string types?

Following are Non-Standard string types:

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

49. 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.

50. Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.

MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.

Q #1) What is MySQL?

Answer:

MySQL is an open source DBMS which is developed and distributed by Oracle Corporation.

It is supported by most of the popular operating systems, such as Windows, Linux etc. It can be used to develop a different type of applications but it is mainly used for developing web applications.

MySQL uses GPL (GNU General Public License) license so that anyone can download and install it for developing those applications which will be published or distributed freely. But if a user wants to develop any commercial application using MySQL then he/she will need to buy the commercial version of MySQL.

Q #2) What are the features of MySQL?

Answer:

MySQL has several useful features which 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 the suitable database software for both large and small application.
  • 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.

Q #3) What is the default port number of MySQL?

Answer:

The default port number of MySQL is 3306.

Q #4) How can you find out the version of the installed MySQL?

Answer:

The version of the installed MySQL server can be found out easily by running the following command from the MySQL prompt.

mysql> SHOW VARIABLES LIKE “%version%”;

Q #5) What are the advantages and disadvantages of using MySQL?

Answer:

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 transaction, data integrity, deadlock identification etc.
  • Maintenance and debugging process 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 routine and trigger are limited to MySQL.

Q #6) What is the function of myisamchk?

Answer:

myisamchk is a useful database utility tool that is used to get information about MyISAM database tables.

It is also used for checking, debugging, repairing and optimizing database tables. It is better to use this command when the server is down or when the required tables are not in use by the server.

Syntax:

myisamchk [OPTION] table_name…

The available options of this tool can be retrieved by using the following command.

myisamchk –help

To check or repair all MyISAM tables, the following command will be required for executing from the database directory location.

myisamchk *.MYI

Q #7) What are the purposes of using ENUM and SET data types?

Answer:

ENUM data type is used in the MySQL database table to select any one value from the predefined list.

The value of a particular field can be restricted by defining the predefined list as the field which is declared as ENUM will not accept any value outside the list.

The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.

Example:

Run MySQL server from the command prompt and execute the following SQL commands to know the use of ENUM and SET data type.

The following SQL commands create a new database named ‘newdb’ and select the database for use.

CREATE DATABASE newdb;

USE newdb;

ENUM and SET data types

The following SQL command will create a table named clients with the fields ENUM and SET data type.

1 CREATE TABLE clients (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(50),
4    membership ENUM('Silver''Gold''Diamond'),
5    interest SET('Movie''Music''Concert'));

ENUM and SET data types?2

Insert query will create two records in the table. ENUM field only accepts data from the defined list.

Premium’ value does not exist on the ENUM list. Hence, the value of the ENUM field will be empty for the second record. SET can accept multiple values and both the data will be inserted in the second record.

1 INSERT INTO clients (name, membership,interest)
2 VALUES ('Sehnaz','Gold''Music'),
3                  ('Sourav','Premium''Movie,Concert');
4 SELECT FROM clients;

ENUM and SET data types3

Q #8) What are the differences between a primary key and foreign key?

Answer:

Database table uses a primary key to identify each row uniquely. It is necessary to declare the primary key on those tables that require to create a relationship among them. One or more fields of a table can be declared as the primary key.

When the primary key of any table is used in another table as the primary key or another field for making a database relation, then it is called a foreign key.

The differences between these two keys are mentioned below.

  • The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.
  • The primary key can never accept a NULL value but foreign key accepts a NULL value.
  • When a record is inserted in a table that contains the primary key then it is not necessary to insert the value on the table that contains this primary key field as the foreign key.
  • When a record is deleted from the table that contains the primary key then the corresponding record must be deleted from the table containing the foreign key for data consistency. But any record can be deleted from the table that contains a foreign key without deleting a related record of another table.

Example:

Two tables named manufacturers and items will be created after executing the following two SQL commands.

Here, the primary key of the manufacturers table is used as foreign key in the itemstable with the field name manufacturer_id. Hence, the manufacturer_id field will contain only those values that exist in the manufacturers table.

1 CREATE TABLE manufacturers (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(50));
4 CREATE TABLE items (
5    id INT AUTO_INCREMENT PRIMARY KEY,
6    name VARCHAR(50),
7    type VARCHAR(50),
8    brand VARCHAR(50),
9    manufacturer_id INT,
10    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id));

primary key and foreign key

Q #9) What are the differences between CHAR and VARCHAR data types?

Answer:

Both CHAR and VARCHAR data types are used to store string data in the field of the table.

The differences between these data types are mentioned below:

  • CHAR data type is used to store fixed-length string data and VARCHAR data type is used to store variable-length string data.
  • The storage size of CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.
  • CHAR is used to store small data whereas VARCHAR is used to store large data.
  • CHAR works faster and VARCHAR works slower.

Example:

The following SQL statement will create a table named customers. In this table, the data type of name field is VARCHAR and the data type of phone field is CHAR.

The size of the name field will depend on the length of the inserted value. The size of the phone field will always be 14 characters even if the length of the inserted value is less than 14 characters.

1 CREATE TABLE customers (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(50),
4    phone CHAR(14))

differences between CHAR and VARCHAR data types

Q #10) What is the purpose of using TIMESTAMP data type?

Answer:

A TIMESTAMP data type is used to store the combination of date and time value which is 19 characters long.

The format of TIMESTAMP is YYYY-MM-DD HH:MM: SS. It can store data from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. By default, the current date and time of the server get inserted in the field of this data type when a new record is inserted or updated.

Q #11) What is the difference between mysql_fetch_array() and ysql_fetch_object() ?

Answer:

Both mysql_fetch_array() and mysql_fetch_object() are built-in methods of PHP to retrieve records from MySQL database table.

The difference between these methods is that mysql_fetch_array() returns the result set as an array and mysql_fetch_object() returns the result set as an object.

Example:

1 $result = mysql_query("SELECT id, name FROM clients");
2
3 //using mysql_fetch_array()
4 while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
5     printf("ID: %s  Name: %s", $row[0], $row[1]);
6 }
7
8 //using mysql_fetch_object()
9 while ($row = mysql_fetch_object($result)) {
10     printf("ID: %s  Name: %s", $row->id, $row->name);
11 }

Q #12) How can you filter the duplicate data while retrieving records from the table?

Answer:

A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

Example:

The following SQL command shows all the records of the items table. The output shows that the table contains duplicate values in the type field.

SELECT * from items;

filter the duplicate data when retrieving records from the table

The following SQL command will display the values of type field by removing the duplicate values.

SELECT DISTINCT type from items;

filter the duplicate data when retrieving records from the table

Q #13) What is the difference between NOW() and CURRENT_DATE()?

Answer:

Both NOW() and CURRENT_DATE() are built-in MySQL methods. NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

SELECT now();

SELECT NOW()

SELECT current_date();

Select CURRENT_DATE()

Q #14) Which statement is used in a select query for partial matching?

Answer:

REGEXP and LIKE statements can be used in a select query for partial matching. REGEXP is used to search records based on the pattern and LIKE is used to search any record by matching any string at the beginning or end or middle of a particular field value.

Example:

First, check the existing records of the ‘clients’ table by executing the select query.

SELECT * FROM clients;

statement used in a select query for partial matching1

Run SELECT query with REGEXP clause to search those records from the clients where the client name starts with ‘S

SELECT * FROM clients WHERE name REGEXP “^S”;

statement used in a select query for partial matching2

Run SELECT query with LIKE clause to search those records from the clients where the client name starts with ‘A

SELECT * FROM clients WHERE name LIKE “A%”;

statement used in a select query for partial matching3

Q #15) Which MySQL function is used to concatenate string?

Answer:

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’);

function used to concatenate string1

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;

function used to concatenate string 2

Q #16) How can you change the name of any existing table by using the SQL statement?

Answer:

The following SQL command is used to rename an existing table of the database.

RENAME TABLE table_name TO new_name

Example:

The following command will show the table list of the newdb database.

SHOW TABLES;

change the name of any existing table by using the SQL statement1

The following rename command will rename the table items by new name products.

RENAME TABLE items TO products;

SHOW TABLES;

change the name of any existing table by using the SQL statement2

Q #17) How can you retrieve a portion of any column value by using a select query?

Answer:

SUBSTR() function is used to retrieve the portion of any column. The use of this function is explained here with an example.

Example:

Here, the first select command is used to show all the records of the products table and the second select command is executed using SUBSTR function and that prints only the first five characters of the name field.

SELECT * FROM products;

SELECT SUBSTR(name,1,5) FROM products;

retrieve the portion of any column value by using a select query1

Q #18) What is the purpose of using a HEAP table?

Answer:

The table which uses a hashed index and stores in the memory is called HEAP table. It works as a temporary table and it uses the indexes that make it faster than another table type.

When MySQL crashes for any reason then all the data stored in this table can be lost. It uses fixed length data types. Hence BLOB and TEXT data types are not supported by this table. It is a useful table for those MySQL tasks where speed is the most important factor and temporary data is used.

Q #19) How can you add and remove any column of a table?

Answer:

The syntax for adding any column in an existing table is shown below.

ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]

Example:

DESCRIBE command is used to show the structure of the products table.

DESCRIBE products;

add and remove any column of a table1

The following ALTER command with ADD COLUMN clause will add a new field named ‘price’ in the table products.

ALTER TABLE products ADD COLUMN price DECIMAL(5,2);

DESCRIBE products;

add and remove any column of a table

The syntax for removing any column from an existing table is shown below.

ALTER TABLE table_name DROP COLUMN column_name;

Example:

The following ALTER command with a DROP COLUMN clause will remove the field named ‘brand’ in the table ‘products’.

ALTER TABLE products DROP COLUMN brand;

DESCRIBE products;

add and remove any column of a table?

Q #20) What is an index? How can an index be declared in MySQL?

Answer:

An index is a data structure of 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.

1 CREATE TABLE users(
2         username VARCHAR(50) PRIMARY KEY,
3         email VARCHAR(50) NOT NULL,
4         password VARCHAR(50) NOT NULL,
5         INDEX (username, email));

How index can be declared in MySQL1

The following command will show the index key information of the ‘users’ table.

SHOW INDEXES FROM users;

Show indexes from Users

Q #21) What is meant by decimal (5,2)?

Answer:

A decimal data type is used in MySQL to store the fractional data.

The meaning of decimal (5,2) means that the total length of the fractional value is 5. The field can contain 3 digits before the decimal point and 2 digits after the decimal point. If a user adds any value larger than the defined length then it will insert 999.99 in the field.

The use of this data type is explained in the following example.

Example:

In the following insert query, 789.56 is inserted in the price field. This value is less than 1000 and the total digits with the fractional part are 5. So, this value is valid for this field.

1 INSERT INTO products (type, name, price, manufacturer_id)
2 VALUES ('Mobile''iPhone 8', 789.56, 1);
3 SELECT FROM products;

decimal (5,2) example

In the following insert query, 34789.567 is set for the price field. Then this value is greater than 1000 and the total digits with fractional part are 8. So, the default value 999.99 is inserted in the place of 34789.567.

1 INSERT INTO products (type, name, price, manufacturer_id)
2 VALUES('TV','Sony 32" Smart TV',34789.567, 2);
3 SELECT FROM products;

decimal (5,2) - Incorrect Example

Q #22) What is view? How can you create and drop view in MySQL?

Answer:

A view works as a virtual table that is used to store query and returns a result set when it is called. An updatable view is also supported by MySQL.

The ways in which a view can be created or deleted in MySQL is shown in the following examples.

Create View Example:

The following statement will create a view file named ‘client_list’ based on the table clients.

CREATE VIEW `client_list` AS SELECT `name` as ‘Name’, `membership` as ‘Membership’ FROM `clients`;

Select statement will display the records of client_list value.

SELECT * FROM client_list;

create view in MySQL

Drop View Example:

Drop view statement will delete the view file. Select query will show an error after deleting the view.

DROP VIEW client_list;

SELECT * FROM client_list;

drop view in MySQL

Q #23) What is the function of mysqldump?

Answer:

mysqldump is an useful utility tool of MySQL that is used to dump one or more or all databases from the server for backup or transfer to another database server.

Syntax:

For a single database,

mysqldump [OPTIONS] db_name [TABLES]

For multiple databases,

mysqldump [OPTIONS] –databases DB1 [DB2 DB3…]

For all databases,

mysqldump [OPTIONS] –all-databases

Example:

The following command will create a dump of the ‘newdb’ database and export the content of the database in the file, newdb.sql.

mysqldump –databases newdb > newdb.sql

function of mysqldump

Q #24) How can you change the password of a MySQL user?

Answer:

SET PASSWORD statement is used to change the password of a MySQL user.

Syntax:

SET PASSWORD FOR ‘username’@’hostname’ = PASSWORD(‘password’);

Example:

The following statement will set or change the root password.

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘123456′);

Example: change the password of MySQL user

Q #25) What is the difference between UNIX TIMESTAMP and MySQL TIMESTAMP?

Answer:

Both UNIX TIMESTAMP and MySQL TIMESTAMP are used to represent the date and time value. The main difference between these values is that UNIX TIMESTAMP represents the value by using 32-bits integers and MySQL TIMESTAMP represents the value in the human-readable format.

Example:

A UNIX time value is used by FROM_UNIXTIME function in SELECT query to get the date and time value in the human-readable format.

SELECT FROM_UNIXTIME (1596222320) AS ‘MySQLTIMESTAMP’;

UNIX TIMESTAMP and MySQL TIMESTAMP in a human readable format

Date and time value is used by UNIX_TIMESTAMP function in SELECT query to get the date and time value in the UNIX format.

SELECT UNIX_TIMESTAMP (‘2018-12-25 09:45:40′) AS ‘UNIXTIMESTAMP’;

UNIX TIMESTAMP and MySQL TIMESTAMP in UNIX format

Q #26) How can you import tables from a SQL file into a database by using the MySQL client?

Answer:

Database tables can be imported into a database from a SQL file by using the following MySQL statement.

mysql -u username -p database_name < sql_filename

Example:

If the root user’s password is empty, then the following command will import tables from ‘newdb.sql’ file into the database `mydb`.

mysql -u root mydb < newdb.sql

import tables from ‘newdb.sql’ file into the database `mydb`

database mydb

Q #27) What is the difference between Primary key and Unique key?

Answer:

Unique data is stored in the primary key and unique key fields. Primary key field never accepts NULL value but unique key field accepts a NULL value.

Example:

In the users’ table, id field is the primary key and email field is a unique key. Two records are inserted in the table where the email field is NULL for the 2nd record. The records are inserted properly as the unique field supports a NULL value.

1 INSERT INTO users (username, email, password)
2 VALUES('admin''admin@example.com''7890'),
3                 ('staff''NULL''1234');
4 SELECT FROM users;

difference between primary key and unique key

Q #28) What is the purpose of using IFNULL() function?

Answer:

IFNULL() function takes two arguments. It returns the first argument value if the value of the first argument is not NULL and it returns the second argument if the value of the first argument is NULL.

Example:

Here, the first argument of IFNULL function is not NULL. So, the output is the first argument value.

SELECT IFNULL (“Tutorial”, “fahmidasclassroom.com”);

first argument of IFNULL function is not NULL

Here, the first argument of IFNULL function is NULL. So, the output is NULL.

SELECT IFNULL (“NULL”, “fahmidasclassroom.com”);

First argument of IFNULL function is NULL

Q #29) What is a join? Explain the different types of MySQL joins.

Answer:

The SQL statement that is used to make a connection between two or more tables based on the matching columns is called a join. It is mainly used for complex queries.

Different types of SQL joins are mentioned below:

  • Inner Join: It is a default join. It returns records when the values match in the joining tables.
  • Left Outer Join: It returns all the records from the left table based on the matched records from the right table.
  • Right Outer Join: It returns all the records from the right table based on the matched records from the left table.
  • Full Outer Join: It returns all the records that match from the left or right table.

Example:

Two tables, manufacturers and products are used in this example to show the use of INNER JOIN. Here, SELECT queries are used to show the current records of these two tables.

SELECT * FROM manufacturers;

SELECT * FROM products;

Use of Inner Join - Example

INNER JOIN is used in the following SELECT query where all the id and name of products table will be displayed based on matching manufacturer_id of the productswith an id of the manufacturers table.

1 SELECT products.id, products.name
2 FROM products
3 INNER JOIN manufacturers ON manufacturers.id= products.manufacturer_id;

MySQL Inner Join using SELECT Query

Q #30) How can you retrieve a particular number of records from a table?

Answer:

LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause.

Syntax:

LIMIT starting_number, number_of_rows

Example:

Products table has 5 records which are displayed by the first select query and the second select query is used to display the records from 2nd to 3rd by using LIMIT 1, 2.

SELECT * FROM products;

SELECT * FROM products LIMIT 1, 2;

LIMIT Clause in MySQL

Q #31) How can you export the table as an XML file in MySQL?

Answer:

‘-X’ option is used with `mysql` command for exporting the file as XML. The following statement will export any table from a database as an XML file.

mysql -u username -X -e “SELECT query” database_name

Example:

The following command will export the data of the items table into an xmlData.xml file.

mysql -u root -X -e “SELECT * from products” newdb > xmlData.xml

export the table as a XML file in MySQL

Q #32) What is a CSV table?

Answer:

MySQL table that uses CSV storage engine is called a CSV table. Data are stored as comma-separated values in the CSV table. MySQL server creates a data file with an extension ‘.csv’ to store the content of the CSV table.

Example:

The following create statement will create a CSV file named book.

CREATE TABLE book ( id INT NOT NULL) ENGINE=CSV;

CSV table

Q #33) How can you calculate the sum of any column of a table?

Answer:

SUM() function is used to calculate the sum of any column.

Syntax:

SUM(DISTINCT expression)

Example:

Products table has a numeric field named, price. In this example, the SUM() function is used to calculate the total value of the price field.

1 SELECT FROM products;
2 SELECT SUM(price) as total FROM products;

SUM() function in MySQL

Q #34) How can you count the total number of records of any table?

Answer:

COUNT() function is used to count the total number of records of any table.

Syntax:

COUNT(expression)

Example:

The following select query is used to count the total number of records of the productstable.

SELECT COUNT(*) as `Total Records` FROM products;

COUNT() function in MySQL

Q #35) Explain the difference between delete and truncate.

Answer:

Both DELETE and TRUNCATE commands are used to delete the records from any database table. However, there are some significant differences between these commands. If the table contains AUTO_INCREMENT PRIMARY KEY field then the effect of these commands can be shown properly.

Two differences between these commands are mentioned below.

#1) DELETE command is used to delete a single or multiple or all the records from the table. TRUNCATE command is used to delete all the records from the table or make the table empty.

#2) When DELETE command is used to delete all the records from the table then it doesn’t re-initialize the table. So, AUTO_INCREMENT field does not count from one when the user inserts any record.

But when all the records of any table are deleted by using TRUNCATE command then it re-initializes the table and a new record will start from one for the AUTO_INCREMENT field.

Example:

Previously created users table is used in this example.

First, the SELECT query will show all the records of the users table. DELETE query will delete all the records from the user’s table. INSERT query will insert a new record into the users table. After insert, if the SELECT query executes again then it will be shown that a new id is calculated after the deleted id.

1 SELECT FROM users;
2 DELETE FROM users;
3 INSERT INTO users (username, email)
4 VALUES ('Durjoy''durjoy@gmail.com');
5 SELECT FROM users;

Currently, there are two records in the users table and when a new record is inserted after deleting all the records then the new id is 3, and not 1.

DELETE Command in MySQL

Same queries are executed in this part, just used TRUNCATE statement in place of DELETE. It is shown that the id value of the new record is 1.

1 TRUNCATE table users;
2 INSERT INTO users (username, email)
3 VALUES ('Farheen''farheen@gmail.com');
4 SELECT FROM users;

TRUNCATE Command in MySQL

Q #36) What is a storage engine? What are the differences between InnoDB and MyISAM engines?

Answer:

One of the major components of the MySQL server is the storage engine for doing different types of database operations. Each database table created is based on the specific storage engine.

MySQL supports two types of storage engines i.e transactional and non-transactional. InnoDB is the default storage engine of MySQL which is a transactional storage engine. MyISAM storage engine is a non-transactional storage engine.

The differences between InnoDB and MyISAM storage engines are discussed below:

  • MyISAM supports FULLTEXT index but InnoDB doesn’t support FULLTEXT index.
  • MyISAM is faster and InnoDB is slower.
  • InnoDB supports ACID (Atomicity, Consistency, Isolation, and Durability) property but MyISAM doesn’t.
  • InnoDB supports row-level locking and MyISAM support table-level locking.
  • InnoDB is suitable for large database and MyISAM is suitable for a small database.

Q #37) What is a transaction? Describe MySQL transaction properties.

Answer:

When a group of database operations is done as a single unit then it is called a transaction. If any task of the transactional tasks remains incomplete then the transaction will not succeed. Hence, it is mandatory to complete all the tasks of a transaction to make the transaction successful.

A transaction has four properties which are known as ACID property. These properties are described below.

  • Atomicity: It ensures that all the tasks of a transaction will be completed successfully otherwise all the completed tasks will be rolled back to the previous state for any failure.
  • Consistency: It ensures that the database state must be changed accurately for the committed transaction.
  • Isolation: It ensures that all the tasks of a transaction will be done independently and transparently.
  • Durability: It ensures that all the committed transaction is consistent for any type of system failure.

Q #38) What are the functions of commit and rollback statements?

Answer:

Commit is a transaction command that executes when all the tasks of a transaction are completed successfully. It will modify the database permanently to confirm the transaction.

Syntax:

COMMIT;

Rollback is another transactional command that executes when any of the transactional tasks becomes unsuccessful and undoes all the changes that are made by any transactional task to make the transaction unsuccessful.

Syntax:

ROLLBACK;

Q #39) What is the difference between MyISAM static and MyISAM dynamic?

Answer:

MyISAM static and MyISAM dynamic are the variations of the MyISAM storage engine. The differences between these tables are mentioned below.

  • All the fields of MyISAM static table are of a fixed length and MyISAM dynamic table accepts variable length fields such as BLOB, TEXT etc.
  • After data corruption, it is easier to restore MyISAM static table than MyISAM dynamic table.

Q #40) What is a trigger? How you can create a trigger in MySQL?

Answer:

One of the important features of the MySQL database is a trigger that executes automatically when a particular database event occurs.

It fires after or before the execution of an insert or update or delete statement. It is a very useful option when a database user wants to do some database operations automatically.

Trigger Example:

If you want to delete the items of a supplier from the items table automatically after deleting the entry of the particular supplier from the ‘suppliers’ table then write the trigger in the following way.

Example:

This is an example of after delete trigger that will fire automatically when any record is removed from the manufacturer table and deletes all the records from the productstable where the deleted id of the manufacturer table matches with themanufacturer_id field of the products table.

1 DELIMITER //
2 CREATE TRIGGER manufacturer_after_delete
3 AFTER DELETE
4 ON manufacturers FOR EACH ROW
5 BEGIN
6 DELETE FROM products WHERE products.manufacturers_id = OLD.id;
7 END;
8 //

TRIGGER in MySQL

1) What is MySQL?

MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world’s second most popular and widely used open source database. It is interesting how MySQL name was given to this query language. The term My is coined by the name of the daughter of co-founder Michael Widenius`s daughter, and SQL is the short form of Structured Query Language. Using MySQL is free of cost for the developer, but enterprises have to pay a license fee to Oracle.

Formerly MySQL was initially owned by a for-profit firm MySQL AB, then Sun Microsystems bought it and then Oracle bought Sun Microsystems, so Oracle currently owns MySQL.

MySQL is an Oracle-supported Relational Database Management System (RDBMS) which is based on structured query language. MySQL supports wide ranges of operating systems most famous of those include Windows, Linux & UNIX. Although it is possible to develop a wide range of application with MySQL, it is only used for web applications & online publishing. It is a fundamental part of an open source enterprise known as Lamp.

What is Lamp?

Lamp is a platform used for web development. Lamp uses Linux, Apache, MySQL, and PHP as an operating system, web server, database & object-oriented scripting language respectively. And hence abbreviated as LAMP.


2) In which language MySQL has been written?

MySQL is written in C and C++, and its SQL parser is written in yacc.


3) What are the technical specifications of MySQL?

MySQL has the following technical specifications –

  • Flexible structure
  • High performance
  • Manageable and easy to use
  • Replication and high availability
  • Security and storage management
  • Drivers
  • Graphical Tools
  • MySQL Enterprise Monitor
  • MySQL Enterprise Security
  • JSON Support
  • Replication & High-Availability
  • Manageability and Ease of Use
  • OLTP and Transactions
  • Geo-Spatial Support

4) What is the difference between MySQL and SQL?

SQL is known as the standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

SQL is a computer language, whereas MySQL is a software or an application

SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data


5) What is the difference between database and table?

There is a major difference between a database and a table. The differences are as follows:

  • Tables are a way to represent the division of data in a database while the database is a collection of tables and data.
  • Tables are used to group the data in relation with each other and create a dataset. This dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but the reverse is not true.
  • A database is a collection of organized data and also features which are used to access them, whereas table is a collection of rows and columns which are used to store the data.

6) Why do we use the MySQL database server?

First of all MYSQL server is free to use for developers and a small fee for enterprises.

MySQL server is open source.

The community of MySQL is tremendous and supportive hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market since a long time so all bugs arising in the previous builds have been continuously removed and a very stable version is provided after every update.

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.


7) What are the different tables present in MySQL?

There are many tables that remain present by default. But, MyISAM is the default database engine used in MySQL. There are five types of tables that are present:

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

8) What is the difference between CHAR and VARCHAR?

A list of differences between CHAR and VARCHAR:

  • CHAR is variable-length whereas VARCHAR is of fixed length.
  • CHAR and VARCHAR types are different in storage and retrieval.
  • CHAR column length is fixed to the length that is declared while creating a table. The length value ranges from 1 and 255.
  • When CHAR values are stored when they are right-padded using spaces to a specific length. Trailing spaces are removed when CHAR values are retrieved.
  • CHAR uses static memory allocation whereas VARCHAR uses dynamic memory allocation.
  • CHAR is 50% faster than VARCHAR.

9) What is the difference between TRUNCATE and DELETE in MySQL?

TRUNCATE is a DDL command, DELETE is a DML command.

It is not possible to use Where command with TRUNCATE but you can use it with DELETE command.

TRUNCATE cannot be used with indexed views whereas DELETE can be used with indexed views.

The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.


10) How many Triggers are possible in MySQL?

There are only six Triggers allowed to use in MySQL database.

  1. Before Insert
  2. After Insert
  3. Before Update
  4. After Update
  5. Before Delete
  6. After Delete

11) What is heap table?

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.


12) What is BLOB and TEXT in MySQL?

BLOB is an acronym stands for a large binary object. It is used to hold a variable amount of data.

There are four types of BLOB.

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set, and values are stored, and compared based on the collation of the character set.

There are four types of TEXT.

  1. TINYTEXT
  2. TEXT
  3. MEDIUMTEXT
  4. LONGTEXT

13) What is a trigger in MySQL?

A trigger is a set of codes that executes in response to some events.


14) What is the difference between heap table and temporary table?

Heap tables:

Heap tables are found in memory. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The Temporary table is deleted after the current client session terminates.

Main differences:

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).


15) What is the difference between FLOAT and DOUBLE?

FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.


16) What are the advantages of MySQL in comparison to Oracle?

  1. 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.
  2. MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.
  3. MySQL is great for database enabled websites while Oracle is made for enterprises.
  4. MySQL is portable.

17) What are the disadvantages of MySQL?

  1. MySQL is not so efficient for large scale databases.
  2. It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
  3. Transactions are not handled very efficiently.
  4. Functionality of MySQL is highly dependent of other addons.
  5. Development is not community driven.

18) What is the difference between CHAR and VARCHAR?

  1. CHAR and VARCHAR are differ in storage and retrieval.
  2. CHAR column length is fixed while VARCHAR length is variable.
  3. The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
  4. CHAR is 50% faster than VARCHAR.
  5. CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.

19) What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

  1. It opens a new connection to the database.
  2. Every time you need to open and close database connection, depending on the request.
  3. Opens page whenever it is loaded.

Mysql_pconnect:

  1. In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
  2. The database connection cannot be closed.
  3. It is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

20) What does “i_am_a_dummy flag” do in MySQL?

The “i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present. Hence it can save the programmer from deleting the entire table my mistake if he does not use WHERE clause.


21) How to get the current date in MySQL?

To get current date, use the following syntax:

  1. SELECT CURRENT_DATE();

22) What are the security alerts while using MySQL?

Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change root username and password Restrict or disable remote access.


23) How to change a password for an existing user via Mysqladmin?

Mysqladmin -u root -p password “newpassword”.


24) What is the difference between Unix timestamps and MySQL timestamps?

Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.


25) How to display Nth highest salary from a table in a MySQL query?

Let us take a table named the employee.

To find Nth highest salary is:

select distinct(salary)from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:

select distinct(salary)from employee order by salary desc limit 2,1


26) What is MySQL default port number?

MySQL default port number is 3306.


27) What is REGEXP?

REGEXP is a pattern match using a regular expression. A Regular expression is a powerful way of specifying a pattern for a sophisticated search.

Basically it is a special text string for describing a search pattern. To understand it better you can think of a situation of daily life when you search for .txt files to list all text files in the file manager. The regex equivalent for .txt will be .*\.txt.


28) How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.


29) What is the difference between NOW() and CURRENT_DATE()?

NOW() command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE() shows the current year with month and date only.


30) What is the query to display top 20 rows?

SELECT * FROM table_name LIMIT 0,20;


31) Write a query to display current date and time?

If you want to display current date and time, use –

SELECT NOW();

If you want to display current date only, use:

SELECT CURRENT_DATE();


32) What is save point in MySQL?

A defined point in any transaction is known as savepoint.

SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.


33) What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.


34) How do you backup a database in MySQl?

It is easy to back up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left-hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to back up. Then specify the option you want under export and save the output.


35) What are the different column comparison operators in MySQL?

The =, <>, <=, <, >=, >, <<, >>, < = >, AND, OR or LIKE operator are the comparison operators in MySQL. These operators are generally used with SELECT statement.


36) Write a query to count the number of rows of a table in MySQL.

SELECT COUNT user_id FROM users;


37) Write a query to retrieve a hundred books starting from 20th.

SELECT book_title FROM books LIMIT 20, 100;


38) Write a query to select all teams that won either 1, 3, 5 or 7 games.

SELECT team_name FROM team WHERE team_won IN (1, 3, 5, 7);


39) What is the default port of MySQL Server?

The default port of MySQL Server is 3306.


40) How is MyISAM table stored?

MyISAM table is stored on disk in three formats.

  • ‘.frm’ file : storing the table definition
  • ‘.MYD’ (MYData): data file
  • ‘.MYI’ (MYIndex): index file

41) What is the usage of ENUMs in MySQL?

ENUMs are string objects, by defining ENUMs we allow the end user to give correct input as in case the user provides an input which is not part of the ENUM defined data then the query won’t execute and an error message will be displayed which says “Wrong Query”. For instance suppose we want to take the gender of the user as an input so we specify ENUM(‘male’, ‘female’, ‘other’) and hence whenever the user tries to input any string any other than these three it results in an error.

ENUMs are used to limit the possible values that go in the table:

For example:

CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’); INSERT months VALUES (‘April’).


42) What are the advantages of MyISAM over InnoDB?

MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.


43) What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

Mysql_fetch_object is used to retrieve the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

For example:

Using mysql_fetch_object field can be accessed as $result->name.

Using mysql_fetch_array field can be accessed as $result->[name].

Using mysql_fetch_row($result) where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

  1. $result = mysql_query(“SELECT * from students”);
  2. while($row = mysql_fetch_row($result))
  3. {
  4.         Some statement;
  5. }

44) What is the difference between mysql_connect and mysql_pconnect?

Mysql_connect() is used to open a new connection to the database while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded mysql_pconnect() does not open the database.


45) What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().


46) What is MySQL data directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.


47) How do you determine the location of MySQL data directory?

The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.


48) What is the usage of regular expressions in MySQL?

In MySQL, regular expressions are used in queries for searching a pattern in a string.

    • * Matches 0 more instances of the string preceding it.
    • + matches one more instances of the string preceding it.
    • ? Matches 0 or 1 instances of the string preceding it.
    • . Matches a single character.
    • [abc] matches a or b or z
    • | separates strings
    • ^ anchors the match from the start.
    • “.” Can be used to match any single character. “|” can be used to match either of the two strings
    • REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

  1. Select employee_name
  2. From employee
  3. Where employee_name REGEXP ‘1000’
  4. Order by employee_name

49) What is the usage of “i-am-a-dummy” flag in MySQL?

In MySQL, the “i-am-a-dummy” flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.


50) Which command is used to view the content of the table in MySQL?

The SELECT command is used to view the content of the table in MySQL.


51) Explain Access Control Lists.

An ACL is a list of permissions which are associated with an object. MySQL keeps the Access Control Lists cached in memory and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object and if the permissions are available then execution completes successfully.


52) What is InnoDB?

InnoDB is a storage database for SQL. The ACID-transactions are also provided in addition InnoDB also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.


53. What is ISAM?

It is a system for file management developed by IBM which allows records to access sequentially or even randomly.


54. How can we run batch mode in MySQL?

To perform batch mode in MySQL we use the following command:

mysql;

mysql mysql.out;


55. What are federated tables?

Federated tables are tables which points to the tables located on other databases on some other server.


56. What is the difference between primary key and candidate key?

To identify each row of a table, a primary key is used. For a table, there exists only one primary key.

A candidate key is a column or a set of columns which can be used to uniquely identify any record in the database without having to reference any other data.


57. What are the drivers in MySQL?

Following are the drivers available in MySQL:

  • PHP Driver
  • JDBC Driver
  • ODBC Driver
  • C WRAPPER
  • PYTHON Driver
  • PERL Driver
  • RUBY Driver
  • CAP11PHP Driver
  • Ado.net5.mxz

58. What Is DDL, DML, And DCL?

Majorly SQL commands can be divided into three categories i.e. DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.

Data Manipulative Language (DML) deals with operations and manipulations on the data the commands in DML are Insert, Select etc.

Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of database is defined by these.

1. Using SELECT statement find the version of the server you are running and print the name of the current database?

Ans: The below MySQL command will show server version and currently selected Database.

mysql> SELECT VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | NULL       |
+-------------------------+------------+
1 row in set (0.06 sec)

In the Database Column it is showing NULL value because we have not selected any database. So, select database as shown in the following command.

mysql> use Tecmint;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | tecmint    |
+-------------------------+------------+
1 row in set (0.00 sec)
2. USE NOT Operator (!) to select all the users except ‘SAM’ from a table say ‘Tecmint’

Ans: The below statement will show all the columns of all the users from table ‘Tecmint‘ except the user ‘SAM‘.

mysql> SELECT * FROM Tecmint WHERE user !=SAM;

+---------------------+---------+---------+---------+---------+-------+ 
| date                | user    | host 	 | root     | local   | size  | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus  | barb     | venus   | 98151 | 
| 2001-05-15 08:50:57 | TIM     | venus  | phil     | venus   | 978   | 
+---------------------+---------+---------+---------+---------+-------+
3. Is it possible to implement ‘AND’ with NOT (!) Operator.

Ans: The AND operator is used when we use (=) and the operator OR is used when we use (!=). An example of (=) with AND Operator.

mysql> SELECT * FROM mail WHERE user = SAM AND root = phil

An Example of (!=) with OR Operator.

mysql> SELECT * FROM mail WHERE user != SAM OR root != phil

+---------------------+---------+---------+---------+---------+-------+ 
| date                | user    | host    | root    | local   | size  | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus   | barb    | venus   | 98151 | 
+---------------------+---------+---------+---------+---------+-------+
  1. = : means Equal to
  2. != : Not Equal to
  3. ! : represents NOT Operator

The AND & OR are treated as joining operators in MySQL.

4. What IFNULL() statement is used for in MySQL?

Ans: The Query in MySQL can be written precisely using IFNULL()statement. The IFNULL() statement test its first argument and returns if it’s not NULL, or returns its second argument, otherwise.

mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM taxpayer;

+---------+---------+ 
| name 	  | id      | 
+---------+---------+ 
| bernina | 198-48  | 
| bertha  | Unknown | 
| ben     | Unknown | 
| bill    | 475-83  | 
+---------+---------+
5. 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?

Ans: We need to use LIMIT clause along with ORDER BY to achieve the above described scenario.

Show 1 Record
mysql> SELECT * FROM name LIMIT 1;

+----+------+------------+-------+----------------------+------+ 
| id | name | birth      | color | foods                | cats | 
+----+------+------------+-------+----------------------+------+ 
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0    | 
+----+------+------------+-------+----------------------+------+
Show 5 Record
mysql> SELECT * FROM profile LIMIT 5;

+----+------+------------+-------+-----------------------+------+ 
| id | name | birth      | color | foods                 | cats | 
+----+------+------------+-------+-----------------------+------+ 
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza  | 0    | 
| 2  | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3    | 
| 3  | Brit | 1957-12-01 | red   | burrito,curry,pizza   | 1    |   
| 4  | Carl | 1973-11-02 | red   | eggroll,pizza         | 4    | 
| 5  | Sean | 1963-07-04 | blue  | burrito,curry         | 5    | 
+----+------+------------+-------+-----------------------+------+
mysql> SELECT * FROM profile ORDER BY birth LIMIT 1;

+----+------+------------+-------+----------------+------+ 
| id | name | birth      | color | foods          | cats | 
+----+------+------------+-------+----------------+------+ 
| 9  | Dick | 1952-08-20 | green | lutefisk,fadge | 0    | 
+----+------+------------+-------+----------------+------+
6. Oracle Vs MySQL. Which one and Why?

Ans: Well both has its advantages and disadvantages. As a matter of time I prefer MySQL.

Reason for Selection MySQL Over oracle
  1. Mysql is FOSS.
  2. MySQL is portable.
  3. MYSQL supports both GUI as well as Command Prompt.
  4. MySQL Administration is supported over Query Browser.
7. How will you get current date in MySQL?

Ans: Getting current date in MySQL is as simple as executing the below SELECT Statement.

mysql> SELECT CURRENT_DATE();

+----------------+
| CURRENT_DATE() |
+----------------+
| 2014-06-17     |
+----------------+
8. How will you export tables as an XML file in MySQL?

Ans: We use ‘-e‘ (export) option to export MySQL table or the whole database into an XML file. With large tables we may need to implement it manually but for small tables, applications like phpMyAdmin can do the job.

A native command of MySQL can do it.

mysql -u USER_NAME –xml -e 'SELECT * FROM table_name' > table_name.xml

Where USER_NAME is username of Database, table_name is the table we are exporting to XML and table_name.xml is the xml file where data is stored.

9. What is MySQL_pconnect? And how it differs from MySQL_connect?

Ans: MySQL_pconnect() opens a connection that is persistent to the MySQL Database which simply means that the database is not opened every-time the page loads and hence we can not use MySQL_close() to close a persistent connection.

A brief difference between MySQL_pconnect and MySQL_connect are.

Unlike MySQL_pconnect, MySQL_connect – Opens the Database every-time the page is loaded which can be closed any-time using statement MySQL_close().

10. You need to show all the indexes defined in a table say ‘user’ of Database say ‘mysql’. How will you achieve this?

Ans: The following command will show all the indexes of a table ‘user’.

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| user  |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | 
| user  |          0 | PRIMARY  |            2 | User        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
2 rows in set (0.00 sec)
11. What are CSV tables?

Ans: CSV stands for Comma-Separated Values aka Character-Separated Values. CSV table stores data in plain text and tabular format. It typically contains one record per line.

Each record is separated by specific delimiters (Comma, Semi-colon, …) where each record has same sequence of field. CSV tables are most widely used to store phone contacts to Import and Export and can be used to store any sort of plain text data.

That’s all for now. I’ll be here again with another Interesting article, you people will love to read. Till then stay tuned and connected to Tecmint and Don’t forget to provide us with your valuable feedback in the comment section below.

MySQL is basically an open sourced and one of the most commonly used Relational Database Management System (RDBMS). It provides its extensive support and compatibility towards Linux, macOS, Windows operating systems. It is mainly one of the most common extensions of SQL. It is the central component of a LAMP (Linux, Apache, MySQL and Perl/PHP/Python) and is written in C and C++. Some of the important features of MySQL are mentioned below:

  1. It supports DDL (Data definition language) with respect to InnoDB Storage Engine
  2. It provides support to Information Schema
  3. There is SSL support present
  4. It has embedded database library
  5. It has built-in replication support
  6. It provides cross-platform support.

Now, if you are looking for a job which is related to MySQL then you need to prepare for the MySQL Interview Questions. It is true that every interview is different as per the different job profiles but still to clear the interview you need to have a good and clear knowledge of MySQL processes. Here, we have prepared the important MySQL Interview Question and answers which will help you get success in your interview.
Below are the 10 important MySQL Interview Questions and answers that are frequently asked in an interview. These questions are divided into two parts are as follows:

Part 1 – MySQL Interview Questions (Basic)

This first part covers basic MySQL Interview Questions and Answers

Q1. What do you mean by MySQL Workbench?

Answer:
MySQL Workbench is basically the officially integrated environment for MySQL. It is implemented to graphically control the MySQL and visually design database structures. It basically gives users the provision to control the functionalities as per their requirements. It is mainly available in two versions, one is the open source free community edition and the other one is the proprietary standard edition.

Q2. Explain MySQL Database Workbench?

Answer:
MySQL Database Workbench is basically the software application which has been used to develop and administrate different relational databases using SQL. It has been developed by Upscene Productions. It provides the support to the below mentioned relational databases:

  1. MySQL Database
  2. Microsoft SQL Server
  3. Oracle Databases
  4. Nexus DB
  5. Inter Base
  6. SQL Anywhere

Let us move to the next MySQL Interview Questions.

Q3. What do you mean my Joins and explain different types of MySQL Joins?

 Popular Course in this category
JDBC Training Course9 Online Courses | 27+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (1,133 ratings)
Course Price
₹4999 ₹12999
View Course

Related Courses

PHP CourseWindows 10 Training

Answer:
In MySQL, joins are basically used to fetch the records from two or more table using some interconnected common fields or keys among the selected tables. There are mainly three types joins present in MySQL.

a.Inner Join: In case of inner join, it is used to fetch the list of rows when there is a match among the specified tables. It is the default join type. Syntax for the same is given below: Syntax: SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;

b. Left Join: In case of left join, it returns all rows from the left table even if there is no match in the right table.
Syntax: SELECT column_name(s)
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

c. Right Join: In case of right join, it returns all rows from the right table even if there is no match in the left table.
Syntax: SELECT column_name(s)
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Q4. Explain different types of MySQL functions?

Answer:
This is the basic MySQL Interview Questions asked in an interview. Find below the different types of functions used in MySQL.

  1. MySQL String Functions: Examples are- INSERT, CONCAT, FORMAT, INSERT, LENGTH, LEFT, FIELD, LTRIM, TRIM, UPPER etc.
  2. MySQL Numeric Functions: Examples are- AVG, DIV, EXP, MAX, MIN, POWER, TRUNCATE etc.
  3. MySQL Date Functions: Examples are – ADDDATE, ADDTIME, DATE, MONTH, MINUTE etc.
  4. MySQL Advanced Functions: Examples are – BIN, CASE, CAST, DATABASE, IF, ISNULL, VERSION etc.

Q5. Explain the differences between primary key and foreign key constraint?

Answer:
A primary key constraint is used to uniquely identify every record in a table. It always contains unique values and never contains any NULL values. Below is the syntax for the same whereas foreign key constraint is used to link between two tables. It is basically the field which refers to the primary key of the other table.

Part 2 – MySQL Interview Questions (Advanced)

Let us now have a look at the advanced MySQL Interview Questions.

Q6. Explain the differences between delete, drop and truncate?

Answer:
Find below the basic differences:

Delete: Delete is a DML statement and it can be implemented using ‘where’ clause and can also be rolled back. Syntax: DELETE FROM table_name WHERE column_name = column_value;

Drop: Drop is a DDL statement and it can’t be rolled back. By using this, the entire table and all its constraints and privileges will be removed. Syntax: DROP TABLE table_name;

Truncate: Truncate is a DDL statement and it is used to remove all the rows from a table but its structures, constraints, and indexes will remain as it is. Syntax: TRUNCATE TABLE table_name;

Q7. Explain the main difference between InnoDB and MyISAM?

Answer:
In the case of InnoDB, it is used to store the tables in tablespace whereas, in the case of MyISAM, it stores each MyISAM table in a separate file.

Let us move to the next MySQL Interview Questions.

Q8. Explain different types of tables in MySQL?

Answer:
Find below different types of a table in MySQL.

InoDB: This type of table is mainly used to enhance and support transaction using COMMIT and ROLLBACK commands.

HEAP: This type of table is mainly used to process data access in a more faster than the normal speed. But on the downside of it, all data will be lost in case of table deletion.

BDB: This type of table is also used for transaction support implemented using COMMIT command but its processing speed is much slower than that of InoDB.

Q9. Explain MySQL transaction properties?

Answer:
This is the most asked MySQL Interview Questions asked in an interview. MySQL transactions mainly consist of four properties.

  1. Atomicity: The property is mainly used to control all the operations and ensure all the transactions get completed successfully.
  2. Consistency: This ensures the database changes according to the successfully committed transactions.
  3. Isolation: This is basically used to control the independent transactions.
  4. Durability: This is used to control and measure the effect and presence of committed transactions in case of system or database failure.

Q10. Explain few pros and cons of MySQL?

Answer:
Below are few important pros and cons of MySQL

Pros:

  1. The MySQL products are more stable and solid compared to its competitors
  2. It is easier to use from the user experience perspective
  3. It is open source and thus is free to use.
  4. There is large and extensive community support exists for MySQL

Cons:

  1. There is sometimes a bit performance scaling issues exists in case of MySQL
  2. Sometimes development progress gets lagged due to lacking of community development approach.

Leave a Reply

Your email address will not be published. Required fields are marked *