What are the advantages of stored procedures, triggers, indexes?

Stored procedure : Stored procedureis a set of SQL commands that can be compiled and stored in the server. Once this process has been done, user don’t need to keep re-issuing the entire query he can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once and less information needs to be sent between the server and the user. We can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side.

Trigger : A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, we can install a stored procedure that is triggered each time a record is deleted from a transaction table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

Indexes : Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

Sql injection

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.



Source:wikipedia.org

What are the differences between DROP a table and TRUNCATE a table

DROP TABLE table_name - This will delete the table and its data.
TRUNCATE TABLE table_name - This will delete the data of the table, but not the table definition.

How can we encrypt the username and password using PHP?

1)
encrypt a password with the following Mysql>SET PASSWORD=PASSWORD("Password");
2)
use the MySQL PASSWORD() function to encrypt username and password. For example, INSERT into user (password, ...) VALUES (PASSWORD($password)), ...);

What is the difference between mysql_fetch_object and mysql_fetch_array?

MySQL fetch object will collect first single matching record where mysql_fetch_array will collect all matching records from the table in an array

Explain the difference between FLOAT, DOUBLE and REAL

FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

Explain the difference between BOOL, TINYINT and BIT.

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.

What does myisamchk do?

It compressed the MyISAM tables, which reduces their disk usage.

Explain the difference between MyISAM Static and MyISAM Dynamic

MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

How can we know the number of days between two given dates using MySQL?

SELECT DATEDIFF('2007-03-07','2005-01-01');

How can I load data from a text file into a table?

We can use LOAD DATA INFILE file_name;
syntax to load data
from a text file. but we have to make sure that
a) data is delimited
b) columns and data matched correctly

What is the difference between char and varchar data types?

Set char to occupy n bytes and it will take n bytes even if u r
storing a value of n-m bytes
Set varchar to occupy n bytes and it will take only the required space
and will not use the n bytes
eg. name char(15) will waste 10 bytes if we store 'mysql', if each char
takes a byte
eg. name varchar(15) will just use 5 bytes if we store 'mysql', if each
char takes a byte. rest 9 bytes will be free.

What is the difference between GROUP BY and ORDER BY in Sql?

ORDER BY [col1],[col2],…,[coln];
Tells DBMS according to what columns
it should sort the result. If two rows will have the same value in col1
it will try to sort them according to col2 and so on.
GROUP BY
[col1],[col2],…,[coln]; Tells DBMS to group results with same value of
column col1. You can use COUNT(col1), SUM(col1), AVG(col1) with it, if
you want to count all items in group, sum all values or view average

What is the difference between Primary Key and Unique key?

Primary Key: A column in a table whose values uniquely identify the
rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in the
table. There can be one and only one row for each unique key value. So
NULL can be a unique key.There can be only one primary key for a table but there can be more than one unique for a table.

How many ways we can we find the current date using MySQL?

SELECT CURDATE();
CURRENT_DATE() = CURDATE()
for time use
SELECT CURTIME();
CURRENT_TIME() = CURTIME()

How can we find the number of rows in a table using MySQL?

Use this for mysql
>SELECT COUNT(*) FROM table_name;

Explain Normalization concept?

The normalization process involves getting our data to conform to
three progressive normal forms, and a higher level of normalization
cannot be achieved until the previous levels have been achieved (there
are actually five normal forms, but the last two are mainly academic and
will not be discussed).The First Normal Form (or 1NF) involves removal of redundant data
from horizontal rows. We want to ensure that there is no duplication of
data in a given row, and that every column stores the least amount of
information possible (making the field atomic).Second Normal FormWhere the First Normal Form deals with redundancy of data across a
horizontal row, Second Normal Form (or 2NF) deals with redundancy of
data in vertical columns. As stated earlier, the normal forms are
progressive, so to achieve Second Normal Form, your tables must already
be in First Normal Form.Third Normal Form
I have a confession to make; I do not often use Third Normal Form. In
Third Normal Form we are looking for data in our tables that is not
fully dependant on the primary key, but dependant on another value in
the table

Give the syntax of Grant and Revoke commands?

The generic syntax for grant is as following
> GRANT [rights] on [database/s] TO [username@hostname] IDENTIFIED BY
[password]
now rights can be
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.We can grant rights on all databse by using *.* or some specific
database by database.* or a specific table by database.table_name
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition
password is simply the password of userThe generic syntax for revoke is as following
> REVOKE [rights] on [database/s] FROM [username@hostname]
now rights can be as explained above
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition

What is maximum size of a database in MySQL?

If the operating system or filesystem places a limit on the number
of files in a directory, MySQL is bound by that constraint.The efficiency of the operating system in handling large numbers of
files in a directory can place a practical limit on the number of tables
in a database. If the time required to open a file in the directory
increases significantly as the number of files increases, database
performance can be adversely affected.
The amount of available disk space limits the number of tables.
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM
storage engine in MySQL 3.23, the maximum table size was increased to
65536 terabytes (2567 – 1 bytes). With this larger allowed table size,
the maximum effective table size for MySQL databases is usually
determined by operating system constraints on file sizes, not by MySQL
internal limits.The InnoDB storage engine maintains InnoDB tables within a tablespace
that can be created from several files. This allows a table to exceed
the maximum individual file size. The tablespace can include raw disk
partitions, which allows extremely large tables. The maximum tablespace
size is 64TB.
The following table lists some examples of operating system file-size
limits. This is only a rough guide and is not intended to be definitive.
For the most up-to-date information, be sure to check the documentation
specific to your operating system.
Operating System File-size LimitLinux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB

What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?

In MySql, the default table type is MyISAM.
Each MyISAM table is stored on disk in three files. The files have names
that begin with the table name and have an extension to indicate the
file type.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,