9. Basic keywords and Its uses

SELECT

SELECTcolumn_name,column_name

FROMtable_name;

and

SELECT*FROMtable_name;

DISTINCT

Used to return only distinct (different) values.

SELECTDISTINCTcolumn_name,column_name

FROMtable_name;

WHERE

Used to extract only those records that fulfill a specified criterion.

SELECTcolumn_name,column_name

FROMtable_name

WHEREcolumn_name operator value;

AND & OR Operators

Used to filter records based on more than one condition.

SELECT*FROM Customers

WHERE Country='Germany'

AND City='Berlin';

SELECT*FROM Customers

WHERE City='Berlin'

OR City='Mnchen';

SELECT TOP Clause

Used to specify the number of records to return.

SELECTTOPnumber|percentcolumn_name(s)

FROMtable_name;

LIKE Operator

Used to search for a specified pattern in a column.

SELECTcolumn_name(s)

FROMtable_name

WHEREcolumn_nameLIKE pattern;

Examples

§ Selects all customers with a City starting with the letter "s":

SELECT*FROM Customers

WHERE City LIKE's%';

§ Selects all customers with a City ending with the letter "s":

SELECT*FROM Customers

WHERE City LIKE'%s';

§ Selects all customers with a Country containing the pattern "land":

SELECT*FROM Customers

WHERE Country LIKE'%land%';

§ Selects all customers with Country NOT containing the pattern "land":

SELECT*FROM Customers

WHERE Country NOTLIKE'%land%';

Wildcard Characters

§ Wildcard characters are used with the SQL LIKE operator.

§ Wildcards are used to search for data within a table.

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

§ Syntax

SELECTcolumn_name(s)

FROMtable_name

WHEREcolumn_nameIN(value1,value2,...);

§ Example

SELECT*FROM Customers

WHERE City IN('Paris','London');

BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

§ Syntax

SELECTcolumn_name(s)

FROMtable_name

WHEREcolumn_nameBETWEEN value1 AND value2;

§ Example

SELECT*FROM Products

WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Operator

The NOT BETWEEN operator selects values except that range.(outside of the range specified)

§ Example

SELECT*FROM Products

WHERE Price NOTBETWEEN 10 AND 20;

BETWEEN Operator with IN Example

Selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

§ Example

SELECT*FROM Products

WHERE (Price BETWEEN 10 AND 20)

ANDCategoryIDNOTIN(1,2,3);

BETWEEN Operator with Text Value Example

Selects all products with a ProductName beginning with any of the letter BETWEEN 'C' and 'M':

§ Example

SELECT*FROM Products

WHEREProductNameBETWEEN'C'AND'M';

NOT BETWEEN Operator with Text Value Example

Selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':

§ Example

SELECT*FROM Products

WHEREProductNameNOTBETWEEN'C'AND'M';

BETWEEN Operator with Date Value Example

Selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':

§ Example

SELECT*FROM Orders

WHEREOrderDateBETWEEN #07/04/1996# AND #07/09/1996#;






















<<Prev                                                                          Next>>