SQL Reporting  «Prev  Next»
Lesson 2Project review
Objective Prepare for the course project.

Complex Queries for use against a SQL Engine

When you create more complex queries for use against a SQL engine, you will find yourself using the different clauses and techniques we have outlined throughout this course. Just pulling rows from a table, as the case is with a SELECT * FROM... approach, is not going to be enough as you need to start working from larger, more diverse databases and tables. That is where the different techniques come into play. You will need to consider the results to determine what the logical breaks are in the information. Look for ways to group things, ways to eliminate some information from the results set, and so on. Each of the sidebars below contains a quick review of the main topics we have covered in this course. Feel free to skip them if you remember the general concepts, and refer back to them if you need some guidance during the course project.

The power of SQLAlchemy to construct complex queries becomes clear when we compare the previous code to the SQL generated:
SELECT tf_user.first_name AS tf_user_first_name,
tf_user.last_name AS tf_user_last_name,
tf_user.created AS tf_user_created,
tf_user.user_name AS tf_user_user_name,
tf_user.password AS tf_user_password,
tf_user.email_address AS tf_user_email_address,
tf_user.id AS tf_user_id
FROM tf_user
JOIN user_group ON tf_user.id = user_group.user_id
JOIN tf_group ON tf_group.id = user_group.group_id
JOIN group_permission ON tf_group.id = group_permission.group_id
JOIN tf_permission ON tf_permission.id =
... group_permission.permission_id
WHERE tf_permission.permission_name = ? ORDER BY tf_user.oid
SQLAlchemy

Basic Query Construction

SQLAlchemy makes simple SQL queries easy to express, while also enabling the construction of quite complex queries in a straightforward manner. This section describes the basic building blocks of query construction in SQLAlchemy.
Select( ) function versus the select( ) Method: Like the DML statements INSERT, UPDATE, and DELETE, SELECT statements can be generated using either a function or a Table method. Unlike the DML statements, however, there is a minor difference in functionality between the select( ) function and the Table.select( ) method. The select( ) function requires you to specify which columns you want in your result set. So, to select one column from the prod uct_table shown previously, you could use the select( ) function:
>>> stmt = select([product_table.c.sku])
>>> for row in stmt.execute():
... print row
...
(u'123',)
(u'456',)
(u'789',)

To select all columns from the product_table, you would use the Table.select( ) method:
>>> stmt = product_table.select()
>>> for row in stmt.execute():
... print row
...
(u'123', Decimal("12.34"))
(u'456', Decimal("22.12"))
(u'789', Decimal("41.44"))

To achieve the same result using the select( ) function, simply provide the table in lieu of columns:
>>> stmt = select([product_table])
>>> for row in stmt.execute():
... print row
...
(u'123', Decimal("12.34"))
(u'456', Decimal("22.12"))
(u'789', Decimal("41.44"))


GROUP BY Statement

The GROUP BY clause will help you break up results based on column values. GROUP BY will let you use some of the functions against logical groupings of the information in your tables. You can also use it to form logical breaks in the information in the results set. For example, if you're working with a sales history table, you can use GROUP BY to help in breaking the information up by state. You can then subtotal the sales values for a state:
SELECT * FROM Sales 
GROUP BY State

The GROUP BY clause, which groups results according to the parameters set forth in the clause, finds out information about a particular record, whereas aggregation summarizes more than one record. This module examines
  1. the COUNT() function, which counts records;
  2. the SUM() function, which adds the value of records together;
  3. the AVG() function, which finds averages; and finally, the MAX() and MIN() functions, which find the lowest and highest values in a set of records.

This section examines the GROUP BY clause, which is used in conjunction with the SELECT statement. It allows you to group identical data into one subset rather than listing each record. The GROUP BY clause is at its most powerful when used with SQL's summarizing and aggregating functions, which are covered in the next section. The aim of this module is to get a handle on how GROUP BY works and how to use it more effectively. Begin by looking at how GROUP BY can answer the question,
Which states do members of the film club live in?

The answer does not require a list of every member and the state they live in; you simply want a list of the specific different states. Use the GROUP BY clause to answer this question, even though strictly speaking SELECT DISTINCT would work just as well:
SELECT State
FROM MemberDetails
GROUP BY State;

The GROUP BY clause must go after any FROM or WHERE clauses in the SELECT statement. All the columns you want to be grouped must be listed in the GROUP BY column list. For example, the preceding code groups by the State column. If you want to include more than one column in the GROUP BY clause, then separate the columns with commas, in the same way that you would separate columns in a SELECT statement's column list. The preceding SQL produces the results shown in the following table.
One of the values in the table is NULL, so you end up with one group that is NULL:
State
NULL
Golden State
Mega State
New State

  1. SubQuery Statement
  2. DISTINCT
  3. Views
  4. Functions

SEMrush Software