Sql Tutorial of Select Keyword,Distinct Keyword,Where Keyword,And Or Keyword,In Keyword,Between Keyword,Wild Card Charcters ,Like Keyword,Order By

Sql Tutorial of Select Keyword,Distinct Keyword,Where Keyword,And Or Keyword,In Keyword,Between Keyword,Wild Card Charcters ,Like Keyword,Order By
Select Keyword:

What do we use SQL commands for? A common use is to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table

SELECT "column_name" FROM "table_name"

To illustrate the above example, assume that we have the following table:

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

We shall use this table as an example throughout the tutorial (this table will appear in all sections). To select all the stores in this table, we key in,

SELECT store_name FROM Store_Information

Result:

store_name

Los Angeles

San Diego

Los Angeles

Boston

Multiple column names can be selected, as well as multiple table names.

Distinct Keyword

The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:

SELECT DISTINCT "column_name"
FROM "table_name"

For example, to select all distinct stores in Table Store_Information,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT DISTINCT store_name FROM Store_Information

Result:

store_name

Los Angeles

San Diego

Boston

Where Keyword

Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "condition"

For example, to select all stores with sales above $1,000 in Table Store_Information,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT store_name
FROM Store_Information
WHERE Sales > 1000

Result:

store_name

Los Angeles

And /Or Keyword

In the previous section, we have seen that the WHERE keyword can be used to conditionally select data from a table. This condition can be a simple condition (like the one presented in the previous section), or it can be a compound condition. Compound conditions are made up of multiple simple conditions connected by AND or OR. There is no limit to the number of simple conditions that can be present in a single SQL statement.

The syntax for a compound condition is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+

The {}+ means that the expression inside the bracket will occur one or more times. Note that AND and OR can be used interchangeably. In addition, we may use the parenthesis sign () to indicate the order of the condition.

For example, we may wish to select all stores with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)

Result:

store_name

Los Angeles

San Francisco

In Keyword

In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the WHERE clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns. The syntax for using the IN keyword is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters. If there is only one value inside the parenthesis, this commend is equivalent to

WHERE "column_name" = 'value1'

For example, we may wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles', 'San Diego')

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999

Between Keyword

Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN keyword allows for selecting a range. The syntax for the BETWEEN clause is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'

This will select all rows whose column has a value between 'value1' and 'value2'.

For example, we may wish to select view all sales information between January 6, 1999, and January 10, 1999, in Table Store_Information,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'

Note that date may be stored in different formats in different databases. This tutorial simply choose one of the formats.

Result:

store_name	Sales	Date
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Wild card characters:

There are times when we want to match on a string pattern. To do that, we will need to employ the concept of wildcard. In SQL, there are two wildcards:

% (percent sign) represents zero, one, or more characters.

_ (underscore) represents exactly one character.

Wildcards are used with the LIKE keyword in SQL.

Below are some wildcard examples:

• 'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one).

• 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition.

• '%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would both satisfy the condition.

• '%AN%': All strings that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.

• '_AN%': All strings that contain a character, then 'AN', followed by anything else. For example, 'SAN FRANCISCO' would satisfy the condition, while 'LOS ANGELES' would not satisfy the condition.

Like Keyword:

LIKE is another keyword that is used in the WHERE clause. Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN). The syntax is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}

{PATTERN} often consists of wildcards.

Table Store_Information

store_name	Sales	Date
LOS ANGELES	$1500	Jan-05-1999
SAN DIEGO	$250	Jan-07-1999
SAN FRANCISCO	$300	Jan-08-1999
BOSTON	$700	Jan-08-1999

We want to find all stores whose name contains 'AN'. To do so, we key in,

SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%'

Result:

store_name	Sales	Date
LOS ANGELES	$1500	Jan-05-1999
SAN DIEGO	$250	Jan-07-1999
SAN FRANCISCO	$300	Jan-08-1999

Order By:

So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.

The syntax for an ORDER BY statement is as follows:

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]

The [] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.

It is possible to order by more than one column. In this case, the ORDER BY clause above becomes

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

Assuming that we choose ascending order for both columns, the output will be ordered in ascending order according to column 1. If there is a tie for the value of column 1, we then sort in ascending order by column 2.

For example, we may wish to list the contents of Table Store_Information by dollar amount, in descending order:

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

we key in,

SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC

Result:

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
Boston	$700	Jan-08-1999
San Francisco	$300	Jan-08-1999
San Diego	$250	Jan-07-1999

In addition to column name, we may also use column position (based on the SQL query) to indicate which column we want to apply the ORDER BY clause. The first column is 1, second column is 2, and so on. In the above example, we will achieve the same results by the following command:

SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY 2 DESC