Write the syntax used to connect to MySQL server.
To connect to the server, you will usually need to provide a MySQL user name when you invoke mysql and, most likely, a password. If the server runs on a machine other than the one where you log in, you will also need to specify a host name. Contact your administrator to
find out what connection parameters you should use to connect (that is, what host, user name, and password to use). Once you know the proper parameters, you should be able to connect like this:
user represent the host name where your MySQL server is running and the user name of your MySQL account. Substitute appropriate values for your setup. The
******** represents your password; enter it when mysql displays the
If that works, you should see some introductory information followed by a
Write about float and double data types.
1. FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]: A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and
1.175494351E-38 to 3.402823466E+38.These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
UNSIGNED, if specified, disallows negateve values.
These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
UNSIGNED, if specified, disallows negative values.
DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]: A normal – size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.
Write and explain the ALTER DATABASE Syntax.
You can later an existing table with the ALTER TABLE command, which allows you to add, remove, or modify table fields or indices without the need to re-create the entire table
The action component here can be any of the keywords ADD, DROP, ALTER, or CHANGE, and is followed by a field definition similar to that used by the CREATE TABLE command. This definition consists of the name of the field to be modified and (depending on the operation) a field definition consisting of a new field name, type, and constraints.
Discuss the syntax of Insert statement with a suitable example.
Once a database and its tables have been created, the next step is to enter data into them. This is accomplished by means of the INSERT command, whose basic syntax is given below:
You can also use an abbreviated form of the INSERT statement, in which the field list is left unspecified. The following example, an equivalent of the previous INSERT statement is given below:
Give the syntaxes of the following SQL statements:
UNION is used to combine the result from multiple
SELECT statements into a single result set.
The column names from the first
SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each
SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
If the data types of corresponding
SELECT columns do not match, the types and lengths of the columns in the
UNION result take into account the values retrieved by all of the
The default behavior for
UNION is that duplicate rows are removed from the result. The optional
DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional
ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the
You can mix
UNION in the same query.
DELETE statement deletes rows from
tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the
WHERE clause, if given, specifies the conditions that identify which rows to delete. With no
WHERE clause, all rows are deleted.
ORDER clause is specified, the rows are deleted in the order that is specified. The
LIMIT clause places a limit on the number of rows that can be deleted. As stated, a
DELETE statement with no
WHERE clause deletes all rows.
A faster way to do this, when you do not need to know the number of deleted rows, is to use
TRUNCATE. However, within a transaction or if you have a lock on the table,
TRUNCATE cannot be used whereas
For the single-table syntax, the
UPDATE statement updates columns of existing rows in
tbl_name with new values.
SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword
DEFAULT to set a column explicitly to its default value.
WHERE clause, if given, specifies the conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the
ORDER clause is specified, the rows are updated in the order that is specified.
LIMIT clause places a limit on the number of rows that can be updated.
UPDATE assignments are generally evaluated from left to right.
If you set a column to the value it currently has, MySQL notices this and does not update it. If you update a column that has been declared
NOT by setting to
NULL, the column is set to the default value appropriate for the data type and the warning count is incremented. The default value is
0 for numeric types, the empty string (
'') for string types, and the “zero” value for date and time types.
Define a correlated subquery with an example.
Most of the time, if a subquery is going to produce the same result set every time it runs, it makes sense, performance-wise, to run it only once and use the same result set to test every record generated in the main query.
Sometimes a situation arises in which a subquery uses a field from the main query in its clause. Such a reference by a subquery to a field in its enclosing query, is called an outer reference, and the corresponding subquery is called a correlated subquery, because it’s correlated with the result set of one or more of the queries enclosing it.
When an outer reference appears within a subquery, MySQL has to run the subquery once for every record generated by the outer query, and therefore test the subquery as many times as there are records in the outer query’s result set. In such a context, the EXISTS operator comes in handy, to filter out certain records from the final result set.
If you look at the data of the corresponding tables mentioned in the above example query, you will see in fact, only one branch is using four or more services (branch ID 1011).
In this case since the inner query contains a reference to a field in the outer query, MySQL cannot run the inner query only once. Rather, it has to run it over and over, once for every row in the outer table, substitute the value of the named field from that row in the subquery, and then decide whether to include that outer row in the final result set on the basis of whether the corresponding subquery returns a result.
Define the following terms:
Use parentheses to force the order of evaluation in an expression.
b. Comparison Operators
When working with MySQL SELECT queries, you may find the need to use the numerous comparison operators that allow you to compare the left side of an expression with its right side. The result of such a comparison operator is always 1(TRUE) or 0 (FALSE) or NULL (cannot be determined).
A comparison test using the BETWEEN operator returns true if the expression being tested lies between the specified range, inclusive of both end points of the range. This works not just with numbers, but with strings as well,
The <> operator can also be used for comparison with NULL values:
The comparison operators can be used to compare both numbers and strings. Numbers are compared as floating point values, while strings are compared in a case-insensitive manner.
A comparison test using the BETWEEN operator returns true if the expression being tested lies between the specified range, inclusive of both end points of the range. This works not just with numbers, but with strings as well, as shown in the example below:
MySQL uses four logical operators, which makes it possible to test the logical validity of one or more expressions (or sets of expressions). The result of an operation involving these operators is always 1(true), 0 (false), or NULL (could not be determined).
The NOT Operator: It reverses the logical sense of the test following it, turning true into false and false into true.
The AND Operator: This operator makes it possible to test the validity of two or more values (or expressions evaluating to values); it returns true if all its components are true and not NULL, and it returns false otherwise.
The OR Operator: This operator returns true if any of the values or expressions involved are true and NOT NULL, and false otherwise.
The XOR (Exclusive OR) Operator: This operator is included in the MySQL 4.x or later versions. This operator returns true if if either one (but not both) of its arguments is true.
Explain few Encryption functions.
These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough.
Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL 3.22.16, salt may be longer than two characters.)
Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. The return value can, for example
SHA(str) Calculates an SHA1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. One of the possible uses for this function is as a hash key. You can also use it as cryptographically safe function for storing passwords.
Discuss some of the transactional statements that do cause an implicit commit.
UNLOCK TABLES also ends a transaction if any tables currently are locked.
Prior to MySQL 4.0.13, CREATE TABLE ends a transaction if the binary update log is enabled.
Transactions cannot be nested. This is a consequence of the implicit COMMIT performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.
ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.).
However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence.
The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.
Beginning with MySQL 5.1.3, ALTER VIEW, CREATE TRIGGER, CREATE VIEW, DROP TRIGGER, and DROP VIEW cause an implicit commit.
Beginning with MySQL 5.1.15, CREATE TABLE … SELECT causes an implicit commit before and after the statement is executed when you are creating non-temporary tables. (No commit occurs for CREATE TEMPORARY TABLE … SELECT.
This is to prevent an issue during replication where the table could be created on the master after a rollback, but fail to be recorded in the binary log, and therefore not replicated to the slave.
Statements that implicitly use or modify tables in the mysql database: Beginning with MySQL 5.1.3, CREATE USER, DROP USER, and RENAME USER cause an implicit commit. Beginning with MySQL 5.1.23, GRANT, REVOKE, and SET PASSWORD statements cause an implicit commit.
· Transaction-control and locking statements: BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.
UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES. This does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table-level locks.
· Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.
Statements that cause an implicit commit cannot be used in an XA transaction while the transaction is in an ACTIVE state.
The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN … END compound statement. The latter does not cause an implicit commit.
· Data loading statements: LOAD DATA INFILE. Before MySQL 5.1.12, LOAD DATA INFILE caused an implicit commit for all storage engines. As of MySQL 5.1.12, it causes an implicit commit only for tables using the NDB storage engine.
· Administrative statements: CACHE INDEX, LOAD INDEX INTO CACHE. Beginning with MySQL 5.1.10, ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE cause an implicit commit.
Explain DECLARE statement.
The DECLARE statement is used to define various items local to a routine: local variables, , conditions and handlers, and cursors.
SIGNAL and RESIGNAL statements are not currently supported.
The DECLARE statement may only be used inside a BEGIN … END
compound statement and must be at its start, before any other statements.
This command is used to declare local variables. The scope of a variable is within the BEGIN … END block.
The SET statement in stored procedures is an extended version of the general SET command. Referenced variables may be ones declared inside a routine, or global server variables. The SET statement in stored procedures is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, … where different variable types (locally declared variables, server variables, and global and session server variables) can be mixed. This also allows combinations of local variables and some options that only make sense for global/system variables; in that case the options are accepted but ignored.
Certain conditions may require specific handling. These conditions can relate to errors, as well as general flow control inside a routine.
This statement specifies conditions that will need specific handling. It associates a name with a specified error condition. The name can subsequently be used in a DECLARE HANDLER statement. In addition to SQLSTATE values, MySQL error codes are also supported.