SQL Query Syntax Tutorial for Beginner

SQL is a standard language for accessing databases.

A database query is a piece of code (a query) that is sent to a database in order to get information back from the database. It is used as the way of retriving the information from database
A database "query" is basically a "question" that you ask the database. The results of the query is the information that is returned by the database management system. Queries are usually constructed using SQL (structured query language) which resembles a high-level programming language.
The term 'query' means to search, to question, or to find. When you query a database, your searching for information in the database. Different query languages exist for different type of databases. MS Access uses SQL, which stands for Structured Query Language. MS Access contains Tables, Forms, and Queries. The Forms are used to enter or display the data, the Tables are where the data is saved, and the queries are used to search for specific data.

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.
CREATE DATABASE database_name

For example:
CREATE DATABASE my_db

Creating Tables
You must create your tables before you can enter data into them. Use the Create Table command.
Syntax:
Create table tablename using filename
(fieldname fieldtype(length),
fieldname fieldtype(length),
fieldname fieldtype(length));

For example:
Create table TrnAPDoc using 'TAPDoc.dat'
( VendId char(10),
RefNbr char(10),
DocDate date(4),
OrigDocAmt float(8),
CuryDocBal float(8),
OpenDoc logical(2));

Importing Data

You can import data into SQLScope from another data source by using the Insert command. Before completing the following exercises, you must import data into the tables you created. The example given in this section will step you through the process.
Syntax:
Insert into tablename
(fieldname, fieldname, fieldname)
Values
(@fieldname, @fieldname, @fieldname);

For example:
Insert into TrnVendor
(Vendid, Name, Add1, City, State, CurrBal, ExpAcct )
Values
(@Vendid, @Name, @Add1, @City, @State, @CurrBal, @ExpAcct)

Retrieving All Data

Select statements are used to retrieve data from  SQL tables. The Select statement illustrated below retrieves all of the columns and rows  from the named table.
Syntax:
Select *
from tablename;

For example:
Select *
from TrnVendor;

Results:

VendId	Name	Add1	City	State		CurrBal	ExpAcct
TV001	Wet Off Towels	66 Keep Dry Way	Toledo	OH	1.31300E+001		7230
TV002	The Games All Here	85 Olympic Drive	Warren	NY	4.53170E+002		4110

Retrieving a Single Column

You can use SQL to retrieve a single column of data.
Syntax:
Select fieldname
from tablename;

For example:
Retrieve the vendor name from each record:
Select Name
from TrnVendor;

Results:

Name

Wet Off Towels

The Games All Here

Retrieving Multiple Columns

You can use SQL to retrieve multiple columns.
Syntax:
Select fieldname, fieldname, fieldname
from tablename;

For example:
Retrieve the vendor ID, name, and current balance for each record:
Select VendId, Name, CurrBal
from TrnVendor;

Results:

VendId	Name	CurrBal
TV001	Wet Off Towels	1.31300E+001
TV002	The Games All Here	4.53170E+002

Formatting Numbers

By default, Scalable SQL displays numbers using scientific notation. To change the format, you must add a mask.
Syntax:
Select fieldname<mask>, fieldname<mask>, fieldname<mask>
from  tablename;

Number Masks
9	Display number.
Z	Display number, drop leading zeros.
+	Display a plus in front of positive numbers. Display a negative in front of negative numbers.
-	Display a negative in front of negative numbers. Display nothing in front of positive numbers.
( )	Display negative numbers enclosed in parentheses.
.	Display decimal point.
$	Display dollar sign.
,	Use comma to separate thousands.

For example:
Select VendId, Name, CurrBal [($Z,ZZZ,ZZZ.99)]
from TrnVendor;

Results:

VendId	Name	CurrBal
TV001	Wet Off Towels	$13.13
TV007	Spot Out	$1,606.54
TV010	The Soda Factory	$0.00
TV011	The Freelance	($109.23)

Ordering Rows

Data is stored in Scalable SQL in no particular sequence. If you want to see your data displayed in sequence, you must add an Order By clause to your Select statement.
Syntax:
Select */fieldname<mask> ...
from tablename
order by fieldname;

For example:
Retrieve the city, vendor Id, and name from the TrnVendor table. Order your data by city:
Select City, VendId, Name
from TrnVendor
order by City;

Results:

City	VendId	Name
Bayshore	TV019	Bayshore Consulting
Burlington	TV020	BayCon Group
Chicago	TV003	BedMakers Linen

Displaying Rows in Descending Order

If you would like to see fields displayed in descending order, follow the field name with "desc" in the Order By clause.
Syntax:
Select */fieldname ...
from tablename
order by fieldname <desc> ...

For example:
Retrieve the city, vendor ID, and name from the TrnVendor table. Order your data by city in descending order:
Select City, VendId, Name 
from TrnVendor
order by City desc;

Results

City	VendId	Name
Youngstown	TV018	Computer Bytes
Warren	TV002	The Games All Here
Toledo	TV027	Narrow Nest

Ordering Multiple Columns

When ordering your data, you can have multiple sort levels. For example, you can order your data by city and then by name within the city.
Syntax:
Select fieldname, fieldname, fieldname
from tablename
order by fieldname <desc>, fieldname <desc>, fieldname <desc>

For example:
Retrieve the city, name, and vendor ID from the TrnVendor table. Order your data by city and then by name within city:
Select City, Name, VendId
from TrnVendor
order by City, Name;

Results

City	Name	VendId
Bayshore	Bayshore Consulting	TV019
Burlington	BayCon Group	TV020
Chicago	BedMakers Linen	TV003
Chicago	Distant Horizons	TV028
Chicago	Make Shift Tilts	TV030

Retrieving Specific Rows

So far, you have been retrieving all of the rows in the table. You can, however, specify which rows you wish to retrieve. For example, you could retrieve only those vendors who are in Chicago.
Syntax:
Select */fieldname ...
from tablename
where fieldname =/!=/<>/>/>=/</<=/in/not in/between/not between/begins with/contains/not contains/ is null/is not null/like/not/like value
order by fieldname <desc>.

Logical Operators
=	Equal to
!= or <>	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
in	Equal to any item in a list
not in	Not equal to any item in a list
between	Between two values, greater than or equal to one and less than or equal to the other
not between	Not between two values
begins with	Begins with specified value
contains	Contains specified value
not contains	Does not contain specified value
is null	Is blank
is not null	Is not blank
like	Like a specified pattern. % means any series of characters. _ means any single character.
not like	Not like a specified pattern. % means any series of characters. _ means many single character.

For example:
Retrieve all vendors located in Chicago.
Select City, Name, VendId
from TrnVendor
where City = 'Chicago';'

Results:

City	Name	VendId
Chicago	BedMakers Linen	TV003
Chicago	Music Maker	TV016

For example 2:
Retrieve all vendors who are not located in Chicago. Order the results by city.
Select City, Name, VendId
from TrnVendor
where City <> 'Chicago'
order by City;

Results:

City	Name	VendId
Bayshore	Bayshore Consulting	TV019
Burlington	BayCon Group	TV020
Dallas	Cooperative Operatives	TV021
Dallas	Clampett Oil	TV026

For example 3:
Retrieve all vendors in Mercer, New York, or Park Ridge.
Select City, Name, VendId
from TrnVendor
where City in ( 'Mercer', 'New York', 'Park Ridge')
order by City;

Results:

City	Name	VendId
Mercer	Bed Room Furniture, Inc.	TV004
Mercer	The Freelance	TV011
New York	Paper People	TV012
Park Ridge	Mosquito No Bite	TV006
Park Ridge	No Waste Disposal	TV015

For example 4:
Retrieve all vendors whose names begin with "Co."
Select Name, City, VendId
from TrnVendor
where Name begins with 'Co'
order by Name;

Results:

Name	City	VendId
Computer Bytes	Youngstown	TV018
Cooperative Operatives	Dallas	TV021

For example 5:
Retrieve all vendors whose city ends with the letters "do."
Select Name, City, VendId
from TrnVendor
where City like '%do'
order by City;

Results:

Name	City	VendId
The Soda Factory	Orlando	TV010
Against the Tide	Orlando	TV025
Wet Off Towels	Toledo	TV001

For example 6:
Retrieve all vendors with a current balance between 500 and 1000 dollars.
Select VendId, Name, CurrBal [ZZ,ZZZ.99]
from TrnVendor
where CurrBal between 500 and 1000
order by CurrBal;

Results:

VendId	Name	CurrBal
TV003	BedMakers Linen	500
TV012	Paper People	617
TV017	Food Four	642.98

Multiple Conditions

You can add multiple criteria to your Where clauses by using "and" or "or."
Syntax:
Select */fieldname<mask> ... 
from tablename ... 
where fieldname =/!=/<> ... value
and/or
fieldname =/!=/<>... value
and/or
fieldname =/!=/<> ... value 
order by fieldname <desc> ...

For example:
Retrieve all vendors who are located in Chicago and have a current balance over 1,500 dollars. Order the results by current balance.
Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = 'Chicago'
and CurrBal > 1500
order by CurrBal;

Results:

Name	City	CurrBal
Make Shift Tilts	Chicago	1,793.23
Music Maker	Chicago	4,109.14

For example 2:
Retrieve all vendors whose expense account is 4110 or who have a current balance over 1200. The vendor must be located in Chicago.
Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from Vendor
where City = 'Chicago'
and (ExpAcct = '4110'
or CurrBal > 1200) 
order by City, ExpAcct;

Results:

City	CurrBal	ExpAcct
Chicago	0.00	4030
Chicago	4,135.24	4110
Chicago	1,309.95	4110
Chicago	100.00	6010
Chicago	500.00	6040
Chicago	47.00	7010
Chicago	106.00	7190

Joining Tables

So far, you have worked exclusively with a single table -- the TrnVendor table. You might have noticed that each module within Solomon IV consists of several tables. Within the Accounts Payable module, you will find the following tables: APAdjust, APDoc, APHist, APRefNbr, APSetup, APTran, and Vendor. Each table contains specific information. At times, you might need to join two or more tables to retrieve the information you need. For example, if you look at the TrnAPDoc table, you will see that it contains the vendor ID; however, it does not contain the vendor name. If, when retrieving information from the APDoc table, you want to see the vendor name, you have to join the TrnVendor table to the TrnAPDoc table.

Select */tablename.fieldname<mask> ...
from tablename <alias>, tablename <alias>, tablename <alias>
where tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
order by fieldname <desc>...

For example:
Retrieve the vendor ID, the vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables.
Select TrnAPDoc.VendId, TrnVendor.Name,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId;

Results:

TrnAPDoc.VendId	TrnVendor.Name	TrnAPDoc.OrigDocAmt
TV020	BayCon Group	542.98
TV019	Bayshore Consulting	237.60
TV018	Computer Bytes	55.50

For example 2:
Retrieve the vendor ID, vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables, using a table alias.
Select a.VendId, b.Name,
a.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc a, TrnVendor b
where a.VendId = b.VendId;

Results:

a.VendId	b.Name	a.OrigDocAmt
TV020	BayCon Group	542.98
TV019	Bayshore Consulting	237.60
TV018	Computer Bytes	55.50

For example 3:
Retrieve the vendor ID, vendor name, reference number, and original document amount from the APDoc and Vendor tables for Vendor V00104. Order the results by RefNbr.
Select TrnAPDoc.VendId, TrnVendor.Name, TrnAPDoc.RefNbr,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId
and
TrnAPdoc.VendId = 'TV004'
order by TrnAPDoc.RefNbr;

Results:

TrnAPDoc.VendId	TrnVendor.Name	TrnAPDoc.RefNbr	TrnAPDoc.OrigDocAmt
TV004	Bed Room Furniture, Inc.	000222	55.50
TV004	Bed Room Furniture, Inc.	000551	3600.00

Updating Tables

So far, you have looked at several different ways to retrieve and review your data. In this section, you will learn how to update your data. In the following two sections, you will learn about deleting and inserting rows. When you update, delete, and insert, you change the data -- you should perform these operations very cautiously. Before performing any of these operations on a production database, make sure your data is backed up and use the Start Transaction command. If you use the Start Transaction command, all of your changes are temporary until you commit your work and can be rolled back . If you have issued the Start Transaction command, you can undo your changes simply by typing "rollback work."

Syntax:
Start transaction;
Update tablename
set fieldname = value
where fieldname = value;
Rollback work;
Commit work;

Explanation:

• Issue a Start Transaction command before updating your table. This will allow you to roll back the changes, if necessary. If you do not issue a Start Transaction command, you will not be able the roll back your work.
• If you find that you have updated a row in error, execute the Rollback Work command.
• When you are satisfied with your changes, issue the Commit Work command.
• Use a Where clause to specify which rows will be updated. If you do not include a Where clause, all rows will be updated.
• Remember to end each command with a semicolon.

Example:

1. Change the name of vendor "TV001" to Genie R. Corp., and then roll back the change. Then change the name of vendor "TV001" to Vanix and commit your work. To start the transaction, type:

Start transaction;

2. Note the current vendor name, and type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

3. To update the vendor name, type:

Update TrnVendor
Set Name = 'Genie R Corp.'
Where VendId = 'TV001';

4. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

5. To roll back the change, type:

Rollback work;

6. To check the vendor name to see that it has reverted to the original, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

7. To update the vendor name, type:

Update TrnVendor
Set Name = 'Vanix'
Where VendId = 'TV001';

8. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

9. To commit the change, type:

Commit work;

Deleting Rows

You can use Scalable SQL to delete rows of data.
Syntax:
Delete from tablename
where fieldname =/<>/ ... value
and/or ... 
fieldname =/<>/ ... value
and/or
fieldname =/<>/ ... value

Explanation:

• If you do not include a Where clause, all of the rows in the table will be deleted.
• Every table has a primary key -- a field or combination of fields that uniquely identify each row in the table. VendId is the primary key for the vendor table. Each vendor is uniquely identified by the vendor Id. RefNbr is the primary key for APDoc.
• If you want to delete a single row of data, you can refer to the row in the Where clause by using the primary key.
• When deleting data, use the Start Transaction command so that any errors can be rolled back.
• Use the Rollback Work command to undo changes.
• Use the Commit Work command to finalize changes.

Examples:
Delete vendor TV011 by executing the following commands.

1. To view the record, type:

Select *
from TrnVendor
where VendId = 'TV011';

2. To start the transaction, type:

Start transaction;

3. To delete the record, type:

Delete from TrnVendor
where VendId = 'TV011 ';

4. To check to make sure the records have been deleted, type:

Select *
from TrnVendor
where VendId = 'TV011';

5. To roll back the deletion, type:

Rollback work;
Delete all vendors with a current balance over 2000 dollars.

1. To view the records, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

2. To start the transaction, type:

Start transaction;

3. To delete the records, type:

Delete from TrnVendor
where CurrBal > 2000;

4. To check to make sure the records have been deleted, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

5. To roll back the deletion, type:

Rollback work;

Inserting Rows

You can insert new rows into a table by using Scalable SQL.
Syntax:
Insert into tablename ( fieldname, fieldname, fieldname)
values ( value, value, value);

Explanation:

• When inserting data, use the Start Transaction command so that any errors can be rolled back.
• You must specify the values to be inserted.
• When performing an insert, enclose character values in single quotes.
• Do not enclose numeric values in single quotes.
• Use the Rollback Work command to undo changes.
• Use the Commit Work command to finalize changes.

Example:
Insert the following vendor into the TrnVendor table.
Vendor Id: TV055
Name: Party Games
Address: PO Box 136
City: Chicago
State: IL
Current Balance: 2498.62

1. To start the transaction, type:

Start transaction;

2. To insert the records, type:

Insert into TrnVendor (VendId, Name, Addr1, City, State, 
CurrBal )
values ( 'TV055', 'Party Games', 'PO Box 136', 'Chicago', 'IL', 2498.62);

3. To view the record, type:

Select VendId, Name, Addr1, City, State, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where VendId = 'TV055';

4. To roll back your work, type:

Rollback work;

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
For example:
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

In MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

To DROP a PRIMARY KEY Constraint

In SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

In MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

In SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

In MySQL:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
In SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

In MYSQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
SELECT DISTINCT column_name(s)
FROM table_name
SELECT DISTINCT Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:
SELECT DISTINCT City FROM Persons

The result-set will look like this:

City

Sandnes

Stavanger

SQL VIEWS
A view is a virtual table.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the view above as follows:
SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:
SELECT * FROM [Products Above Average Price]

Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

---

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name
SQL Functions
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name
SQL AVG() Example
We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find the average value of the "OrderPrice" fields.
We use the following SQL statement:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage

950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.
We use the following SQL statement:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The result-set will look like this:

Customer

Hansen

Nilsen

Jensen

SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria.

---

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

---

SQL COUNT(column_name) Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to count the number of orders from "Customer Nilsen".
We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen

2

---

SQL COUNT(*) Example

If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result-set will look like this:

NumberOfOrders

6

which is the total number of rows in the table.

---

SQL COUNT(DISTINCT column_name) Example

Now we want to count the number of unique customers in the "Orders" table.
We use the following SQL statement:
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers

3

which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
SQL GROUP BY Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result-set will look like this:

Customer	SUM(OrderPrice)
Hansen	2000
Nilsen	1700
Jensen	2000

Nice! Isn't it? :)

Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders

The result-set will look like this:

Customer	SUM(OrderPrice)
Hansen	5700
Nilsen	5700
Hansen	5700
Hansen	5700
Jensen	5700
Nilsen	5700

The result-set above is not what we wanted.

Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for each row in the "Orders" table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.

---

GROUP BY More Than One Column
We can also use the GROUP BY statement on more than one column, like this:
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

---

SQL HAVING Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer	SUM(OrderPrice)
Nilsen	1700

Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer	SUM(OrderPrice)
Hansen	2000
Jensen	2000