1) 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 current_date();
2) What is an index? How can an index be declared in MySQL?
Answer:
An index is a data structure of a MySQL table that is used to speed up the queries.
It is used by the database search engine to find out the records faster. One or more fields of a table can be used as an index key. Index key can be assigned at the time of table declaration or can be assigned after creating the table.
Example:
username and email fields are set as the index in the following create table statement.
CREATE TABLE employee(
username VARCHAR(50) PRIMARY KEY,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
INDEX (username, email));
SHOW INDEXES FROM employee;
3) What is the default port number of MySQL?
Answer:
The default port number of MySQL is 3306.
4)How to clear screen in MySQL? [duplicate]
Answer:
mysql> system clear;
6) 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 Employee table and the second SELECT command is executed using SUBSTR function and that prints only the first 2 characters of the username field.
SELECT * FROM employee;
SELECT SUBSTR(username,1,2) FROM employee;
7) What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.
There are only six Triggers allowed to use in MySQL database.
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
8) 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.
9) What is the difference between MySQL_connect and MySQL_pconnect?
Mysql_connect:
It opens a new connection to the database.
Every time you need to open and close database connection, depending on the request.
Opens page whenever it is loaded.
Mysql_pconnect:
In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.
The database connection cannot be closed.
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.
10) 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
11) What is MySQL default port number?
MySQL default port number is 3306.
12) How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.
13) What is the query to display top 20 rows?
SELECT * FROM table_name LIMIT 0,20;
