Implementing Computed Values and Sub Queries

Implementing Database in Sybase - Part 12

Division 4

Forward: In this part of the series, we see how to implement computed values and we learn sub-queries is Sybase SQL Anywhere 12.

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 12 of my series, Implementing Database in Sybase. This series is part of the fourth division of my larger series, Database. I assume you have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to implement computed values and we learn sub-queries is Sybase SQL Anywhere 12.

I said sometime ago that in the design phase of a database, you should not worry about computed values. That is alright. Now in the implementation query phase you can type the expression for computed values. The query phase is when you are retrieving data. You can do it now with the DBMS or wait and do it when producing the forms and reports for the database. When you have this choice, it is advisable to do it with the DBMS. We shall do it now, with the Sybase SQL Anywhere 12 (DBMS).

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.

Example
Consider the following SaleDetails table:

    SaleDetails(SaleID, ProductID, Qty, SoldPrice)

Qty is the quantity of the particular product sold. When retrieving this table it is nice to show to the user the Total value of the particular product sold. This is done when you are querying the database with the SELECT statement. For each row, this Total is given by,

    Total = Qty * SoldPrice

This is easily done in the SELECT statement at the level of the table-columns. The multiplication sign in the computer is * and not X. The SQL statement to do this for the above table is:

SELECT ProductID, Qty, SoldPrice, Qty * SoldPrice AS Total
FROM SaleDetails;

Note the use of the AS operator. Total is a name of your choice. The result will show a column of ProductIDs, then a column of Quantities sold, then a column of unit price sold and then a column of Totals.

This result would have a small problem in the sense that you would see the ProductIDs (numbers) and not the name of the product. In order to have the name of each product, you have to JOIN the SaleDetails table with the Products table. The SQL SELECT Statement would then be:

SELECT Products.ProductName, SaleDetails.Qty, SaleDetails.SoldPrice, SaleDetails.Qty * SaleDetails.SoldPrice AS Total
FROM SaleDetails
JOIN Products
ON SaleDetails.ProductID = Products.ProductID;

- Start the server, start the database and connect to the database using Interactive SQL with the following commands, as usual:

cd c:
dbeng12 -n wholesalesrv c:\wholesale\wholesale.db
dbisql -c "server=wholesalesrv;DBF=c:\sholesale\wholesale.db;UID=DBA;PWD=sql"

The table we had for SaleDetails is,

    SaleDetails(SaleID, ProductID, SoldPrice)

without Qty and not

    SaleDetails(SaleID, ProductID, Qty, SoldPrice)

So we have to drop (remove) the current SaleDetails table, create a new table that includes, Qty, feed it with data, before we execute the SELECT statement.

- Type and execute the following SQL statements (erasing any previously typed statement):

DROP TABLE SaleDetails;

CREATE TABLE SaleDetails
(
    SaleID SMALLINT,
    ProductID SMALLINT,
    Qty INTEGER,
    SoldPrice NUMERIC(19,2),

    PRIMARY KEY (SaleID, ProductID),
    FOREIGN KEY (SaleID) REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE,
);

INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (1, 1, 9, 0.75);
INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (1, 3, 12, 3);
INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (1, 5, 8, 1.25);
INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (2, 7, 3, 21);
INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (2, 9, 5, 3);

- Now read and type the following query (erase any previously typed statement):

SELECT Products.ProductName, SaleDetails.Qty, SaleDetails.SoldPrice, SaleDetails.Qty * SaleDetails.SoldPrice AS Total
FROM SaleDetails
JOIN Products
ON SaleDetails.ProductID = Products.ProductID;

The result should be as expected with the new column, Total.

Let us leave it at that for computed values. Use a similar reasoning for your different problems.

Sub Query
The basic syntax for a query 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

A sub query is the same thing but it would fit in the select-list or search-condition for WHERE or HAVING clause. In the example that follows, I will put a sub-query in the select list.

The SalesDetails table now is:

    SaleDetails(SaleID, ProductID, Qty, SoldPrice)

The Sales table is:

    Sales(SaleID, DateAndTime, EmployeeID, CustomerID)

For each SaleID there are a number of products, and those products are in the SaleDetail tables. The sum (column) of the products sold for a particular sale can be got from the SaleDetails table using a query and the SUM aggregate function. This query will now form a sub-query for the main query on the Sales table to show the amount sold in each sale.

- Read , type and execute the following query, which shows the amount sold for each sale:

SELECT SaleID, DateAndTime,
    (SELECT SUM(Qty * SoldPrice) AS Amount
    FROM SaleDetails
    WHERE SaleID = Sales.SaleID
    GROUP BY SaleID)
FROM Sales;

The result is as expected. The result also shows that for some sales, no product was sold, which is logically correct based on the data fed into the SaleDetails table. The SaleDetails table has only two SaleIDs and not five SaleIDs that are in the Sale table.

The sub-query is in brackets, and it does not end in a semicolon. The WHERE clause in the sub-query makes the aggregate sum to be calculated for the particular SaleID of the parent Sale query.

Now, drop the connection, stop the database and stop the server, with the following command as usual:

    dbstop wholesalesrv

We can stop here. There is more to implementing a database and retrieving data. However I have given you the basics in this division. We continue in the next part.

Chrys

Broad Network

Related Articles

Implementing Computed Values and Sub Queries

Implementing Database in Sybase - Part 12

Division 4

Introduction

Related Courses

Comments