Implementing Computed Values and Sub Queries in MySQL
Implementing Database in MySQL – Part 12
Division 4
Forward: In this part of the series, we see how to implement computed values and we learn sub-queries in MySQL. Remember, we are dealing with MySQL 5.1.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
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 MySQL 5.1(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, Quantity, SoldPrice)
Quantity 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 = Quantity * 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, Quantity, SoldPrice, Quantity * 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.Quantity, SaleDetails.SoldPrice, SaleDetails.Quantity * SaleDetails.SoldPrice AS Total
FROM SaleDetails
JOIN Products
ON SaleDetails.ProductID = Products.ProductID;
- Start and connect to the server and choose the wholesale database with the following commands:
cd c:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -u root -p
Enter password: sql
USE wholesale;
- Now read and type the following query:
SELECT Products.ProductName, SaleDetails.Quantity, SaleDetails.SoldPrice, SaleDetails.Quantity * 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 similar problems.
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 It may be possible to put it in the search-condition for WHERE or HAVING clause. In the example that follows, I put a sub-query in the select list.
The SalesDetails table is:
SaleDetails(SaleID, ProductID, Quantity, 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(Quantity * 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:
QUIT
We can stop here. There is more to implementing a database and retrieving data. However I have given you the basics in this series. We continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course