Most frequently Asked MySQL Interview Questions and Answers for freshers and experienced pdf free download
1. What is DDL, DML and DCL?
If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
2. What is primary key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
3. What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.
4. What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
5. What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
6. What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNurrber id, firstname name
FROM employees
id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen
7. What Is ISAM?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
8. What Is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
What Is BDB (BerkeleyDB)?
BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).
9. What Is CSV?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.
10. What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
11. What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
12. What Is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.
13. What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
CHARs full name is CHARACTER.
NCHARs full name is NATIONAL CHARACTER.
By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character
could be stored as 1 byte or upto 4 bytes.
Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.
14. How To Escape Special Characters in SQL statements?
There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:
The escape character () needs to be escaped as (\).
The single quote (‘) needs to be escaped as (‘) or (“) in single-quote quoted strings.
The double quote () needs to be escaped as (“) or (““) in double-quote quoted strings.
The wild card character for a single character () needs to be escaped as (_).
The wild card character for multiple characters (%) needs to be escaped as (%).
The tab character needs to be escaped as (t).
The new line character needs to be escaped as (n).
The carriage return character needs to be escaped as (r).
15. How To Concatenate Two Character Strings?
If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:
SELECT CONCAT(’Welcome’,’ to’) FROM DUAL;
Welcome to
SELECT CONCAT(ggl’,’center’,’.com’) FROM DUAL;
GlobalGuideLi ne.com
16. How To Enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can quote 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. Here are some good examples:
SELECT X313233’ FROM DUAL;
123
SELECT 0x414243 FROM DUAL;
ABC
SELECT Ox46594963656E7465722E636F6D FROM DUAL;
GlobalGuideLi ne.com
17. How To Enter Boolean Values in SQL Statements?
If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:
SELECT TRUE, true, FALSE, false FROM DUAL;
18. How to get rid of the last 2 0’s?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
19. How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(datel, date2) function as shown below:
SELECT DATEDI FF( DATE(’1997-02-28’), DATE(’1997-03-01’))
FROM DUAL;
-1 and different between two time is TIMEDIFF()
20. How To Add a New Column to an Existing Table in MySQL?
ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
1. What is DDL, DML and DCL?
If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
2. What is primary key?
A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.
3. What Is Foreign Key?
A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.
4. What Is Index?
An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.
5. What Is Join?
Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.
6. What Is Union?
Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNurrber id, firstname name
FROM employees
id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen
7. What Is ISAM?
ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.
8. What Is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).
What Is BDB (BerkeleyDB)?
BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).
9. What Is CSV?
CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.
10. What Is Transaction?
A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).
11. What Is Commit?
Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.
12. What Is Rollback?
Rollback is a way to terminate a transaction with all database changes not saving to the database server.
13. What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
CHARs full name is CHARACTER.
NCHARs full name is NATIONAL CHARACTER.
By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character
could be stored as 1 byte or upto 4 bytes.
Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.
14. How To Escape Special Characters in SQL statements?
There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:
The escape character () needs to be escaped as (\).
The single quote (‘) needs to be escaped as (‘) or (“) in single-quote quoted strings.
The double quote () needs to be escaped as (“) or (““) in double-quote quoted strings.
The wild card character for a single character () needs to be escaped as (_).
The wild card character for multiple characters (%) needs to be escaped as (%).
The tab character needs to be escaped as (t).
The new line character needs to be escaped as (n).
The carriage return character needs to be escaped as (r).
15. How To Concatenate Two Character Strings?
If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:
SELECT CONCAT(’Welcome’,’ to’) FROM DUAL;
Welcome to
SELECT CONCAT(ggl’,’center’,’.com’) FROM DUAL;
GlobalGuideLi ne.com
16. How To Enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can quote 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. Here are some good examples:
SELECT X313233’ FROM DUAL;
123
SELECT 0x414243 FROM DUAL;
ABC
SELECT Ox46594963656E7465722E636F6D FROM DUAL;
GlobalGuideLi ne.com
17. How To Enter Boolean Values in SQL Statements?
If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:
SELECT TRUE, true, FALSE, false FROM DUAL;
18. How to get rid of the last 2 0’s?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
19. How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(datel, date2) function as shown below:
SELECT DATEDI FF( DATE(’1997-02-28’), DATE(’1997-03-01’))
FROM DUAL;
-1 and different between two time is TIMEDIFF()
20. How To Add a New Column to an Existing Table in MySQL?
ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
No comments:
Post a Comment