Snippets

orElse returns the original Option if not None, or returns the provided Option as an alternative in that case:

def orElse[B >: A](ob: => Option[B]): Option[B] = this map (Some(_)) getOrElse ob

filter function that will turn any Option into a None if it doesn't satisfy the provided predicate can be implemented in a following way:

def filter(f: A => Boolean): Option[A] = this match {
  case Some(a) if f(a) => this
  case _ => None
}

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of CARTESIAN JOIN or CROSS JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Read more: SQL - CARTESIAN or CROSS JOINS

Example:

Consider the following two tables,
(a) CUSTOMERS table is as follows:

| ID | NAME     |
+----+----------+
|  1 | Ramesh   |
|  2 | Khilan   |

(b) Another table is ORDERS as follows:

|OID  | DATE  | C_ID | AMOUNT |
+-----+-------+------+--------+
| 102 | 10-08 |    3 |   3000 |
| 100 | 10-08 |    3 |   1500 |
| 101 | 11-20 |    2 |   1560 |

Now, let us join these two tables using INNER JOIN as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;

This would produce the following result:

| ID | NAME     | AMOUNT | DATE  |
+----+----------+--------+-------+
|  1 | Ramesh   |   3000 | 10-08 |
|  1 | Ramesh   |   1500 | 10-08 |
|  1 | Ramesh   |   1560 | 11-20 |
|  2 | Khilan   |   3000 | 10-08 |
|  2 | Khilan   |   1500 | 10-08 |
|  2 | Khilan   |   1560 | 11-20 |

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.
Note: All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.

Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]

Read more: SQL - TOP, LIMIT or ROWNUM Clause

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT TOP 3 * FROM CUSTOMERS;

This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |

Read more: SQL - TOP, LIMIT or ROWNUM Clause

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

Following is an example on MySQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT * FROM CUSTOMERS
LIMIT 3;

This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |

Read more: SQL - TOP, LIMIT or ROWNUM Clause

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

Following is an example on Oracle server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;

This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |

Read more: SQL - TOP, LIMIT or ROWNUM Clause

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator	Description
=	Equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Read more: SQL WHERE Clause