Sql

Learn Sql With Example

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 would 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)

Sometimes, we want to provide a default value for each column. A default value is used when you do not specify a column's value when inserting data into the table. To specify a default value, add "Default [value]" after the data type declaration. In the above example, if we want to default column "Address" to "Unknown" and City to "Mumbai", we would type in

CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50) default 'Unknown', City char(50) default 'Mumbai', Country char(25),
Birth_Date date)


Alter Table Statement

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table. Typical cases include the following:
- Add a column

- Drop a column

- Change a column name

- Change the data type for a column

Please note that the above is not an exhaustive list. There are other instances where ALTER TABLE is used to change the table structure, such as changing the primary key specification or adding a unique constraint to a column.
The SQL syntax for ALTER TABLE is

ALTER TABLE "table_name"
[alter specification]

[alter specification] is dependent on the type of alteration we wish to perform. For the uses cited above, the [alter specification] statements are:

• Add a column: ADD "column 1" "data type for column 1"

• Drop a column: DROP "column 1"

• Change a column name: CHANGE "old column name" "new column name" "data type for new column name"

• Change the data type for a column: MODIFY "column 1" "new data type"
Let's run through examples for each one of the above, using the "customer" table
created in the CREATE TABLE section:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Birth_Date	date
Country	char(25)
Address	char(50)
City	char(50)

First, we want to add a column called "Gender" to this table. To do this, we key in:

ALTER table customer add Gender char (1)

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Birth_Date	date
Address	char(50)
Country	char(25)
Gender	char(1)
City	char(50)

Next, we want to rename "Address" to "Addr". To do this, we key in,

ALTER table customer change Address Addr char(50)

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Birth_Date	date
Addr	char(50)
Country	char(25)
Gender	char(1)
City	char(50)

Then, we want to change the data type for "Addr" to char(30). To do this, we key in,
ALTER table customer modify Addr char(30)

Resulting table structure:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Birth_Date	date
Addr	char(5030)
Country	char(25)
Gender	char(1)
City	char(50)

Finally, we want to drop the column "Gender". To do this, we key in,
ALTER table customer drop Gender

Resulting table structure:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Birth_Date	date
Address	char(50)
Country	char(25)
City	char(50)

Drop Table Statement

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 CREATE TABLE section, we simply type
DROP TABLE customer.

Truncate Table Statement

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 TABLE, we simply type,

TRUNCATE TABLE customer

USE
The USE keyword is used to select a database in MySQL. The syntax is as follows:
USE "Database Name"

For example, if you want to connect to a database called "Scores", you can type in the following:
USE Scores;

In MySQL, you can access tables in multiple databases by specifying [Database Name].[Table Name]. If the table you want to access is currently in the database you use, there is no need to specify the database name.
For example, if you want to access table "Course_110" from database "Scores" and table "Students" from database "Personnel", you can type in the following:
USE Scores;

SELECT ...
FROM Course_110, Personnel.Students
WHERE ...
;

Insert Into Statement

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.
In SQL, there are essentially basically two ways to INSERT data into a table: One is to insert it one row at a time, 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:

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 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, we'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 to_char(date,'yyyy')=1998.

Update Statement

Once there's data 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}

For example, say we currently have a table as below:

Table Store_Information
Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-

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 notice that the sales for Los Angeles on 01/08/1999 is actually $500 instead of $300, and that particular entry needs to be updated.
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 multiple rows that satisfy the condition, all of them will be modified. If no WHERE clause is specified, all 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_name" SET column_1 = [value1], column_2 = [value2] WHERE {condition} 



Delete From Statement
 Sometimes we may wish to get rid of records from a 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.
To accomplish this, we type the following SQL:
  DELETE FROM Store_Information WHERE store_name = "Los Angeles"
 Now the content of table would look like,
 Table Store_Information store_name Sales Date San Diego $250 Jan-07-1999 Boston $700 Jan-08-1999
 You can also limit the type of information a table / a column can hold. This is done through the CONSTRAINT keyword, which is discussed next.
 Constraint
You can place constraints to limit the type of data that can go into a table. Such constraints can be specified when the table when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement. Common types of constraints include the following:

NOT NULL Constraint: Ensures that a column cannot have NULL value.
• DEFAULT Constraint: Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
• Primary Key Constraint: Used to uniquely identify a row in the table.
• Foreign Key Constraint: Used to ensure referential integrity of the data.

NOT NULL Constraint By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value. For example, in the following statement,
CREATE TABLE Customer (SID integer NOT NULL, Last_Name varchar (30) NOT NULL, First_Name varchar(30));
 Columns "SID" and "Last_Name" cannot include NULL, while "First_Name" can include NULL. An attempt to execute the following SQL statement,
  INSERT INTO Customer (Last_Name, First_Name) values ('Wong','Ken');
will result in an error because this will lead to column "SID" being NULL, which violates the NOT NULL constraint on that column.

DEFAULT Constraint The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. For example, if we create a table as below:
CREATE TABLE Student (Student_ID integer Unique, Last_Name varchar (30), First_Name varchar (30), Score DEFAULT 80);
and execute the following SQL statement,
  INSERT INTO Student (Student_ID, Last_Name, First_Name) values ('10','Johnson','Rick');
 The table will look like the following: Student_ID Last_Name First_Name Score 10 Johnson Rick 80 Even though we didn't specify a value for the "Score" column in the INSERT INTO statement, it does get assigned the default value of 80 since we had already set 80 as the default value for this column.

UNIQUE Constraint

 The UNIQUE constraint ensures that all values in a column are distinct. For example, in the following CREATE TABLE statement,
  CREATE TABLE Customer (SID integer Unique, Last_Name varchar (30),
First_Name varchar(30));
 column "SID" has a unique constraint, and hence cannot include duplicate values. Such constraint does not hold for columns "Last_Name" and "First_Name". So, if the table already contains the following rows: SID Last_Name First_Name 1 Johnson Stella 2 James Gina 3 Aaron Ralph Executing the following SQL statement,
  INSERT INTO Customer values ('3','Lee','Grace'); 
will result in an error because '3' already exists in the SID column, thus trying to insert another row with that value violates the UNIQUE constraint. Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key. In addition, multiple UNIQUE constraints can be defined on a table.

CHECK Constraint

 The CHECK constraint ensures that all values in a column satisfy certain conditions. Once defined, the database will only insert a new row or update an existing row if the new value satisfies the CHECK constraint. The CHECK constraint is used to ensure data quality For example, in the following CREATE TABLE statement,
  CREATE TABLE Customer (SID integer CHECK (SID > 0), Last_Name varchar (30), First_Name varchar(30));
Column "SID" has a constraint -- its value must only include integers greater than 0. So, attempting to execute the following statement,
  INSERT INTO Customer values ('-3','Gonzales','Lynn'); 
will result in an error because the values for SID must be greater than 0. Please note that the CHECK constraint does not get enforced by MySQL at this time.



Primary Key

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
Below are examples for specifying a primary key when creating a table:

CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30),
First_Name varchar(30));

Below are examples for specifying a primary key by altering a table:

ALTER TABLE Customer ADD PRIMARY KEY (SID);
Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.


Foreign Key
A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.
The structure of these two tables will be as follows:

Table CUSTOMER

column name	characteristic
SID	Primary Key
Last_Name
First_Name

Table ORDERS

column name	characteristic
Order_ID	Primary Key
Order_Date
Customer_SID	Foreign Key
Amount

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
Below we show examples of how to specify the foreign key when creating the ORDERS table:

CREATE TABLE ORDERS (Order_ID integer primary key, Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Select:

SELECT information FROM a table. (Note that a table is a container that resides in the database where the data is stored.

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

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

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

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

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

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


Wildcard

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

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. We saw several examples of wildcard matching in the previous section. Below we use an example to see how wildcard is used in conjunction with LIKE:

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
Aggregate 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: Average of the column.
SQL uses the AVG() function to calculate the average of a column. The syntax for using this function is,
SELECT AVG("column_name")
FROM "table_name"
For example, if we want to get the average of all sales from the following 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 would type in
SELECT AVG(Sales) FROM Store_Information

Result:
AVG(Sales)
$687.5
$687.5 represents the average of all Sales entries: ($1500 + $250 + $300 + $700) / 4


COUNT: Number of records.

Another arithmetic 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
San Francisco	$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



MAX: Maximum of the column.

SQL uses the MAX function to find the maximum value in a column. The syntax for using the MAX function is,
SELECT MAX("column_name")
FROM "table_name"
For example, if we want to get the highest sales from the following 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 would type in
SELECT MAX(Sales) FROM Store_Information

Result:
MAX(Sales)
$1500
$1500 represents the maximum value of all Sales entries: $1500, $250, $300, and $700.


MIN: Minimum of the column.

SQL uses the MIN function to find the maximum value in a column. The syntax for using the MIN function is,
SELECT MIN("column_name")
FROM "table_name"
For example, if we want to get the lowest sales from the following 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 would type in
SELECT MIN(Sales) FROM Store_Information

Result:
MIN(Sales)
$250
$250 represents the minimum value of all Sales entries: $1500, $250, $300, and $700.


SUM: Sum of the column.

The SUM function is used to calculate the total for a column. The syntax is,
SELECT SUM("column_name")
FROM "table_name"
For example, if we want to get the sum of all sales from the following 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 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.


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"
Let's illustrate using the following 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 want to find total sales for each store. To do so, 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 arithmetic 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 arithmetic operator.


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 (arithmetic 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
San Francisco	$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


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
San Francisco	$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. 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 SQL Joins section.


AS

In the SQL Alias section, we saw that the syntax for using table and column aliases is as follows:
SELECT "table_alias"."column_name1" "column_alias"
FROM "table_name" "table_alias"
The keyword AS is used to assign an alias to the column or a table. It is insert between the column name and the column alias or between the table name and the table alias. The syntax for using AS is as follows:
SELECT "table_alias"."column_name1" AS "column_alias"
FROM "table_name" AS "table_alias"
Let's take a look at the same example as we used in SQL Alias. Assume we have the following 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

To find total sales by store using AS as part of the table and column alias, we type in:
SELECT A1.store_name Store, SUM(A1.Sales) AS "Total Sales"
FROM Store_Information AS A1
GROUP BY A1.store_name

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


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 Stores_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

Table Geography

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

and we want to 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.
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 clause 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
San Francisco	$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 Geography 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.
Concatenate

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:
• MySQL: CONCAT()
• Oracle: CONCAT(), ||
• SQL Server: +
The syntax for CONCAT() is as follows:
CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.
Let's look at some examples. Assume we have the following table:


Table Geography

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

Example 1:

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';

Result:
'EastBoston'

Example 2:

Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';

Result:
'East Boston'

Example 3:

SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';

Result:
'East Boston'


Substring

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:
• MySQL: SUBSTR(), SUBSTRING()
• Oracle: SUBSTR()
• SQL Server: SUBSTRING()
The most frequent uses are as follows (we will use SUBSTR() here):
SUBSTR(str,pos): Select all characters from starting with position . Note that this syntax is not supported in SQL Server.
SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.
Assume we have the following table:

Table Geography

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

Example 1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

Result:
's Angeles'

Example 2:
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

Result:
'an D'


Trim

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:
• MySQL: TRIM(), RTRIM(), LTRIM()
• Oracle: RTRIM(), LTRIM()
• SQL Server: RTRIM(), LTRIM()

The syntax for these trim functions are:

TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.
LTRIM(str): Removes all white spaces from the beginning of the string.
RTRIM(str): Removes all white spaces at the end of the string.

Example 1:

SELECT TRIM(' Sample ');

Result:
'Sample'

Example 2:

SELECT LTRIM(' Sample ');

Result:
'Sample '

Example 3:
SELECT RTRIM(' Sample ');

Result:
' Sample'



Length

The Length function in SQL is used to get the length of a string. This function is called differently for the different databases:
• MySQL: LENGTH()
• Oracle: LENGTH()
• SQL Server: LEN()
The syntax for the Length function is as follows:
Length(str): Find the length of the string str.
Let's take a look at some examples. Assume we have the following table:

Table Geography

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

Example 1:

SELECT Length(store_name)
FROM Geography
WHERE store_name = 'Los Angeles';

Result:
11

Example 2:

SELECT region_name, Length(region_name)
FROM Geography;

Result:
region_name Length(region_name)
East 4
East 4
West 4
West 4


Replace

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:
Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.
Assume we have the following table:

Table Geography

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

If we apply the following Replace function:
SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

Result:
region_name
Eastern
Eastern
West
West


Index

Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need.
The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns. The syntax for creating a table index is shown in the CREATE INDEX section. Below we discuss some general strategies when building and using an index:
1. Build index on columns of integer type
Integers take less space to store, which means the query will be faster. If the column you want to build an index for is not of type integer, consider creating a surrogate integer key (or simply a surrogate column of type integer) which maps one-to-one to the column you want to build the index for.
2. Keep index as narrow as possible
Narrower indexes take less space, require less time to process, which in turn means the query will run faster.
3. Column order is important
For indexes covering multiple columns, the order of the columns in the index is important. The best practice is to use the column with the lowest cardinality first, and the column with the highest cardinality last. Recall cardinality means the number of distinct values for that column. So, if "SELECT DISTINCT (COLUMN1) FROM TABLE_NAME;" returns 5, that means the cardinality for COLUMN1 is 5.
4. Make sure the column you are building an index for is declared NOT NULL
This can decrease the size of the index, which in turn will speed up the query.


Create Index Statement
As mentioned in the Index overview page, a table index helps SQL statements run faster. The syntax for creating an index is:
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
Let's assume that we have the following table,
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
and we want to create an index on the column Last_Name, we would type in,
CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)
If we want to create an index on both City and Country, we would type in,
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)
There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects. It is also a good idea to provide information on which table and column(s) the index is used on.
Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax



View

A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple tables. It can also be built on top of another view. In the SQL Create View page, we will see how a view can be built.
Views offer the following advantages:
1. Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables.
2. Space savings: Views takes very little space to store, since they do not store actual data.
3. Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes.
Create View Statement
Views can be considered as virtual tables. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.
The syntax for creating a view is as follows:
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
"SQL Statement" can be any of the SQL statements we have discussed in this tutorial.
Let's use a simple example to illustrate. Say we have the following table:
TABLE Customer (First_Name char(50), Last_Name char(50), Address char(50),
City char(50), Country char(25), Birth_Date date)
and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,
CREATE VIEW V_Customer AS SELECT First_Name, Last_Name, Country
FROM Customer
Now we have a view called V_Customer with the following structure:
View V_Customer (First_Name char(50), Last_Name char(50), Country char(25))

We can also use a view to apply joins to two tables. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:




Table Stores_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

Table Geography

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

and we want to build a view that has sales by region information. We would issue the following SQL statement:
CREATE VIEW V_REGION_SALES
AS 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
This gives us a view, V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view, we type in,
SELECT * FROM V_REGION_SALES

Result:
REGION SALES
East $700
West $2050




Stored Procedure

Stored procedure by definition is a segment of code which contains declarative or procedural SQL statements. A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from client applications.
CREATE PROCEDURE Delete_Employee (@EmployeeId INT)

AS

BEGIN
DELETE FROM Employees
WHERE EmployeeId = @EmployeeId;
END
Calling a stored procedure

EXEC spName(parameter_value_list)

EXEC Delete_Employee @EmployeeId = 8


Inner Queries
Inner queries are use to return value to its parent query.
Table Employee
Id Name DepartmentId
1 Rousell 2
2 John 2
3 Stephen 1
Table Department
Id Description
1 Finance
2 Sales
3 Purchase

Select * from Employee where DepartmentId = (select Id from Department where Description = ‘Sales’)
It will Return

1 Rousell 2
2 John 2