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
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.
1 |
Create
table size(name ENUM('Small', 'Medium','Large'); |
|
|
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:
13. Give string types available for
column?
The string types are:
·
SET
·
BLOB
·
ENUM
·
CHAR
·
TEXT
·
VARCHAR
14. How to get current MySQL version?
1 |
SELECT
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:
16. What are the drivers in MySQL?
Following are the drivers available in MySQL:
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
1 |
SELECT
COUNT (user_id) FROM users; |
|
|
32. Is Mysql
query is case sensitive?
No.
1 2 3 |
SELECT
VERSION(), CURRENT_DATE; SeLect version(), current_date; seleCt vErSiOn(), current_DATE; |
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 %.
1 2 |
SELECT
* FROM employee WHERE emp_name REGEXP
"^b"; SELECT
* FROM employee WHERE emp_name LIKE "%b"; |
|
|
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
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
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:
1 2 |
mysql ; mysql mysql.out |
|
|
37. Where MyISAM
table will be stored and also give their formats of storage?
Each MyISAM table is
stored on disk in three formats:
38. What are the different tables present in
MySQL?
Total 5 types of tables are present:
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.
1 |
SELECT
DISTINCT t1.a FROM t1,t2 where t1.a=t2.a; |
|
|
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:
1 2 |
SELECT
* FROM LIMIT
0,50; |
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:
47. How many TRIGGERS are allowed in MySql table?
SIX triggers are allowed in MySql table. They are as follows:
48. What are the nonstandard string types?
Following are Non-Standard string types:
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 servers
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.