Comparison Operator

OPERATOR             MEANING

      =                         Equal to
      >                         Greater Than
      >=                       Greater than or equal to
      <                         Less than
      <=                       Less than or equal to
      <>                       Not equal to

Using Comparison Conditions

In the above example, the SELECT statement retrieves the name,salary from emp table whose salary is greater than or equal (>=) 2500.

 Other Comparison Conditions

OPERATOR                        MEANING

BETWEEN....AND...        Between two values (inclusive)

IN (set)                              Match any of a list of values

LIKE                                 Match a character pattern

IS NULL                           Is a null value

These examples will be continued to next post. 

WHERE Clause

• Restrict the rows returned by using the WHERE Clause.

Syntax:

    SELECT *|{[DISTINCT] column|expression [alias],...}

   FROM table

   [WHERE condition(s)];

• The WHERE clause follows the FROM clause.

WHERE     Restricts the query to rows that meet a condition.

conditions   is composed of column names, expressions, constants and a comparison operator.

Using the WHERE Clause

In the example, the SELECT statement retrieves the employee name, name, job and department number of all employees whose department number is 10.

Another Example:

In the above example the SELECT statement retrieves data according to string conditions and string data is enclosed with single quotes.

NOTE:

• Character strings and data values are enclosed in single quotation marks.
• Character values are case sensitives and date values are Format Sensitives.
• The default date format is DD-MON-RR.

Unit 2 (RESTRICTING AND SORTING DATA )

TOPIC Objective:-

•  Limit the rows retrieved by a Query
•  Sort the rows retrieved by a Query

Through SELECT statement we can do a lot of selection and filtration to our search query, so we need to use some CLAUSES like (WHERE, ORDER BY), CONDITIONS like (BETWEEN, IN, LIKE, NULL) and some OPERATORS like (AND, OR, NOT).

So This Section covers following topics:

1) WHERE Clause
2) Comparison Operator
3) BETWEEN Condition
4) IN Condition
5) LIKE Condition
6) NULL Condition
7) Logical Conditions  ( AND, OR, NOT Operators)
8) AND Operator
9) OR Operator
10) NOT Operator
11) Operator Precedence
12) ORDER BY Clause
13) SORTING

TWO Tables is used for all operations (emp and dept)

EMP TABLE:

DEPT TABLE: 

DISTINCT - Eliminating Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Note:

You can specify multiple columns after the DISTINCT clause. The DISTINCT clause affects all the selected columns, and the result is every distinct combination of the columns.

EX: 

Without DISTINCT the above output:  (Notice the difference)