Basics of the SELECT Statement in MySQL

Implementing Database in MySQL – Part 11

Foreword: In this part of the series, I explain the main clauses of the SELECT Statement in MySQL.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 11 of my series, Implementing Database in MySQL. In this part of the series, I explain the main clauses of the SELECT Statement in MySQL. I assume you have read the previous parts of the series before reaching here; this is the continuation.

Recall
In the previous part of the series, you learned that in order to select all the columns from a particular table, you would type:

SELECT * FROM table-name;

The asterisk means all the columns. Before we carry on, you will start and connect to the server and then choose the database.

- Start and connect to the server and choose the wholesale database with the following commands:

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

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, Quantity, CostPrice
FROM Products;

Note that there is no comma after the last column name, before FROM in the statement. Only the columns you choose, which are ProductName, Category, Quantity, and CostPrice have been displayed.

Joining Tables
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:

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

Selecting Rows
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. Quantity,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 other operators and the brackets, here. Use brackets to insist that a particular operation 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 MySQL 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:

SELECT *
FROM Products
WHERE (Products.CostPrice >=2) AND (Products.CostPrice <=20);

Aggregate Functions
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, type and execute the following SQL statement.

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:

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;

Grouping
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:

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.

The ORDER BY Clause
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:

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:

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, close the connection, and quit the tool, with the following commands, as usual:

    CLOSE
    QUIT

Wow, we have seen it. I hope you find it interesting. Time to take a break. Rendezvous in the next part of the series.

Chrys

Broad Network

Related Articles

Basics of the SELECT Statement in MySQL

Implementing Database in MySQL – Part 11

Introduction

Related Links

Comments