B.Sc. IT BT0075 (Semester 3, RDBMS with MySQL) Assignment

Spring 2012

Bachelor of Science in Information Technology (BScIT) – Semester 3

BT0075 – RDBMS with MySQL – 4 Credits (Book ID: B1003)

Assignment Set – 1 (60 Marks)

1. Mention 4 features of MySQL.

Ans. - This section describes some of the important characteristics of the MySQL Database Software.

1.      Internals and Portability:

·         Written in C and C++.

·         Tested with a broad range of different compilers.

·         Works on many different platforms.

·         Uses GNU Automake, Autoconf, and Libtool for portability.

·         The MySQL Server design is multi-layered with independent modules.

·         Fully multi-threaded using kernel threads. It can easily use multiple CPUs if they are available.

·         Provides transactional and non-transactional storage engines.

·         Uses very fast B-tree disk tables (MyISAM) with index compression.

·         Relatively easy to add other storage engines. This is useful if you want to provide an SQL interface for an in-house database.

·         A very fast thread-based memory allocation system.

·         Very fast joins using an optimized one-sweep multi-join.

·         In-memory hash tables, which are used as temporary tables.

·         SQL functions are implemented using a highly optimized class library and should be as fast as possible. Usually there is no memory allocation at all after query initialization.

·         The MySQL code is tested with Purify (a commercial memory leakage detector) as well as with Valgrind, a GPL tool.

·         The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available.

2.      Data Types:

·         Supports the following data types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, ENUM, and OpenGIS spatial types.

·         Fixed-length and variable-length records.

3.      Security:

·         A privilege and password system that is very flexible and secure, and that allows host-based verification.

·         Passwords are secure because all password traffic is encrypted when you connect to a server.

4.      Clients and Tools:

·         MySQL AB provides several client and utility programs. These include both command-line programs such as mysqldump and mysqladmin, and graphical programs such as MySQL Administrator and MySQL Query Browser.

·         MySQL Server has built-in support for SQL statements to check, optimize, and repair tables. These statements are available from the command line through the mysqlcheck client. MySQL also includes myisamchk, a very fast command-line utility for performing these operations on MyISAM tables.

·         MySQL programs can be invoked with the --help or -? Option to obtain online assistance.

2. Write the differences between char and varchar data types in MySQL.

Ans. -  The CHARand VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHARcolumn is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHARvalues are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHARcolumns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

3. Write about the operations supported by indexes in MySQL.

Ans.-   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 is the cost. 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.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.

MySQL uses indexes for these operations:

To find the rows matching a WHERE clause quickly.

To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.

To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. These rules out use of any indexes for the string column.

To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:

SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order.

In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name WHERE key_part1=1

Suppose that you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;  

4. Write the SQL statements to demonstrate the following using SELECT command:

a. Expression Evaluation:-   The simplest, but not the most efficient way to perform expression evaluation is by using one of the static convenience methods of the ExpressionEvaluator class:

public static object GetValue(object root, string expression);

public static object GetValue(object root, string expression, IDictionary variables)

public static void SetValue(object root, string expression, object newValue)

public static void SetValue(object root, string expression, IDictionary variables, object newValue)

The first argument is the 'root' object that the expression string (2nd argument) will be evaluated against. The third argument is used to support variables in the expression and will be discussed later. Simple usage to get the value of an object property is shown below using the Inventor class. 

Inventor tesla = new Inventor("Nikola Tesla", new DateTime(1856, 7, 9), "Serbian");

tesla.PlaceOfBirth.City = "Smiljan";

string evaluatedName = (string) ExpressionEvaluator.GetValue(tesla, "Name");

string evaluatedCity = (string) ExpressionEvaluator.GetValue(tesla, "PlaceOfBirth.City"));

The value of 'evaluatedName' is 'Nikola Tesla' and that of 'evaluatedCity' is 'Smiljan'. A period is used to navigate the nested properties of the object. Similarly to set the property of an object, say we want to rewrite history and change Tesla's city of birth, we would simply add the following line

ExpressionEvaluator.SetValue(tesla, "PlaceOfBirth.City", "Novi Sad");

A much better way to evaluate expressions is to parse them once and then evaluate as many times as you want usingExpressionclass. Unlike ExpressionEvaluator, which parses expression every time you invoke one of its methods, Expressionclass will cache the parsed expression for increased performance. The methods of this class are listed below:

public static IExpression Parse(string expression)

public override object Get(object context, IDictionary variables)

public override void Set(object context, IDictionary variables, object newValue)

The retrieval of the Name property in the previous example using the Expression class is shown below

IExpression exp = Expression.Parse("Name");

string evaluatedName = (string) exp.GetValue(tesla, null);

The difference in performance between the two approaches, when evaluating the same expression many times, is several orders of magnitude, so you should only use convenience methods of the ExpressionEvaluatorclass when you are doing one-off expression evaluations. In all other cases you should parse the expression first and then evaluate it as many times as you need.

There are a few exception classes to be aware of when using the ExpressionEvaluator. These are InvalidPropertyException, when you refer to a property that doesn't exist, NullValueInNestedPathException, when a null value is encountered when traversing through the nested property list, and ArgumentException and NotSupportedExceptionwhen you pass in values that are in error in some other manner.

The expression language is based on a grammar and uses ANTLR to construct the lexer and parser. Errors relating to bad syntax of the language will be caught at this level of the language implementation. For those interested in the digging deeper into the implementation, the grammar file is named Expression.g and is located in the src directory of the namespace. As a side note, the release version of the ANTLR DLL included with Spring.NET was signed with the Spring.NET key, which means that you should always use the included version of antlr.runtime.dll within your application. Upcoming releases of ANTLR will provide strongly signed assemblies, which will remove this requirement.

b. Using table aliases:-           In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select only the distinct values from the column named "City" from the table above.

We use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result-set will look like this:

City

Sandnes

Stavanger

c. ORDER BY:-        The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sorts the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

ORDER BY Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Tom	Vingvn 23	Stavanger

Now we want to select all the persons from the table above, however, we want to sort the persons by their last name.

We use the following SELECT statement:

SELECT * FROM Persons
ORDER BY LastName

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
4	Nilsen	Tom	Vingvn 23	Stavanger
3	Pettersen	Kari	Storgt 20	Stavanger
2	Svendson	Tove	Borgvn 23	Sandnes

5. Define the following types of Joins:

a. Inner Join:-            An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product(or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a hash join or a sort-merge join where possible, since computing the Cartesian product is very inefficient.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT *

FROM employee

INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

The "implicit join notation" simply lists the tables for joining, in the FROMclause of the SELECT statement, using commas to separate them. Thus it specifies a cross join, and the WHEREclause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example is equivalent to the previous one, but this time using implicit join notation:

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID;

The queries given in the examples above will join the Employee and Department tables using the DepartmentID column of both tables. Where the DepartmentID of these tables match (i.e. the join-predicate is satisfied), the query will combine the LastName, DepartmentIDand DepartmentName columns from the two tables into a result row. Where the DepartmentID does not match, no result row is generated.

Thus the result of the execution of either of the two queries above will be:

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Robinson	34	Clerical	34
Jones	33	Engineering	33
Smith	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31

Note: Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses the IS NULL or IS NOT NULLpredicates.

Notice that the employee "John" and the department "Marketing" do not appear in the query execution results. Neither of these has any matching records in the other respective table: "John" has no associated department, and no employee has the department ID 35 ("Marketing"). Depending on the desired results, this behavior may be a subtle bug, which can be avoided with an outer join.

One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.

b. Left Outer Join:- The result of a left outer join(or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ONclause matches 0 (zero) records in B (for a given record in A), the join will still return a row in the result (for that record)—but with NULL in each column from B. A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table. From Oracle 9i onwards the LEFT OUTER JOIN statement can be used as well as Oracle's older (+) syntax.

For example, this allows us to find an employee's department, but still shows the employee(s) even when they have not been assigned to a department (contrary to the inner-join example above, where unassigned employees were excluded from the result).

Example of a left outer join, with the additional result row (compared with the inner join) italicized:

SELECT *

FROM employee

LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
John	NULL	NULL	NULL
Steinberg	33	Engineering	33

Oracle supports the alternate syntax:

SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID(+)

Sybase supports the alternate syntax:

SELECT * FROM employee, department WHERE employee.DepartmentID *= department.DepartmentID

c. Right Outer Join:-A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees.

Below is an example of a right outer join, with the additional result row italicized:

SELECT *

FROM employee

RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Employee.LastName	Employee.DepartmentID	Department.DepartmentName	Department.DepartmentID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

Oracle supports the alternate syntax:

SELECT * FROM employee, department WHERE employee.DepartmentID(+) = department.DepartmentID

Right and left outer joins are functionally equivalent. Neither provides any functionality that the other does not, so right and left outer joins may replace each other as long as the table order is switched.

6. Give the advantages of subqueries.

Ans.-   The main advantages of subqueries are:

·         They allow queries that are structured so that it is possible to isolate each part of a statement.

·         They provide alternative ways to perform operations that would otherwise require complex joins and unions.

·         They are, in many people's opinion, more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”

7. Discuss any five string functions.

Ans.-   These functions can be used to manipulate the string data. Here we have compiled the almost all String functions that you can use to learn more about string functions supported by MySQL.

1.ASCII (str):-           The ASCII(str) function returns the ASCII code value of the leftmost character of the String str. Returns 0 if str is the empty string. Returns NULL if stris NULL. Here are the some example of the ASCII(str) function:
 

mysql> SELECT ASCII('0'); -> 48 mysql> SELECT ASCII(0); -> 48 mysql> SELECT ASCII('d'); -> 100

2.BIN (N):-     The BIN string function return a string value representing of the binary value of N, where N is a longlong(BIGINT) number. This function is equivalent to CONV(N, 10 , 0). If the function return the null then N is null. Here are the some example of the BIN(N) function:
 

mysql> SELECT BIN(5); -> '101'

3.BIT_LENGTH (str):-        The BIT_LENGTH(str) function return the String str length in bits . Here are the some example of the BIT_LENGTH(str) function:
 

mysql> SELECT BIT_LENGTH('a'); -> 8

4.CHAR(N,... [USING charset_name] ):-   The CHAR(N,... [USING charset_name] ) function  return a string consisting the character and given the integer value. This function skipped the NULL values. Here are the some example of the CHAR(N,... [USING charset_name] ) function:
Example:
 

mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(66,66.3,'66.3'); -> 'BBB'

5.CONCAT(str1, str2..):-     The CONCAT(str1, str2?.) function can have one or more arguments and its returns a string that is the result of concatenating the arguments. In this function all arguments are non-binary strings then the result is also non-binary string but if any argument is binary string then result is binary string. And a numeric argument is converted to its equivalent binary string form. But if any argument is NULL then it also returns NULL.
Example:
  

mysql> SELECT CONCAT('In', 'd', 'ia'); -> 'India' mysql> SELECT CONCAT('my', NULL, 'ql'); -> NULL mysql> SELECT CONCAT(10.3); -> '10.3'

8. Describe the operators that support the boolean full-text searches.

Ans.-   MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier:

The + and - operators indicate that a word is required to be present or absent, respectively, for a match to occur. Thus, this query retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL”.

Note: In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which:

·         + stands for AND

·         - stands for NOT

·         [no operator] implies OR

Boolean full-text searches have these characteristics:

·         They do not use the 50% threshold.

·         They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains “MySQL” twice, but it is listed last, not first.

·         They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

·         The minimum and maximum word length full-text parameters apply.

·         The stop word list applies.

The boolean full-text search capability supports the following operators:

·         +: A leading plus sign indicates that this word must be present in each row that is returned.

·         -: A leading minus sign indicates that this word must not be present in any of the rows that are returned.

Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

·         (no operator): By default (when neither + nor - is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.

·         > <: These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it.

·         ( ): Parentheses group words into subexpressions. Parenthesized groups can be nested.

·         ~: A leading tilde acts as a negation operator, causing the word's contribution to the row's relevance to be negative. This is useful for marking “noise” words. A row containing such a word is rated lower than others, but is not excluded altogether, as it would be with the - operator.

·         *: The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

If a stopword or too-short word is specified with the truncation operator, it will not be stripped from a boolean query. For example, a search for '+word +stopword*' will likely return fewer rows than a search for '+word +stopword' because the former query remains as is and requires stopword* to be present in a document. The latter query is transformed to +word.

·         ": A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words, performs a search in the FULLTEXT index for the words. Non-word characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty.

9. Describe the Savepoint and Rollback to Savepoint Syntaxes.

Ans.-   The SAVEPOINT statement is used to define an SQL savepoint – and therefore the start of a subtransaction within a transaction – and to assign this SQL savepoint a name. A subsequent ROLLBACK TO statement with the SQL savepoint name reverses any modifications that have been made in the meantime, without affecting the database operations that were executed within the transaction before the start of this subtransaction. The SQL savepoint is also deleted in the process. It is also possible to delete SQL savepoints using the RELEASE SAVEPOINT statement.

Syntax

<savepoint_statement> ::= SAVEPOINT <sql_savepoint_name>

<rollback_to_statement> ::= ROLLBACK TO [SAVEPOINT] <sql_savepoint_name>

<release_savepoint_statement> ::= RELEASE SAVEPOINT <sql_savepoint_name>

<sql_savepoint_name> ::= <identifier>

Explanation

SAVEPOINT statement

The SAVEPOINT statement opens a subtransaction. This means the database system records the current position (SQL savepoint) in the transaction and assigns it the name SQL_savepoint_name. The SQL savepoint is identified as active. Any sequence of SQL statements can then follow within one transaction. The sequence of SQL statements can contain other SAVEPOINT statements; however, no more than 50 SQL savepoints can be active in one transaction.

Names of SQL savepoints in a transaction must be different. If an SQL savepoint name is assigned twice within a transaction, the SQL savepoint in this transaction defined by the first SAVEPOINT statement becomes inactive.

ROLLBACK TO Statement

A ROLLBACK TO statement reverses all database modifications made in the active transaction following the SAVEPOINT statement.

The SQL savepoint specified in the ROLLBACK TO statement must be an active SQL statement in the transaction. All SQL savepoints created after this SQL savepoint are inactive. All SQL savepoints created before this SQL savepoint remain active.

The specified SQL savepoint also remains active after the ROLLBACK TO statement has been executed. This means the ROLLBACK TO statement can be executed in the same transaction more than once by specifying the same SQL savepoint name.

10. Give the syntax of BEGIN…END compound statement.

Ans.-  

Stored routines may contain multiple statements, using a BEGIN ... END compound statement. begin_label and end_label must be the same, if both are specified. Please note that the optional [NOT] ATOMIC clause is not yet supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction.

Multiple statements requires that a client is able to send query strings containing „;‟. This is handled in the mysql command-line client with the delimiter command. Changing the „;‟ end-of-query delimiter (for example, to „|‟) allows „;‟ to be used in a routine body.

  

Spring 2012

Bachelor of Science in Information Technology (BScIT) – Semester 3

BT0075 – RDBMS with MySQL – 4 Credits (Book ID: B1003)

Assignment Set – 2 (60 Marks)

1. Write the syntax used to connect to MySQL server.

Ans.-   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:

host and 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 Enter password:prompt.

If that works, you should see some introductory information followed by a mysql> prompt:

                   

2.Write about float and double data types.

Ans.-   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.

2.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.

3.Write and explain the ALTER DATABASE Syntax.

Ans.-   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.

4. Discuss the syntax of Insert statement with a suitable example.

Ans.-   The INSERT statement allows you to insert a single record or multiple records into a table.

The syntax for the INSERT statement is:

INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

Example:-

Let's take a look at a very simple example.

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');

This would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

You can also perform more complicated inserts using sub-selects.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement beforeperforming the insert.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

5. Give the syntaxes of the following SQL statements:

a. Union:-       The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

b. Delete:-       The DELETE statement is used to delete records in a table.

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value

c. Update:-     The UPDATE statement is used to update records in a table.

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

6. Define a correlated subquery with an example.

Ans.-   A correlated reference can appear in a search condition in a subquery. The reference is always of the form X.C, where X is a correlation name and C is the name of a column in the table that X represents.

You can define a correlation name for any table appearing in a FROM clause. A correlation name provides a unique name for a table in a query. The same table name can be used many times within a query and its nested subselects. Specifying different correlation names for each table reference makes it possible to uniquely designate which table a column refers to.

The correlation name is defined in the FROM clause of a query. This query can be the outer-level SELECT, or any of the subqueries that contain the one with the reference. Suppose, for example, that a query contains subqueries A, B, and C, and that A contains B and B contains C. Then a correlation name used in C can be defined in B, A, or the outer-level SELECT. To define a correlation name, include the correlation name after the table name. Leave one or more blanks between a table name and its correlation name, and place a comma after the correlation name if it is followed by another table name. The following FROM clause defines the correlation names TA and TB for the tables TABLEA and TABLEB, and no correlation name for the table TABLEC.

  FROM TABLEA TA, TABLEC, TABLEB TB

Any number of correlated references can appear in a subquery. For example, one correlated name in a search condition can be defined in the outer-level SELECT, while another can be defined in a containing subquery.

Before the subquery is executed, a value from the referenced column is always substituted for the correlated reference.

Example: Correlated subqueries in a DELETE statement

When you use a correlated subquery in a DELETE statement, the correlation name represents the row you delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row.

Suppose a row in the CORPDATA.PROJECT table was deleted. Rows related to the deleted project in the CORPDATA.EMPPROJACT table must also be deleted. To do this, you can use:

  DELETE FROM CORPDATA.EMPPROJACT X

    WHERE NOT EXISTS

       (SELECT *

          FROMCORPDATA.PROJECT

          WHERE PROJNO = X.PROJNO)

SQL determines, for each row in the CORPDATA.EMP_ACT table, whether a row with the same project number exists in the CORPDATA.PROJECT table. If not, the CORPDATA.EMP_ACT row is deleted.

7. Define the following terms:

a. Parentheses:-

            ( … )

Use parentheses to force the order of evaluation in an expression.

Example:-

mysql> SELECT 1+2*3;

-> 7

mysql> SELECT (1+2)*3;

-> 9

b. Comparison Operators:-  Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators.

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)	Less than
>= (Greater Than or Equal To)	Greater than or equal to
<= (Less Than or Equal To)	Less than or equal to
<> (Not Equal To)	Not equal to
!= (Not Equal To)	Not equal to (not ISO standard)
!< (Not Less Than)	Not less than (not ISO standard)
!> (Not Greater Than)	Not greater than (not ISO standard)

c. Logical Operators:-           Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

Operator	Meaning
ALL	TRUE if all of a set of comparisons are TRUE.
AND	TRUE if both Boolean expressions are TRUE.
ANY	TRUE if any one of a set of comparisons are TRUE.
BETWEEN	TRUE if the operand is within a range.
EXISTS	TRUE if a subquery contains any rows.
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern.
NOT	Reverses the value of any other Boolean operator.
OR	TRUE if either Boolean expression is TRUE.
SOME	TRUE if some of a set of comparisons are TRUE.

8. Explain few Encryption functions.

Ans.-  

·         AES_DECRYPT(crypt_str,key_str)

This function decrypts data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT().

·         AES_ENCRYPT(str,key_str)

AES_ENCRYPT() and AES_DECRYPT() enable encryption and 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 secure enough for most purposes.

AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.

Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:

16 * (trunc(string_length / 16) + 1)

If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

You can use the AES functions to store data in an encrypted form by modifying your queries:

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.

·         COMPRESS(string_to_compress)

Compresses a string and returns the result as a binary string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS().

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));

        -> 21

mysql> SELECT LENGTH(COMPRESS(''));

        -> 0

mysql> SELECT LENGTH(COMPRESS('a'));

        -> 13

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));

        -> 15

The compressed string contents are stored the following way:

Empty strings are stored as empty strings.

Nonempty strings are stored as a 4-byte length of the uncompressed string (low byte first), followed by the compressed string. If the string ends with space, an extra “.” character is added to avoid problems with endspace trimming should the result be stored in a CHAR or VARCHAR column. (However, use of nonbinary string data types such as CHAR or VARCHAR to store compressed strings is not recommended anyway because character set conversion may occur. Use a VARBINARY or BLOB binary string column instead.)

·         DECODE(crypt_str,pass_str)

Decrypts the encrypted string crypt_str using pass_stras the password. crypt_str should be a string returned from ENCODE().

·         DES_DECRYPT(crypt_str[,key_str])

Decrypts a string encrypted with DES_ENCRYPT(). If an error occurs, this function returns NULL.

This function works only if MySQL has been configured with SSL support. 

If no key_str argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have the SUPER privilege. The key file can be specified with the --des-key-file server option.

If you pass this function a key_str argument, that string is used as the key for decrypting the message.

If the crypt_str argument does not appear to be an encrypted string, MySQL returns the given crypt_str

9. Discuss some of the transactional statements that do cause an implicit commit.

Ans.-   The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.

Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER TABLE, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

ALTER FUNCTION, CREATE FUNCTION and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with UDFs. (ALTER FUNCTION can only be used with stored functions.)

ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARYkeyword 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 InnoDBis 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 nontemporary 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. For more information, see Bug #22865.

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 ACTIVEstate.

The BEGIN statement differs from the use of the BEGINkeyword 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. For more information, see Bug #11151.

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.

10. Explain DECLARE statement.

Ans.-   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.

For More Assignments Click Here