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.

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a set of high-level APIs that allows you to interact with databases in a more Pythonic way, rather than writing raw SQL queries. SQLAlchemy supports multiple database backends, including MySQL, PostgreSQL, SQLite, and others. It abstracts away much of the complexity of dealing with different database systems, allowing developers to interact with the database using Python objects and classes. In the context of MySQL, you can use SQLAlchemy to interact with a MySQL database by configuring it as the backend. SQLAlchemy will then generate the appropriate SQL statements to execute on the MySQL database, handling tasks like connection pooling, transaction management, and query construction. While "SQLAlchemy" is not a MySQL-specific concept, it can be used effectively with MySQL as part of a broader, database-agnostic Python application development framework.
The power of SQLAlchemy to construct complex queries becomes clear by the following example.

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 using SQLAlchemy: 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"))
  

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:

You can use views to create "stored windows" to your data. Views include all the general capabilities of the SELECT statement, and can be updateable or read-only. Remember, views are great tools for reporting. You can make your SELECT statement refined and polished, then create a view, and you will not have to reissue the SELECT statement again. You can simply reference the view.
Views are queries used to provide users with a specific view of the data in the database. In doing so, views serve a variety of purposes. They may tie related tables together and then pull selected fields out that specific users need, or they may pull selected interesting rows of data from a table that contains other rows of no interest to specific users. They may summarize large data sets, returning averages, counts, and the like.
Relational databases are sets of data in tables, when taken alone, are rarely of any use to anyone. Views allow the database administrator (DBA) to pull fields of interest from tables of interest and return a coherent data set useful to some specific user or application.

• Normalized Database: A normalized database is created by designing tables for each object or event being modeled, with the fields of each table representing an attribute of the object or event. Once the developer creates the normalized structure, it becomes necessary to allow users to view specific pieces of data. The users rarely understand the structure, so the DBA is often tasked with building the views that various users require. A view is nothing more than a SQL statement that is stored in the database with an associated name. Views allow the DBA to do the following:
  1. Structure data in a way that users or classes of users find natural or intuitive
  2. Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more
  3. Simplify the DBA's job by building base data structures that the DBA can use to build other views in a natural progression
  4. Summarize data for reports

A SQL subquery, also known as a nested query or inner query, is a query embedded within another SQL query. The subquery is used to retrieve data that will be used in the main query as a condition to further restrict the data that is being fetched. Subqueries can be used in various parts of a query, including the SELECT, FROM, WHERE, and HAVING clauses. In the context of filtering results, subqueries are commonly used in the WHERE clause. They allow you to filter the main query's results based on complex conditions that can't be defined using standard SQL operators alone. Consider a scenario where you have a "Sales" table that records the sales of various products in a store. Each row in the table represents a transaction, with columns for transaction_id, product_id, sales_date, and sales_amount. Suppose you want to find all transactions where the sales amount is above the average.
The SQL statement with a subquery would look something like this:

SELECT transaction_id, product_id, sales_amount 
FROM Sales 
WHERE sales_amount > (SELECT AVG(sales_amount) FROM Sales);

In this case, the subquery (SELECT AVG(sales_amount) FROM Sales) calculates the average sales amount from the "Sales" table. This subquery is run first and the result is used by the main query as a filter. The main query then returns all transactions from the "Sales" table where the sales amount is greater than this average value. Here, the subquery acts as a dynamic filter that is evaluated at runtime. It allows for more flexible conditions, as the filter value doesn't need to be known in advance and can be calculated based on the data itself. In essence, SQL subqueries provide a powerful mechanism to enhance the precision of your data retrieval, permitting intricate relationships to be defined between tables and offering more flexibility in constructing complex queries.

• What are Subqueries?
  Subqueries are regular queries placed inside parenthesis. Subqueries can be used in different ways and at different locations inside a query: Here is an subquery with the IN operator
  

  SELECT columnNames
    FROM tableName1
   WHERE value IN 
  (SELECT columnName
     FROM tableName2 
     WHERE condition)
  

  
  Subqueries can also assign column values for each record:
  

  SELECT column1 = (SELECT columnName 
  FROM tableName WHERE condition),
   columnNames
   FROM tableName
   WEHRE condition
  

• When to use a Subquery: There will be times when you need to limit a SELECT statement to values that relate to another table, but a join is not necessarily the answer. In these cases, the subquery comes into play. You can use a SELECT statement as the filtering mechanism to limit the overall results from a query. For example, from the PUBS database, you can say, "Give me all the titles for publishers who are located in CA." The subquery in the query below limits the publisher IDs to only those who live in the state of California:
  

  SELECT Title
  FROM Titles
  WHERE Pub_ID
  IN (SELECT Pub_ID
  FROM Publishers
  WHERE State= 'CA')    
  

  
  The result is a list of titles, but only those published by California publishers.

---

1. DISTINCT
2. Views
3. Functions