# Informatics Practices for Class XI (CBSE)/Simple queries

Note: Based on the Language chapter of the MySQL book.

## Browsing the databases

The following SQL commands provide information about the databases located on the current server. The INFORMATION_SCHEMA table containing this information is SCHEMATA.
The mysqlshow command line tool can be used instead.
You can't show databases if the server has been started with the—skip-all-databases option.
If you don't have the 'SHOW DATABASES' privilege, you'll only see databases on which you have some permissions.

### List databases

Show all databases:
The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMS's.

#### Add a filter on the databases names

The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my':

#### Add complex filters

You can add more complex filters using the WHERE clause:
WHERE clause allows you to use regular expressions, '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES.

### List tables and views

The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`.
Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table.
The mysqlshow command line tool can be used instead.

#### Show all tables

The 2 forms shown above are equivalent.

#### Apply a filter

You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operatore, as you do in SELECTs or in the DML statements:
Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause:
(see below)

#### Extra info

By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword:
This will add a column called `Table_type`. This can have 3 values: 'BASE TABLE' for tables, 'VIEW' for views ans 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables).
So you can only list tables:
Or, you can only list views:

#### Show only open tables

You can get a list of the non-temporary tables (not views) which are open in the cache:
This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA.

### List fields

The following SQL commands provide information about the columns in a table or in a view. The INFORMATION_SCHEMA table containing this information is COLUMNS.
The mysqlshow command line tool can be used instead.

#### DESCRIBE

DESC can be used as a shortcut for DESCRIBE.
'filter' can be a column name. If a column name is specified, only that column will be shown. If 'filter' contains the '%' or the '_' characters, it will be evaluated as a LIKE condition. For example, you can list all fields which start with 'my':

#### SHOW COLUMNS

FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, but it doesn't support all of its clauses.
A databases name can be specified both in the form
both:

#### Extra info

Using the FULL keyword, extra info can be retried: the columns' collation, privileges you have on the column and the comment.

### List indexes

The following SQL commands provide information about the indexes in a table. Information about keys is contained in the `COLUMNS` table in the INFORMATION_SCHEMA.
The mysqlshow -k command line tool can be used instead.
The KEYS reserved word can be used as a synonym of INDEX. No other clauses are provided.

### INFORMATION_SCHEMA

information_schema is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases.

## Specifying names

In this book, we will quote the MySQL identifiers (tables names, fields, databases, etc.) using backquotes (`).
Backquote is ASCII 96. It can be type on GNU/Linux systems by pressing: ALT+'.
Most often, this is optional. However, this allows better error messages from MySQL. For example, this error is not very helpful:
But this one is better:
Ok, it was just a missing s:
This syntax allows the user to use reserver words and some illegal characters in objects' names. It is even possible to use backquotes by tying it twice:
However, this is not a portable syntax. The SQL standard recommends the use of a double quote ("). If you want to write portable SQL quote, do not quote the identifiers. But is there something like portable SQL, even remotely? ;)

## Definitions: what are DDL, DML and DQL?

* DDL (Data Definition Language) refers to the CREATE, ALTER and DROP statements
* DML (Data Manipulation Language) refers to the INSERT, UPDATE and DELETE statements
* DQL (Data Query Language) refers to the SELECT, SHOW and HELP statements (queries)
* DCL (Data Control Language) refers to the GRANT and REVOKE statements

## User Variables
### Session Variables

* The ability to set variables in a statement with the := assignment operator:
* For e.g. (@total) to calculate the total in an example, you have to have the total column first because it must be calculated before the individual percentage calculations
* Session variables are set for the duration of the thread.
* In the vast majority of cases you'd use a programming language to do this sort of thing.
* Mysql variables can be useful when working on the Mysql command line.
* If no records are returned, the user variable will not be set for that statement.
* A user variable set in the field list cannot be used as a condition.
* The value of a variable is set with the SET statement or in a SELECT statement with :=
* You can copy values retrieved by a SELECT into one or more variables:

### Global Variables

A global variable is visible to all users.

### SHOW VARIABLES
## Alias

An expression and a column may be given aliases using AS. The alias is used as the expression's column name and can be used with order by or having clauses. For e.g.
These aliases can be used in ORDER BY, GROUP BY and HAVING clauses. They should not be used in WHERE clause.
A table name can have a shorter name for reference using AS. You can omit the AS word and still use aliasing. For e.g.
Aliasing plays a crucial role while you are using self joins. For e.g. people table has been referred to as p and c aliases!

## Queries
### SELECT

select syntax is as follows:

#### List of fields

You must specify what data you're going to retrieve in the SELECT clause:
Any SQL expression is allowed here.
You can also retrieve all fields from a table:
If you SELECT only the necessary fields, the query will be faster.

#### The table's name

If you are retrieving results from a table or a view, usually you specify the table's name in the FORM clause:
Or:
You can also use the `db_name`.`table_name` syntax:
But you can also specify the table's name in the SELECT clause:

#### WHERE

You can set a filter to decide what records must be retrieved.
For example, you can retrieve only the record which has an id of 42:
Or you can read more than one record:

#### GROUP BY

You can group all records by one or more fields. The record which have the same value for that field will be grouped in one computed record. You can only select the grouped record and the result of some aggregate functions, which will be computed on all records of each group.
For example, the following will group all records in the table `users` by the field `city`. For each group of users living in the same city, the maximum age, the minimum age and the average age will be returned:
In the following example, the users are grouped by city and sex, so that we'll know the max, min and avg age of male/female users in each city:

#### HAVING

The HAVING clause declares a filter for the records which are computed by the GROUP BY clause. It's different from the WHERE clause, that operates before the GROUP BY. Here's what happens:
* The records which match to the WHERE clause are retrieved
* Those records are used to compute new records as defined in the GROUP BY clause
* The new records that match to the HAVING conditions are returned
This means which WHERE decides what record are used to compose the new computed records.
HAVING decides what computed records are returned, so it can operate on the results of aggregate functions. HAVING is not optimized and can't use indexes.
Incorrect use of HAVING:
This probably gives a wrong results. MAX(age) and other aggregate calculations are made using all values, even if the record's sex value is 'f'. This is hardly the expected result.
Incorrect use of HAVING:
This is correct and returns the expected results, but the execution of this query is not optimized. The WHERE clause can and should be used, because, so that MySQL doesn't computes records which are excluded later.
Correct use of HAVING:
It must group all records, because can't decide the max age of each city before the GROUP BY clause is execute. Later, it returns only the record with a MAX(age)>80.

#### ORDER BY

You can set an arbitrary order for the records you retrieve. The order may be alphabetical or numeric.
By default, the order is ASCENDING. You can also specify that the order must be DESCENDING:
NULLs values are considered as minor than any other value.
You can also specify the field position, in place of the field name:
SQL expressions are allowed:
You can retrieve records in a random order:
If a GROUP BY clause is specified, the results are ordered by the fields named in GROUP BY, unless an ORDER BY clause is present. You can even specify in the GROUP BY clause if the order must be ascending or descending:
If you have a GROUP BY but you don't want the records to be ordered, you can use ORDER BY NULL:

#### LIMIT

You can specify the maximum of rows that you want to read:
This statement returns a maximum of 10 rows. If there are not 10 rows, it returns the number of rows you find. The limit clause is usually used with ORDER BY.
You can get a given number of random records:
You can specify how many rows should be skipped before starting return the record found. The first record is 0, not one:
You can use the LIMIT clause to get the pagination of results:
Also, the following syntax is accepted:
You can LIMIT to check the syntax of a query without waiting it returns the results:
Optimization tips:
* SQL_CALC_FOUND_ROWS speeds down the LIMIT.
* LIMIT is particularly useful for SELECTs which use ORDER BY, DISTINCT and GROUP BY, because their calculations doesn't have to involve all the rows.
* If the query is resolved by the server copying internally the results into a temporary table, LIMIT helps MySQL to calculate how many memory is required by the table.

#### DISTINCT

The DISTINCT keyword can be used to remove all duplicate rows from the resultset:
You can use it to get the list of all values contained in one field:
Or you can use it to get the existing combinations of some values:
If one of the fields you are SELECTing is the PRIMARY KEY or has a UNIQUE index, DISTINCT is useless. Also, it's useless to use DISTINCT in conjunction with the GROUP BY clause.

#### EXISTS and ALL

(Compatible: Mysql 4+)

#### Optimization hints

There are some hints you may want to give to the server to better optimize the SELECTs. If you give more than one hints, the order of the keywords is important:
HIGH_PRIORITY
Usually, DML commands (INSERT, DELETE, UPDATE) have higher priority than SELECTs. If you specify HIGH_PRIORITY though, the SELECT will have higher priority than DML statements.
STRAIGHT_JOIN Force MySQL to evaluate the tables of a JOIN in the same order they are named, from the leftmost.
SQL_SMALL_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return few rows.
SQL_BIG_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return a many rows.
SQL_BUFFER_RESULT Force MySQL to copy the result into a temporary table. This is useful to remove LOCKs as soon as possible.
SQL_CACHE Forces MySQL to copy the result into the query cache. Only works if the value of query_cache_type is DEMAND or 2.
SQL_NO_CACHE Tells MySQL not to cache the result. Useful if the query occurs very seldom or if the result often change.
SQL_CALC_FOUND_ROWS Useful if you are using the LIMIT clause. Tells the server to calculate how many rows would have been returned if there were no LIMIT. You can retrieve that number with another query:

#### UNION and UNION All

(Compatible: Mysql 4+)
Following query will return all the records from both tables.
UNION is the same as UNION DISTINCT. If you type only UNION, then it is considered that you are asking for distinct records. If you want all records, you have to use UNION ALL.

## Data manipulation
### INSERT

The syntax is as follows:
Insert value1 into Column1, value2 into Column2, and value3 into Column3:
Insert one record (values are inserted in the order that the columns appear in the database):
Insert two records:
You can also insert records 'selected' from other table.
Performance tips:
* To insert many rows, consider using LOAD DATA INFILE instead.
* If bulk INSERTs are too slow and they operate on indexed non-empty tables, maybe you should increase the value of bulk_insert_buffer_size.
* Before performing bulk inserts, you may want to disable thekeys.
* LOCKing a table also speeds up the INERT.

### UPDATE

The syntax is:
Examples are:
With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).
It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP:
TODO: describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.
Performance tips
* UPDATEs speed depends of how many indexes are updated.
* If you UPDATE a MyISAM table which uses dynamic format, if you make rows larger they could be splitted in more than one part. This causes reading overhead. So, if your applications often do this, you may want to regularly run an OPTIMIZE TABLE statement.
* Performing many UPDATEs all together on a LOCKed table is faster than performing them individually.

### REPLACE

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.
With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort.
Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

### DELETE

* If you don't use a WHERE clause with DELETE, all records will be deleted.
* It can be very slow in a large table, especially if the table has many indexes.
* If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable).
* For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused.
* TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation).
* TRUNCATE is not transaction-safe nor lock-safe.
* DELETE informs you how many rows have been removed, but TRUNCATE doesn't.
* After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster.
You can order the rows before deleting them, and then delete only a given number of rows.

## Table manipulation
### CREATE TABLE

Create table syntax is: Create table tablename (FieldName1 DataType, FieldName2 DataType)
The rows returned by the "select" query can be saved as a new table. The datatype will be the same as the old table. For e.g. CREATE TABLE LearnHindi
select english.tag, english.Inenglish as English, hindi.Inhindi as Hindi
FROM English, Hindi
WHERE english.tag = hindi.tag

### ALTER TABLE

ALTER TABLE command can be used when you want to add / delete /modify the columns and / or the indexes; or, it can be used to change other table properties.
Add a column:
Modify a column:
Drop a column:
Re-order the record in a table:
(this operation is only supported by some Storage Engines; it could make some query faster)

### Renaming a table

In order to rename a table, you must have ALTER and DROP privileges on the old table name (or on all the tables), and CREATE and INSERT privileges on the new table name (or on all the tables).
You can use ALTER TABLE to rename a table:
You can rename more than one table with a single command:
RENAME is a shortcut. You can also use the ALTER TABLE statement:
Using ALTER TABLE you can only rename one table per statement, but it's the only way to rename temporary tables.

### DROP TABLE

Will completely delete the table and all the records it contains.
You can also drop more than one table with a single statement:
There are come optional keywords:
TEMPORARY must be specified, to drop a temporary table. IF EXISTS tells the server that it must not raise an error if the table doesn't exist.

## Using NULL

Null is a special logical value in SQL. Most programming languages have 2 values of logic: True and False. SQL also has NULL which means "Unknown". A NULL value can be set.
NULL is a non-value, so it can be assigned to TEXT columns, INTEGER columns or any other datatype. A column can not contain NULLs only if it has been declared as NOT NULL (see ALTER TABLE).
Do not quote the NULL. If you quote a Null then you name the person NULL. For some strange reason, NULLs do not show visually on windows XP in Varchar fields but they do in Fedora's version, so versions of mysql can give different outputs. Here we set the value of Sting and Homer's first name to a zero length string "", because we KNOW they have NO first name, but we KNOW we do not know the place they were born. To check for a NULLs use
Remember, COUNT never counts NULLS.
Normal operations (comparisons, expressions...) return NULL if at least one of the compared items is NULL:
because all the expressions between in parenthesis return NULL. It's definitely logical: if you don't know the value represented by NULL, you don't know is it's =1 or <>1. Be aware that even (NULL=NULL and (NOT NULL) return NULL.

### Dealing with NULL

The function 'COALESCE' can simplify working with null values. for example, to avoid showing null values by treating null as zero, you can type:
In a date field, to treat NULL as the current date:
The coalesce function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances.
Use of IFNULL in your SELECT statement is to make the NULL any value you wish.
If expr1 is not NULL, IFNULL returns expr1, else it returns expr2.
IFNULL returns a numeric or string value, depending on the context in which it is used:
Null handling can be very counter intuitive and could cause problems if you have an incorrect function in a delete statement that returns null. For example, the following query will delete all entries.
If you want to have NULL values presented last when doing an ORDER BY, try this:

## Reserved Words

Difficult Column Names, Like `DATE`—use backtick. If using "date" as a column name, enclose it in backticks ` as follows:

## Data Types
### varchar

VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (up to 255 characters) char(n) is similar to varchar(n) with the only difference that char will occupy fixed length of space in the database whereas varchar will need the space to store the actual text. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

### text

A BLOB or TEXT column with a maximum length of 65,535 characters.

### integer

Specifying an n value has no effect whatsoever. Regardless of a supplied value for n, maximum (unsigned) value stored is 429 crores. If you want to add negative numbers, add the "signed" keyword next to it.

### decimal

decimal(n,m) decimal(4,2) means numbers up to 99.99 (and NOT 9999.99 as you may expect) can be saved. Four digits with the last 2 reserved for decimal.

### Dates

Out of the three types DATETIME, DATE, and TIMESTAMP, the DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The DATETIME type is used when you need values that contain both date and time information. The difference between DATETIME and TIMESTAMP is that the TIMESTAMP range is limited to 1970-2037 (see below).
TIME can be used to only store the time of day (HH:MM:SS), without the date. It can also be used to represent a time interval (for example: -02:00:00 for "two hours in the past"). Range: '-838:59:59' => '838:59:59'.
YEAR can be used to store the year number only.
If you manipulate dates, you have to specify the actual date, not only the time - that is, MySQL will not automagically use today as the current date. On the contrary, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD value, which will probably be invalid.
The following examples show the precise date range for Unix-based timestamps, which starts at the Unix Epoch and stops just before the first new year before the $2^{31}-1$ usual limit (2038).

### set and enum

A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them.

## Operators

MySQL uses some standard SQL operators and some non-standard operators. They can be used to write expressions which involve constant values, variables, values contained in fields and / or other expressions.

### Precedence
#### Operator precedence

Table of operator precedence:
Modifiers:
* PIPES_AS_CONCAT - If this SQL mode is enabled, || has precedence on ^, but - and ~ have precedence on ||.
* HIGH_NOT_PRECEDENCE - If this SQL mode is enabled, NOT has the same precedence level as !.

#### Use of parenthesis

You can use parenthesis to force MySQL to evaluate a subexpression before another independently from operator precedence:
You can also use parenthesis to make an expression more readable by humans, even if they don't affect the precedence:

### Assignment operators

You can use the = operator to assign a value to a column:
When you want to assign a value to a variable, you must use the := operator, because the use of = would be ambiguous (is it as assignment or a comparison?)
You can also use SELECT INTO to assign values to one or more variables.

### Comparison operators
#### Equality

If you want to check if 2 values are equal, you must use the = operator:
If you want to check if 2 values are different, you can use the <> or != operators, which have the same meaning:
<> return 1 where = returns 0 and vice versa.

#### IS and NULL-safe comparison

When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null, you can use IS:
You can check if a value is non-NULL:
There is also an equality operator which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:
There is not a NULL-safe non-equality operator, but you can type the following:

#### IS and boolean comparisons

IS and IS NOT can also be used for boolean comparisons. You can use them with the reserved words TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL).

#### Greater, Less...

You can check if a value is greater than another value:
You can also check if a value is minor than another value:
This kind of comparisons also works on TEXT values:
Generally speaking, alphabetical order is used for TEXT comparisons. However, the exact rules are defined by the COLLATION used. A COLLATION defines the sorting rules for a given CHARACTER SET. For example, a COLLATION may be case-sensitive, while another COLLATION may be case-insensitive.
You can check if a value is equal or greater than another value. For example, the following queries have the same meaning:
Similarly, you can check if a value is less or equal to another value:

#### BETWEEN

If you want to check if a value is included in a given range, you can use the BETWEEN ... AND ... operator. AND doesn't have its usual meaning. Example:
The value after BETWEEN and the value after AND are included in the range.
You can also use NOT BETWEEN to check if a value is not included in a range:

#### IN

You can use the IN operator to check if a value is included in a list of values:
You should not include in the list both numbers and strings, or the results may be unpredictable. If you have numbers, you should quote them:
There is not a theorical limit to the number of values included in the IN operator.
You can also use NOT IN:

### Logical operators
#### MySQL boolean logic

MySQL doesn't have a real BOOLEAN datatype.
FALSE is a synonym for 0. Empty strings are considered as FALSE in a boolean context.
TRUE is a synonym for 1. All non-NULL and non-FALSE data are considered as TRUE in a boolean context.
UNKNOWN is a synonym for NULL. The special date 0/0/0 is NULL.

#### NOT

NOT is the only operator which has only one operand. It returns 0 if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL.
! is a synonym for NOT.

#### AND

AND returns 1 if both the operands are TRUE, else returns 0; if one of the operands is NULL, returns NULL.
&& is a synonym for AND.

#### OR

OR returns TRUE if at least one of the operands is TRUE, else returns FALSE; if one of the operands is NULL, returns NULL.
|| is a synonym for OR.

#### XOR

XOR (eXclusive OR) returns 1 if only one of the operands is TRUE and the other operand is FALSE; returns 0 if both the operands are TRUE o both the operands are FALSE; returns NULL if one of the operands is NULL.

#### Synonyms

AND can be written as && OR can be written ad || NOT can be written as !
Only NOT (usually) has a different precedence from its synonym. See operator precedence for detail.

### Arithmetic operators

MySQL supports operands which perform all basic arithmetic operations.
You can type positive values with a '+', if you want:
You can type negative values with a '-'. - is an inversion operand:
You can make sums with '+':
You can make subtractions with '-':
You can multiply a number with '*':
You can make divisions with '/'. Returns a FLOAT number:
You can make integer divisions with DIV. Resulting number is an INTEGER. No reminder. This has been added in MySQL 4.1.
You can get the reminder of a division with '%' or MOD:

#### Using + to cast data

You can convert an INTEGER to a FLOAT doing so:
You can't convert a string to a FLOAT value by adding 0.0, but you can cast it to an INTEGER:

### Text operators

There are no concatenation operators in MySQL.
Arithmetic operators convert the values into numbers and then perform arithmetic operations, so you can't use + to concatenate strings.
You can use the CONCAT function instead.

#### LIKE

The LIKE operator may be used to check if a string matches to a pattern. A simple example:
The pattern matching is usually case insensitive. There are two exceptions:
* when a LIKE comparison is performed against a column which has been declared with the BINARY flag (see CREATE TABLE);
* when the expression contains the BINARY clause:
You can use two special characters for LIKE comparisons:
* _ means "any character" (but must be 1 char, not 0 or 2)
* % means "any sequence of chars" (even 0 chars or 1000 chars)
Note that "\" also escapes quotes ("'") and this behaviour can't be changed by the ESCAPE clause. Also, the escape character does not escape itself.
Common uses of LIKE:
* Find titles starting with the word "hello":
* Find titles ending with the word "world":
* Find titles containing the word "gnu":
These special chars may be contained in the pattern itself: for example, you could need to search for the "_" character. In that case, you need to "escape" the char:
Sometimes, you may want to use an escape character different from "\". For example, you could use "/":
When you use = operator, trailing spaces are ignored. When you use LIKE, they are taken into account.
LIKE also works with numbers.
If you want to check if a pattern doesn't match, you can use NOT LIKE:

#### SOUNDS LIKE

You can use SOUNDS LIKE to check if 2 text values are pronounced in the same way. SOUNDS LIKE uses the SOUNDEX algorythm, which is bases on English rules and is very approximate (but simple and thus fast).
SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.

#### Regular expressions

You can use REGEXP to check if a string matches to a pattern using regular expressions.
You can use RLIKE as a synonym for REGEXP.

### Bitwise operators

Bit-NOT:
Bit-AND:
Bit-OR:
Bit-XOR:
Left shift:
Right shift:

## Import / export

Aside from mysqldump (cf. MySQL/Administration), you can also export raw data using:
Examples:
Beware that the MySQL daemon itself will write the file, not the user you run the MySQL client with. The file will be stored on the server, not on your host. Moreover, the server will need write access to the path you specify (usually, the server can _not_ write in your home directory, e.g.). Hence why we (unsecurely) used /tmp in the examples.

## Exercises
### Practicing SELECT
#### Table `list`

| ID | Name | Surname | FlatHave | FlatWant |
| 1 | Shantanu | Oak | Goregaon | |
| 2 | Shantanu | Oak | Andheri | |
| 3 | Shantanu | Oak | | Dadar |
| 4 | Ram | Joshi | | Goregaon |
| 5 | Shyam | Sharma | | Andheri |
| 6 | Ram | Naik | Sion | |
| 7 | Samir | Shah | Parle | |
| 8 | Ram | Joshi | Dadar | |
| 9 | Shyam | Sharma | Dadar | |

#### Exercise I - Questions

* Who has a flat in "Goreagon" and who wants to buy one?
* Who has a flat in "Parle" and who wants to buy one?
* Where does "Shantanu Oak" own the flats and where does he want to buy one?
* How many entries have been recorded so far?
* How many flats are there for sale?
* What are the names of our clients?
* How many clients do we have?
* List the customers whose name start with "S"?
* Rearrange the list Alphabetically sorted.

#### Exercise I - Answers

* select * from list where FlatHave = "Goregaon" and FlatWant = "Goregaon"
* select * from list where FlatHave = "Parle" and FlatWant = "Parle"
* select FlatHave,FlatWant from list where Name = "Shantanu" and Surname = "Oak"
* select count(*) from list
* select count(FlatHave) from list where FlatHave is not null
* select distinct Name, Surname from list
* select count(distinct Name, surname) from list
* select * from list where Name like "S%"
* select Surname, Name, FlatHave, FlatWant from list order by Name

#### Table `grades`

| ID | Name | Math | Physics | Literature |
| 1 | John | 68 | 37 | 54 |
| 2 | Jim | 96 | 89 | 92 |
| 3 | Bill | 65 | 12 | 57 |
| 4 | Jeri | 69 | 25 | 82 |

#### Exercise II - Questions

* A list of all students who scored over 90
on his or her math paper?
* A list of all students who scored more than 85 in all subjects?
* Declare Results: Print the results of all students with result column.
* Find out total marks of all the students.
* What are the average marks of the class for each subject?
* What are the minimum marks in Math?
* What are the maximum marks in Math?
* Who got the highest marks in Math?

#### Exercise II - Answers

Note: many problems have more than one correct solution.
* SELECT * FROM grades WHERE math > 90
* SELECT name FROM grades WHERE math > 85 AND physics > 85 AND literature > 85
* SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC
* SELECT name, math+physics+literature FROM grades
* SELECT AVG(math), AVG(physics), AVG(literature) FROM grades
* SELECT MIN(math) FROM grades
* SELECT MAX(math) FROM grades
* SELECT * FROM students ORDER BY math DESC LIMIT 1

### Examples
#### Remove duplicate entries.

Assume the following table and data.
CREATE TABLE IF NOT EXISTS dupTest
(pkey int(11) NOT NULL auto_increment, an int, b int, c int, timeEnter timestamp(14), PRIMARY KEY (pkey));
insert into dupTest (a,b,c) values (1,2,3),(1,2,3), (1,5,4),(1,6,4);
Note, the first two rows contains duplicates in columns a and b. It contains other duplicates; but, leaves the other duplicates alone.
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);