The SELECT Statement Basics in Sybase
Implementing Database in Sybase – Part 11
Forward: In this part of the series, we look at the main clauses of the SELECT Statement in Sybase SQL Anywhere 12 database. Retrieving data means querying the database.
By: Chrysanthus Date Published: 27 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Recall
In the previous part of the series, we learned that in order to select all the columns from a particular table, we would type:
SELECT * FROM table-name;
The asterisk means all the columns. Before we carry on, you will start the server, database and Interactive SQL.
- Use the following commands as usual to start:
cd c:\
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"
Selecting Particular Columns from one Table
To select only particular columns for a table you would type the SQL statement:
SELECT column-names
FROM table-name;
- Execute the following statement:
SELECT ProductName, Category, Number, CostPrice
FROM Products;
Note that there is no comma after the last column name and before FROM in the statement.
Remember that in the Saledetails and OrderDetails table you have the product IDs and not the product names. How can you know the products that have been bought and sold? Well, you have to join the tables. You use the word, JOIN, called the join operator. This operator joins the tables through the foreign keys and primary keys. The syntax is:
SELECT table-names.column-names
FROM table1
JOIN table2
ON table.key = table.key
JOIN table3
ON table.key = table.key …;
You can have two or more tables joined. Notice the ON clause. It is typed immediately after the join, giving the exact keys involved. You begin a column name with its table name separated by a dot; this is because you are using more than one table.
- Read and execute the following SQL Statement (erase any previously typed statement first):
SELECT Products.ProductName, OrderDetails.BoughtPrice, SaleDetails.SoldPrice
FROM Products
JOIN OrderDetails
ON OrderDetails.ProductID = Products.ProductID
JOIN SaleDetails
ON SaleDetails.ProductID = Products.ProductID;
You can see the name of an item, how much it was bought for and how much it was sold for. Note, when joining tables the way I have shown, if an item does not appear in all three tables, it will not appear in the query results
We have learned how to select particular columns; what about selecting particular rows. The complete syntax of the SELECT Statement has many clauses. One of them is the WHERE clause. If you want to select only the row of the Products table WHERE the ProductID is 9, you would type
SELECT Products.ProductID, Products.ProductName, Products.Category, Products.Number, Products.CostPrice, Products.SellingPrice
FROM Products
WHERE ProductID = 9;
You result will show the single row from the Products table. If you do not want all the columns to appear in the result, do not type the columns you do not want.
If you want from the Products table, only the rows WHERE the cost price is greater than or equal to 2 AND less than or equal to 20, you would type
SELECT *
FROM Products
WHERE (Products.CostPrice >= 2) AND (Products.CostPrice <= 20);
Note the use of AND, the operators and the brackets here. Use brackets to insist that a particular operation should be done first before the result is combined with another operator. In the above, the operations in the bracket are performed first and then the results from the two brackets are combined with the AND operator. You have to use brackets to insist on the order in which you want the complete condition to be carried out. If you do not use brackets, the condition may not be performed in the order you want. The syntax for the WHERE clause is:
WHERE search-condition
One of the previous parts of this series in this division is titled, “Search Conditions in Sybase SQL Statements”. When you have a particular WHERE condition to write, go there to determine what operators to use.
- Read and Execute the following SQL statement (erase any previously typed statement first):
SELECT *
FROM Products
WHERE (Products.CostPrice >=2) AND (Products.CostPrice <=20);
An aggregate function is a function you can apply to the values of a column. I now describe the most common aggregate functions.
The Average Function
The average function abbreviated AVG is used to find the average of the cell values in a column. Cell with NULL values are not included. In simple terms the syntax is,
AVG(numeric-expression)
Consider numeric-expression for now as the name of a column.
The Count Function
The count function is used to count the number of cells in a column. In simple terms the syntax is:
COUNT(expression)
This syntax will count NULL values as well. Consider, expression, here as a table column name.
The Maximum Function
The maximum function abbreviated, MAX returns the maximum value in a column. In simple terms the syntax is:
MAX(expression)
For now consider expression as a column name.
The Minimum Function
The Minimum Function abbreviated, MIN returns the minimum value in a column. In simple terms its syntax is:
MIN(expression)
Consider, expression, as column name.
The SUM Function
The SUM function returns the sum of cell values in a column. In simple terms the syntax is:
SUM(expression)
Consider, expression, as column name.
The SELECT Statement and Aggregate Functions
An aggregate function can be type along side the column headings just after the words SELECT. It can also be typed in the HAVING clause (see later) or in the ORDER BY clause (see later). For the first option, the syntax is,
SELECT function AS alias-name
Note the use of the AS operator. Alias-name is a name of your choice that you give. Let us now use all the above functions with the Products table.
- Read and type the following SQL statement in Interactive SQL (erase any previously typed statement first).
SELECT AVG(Products.CostPrice) AS AverageCostPrice
FROM Products;
In the result you should see a single column single cell table with the heading AverageCostPrice. The cell should have the average of all the cost prices of the CostPrice column in the Products table.
- Read and try the following SQL statements, one after the other (erasing any previously typed statement):
SELECT COUNT(Products.ProductName) AS NumberOfProducts
FROM Products;
SELECT MAX(Products.SellingPrice) AS MaximumSellPrice
FROM Products;
SELECT MIN(Products.SellingPrice) AS MinimumSellPrice
FROM Products;
SELECT SUM(Products.CostPrice) AS TotalCostPrice
FROM Products;
You would normally not be executing an aggregate function over the entire column of a table as done above. Rows of many tables exist in groups. In the products table, a group is a category. In a category such as Household, you have a number of products (rows); in another category such as Entertainment, you have a number of products (a number of rows), and so on. So rows in many tables exist in groups. You normally would be interested in the result of an aggregate function in groups of rows. For example, you would want to know the total cost of products for each category in the Products table.
For this you have to use the GROUP BY clause in the SELECT statement. The parameter of the GROUP BY clause is a column name such as, Category, in the Products table that has the different group values (Household, Entertainment, etc.) in its cells.
- Read and try the following (erase any previously typed statement first):
SELECT Products.Category, SUM(Products.CostPrice) AS GroupCostTotals
FROM Products
GROUP BY Products.Category;
The results should show two columns with titles, Category and GroupCostTotals. With the GROUP BY clause, it would not make sense to display a column such as the ProductName column. You can only display columns such as Category, which have one value per group. You can also display derived columns such as GroupCostTotals.
The word, Total, here means you are summing the values in a column. Elsewhere the word, Total, would mean something like, Quantity of Products sold times (X) Unit price, in a row.
The HAVING Clause
Now that you can have a result table of groups, you should be able to display selected groups and not all the groups. You use the HAVING clause for this. The WHERE clause is used to select rows from the entire table. The HAVING clause works in a similar way, but it is to select groups and not rows, after the grouping has been done. The parameter for the HAVING clause is also a search-condition. If you use the WHERE clause and the HAVING clause in the same SELECT statement, then the WHERE clause will select rows from the entire table as expected and the HAVING clause will select groups after grouping.
- Read and try the following code, which displays groups whose sub totals are greater than 5.
SELECT Products.Category, SUM(Products.CostPrice) AS GroupCostTotals
FROM Products
GROUP BY Products.Category
HAVING GroupCostTotals > 5;
In the result, all the rows have subtotals greater than 5 because of the HAVING clause and its condition (there is no need for brackets in the condition because it has just one operation, which is >). All SQL statements must end with a semicolon. Note how the HAVING clause is acting on groups and not on individual rows.
Whether your query result is displaying individual rows or it is displaying groups (of rows), you can order the result. In the case of the Products table, you can order the individual rows by product name (a column name) alphabetically. Still with the Products table, you can order the group results by Category (a column name) alphabetically. Let us look at some examples.
In simple terms, the syntax for the ORDER BY clause is:
ORDER BY expression [ASC | DESC]
ASC means ascending alphabetically, numerically or both. DESC is the opposite of ASC.
- Read and execute the following SQL statement that orders the individual rows (erase any previously typed statement):
SELECT *
FROM Products
ORDER BY ProductName ASC;
Note that in the result, the rows of the table have been rearranged such that in the ProductName column the values are put alphabetically and ascending. When you are dealing with only one table in the SELECT statement, you do not need to precede a column name with a table name and the dot.
- Read and execute the following SQL statement that orders the groups of the Products table by category (erase any previously typed statement):
SELECT Products.Category, SUM(Products.CostPrice) AS GroupCostTotals
FROM Products
GROUP BY Products.Category
HAVING GroupCostTotals > 5
ORDER BY Products.Category ASC;
Note that the groups have been arranged such that there is alphabetical ascending order in the Category column. Note that ORDER is a reserved word, so you should not have a table whose name is Order. Use Orders (plural) for a table name instead of Order (singular).
Summary of the SELECT Statement Basics
A summary of the SELECT Statement Basics is:
SELECT select-list
FROM from-expression
JOIN table-columns with ON operators
WHERE search-condition
GROUP BY group-by-expression
HAVING search condition
ORDER BY expression
The order of these clauses has to be respected in your SQL statement. Your SQL statement should have at least the first two lines. The rest of the clauses are optional, but for whatever reason any is included, the order should be respected.
Now, drop the connection, stop the database and stop the server, with the following command as usual:
dbstop wholesalesrv
Wow, we have seen it. I hope you find it interesting. Time to take a break. Rendezvous in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT