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.

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.

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.

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:

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;

Note the current vendor name, and type:

Select VendId, Namefrom TrnVendor where VendId = ‘TV001’;

To update the vendor name, type:

Update TrnVendorSet Name = ‘Genie R Corp.’Where VendId = ‘TV001’;

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

Select VendId, Namefrom TrnVendor where VendId = ‘TV001’;

To roll back the change, type:

Rollback work;

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

Deleting Rows

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.

SQL Dropping a View

SQL DROP VIEW Syntax

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