SQL Syntax

Select
SELECT "column_name" FROM "table_name"
Distinct
SELECT DISTINCT "column_name"
FROM "table_name"
Where
SELECT "column_name"
FROM "table_name"
WHERE "condition"

And/Or
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
In
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)
Between
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
Like
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
Order By
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]
Count
SELECT COUNT("column_name")
FROM "table_name"
Group By
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
Having
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)
Create Table
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Drop Table
DROP TABLE "table_name"
Truncate Table
TRUNCATE TABLE "table_name"
Insert Into
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
Update
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
Delete From
DELETE FROM "table_name"
WHERE {condition}

SQL SELECT

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

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

SQL DISTINCT

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 redundencies. 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

SQL WHERE

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

SQL AND OR

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 interchangably. 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

SQL IN

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

SQL BETWEEN

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

SQL LIKE

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 for is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
{PATTERN} often consists of wildcards. Here are some 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 string that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.

Let's use this last example on our Store_Information table:
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 LIKE '%AN%'

Result:

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

SQL 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 the 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

SQL Functions

Since we have started dealing with numbers, the next natural question to ask is if it is possible to do math on those numbers, such as summing them up or taking their average. The answer is yes! SQL has several arithematic functions, and they are:

• AVG
• COUNT
• MAX
• MIN
• SUM

The syntax for using functions is,
SELECT "function type"("column_name")
FROM "table_name"
For example, if we want to get the sum of all sales from our example 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 would type in
SELECT SUM(Sales) FROM Store_Information

Result:

SUM(Sales)

$2750

$2750 represents the sum of all Sales entries: $1500 + $250 + $300 + $700.
In addition to using functions, it is also possible to use SQL to perform simple tasks such as addition (+) and subtraction (-). For character-type data, there are also several string functions available, such as concatenation, trim, and substring functions. Different RDBMS vendors have different string functions implementations, and it is best to consult the references for your RDBMS to see how these functions are used.

SQL COUNT

Another arithematic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is,
SELECT COUNT("column_name")
FROM "table_name"
For example, if we want to find the number of store entries in our 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'd key in
SELECT COUNT(store_name)
FROM Store_Information
Result:

Count(store_name)

4

COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. For example, if we want to find out the number of distinct stores, we'd type,
SELECT COUNT(DISTINCT store_name)
FROM Store_Information
Result:

Count(DISTINCT store_name)

3

SQL GROUP BY

Now we return to the aggregate functions. Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores. The corresponding SQL syntax is,
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
In our example, 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 would key in,
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
Result:

store_name	SUM(Sales)
Los Angeles	$1800
San Diego	$250
Boston	$700

The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithematic operator appears in the SELECT statement. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithematic operator.

SQL HAVING

Another thing people may want to do is to limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of the SQL statement, and a SQL statement with the HAVING clause may or may not include the GROUP BY clause. The syntax for HAVING is,
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)
Note: the GROUP BY clause is optional.
In our example, 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 would type,
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Result:

store_name	SUM(Sales)
Los Angeles	$1800

SQL ALIAS

We next focus on the use of aliases. There are two types of aliases that are used most frequently: column alias and table alias.
In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(sales). While this is comprehensible, we can envision cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias would greatly make the output much more readable.
The second type of alias is the table alias. This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables (the technical term is 'perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.
Before we get into joins, though, let's look at the syntax for both the column and table aliases:
SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias"
Briefly, both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our 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 use the same example as that in the SQL GROUP BY section, except that we have put in both the column alias and the table alias:
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name

Result:

Store	Total Sales
Los Angeles	$1800
San Diego	$250
Boston	$700

Notice that difference in the result: the column titles are now different. That is the result of using the column alias. Notice that instead of the somewhat cryptic "Sum(Sales)", we now have "Total Sales", which is much more understandable, as the column header. The advantage of using a table alias is not apparent in this example. However, they will become evident in the next section.

SQL Join

Now we want to look at joins. To do joins correctly in SQL requires many of the elements we have introduced so far. Let's assume that we have the following two tables,

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

Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego

and we want to find out sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables. Examining the two tables, we find that they are linked via the common field, "store_name". We will first present the SQL statement and explain the use of each segment later:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Result:

REGION	SALES
East	$700
West	$2050

The first two lines tell SQL to select two fields, the first one is the field "region_name" from table Geography (aliased as REGION), and the second one is the sum of the field "Sales" from table Store_Information (aliased as SALES). Notice how the table aliases are used here: Geography is aliased as A1, and Store_Information is aliased as A2. Without the aliasing, the first line would become
SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES
which is much more cumbersome. In essence, table aliases make the entire SQL statement easier to understand, especially when multiple tables are included.
Next, we turn our attention to line 3, the WHERE statement. This is where the condition of the join is specified. In this case, we want to make sure that the content in "store_name" in table Geography matches that in table Store_Information, and the way to do it is to set them equal. This WHERE statement is essential in making sure you get the correct output. Without the correct WHERE statement, a Cartesian Join will result. Cartesian joins will result in the query returning every possible combination of the two (or whatever the number of tables in the FROM statement) tables. In this case, a Cartesian join would result in a total of 4 x 4 = 16 rows being returned.

SQL Outer Join

Previously, we had looked at left join, or inner join, where we select rows common to the participating tables to a join. What about the cases where we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command.
The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE cluase on the other side of the table for which we want to include all the rows.
Let's assume that we have the following two tables,

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

Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego

and we want to find out the sales amount for all of the stores. If we do a regular join, we will not be able to get what we want because we will have missed 'New York', since it does not appear in the Store_Information table. Therefore, we need to perform an outer join on the two tables above:
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Georgraphy A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Note that in this case, we are using the Oracle syntax for outer join.

Result:

store_name	SALES
Boston	$700
New York
Los Angeles	$1800
San Diego	$250

Note: NULL is returned when there is no match on the second table. In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.

SQL Subquery

It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct. What is subquery useful for? First, it can also be used to join tables. Also, there are cases where the only way to correlate two tables is through a subquery.
The syntax is as follows:
SELECT "column_name1"
FROM "table_name"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name1"
FROM "table_name"
WHERE [Condition])
[Comparison Operator] could be equality operators such as =, >, <, >=, <=. It can also be a text operator such as "LIKE."
Let's use the same example as we did to illustrate SQL joins:

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

Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego

and we want to use a subquery to find the sales of all stores in the West region. To do so, we use the following SQL statement:
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
Result:

SUM(Sales)

2050

In this example, instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we first use the subquery to find out which stores are in the West region, and then we sum up the sales amount for these stores.

SQL Union

The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).
The syntax is as follows:
[SQL Statement 1]
UNION
[SQL Statement 2]
Let's assume that we have the following two tables,

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

Table Internet Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

Please note that if we type "SELECT DISTINCT Date" for either or both of the SQL statement, we will get the same result set.

SQL Union All

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.
The syntax for UNION ALL is as follows:
[SQL Statement 1]
UNION ALL
[SQL Statement 2]
Let's use the same example as the previous section to illustrate the difference. Assume that we have the following two tables,

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

Table Internet Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

Result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

SQL Intersect

Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).
The syntax is as follows:
[SQL Statement 1]
INTERSECT
[SQL Statement 2]

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

Table Internet Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
Result:

Date

Jan-07-1999

Please note that the INTERSECT command will only return distinct values.

SQL Minus

The MINUS operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.
The syntax is as follows:
[SQL Statement 1]
MINUS
[SQL Statement 2]
Let's continue with the same example:

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

Table Internet Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

and we want to find out all the dates where there are store sales, but no internet sales. To do so, we use the following SQL statement:
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
Result:

Date

Jan-05-1999

Jan-08-1999

"Jan-05-1999", "Jan-07-1999", and "Jan-08-1999" are the distinct values returned from "SELECT Date FROM Store_Information." "Jan-07-1999" is also returned from the second SQL statement, "SELECT Date FROM Internet_Sales," so it is excluded from the final result set.
Please note that the MINUS command will only return distinct values.

SQL CREATE TABLE

Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, chances are that you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that table is the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.
Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. Tables are divided into rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. So, for example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.
So what are data types? Typically, data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a string (such as 'sql'), a date/time expression (such as '2000-JAN-25 03:22:22'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(50) - meaning it is a string with 50 characters). One thing to note is that different relational databases allow for different data types, so it is wise to consult with a database-specific reference first.
The SQL syntax for CREATE TABLE is
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
So, if we are to create the customer table specified as above, we may type in
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

SQL DROP TABLE

Sometimes we may decide that we need to get rid of a table in the database for some reason. In fact, it would be problematic if we cannot do so because this could create a maintenance nightmare for the DBA's. Fortunately, SQL allows us to do it, as we can use the DROP TABLE command. The syntax for DROP TABLE is
DROP TABLE "table_name"
So, if we wanted to drop the table called customer that we created in the last section, we simply type
DROP TABLE customer.

SQL TRUNCATE TABLE

Sometimes we wish to get rid of all the data in a table. One way of doing this is with DROP TABLE, which we saw in the last section. But what if we wish to simply get rid of the data but not the table itself? For this, we can use the TRUNCATE TABLE command. The syntax for TRUNCATE TABLE is
TRUNCATE TABLE "table_name"
So, if we wanted to truncate the table called customer that we created in SQL CREATE, we simply type
TRUNCATE TABLE customer.

SQL INSERT INTO

In the previous sections, we have seen how to retrieve information from tables. But how do these rows of data get into these tables in the first place? This is what this section, covering the INSERT statement, and next section, covering tbe UPDATE statement, are about.
There are essentially basically two ways to INSERT data into a table. One is to insert it one row at a time, and the other is to insert multiple rows at a time. Let's first look at how we may INSERT data one row at a time:
The syntax for inserting data into a table one row at a time is as follows:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)
Assuming that we have a table that has the following structure,
Table Store_Information

Column Name	Data Type
store_name	char(50)
Sales	float
Date	datetime

and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales. We will hence use the following SQL script:
INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')
The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:
INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"
Note that this is the simplest form. The entire statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as including table joins and aliases.
So for example, if we wish to have a table, Store_Information, that collects the sales information for year 1998, and you already know that the source data resides in the Sales_Information table, you'll type in:
INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
Here I have used the SQL Server syntax to extract the year information out of a date. Other relational databases will have different syntax. For example, in Oracle, you will use WHERE to_char(date,'yyyy')=1998.

SQL UPDATE

Once we have the information in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The syntax for this is
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
It is easiest to use an example. Say we currently have a table as below:
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 find that the sales for Los Angeles on 01/08/1999 is actually $500 instead of $300, and we need to update that particular entry. To do so, we use the following SQL:
UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
The resulting table would look like
Table Store_Information

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

In this case, there is only one row that satisfies the condition in the WHERE clause. If there are two rows that satisfy the condition, both rows will be modified.

It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:
UPDATE TABLE "table_name"
SET ("column_1", "column_2") = ([new value 1], [new value 2])
WHERE {condition}

SQL DELETE FROM

Sometimes rather than updating, we wish to get rid of records from the table. To do so, we can use the DELETE FROM command. The syntax for this is
DELETE FROM "table_name"
WHERE {condition}
It is easiest to use an example. Say we currently have a table as below:
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

and we decide not to keep any information on Los Angeles in this table. We type the following SQL:
DELETE FROM Store_Information
WHERE store_name = "Los Angeles"
The resulting table would look like
Table Store_Information

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

1.    Differences Between Oracle and MS SQL Server

1.     Data Types

Data Type	Oracle	MS SQL Server
Fixed Length String	CHAR(n) - limit 2KB	CHAR(n), CHARACTER(n) - limit 255 (6.5) - limit 8KB (7.0)
Variable Length String	VARCHAR2(n), VARCHAR(n) - limit 4KB in a column - limit 32KB in a variable - VARCHAR is obsolete	VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
Integer	INTEGER, INTEGER(n), SMALLINT	INTEGER (4 bytes), INT (4 bytes), SMALLINT (2 bytes), TINYINT (1 byte), BIT (1 bit)
Fixed Point	NUMBER, NUMBER(n), NUMBER(n,d), FLOAT, FLOAT(n), FLOAT(n,d)	NUMERIC, NUMERIC(n), NUMERIC(n,d), DECIMAL, DECIMAL(n), DECIMAL(n,d), DEC, DEC(n), DEC(n,d), MONEY, SMALLMONEY
Floating Point	DECIMAL	FLOAT, FLOAT(n), DOUBLE PRECISION, REAL,
Date	DATE	DATETIME, SMALLDATETIME, TIMESTAMP - TIMESTAMP auto-updated
Binary	RAW(n) - limit 255 bytes	BINARY(n), VARBINARY(n), BINARY VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
Large String	LONG, LONG VARCHAR - limit 2GB - limit one per table row CLOB - limit 4GB	TEXT - limit 2GB
Large Binary	LONG RAW - limit 2GB - limit one per table row BLOB - limit 4GB	IMAGE - limit 2GB
Multi-byte chars	NCHAR(n) NVARCHAR(n) NCLOB - same limits as CHAR, VARCHAR, CLOB	NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n) NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n) NTEXT, NATIONAL TEXT - same limits as CHAR, VARCHAR, TEXT
OS File	BFILE	<not supported>
Row Identifier	implicit ROWID column	(use an IDENTITY column)
Secure OS Label	MLSLABEL, RAW MLSLABEL	<not supported>
128-bit Unique Number (UUID, GUID)	<not supported>	UNIQUEIDENTIFIER (version 7.0 only)

2.     Limits

Description	Oracle	MS SQL Server
Columns per table	1000	250 (6.5) 1024 (7.0)
Row size	unlimited	1962 bytes (6.5) 8060 bytes (7.0) - includes pointers, but not data, for TEXT and IMAGE columns
LONG and LONG RAW columns per row	1 (must be last column)	unlimited (16-byte pointer per)
LOB, TEXT, and IMAGE columns per row	unlimited (16-byte pointer per)	unlimited (16-byte pointer per)
Clustered indexes per table	1	1
Non-clustered indexes per table	unlimited	249
Columns per index	16	16
Index row size	2K bytes	900 bytes
Identifier Length	30 chars	30 chars (6.5) 128 chars (7.0)
Tables per SELECT	unlimited	16 (6.5) 256 (7.0)
Source code per stored procedure		64KB (6.5) 250MB (7.0)
Data type limits	(see Data Types)

3.     Operators

Most operators are the same in Oracle and MS SQL Server.  Here are some that differ:

Description	Oracle	MS SQL Server
String concatenation	string1 || string2	string1 + string2

 

4.     Built-In Functions

Oracle and MS SQL Server offer many of the same built-in functions.  For example, they both offer ABS, EXP, ROUND, UPPER, LOWER, AVG, COUNT, SUM, ASCII, etc.  The following table shows some of the corresponding functions that don't have the same name.

Description	Oracle	MS SQL Server
Smallest integer >= n	CEIL	CEILING
Modulus	MOD	%
Truncate number	TRUNC	<none>
Max or min number or string in list	GREATEST, LEAST	<none>
Translate NULL to n	NVL	ISNULL
Return NULL if two values are equal	DECODE	NULLIF
String concatenation	CONCAT(str1,str2)	str1 + str2
Convert ASCII to char	CHR	CHAR
Capitalize first letters of words	INITCAP	<none>
Find string in string	INSTR	CHARINDEX
Find pattern in string	INSTR	PATINDEX
String length	LENGTH	DATALENGTH
Pad string with blanks	LPAD, RPAD	<none>
Trim leading or trailing chars other than blanks	LTRIM(str,chars), RTRIM(str,chars)	<none>
Replace chars in string	REPLACE	STUFF
Convert number to string	TO_CHAR	STR, CAST
Convert string to number	TO_NUMBER	CAST
Get substring from string	SUBSTR	SUBSTRING
Char for char translation in string	TRANSLATE	<none>
Date addition	ADD_MONTH or +	DATEADD
Date subtraction	MONTHS_BETWEEN or -	DATEDIFF
Last day of month	LAST_DAY	<none>
Time zone conversion	NEW_TIME	<none>
Next specified weekday after date	NEXT_DAY	<none>
Convert date to string	TO_CHAR	DATENAME, CONVERT
Convert string to date	TO_DATE	CAST
Convert date to number	TO_NUMBER(TO_CHAR(d))	DATEPART
Date round	ROUND	CONVERT
Date truncate	TRUNC	CONVERT
Current date	SYSDATE	GETDATE
Convert hex to binary	HEXTORAW	CAST
Convert binary to hex	RAWTOHEX	CONVERT
If statement in an expression	DECODE	CASE ... WHEN or COALESCE
User's login id number or name	UID, USER	SUSER_ID, SUSER_NAME
User's database id number or name	UID, USER	USER_ID, USER_NAME
Current user	USER	USER

5.     Differences in SQL Syntax

The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations:

Description	Oracle	MS SQL Server
Left Outer Join	WHERE column1 = column2(+)	FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column2 Note:  The following syntax is also supported, but is no longer recommended: WHERE column1 *= column2
Right Outer Join	WHERE column1(+) = column2	FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2 Note:  The following syntax is also supported, but is no longer recommended: WHERE column1 =* column2
Full Outer Join		FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2
SELECT without FROM	SELECT 'hello world' FROM DUAL	SELECT 'hello world'
SELECT data into a table	CREATE TABLE AS SELECT ...	SELECT ... INTO
Intersection of 2 SELECTS	SELECT ... INTERSECT SELECT ...	SELECT ... WHERE EXISTS (SELECT ...)
Subtraction of 2 SELECTS	SELECT ... MINUS SELECT ...	SELECT ... WHERE NOT EXISTS (SELECT ...)
INSERT into a JOIN	INSERT INTO SELECT ...	Create a VIEW and INSERT INTO it.
UPDATE data in a JOIN	UPDATE SELECT...	Create a VIEW and INSERT INTO it.
UPDATE one table based on criteria in another table	<not supported>	UPDATE table FROM ...
DELETE rows from one table based on criteria in another table	<not supported>	DELETE FROM table FROM ...
DROP a column from a table	<not supported until Oracle 8i>	ALTER TABLE table_name DROP COLUMN column_name
Readonly VIEW	CREATE VIEW ... WITH READONLY	GRANT SELECT ...
Save point	SAVEPOINT	SAVE TRANSACTION
Table lock	LOCK TABLE...IN SHARE MODE	SELECT...table_name (TABLOCK)
Exclusive table lock	LOCK TABLE...IN EXCLUSIVE MODE	SELECT...table_name (TABLOCKX)
Reserving index space	PCTFREE=0	FILLFACTOR=100
Declaring a local variable	DECLARE varname type;	DECLARE @varname type
Initializing a local variable	DECLARE varname type := value;	<not supported>
Declaring a constant	DECLARE varname CONSTANT type := value;	<not supported>
Assigning to a variable	varname := value SELECT value INTO varname	SET @varname = value SELECT @varname = value
Assigning to a variable from a cursor	FETCH cursorname INTO varname	FETCH NEXT FROM cursorname INTO varname
Declaring a cursor	CURSOR curname (params) IS SELECT ...;	DECLARE curname CURSOR FOR SELECT ...
If statement	IF ... THEN ELSIF ... THEN ELSE ENDIF	IF ... BEGIN ... END ELSE BEGIN ... END
While loop	WHILE ... LOOP END LOOP	WHILE ... BEGIN ... END
Other loops	FOR ... END LOOP LOOP ... END LOOP	<not supported>
Loop exit	EXIT, EXIT WHEN	BREAK, CONTINUE
Print output	DBMS_OUTPUT.PUT_LINE	PRINT
Raise error	RAISE_APPLICATION_ERROR	RAISERROR
Statement terminator	Semi-colon (;)	<none required>

 

Differences in Oracle SQL

Basic SQL Features

---

Oracle does not support AS in FROM clauses, but you can still specify tuple variables without AS:

    from Relation1 u, Relation2 v

On the other hand, Oracle does support AS in SELECT clauses, although the use of AS is completely optional.

---

The set-difference operator in Oracle is called MINUS rather than EXCEPT. There is no bag-difference operator corresponding to EXCEPT ALL. The bag-intersection operator INTERSECT ALL is not implemented either. However, the bag-union operator UNION ALLis supported.

---

In Oracle, you must always prefix an attribute reference with the table name whenever this attribute name appears in more than one table in the FROM clause. For example, suppose that we have tables R(A,B) and S(B,C). The following query does not work in Oracle, even though B is unambiguous because R.B is equated to S.B in the WHERE clause:

    select B from R, S where R.B = S.B;    /* ILLEGAL! */

Instead, you should use:

    select R.B from R, S where R.B = S.B;

---

In Oracle, the negation logical operator (NOT) should go in front of the boolean expression, not in front of the comparison operator. For example, "NOT A = ANY (<subquery>)" is a valid WHERE condition, but "A NOT = ANY (<subquery>)" is not. (Note that "A <> ANY (<subquery>)" is also a valid condition, but means something different.) There is one exception to this rule: You may use either "NOT A IN (<subquery>)" or "A NOT IN (<subquery>)".

---

In Oracle, an aliased relation is invisible to a subquery's FROM clause. For example,

   SELECT * FROM R S WHERE EXISTS (SELECT * FROM S)

is rejected because Oracle does not find S in the subquery, but

   SELECT * FROM R S WHERE EXISTS (SELECT * FROM R WHERE R.a = S.a)

is accepted.

---

In Oracle, a query that includes

1.     a subquery in the FROM clause, using GROUP BY; and

2.     a subquery in the WHERE clause, using GROUP BY

can cause the database connection to break with an error (ORA-03113: end-of-file on communication channel), even if the two GROUP BY clauses are unrelated.

---

Comments

In Oracle, comments may be introduced in two ways:

1.     With /*...*/, as in C.

2.     With a line that begins with two dashes --.

Thus:

-- This is a comment

SELECT * /* and so is this */

FROM R;