12SELECT-2

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

In this presentation, we use the same sample database, that contains information about the departments and about the courses offered by the departments.

Information about the departments is kept in a relational table: DEPARTMENT.

The relational table: DEPARTMENT, consists of six columns: department name (name), department code (code), total staff number, chairperson (chair ), and budget.

A column: name, is a primary key and the values in a column: name, are the variable size strings, no longer than 50 characters, A column: name, is a primary key and because of that all values in the column are mandatory (NOT NULL).

A column: code, is a candidate key and all it’s values are fixed size strings 5 characters long. If the codes are shorter than 5 characters, then such values are padded with a sequence of blanks, up to 5 characters long. The values in a column code are mandatory (NOT NULL).

The values in a column: total staff, are integer values, in a range from one to fifty. The values in a column: total staff, are mandatory (NOT NULL). Total staff number per department cannot be greater than 50.

A column: chair, contains the variable size strings, no more than 50 characters long, and all its values are optional (NULL)

A column: budget, contains the real numbers, no more than 9 digits long, with at most one position after a decimal dot. The values in a column budget are mandatory (NOT NULL).

A relational table COURSE contains the descriptions of course.

A description of course consists of a course number, title, credits and name of department that offers a course.

A column course number is a unique identifier of each course. A value of course number is a fixed length string of characters, 7 characters long.

A column title contains titles of the courses. A title is a variable length string of characters, maximum 200 characters long.

A column credits determine the credits granted fro passing a course.The granted credits can be equal to either 6 or 12.

A column offered_by contains the names of departments that offer courses.

A column offered_by is a foreign key referencing the names of departments in. column name in a table DEPARTMENT.

Queries with simple conditions

Sample database

CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

We start from a simple query, that finds the titles of all 6 credit point courses. In the first step, we have to find which relational table or which relational tables, must be accessed to retrieve the required information. From information in CREATE TABLE statements, we find that a relational table: COURSE, contains information about both titles and credits of the courses. WHERE condition is: credits greater than 6. A projection on a column: title, returns only the titles of courses.

An objective of the second SELECT statement is to find the titles of all 6 or 12 credit points courses. It is possible to express a search condition as a disjunction of simple terms: credits = 6 or credits equal to 12. However, if we would like to express a condition where a value in a column can be one of the values in a given set, then it is easier to do it with set membership operation: in.A condition: credits in (6, 12), means that a value in a column: credits, should be a member of a set (6, 12). All rows, that satisfy a condition: credits in (6, 12), will be retrieved from a relational table: COURSE.

Finally in the last SELECT statement, we implement a query that finds the titles and numbers of all courses, that have a word "database" in its title. When we are not sure if the contents of a database is saved in an uppercase or lowercase format, then we apply a row function: UPPER, to a value in a column: title. We use an operation: LIKE, to implement a pattern matching search condition, selecting only the rows where a title starts from a word: database, or it includes a word: database, or it ends with a word database. A percentage sign in a pattern, matches zero or more characters in a string. It is the same pattern as the star pattern in the regular expressions.

Queries with simple conditions

Find the titles of all 6 credit points courses

Find the titles of all 6 or 12 credit points courses


SELECT title
FROM COURSE
WHERE credits IN (6, 12);

Find the titles and numbers of all courses that have a word "database" in its title

SELECT statement (2)

A Boolean expression consist of: elementary terms, Boolean operators: and, or, not, left and right parenthesis. In this presentation, we consider only the elementary terms being the comparisons of a column name and a constant, column name and a set of constants or two column names. In a general case a constant in an elementary term, can be an inline view that evaluates either to a constant or to a set of rows.

In the first example, we implement a query, that finds the titles of all 6 credit points courses offered by a department of Physics First, we find what relational tables must be accessed to retrieve the required information. We find, that all columns: credits and offered by, involved in a search condition in WHERE clause, and a column to be returned as an answer are included in a relational table: COURSE. Such results allow for implementation of SELECT clause as: SELECT title, and FROM clause as : FROM COURSE. WHERE condition, consists of two elementary terms: credits = 6, offered_by = 'Physics', joined with a conjunction operator: and.

In the next example, we implement a query: find the titles of all 6 credit points courses or the titles of all courses offered by a department of Physics. It is almost the same query as in the previous example. The only difference is a disjunction operator: or, that binds the same elementary terms in WHERE clause.

In the last example, we implement a query that finds the titles of all 6 credit points courses, that are not offered by a department of Physics. In this case, SELECT and FROM clauses, are the same as in two previous queries. A negated term: NOT offered_by = 'Physics', means, that the courses we are looking for must not be offered by a department of Physics. A term: credits = 6 expresses the same search condition as before.

An interesting question is, which of the Boolean operators used in WHERE clause, applies first in the evaluation of a search condition ? Do we compute operator AND, first, and then we negate the results ? Or do we negate the result of evaluation of a term: offered_by = 'Physics' and then we apply a conjunction operator ? In SQL, a negation operator: not, has a higher priority than a conjunction operator: and a disjunction operators : or. Hence, in our case, the evaluation of a Boolean expression in WHERE clause, starts from the evaluation of an elementary term offered_by = 'Physics' and negation of the result of such evaluation. Next, a query processor evaluates and elementary term: credits = 6. And finally, a conjunction operator: and, is evaluated on the results of evaluation of a negation and the second elementary term. Such procedure is repeated for each row in a relational table: COURSE.

Queries with Boolean expressions

Find the titles of all 6 credit points courses offered by a department of Physics
Find the titles of all 6 credit points courses or the titles of all courses offered by a department of Physics
Find the titles of all 6 credit points courses that are not offered by a department of Physics

The first example, shows two different ways of implementation of a query: "find the titles of all courses offered by a department of Physics or offered by a department of Mathematics".

In the first case we create a disjunction of two elementary terms: offered by = 'Physics' or offered_by = 'Mathematics'. Evaluation of a disjunction returns true, when either both terms or one of them evaluates to true.

In the second implementation, we use a single term with a set membership operation: IN, and a set constant, that consists of two elements: "Physics" and "Mathematics". Interpretation of set membership operator is such that a value in a column: offered by, must be one of the values included in a set constant. In the second example, we try to implement a query that finds the same titles of all courses offered by a department of Physics and offered by a department of Mathematics. It is probably impossible, that in the reality two departments offer the courses with the same title. Administration of a university would probably never agree on such situation.

However, for the sake of our example, let us assume, that it is possible to have two or more courses with the same title and offered by the different departments. Then, the query we are going to implement is almost identical as the first query. The only difference is, that a disjunction operator: or, is replaced with a conjunction operator: and. Therefore, someone may naively think, that to implement such query it is enough to replace an operator: and, with an operator: or, as it is written in the slide.

Unfortunately, such solution is completely incorrect. The solution in the slide is incorrect, because the evaluation of a Boolean expression in WHERE clause, is performed in a "row by row" mode. If we take a row, and if we try to evaluate a condition: "offered by equals to Physics and offered by equals to Mathematics" then the result of such evaluation is always false, because a value in a row cannot be in the same moment equal to: Physics and equal to: Mathematics. Therefore, none of the rows will be selected and the answer will always be empty, despite that it is possible, that Physics and Mathematics offer a course with the same title. Unfortunately, a query like that, cannot be implemented as a simple query with a Boolean expression. We shall return to this problem later.

Queries with Boolean expressions

Find the titles of all 6 credit points courses that are not offered by a department of Physics

We start from a short review of three operation on data sets: UNION, INTERSECTION and DIFFERENCE.

Assume, that we have two sets: A and B. For example, assume that set each contains the names of colours like: white, blue, red, green and the others. The sets must not contain the repetitions, for example a set cannot have two names: red and red. Assume, that a set A contains the colours red and blue and a set B contains the colours red and green. Assume, that we would like to find, the names of all colours, included in a set A or in a set B. Such action can be implemented with UNION operation on the sets: A and B.

An operation: A UNION B, copies the elements of the sets: A and B to a result of the operation and it eliminates the duplicated elements. Then, the result of: A UNION B, is a set that contains the colours: red, green and blue. Again note, that a colour red is NOT repeated twice.

An operation of DIFFERENCE finds all elements, that are included in the first argument and, that are not included in the second argument. For example A DIFFERENCE B, returns a set that consists of one element: blue. This is because, blue is the only element, that is included in A and, that is not included in B.

The diagrams, that visualize the outcomes of the set operations are presented in the slide.Such diagrams are commonly called Venn diagrams. Venn diagrams have been named after the English mathematician John Venn, who for the first time used the diagrams illustrating the outcomes of set operations, in his research paper published in 1880.

Queries with Boolean expressions

Find the titles of all courses offered by a department of Physics or offered by a department of Mathematics


SELECT title
FROM COURSE
WHERE (offered_by = 'Physics') OR (offered_by = 'Mathematics');


SELECT title
FROM COURSE
WHERE offered_by IN ('Physics', 'Mathematics');

Find the same titles of all courses offered by a department of Physics and offered by a department of Mathematics
- WRONG !!!

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

We start from a short review of three operation on data sets: UNION, INTERSECTION and DIFFERENCE.

Assume, that a set A contains the colours red and blue and a set B contains the colours red and green. Assume, that we would like to find, the names of all colours, included in a set A or in a set B. Such action can be implemented with UNION operation on the sets: A and B. An operation: A UNION B, copies the elements of the sets: A and B to a result of the operation and it eliminates the duplicated elements. Then, the result of: A UNION B, is a set that contains the colours: red, green and blue. Again note, that a colour red is NOT repeated twice.

An operation of INTERSECTION finds the same elements in both arguments of the operation. For example, A INTERSECTION B, returns a set, that consists of a single element: red, because a red colour is member of both sets A and B. An operation of DIFFERENCE finds all elements, that are included in the first argument and, that are not included in the second argument. For example A DIFFERENCE B, returns a set that consists of one element: blue. This is because, blue is the only element, that is included in A and, that is not included in B.

The diagrams, that visualize the outcomes of the set operations are presented in the slide. Such diagrams are commonly called Venn diagrams. Venn diagrams have been named after the English mathematician John Venn, who for the first time used the diagrams illustrating the outcomes of set operations, in his research paper published in 1880.

Set algebra queries

Set operations: UNION, INTERSECTION, and DIFFERENCE
A UNION B: all elements in A or in B
A INTERSECTION B: all elements in A and in B
A DIFFERENCE B: all elements in A and not in B
B DIFFERENCE A: all elements in B and not in A

SQL implements the set operations, because such operations provide a very convenient way of expressing queries, that act on the sets of rows in the relational tables.

For example, an earlier implemented query: "find the credit points of courses offered by a Department of Physics or a Department of Mathematics and do not list duplicated rows", can be implemented as a simple query with a Boolean expression and a DISTINCT keyword in the SELECT clause.

The same query, can be implemented as a union of the results returned by two SELECT statements, that separately find the credits of courses offered by Physics and the credits of courses offered by Mathematics, get the results together and eliminate the duplicates.

A sample implementation of SELECT statement, with a UNION operation, is given at the bottom of the present slide. Please note, that a UNION operation, automatically removes the duplicated rows from the final result.

Set algebra queries

UNION operation

Find the credit points of courses offered by a department of Physics or a department of Mathematics and do not list duplicated rows


SELECT DISTINCT credits
FROM COURSE
WHERE (offered_by = 'Physics') OR (offered_by = 'Mathematics');


SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
UNION
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

UNION operation automatically eliminates duplicated rows !!!

Set algebra queries

UNION ALL operation

Find the credit points of courses offered by a department of Physics or a department of Mathematics


SELECT credits
FROM COURSE
WHERE (offered_by = 'Physics') OR (offered_by = 'Mathematics');


SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
UNION ALL
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

UNION operation does NOT eliminate duplicated rows !!!

What about the other set operations, like the intersection (INTERSECT) and the difference (MINUS) ?

Some larger database systems implement all set operations: UNION, UNION ALL, INTERSECT and MINUS. My SQL implements only the operations: UNION and UNION ALL.

What about the other operations on sets ? The INTERSECT operation can be implemented as the JOIN operation or as a nested query.

The MINUS operation can be implemented as a nested query or as the OUTER JOIN operation. The operations: JOIN, OUTER JOIN, and nested queries will be presented in the future.

Now, we return to our old problem: how to implement a query, that "finds the titles of courses offered by the departments of Physics and Mathematics". We have already tried to implement such query as a simple SELECT statement, with a Boolean expression as a conjunction of two elementary terms. Such solution appeared to be incorrect.

If we would like to find the titles of courses offered by the departments of Physics and Mathematics, then we can separately find a set of titles of courses offered by Physics and a set of titles of courses offered by Mathematics and next, we can compute the intersection of two sets.

It means, that we can implement two simple SELECT statements, one finding the titles of courses offered by a department of Physics and the other finding the courses offered by a department of Mathematics and use operation: INTERSECT, to find the common titles of the courses.

In a database systems that implement the set operation INTERSECT, it is a correct solution. Unfortunately, MySQL does not implement the operation INTERSECT, and instead we have to use an operation JOIN of two inline views. Note, that join of two relational tables that have identical schemas is equivalent to the operation INTERSECT.

Set algebra queries

What about other set operations like intersection (INTERSECT) and difference (MINUS) ?
MySQL does not implement INTERSECT and MINUS set operations :( !!!
So, how do we implement in MySQL queries with INTERSECT and MINUS set operations ?

Find the credit points that can be earned both for a course offered by Physics and for a course offered by Mathematics


SELECT DISTINCT credits
FROM COURSE
WHERE offered_by = 'Physics' AND
      credits IN (SELECT credits
                  FROM COURSE
                  WHERE offered_by = 'Mathematics');

Set algebra queries

What about other set operations like intersection (INTERSECT) and difference (MINUS) ?
MySQL does not implement INTERSECT and MINUS set operations :( !!!
So, how do we implement in MySQL queries with INTERSECT and MINUS set operations ?

Find the credit points that can be earned for a course offered by Physics and cannot be earned for a course offered by Mathematics


SELECT DISTINCT credits
FROM COURSE
WHERE offered_by = 'Physics' AND
      credits NOT IN (SELECT credits
                      FROM COURSE
                      WHERE offered_by = 'Mathematics');

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

Sorting

Sorting re-orders the rows in the results if a query

SORTING

Sorting

Find the numbers and titles of all 6 credit points courses sorted in the ascending order of titles


SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY title ASC;


SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY 2 ASC;

Sorting

Find the numbers and titles of all 6 credit point courses sorted in a descending order of titles


SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY title DESC;


SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY 2 DESC;

Sometimes, a single sorting column, is not sufficient, to sort the results from SELECT statement in the appropriate way. For example, when a column used for sorting, has a small number of distinct values, then we may get a small number of pretty large groups of rows.Then, we need to sort each group of rows again.

For example, assume, that we would like to find the numbers, titles, and credits of all courses sorted in an ascending order of credits and for all courses with the same number of credits, sorted in the descending order by titles.

To sort the results of SELECT statement, first by credits and next in the groups of courses with the same credits, by course title we add ORDER BY clause with a list of columns: credits and title.The names of columns on ORDER BY list, are followed by the sorting orders: ASC for ascending and DESC for descending order.

It is possible to use the numbers of columns on SELECT clause list, instead of the names of columns like in the second SELECT statement in the present slide.

Sorting

Find the numbers, titles, and credits of all courses sorted in an ascending order of credits and for all courses with the same credits sorted in descending order by titles


	      
SELECT cnum, title, credits
FROM COURSE
ORDER BY credits ASC, title DESC;


	      
SELECT cnum, title, credits
FROM COURSE
ORDER BY 3 ASC, 2 DESC;

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

When a relational table is created with CREATE TABLE statement, then it is possible to say, that the values in a column are optional and such fact is represented by a symbol: NULL. It means, that such column may have no values in some of its slots. In an extreme case the entire column can be empty. An empty slot in a column, is represented by a symbol: NULL.

An interesting question is, how SELECT statements refer to the empty slots in the relational tables ?

For example, consider an implementation of a query: "find the titles of all courses that are not offered now".If a course is not offered, then a row that contains information about such course has an empty slot in a column: offered by. An empty slot in a relational table is represented by a symbol: NULL.

To express a condition, that compares a slot in a relational table with an empty values represented by: NULL, we use an operator: IS.The first SELECT statement, in the present slide, correctly compares the slots in a column: offered by, with an empty space represented by a symbol: NULL.The second SELECT statement, uses an equal sign, to compare the slots in a column: offered by, with an empty space.Such comparison is incorrect, because an equal sign cannot be used to compare the values with nothing, represented by a symbol NULL.

Queries about lack of values (NULLs)

Find the titles of all courses which are not offered now
- WRONG !!! Lack of value cannot be compared with a value

Queries about lack of values (NULLs)

Find the titles of all courses offered now


SELECT title
FROM COURSE
WHERE offered_by IS NOT NULL;


SELECT title
FROM COURSE
WHERE offered_by <> NULL;

WRONG !!! Lack of value cannot be compared with a value

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

Now, we consider a relational table that contains the rows with different colours. Assume, that we would like to find the total number of rows that have the same colour in a COLUMN_2.We can see one row in light blue color in a COLUMN_2, two rows in blue color and two rows in dark blue colour in a COLUMN_2.To get such results, we created the groups of rows that have the same colour in a COLUMN_2 and we counted total number of rows in each group.

This is what GROUP BY clause does when it is applied in SELECT statement .GROUP BY clause is always located after WHERE clause in SELECT statement. A list of column names following a GROUP BY keyword, determines the values in the rows, that are used for grouping.I n the other words, a GROUP BY clause, groups the rows with the identical values in the columns listed in GROUP BY clause.

An example of grouping visualized in the present slide creates three groups: GROUP_1, with a dark blue colour, GROUP_2: with light blue colour and GROUP_3 with blue colour.

If we use a GROUP BY clause, then a SELECT clause, must list only the columns included in a GROUP BY clause.It is also possible to append to a SELECT clause, the group functions processing the values in the other columns of a relational table.

If in our example, grouping is performed over the values in a COLUMN_2, then it is possible to list name: COLUMN_2, in SELECT clause and no other column.Then, SELECT statement returns the distinct values included in a COLUMN_2.

Of course, it is not the best way to implement selection of the distinct value from a column.SELECT clause with: DISTINCT COLUMN_2 is a much better solution.

So, why do we need grouping ?Grouping of the rows in a relational table is an intermediate stage, that prepares a relational table to the application of the group functions, also called "aggregation functions". The application of the group functions to the results of grouping is explained in the next slide.

Grouping

Grouping groups the rows before application of aggregation function

GROUPING

We return to our original problem from the previous slide. We would like to find the total number of rows that that have the same colour in a COLUMN_2.

If we apply grouping with a GROUP BY clause, and we use a COLUMN_2 in a SELECT clause, then to count the total number of rows in each group we append: COUNT(*) group function, to a list of columns in a SELECT clause.Then, a group function: COUNT(*) is applied to each group in an intermediate relational table, obtained from the processing of a GROUP BY clause.When a COUNT function is applied to a dark blue group, it returns 2, counting of the rows applied to a light blue group returns one, and counting of the rows in a blue group returns 2.We count the rows because an argument of COUNT function is a star.

It is possible to count the individual values in a given column.We shall return to such aggregations later.

When COUNT function is applied to each group, the results of counting are associated with the values taken from a COLUMN_2 used for grouping.SELECT statement demonstrated in the present slide returns a table that consists of two columns: COLUMN_2 and a column with the results of counting. It is possible to give a new name to a column with the results of counting.To do so, we append a name of the new column to: COUNT(*), in a SELECT clause.

Grouping

Next, aggregation function is applied to each group and the results are associated with the values taken from a column used for grouping

GROUPING

Grouping

Aggregation function can be applied to an empty group of values

GROUPING

Assume, that we would like to "find the names of all departments, that offer at least one course" and we would like to eliminate the duplicates from the results. The names of departments, that offer at least one course are located in a relational table: COURSE, in a column: offered_by. If a department offers more than one course then, its name is listed in a column: offered_by, more than one time. To eliminate the duplicates from the results of SELECT statement, we can use a keyword: DISTINCT, in front of a list of columns in a SELECT clause.

The same result can be obtained through application of a GROUP BY clause with a name of column: offered_by. A solution that uses a GROUP BY clause returns a correct result, however, a complexity of its implementation is higher than in a statement with a DISTINCT clause. Therefore, it is not recommended to use a GROUP BY clause, to eliminate the duplicates.

GROUP BY clause, sorts a relational table over the attributes included in GROUP BY list, and it creates the groups of rows with the identical values of the attributes in a GROUP BY list. The second SELECT statement in the present slide sorts the rows in a table: COURSE, over the values of in a column: offered_by. Then, the groups of rows with the identical values of an attribute: offered by, are created. Finally, the values of an attribute: offered by, are selected, one value from each group. Still, it is faster to use a DISTINCT clause, to eliminate the duplicates.

Grouping

Find the names of all departments that offer at least one course, do not list the same names of departments
GROUP BY sorts a relational table over the attributes included in GROUP BY list and creates the groups of rows with identical values of the attributes in GROUP BY list
In the example above a table COURSE is sorted over the values of an attribute offered_by
The groups of rows with the identical values of an attribute offered_by are created
Finally, the values of an attribute offered_by are selected, one value from each group

Grouping of rows with a GROUP BY clause allows for the applications of group functions, to each one of the groups. Assume, that we would like to "find the names of all departments together with the total number of all courses offered by each department". Assume, that we do not need to list the names of departments, that offer no courses. It is explained later, how to append the departments, that offer no courses into the results of the query.

If a department offers one or more courses then, its name occurs in a column: offered_by, one more time. We group the rows by the names of departments listed in a column: offered_by. Each group contains information about all courses offered by a single department.

Grouping can be implemented by sorting of a relational table: COURSE, over a column: offered by, or by hashing the values in a column offered by. Hashing will group the rows with the same names of departments in the same hash buckets.

he sample contents of a relational table: COURSE, is visualized at the bottom of the present slide. A manual grouping of data in a relational table: COURSE, and counting the total number of rows in each group provide the results where, Computer Science department offers 2 courses, Physics department offers 2 courses and Mathematics department offers only one course. Now, the relational table: COURSE, is sorted and the outcomes are demonstrated in the next slide.

Grouping

Grouping allows for application of group functions to each group created by GROUP BY clause

Find the names of all departments together with the total number of all courses offered by each department


SELECT offered_by, COUNT(*)
FROM COURSE
GROUP BY offered_by;

Before sorting


+---------+------------+---------+------------------+
| cnum    | title      | credits | offered_by       |
+---------+------------+---------+------------------+
| CSCI111 | C++        |       6 | Computer Science |
| PHYS312 | Relativity |       6 | Physics          |
| MATH111 | Calculus   |      12 | Mathematics      |
| CSCI235 | Databases  |       6 | Computer Science |
| MECH111 | Mechanics  |      12 | Physics          |
+---------+------------+---------+------------------+

Grouping

Grouping allows for application of group functions to each group created by GROUP BY clause

Find the names of all departments together with the total number of all courses offered by each department


SELECT offered_by, COUNT(*)
FROM COURSE
GROUP BY offered_by;

After grouping by offered_by


+---------+------------+---------+------------------+
| cnum    | title      | credits | offered_by       |
+---------+------------+---------+------------------+
| CSCI111 | C++        |       6 | Computer Science |
| CSCI235 | Databases  |       6 | Computer Science |
| MATH111 | Calculus   |      12 | Mathematics      |
| MECH111 | Mechanics  |      12 | Physics          |
| PHYS312 | Relativity |       6 | Physics          |
+---------+------------+---------+------------------+

Grouping

Grouping allows for application of group functions to each group created by GROUP BY clause

Find the names of all departments together with the total number of all courses offered by each department


SELECT offered_by, COUNT(*)
FROM COURSE
GROUP BY offered_by;

After counting rows in each group


+------------------+----------+
| offered_by       | count(*) |
+------------------+----------+
| Computer Science |        2 |
| Mathematics      |        1 |
| Physics          |        2 |
+------------------+----------+

Now, we consider few more examples of grouping and application of group functions like: COUNT, SUM, and MAX. The first query in the present slide, finds the total number of courses per each value of credits. To implement such query, we have to use a relational table: COURSE, because the table contains information about the credit points granted for the successful completions of the courses. Next, we find, that grouping must be performed over the values in a column: credits. It also indicates, that we must use a column: credits, in a SELECT clause. To count the total number of rows in each group, we append a group function: COUNT(*), to a SELECT clause.

In the second example, we would like to "find the total number of credit points available for the course offered by each department". If we assume, that we are interested only in the departments that offer at least one course, then we can get all required information from a relational table: COURSE. To find the total number of credit points per each department, we have to group the rows in a relational table: COURSE, by the values in a column: offered by. It is the same idea as in the earlier example, where we counted the total number of courses per department. This time, we do not count the courses. Instead, we perform summation of the values in a column: credits, for each department. This is why, we use a group function: SUM, with an argument: credits. A group function: SUM, returns the results of summation of all values in a column: credits.

In the last example, we would like to find the largest number of courses offered by any department. To implement such query as a SELECT statement, we decompose the query into two sub queries. The first one finds the total number of courses offered by each department. We have already implemented such query in the previous slide. Now, we assume, that we have a relational table: T that consists of a single column: total, created by grouping of the rows in a relational table: COURSE, by the values in a column: offered by and counting the total number of rows in each group. The results of counting are stored in a column: total, in a relational table T. Then, a simple application of MAX function to a column: total, provides the largest number of courses offered by any department. Note, that SELECT statement finding the total number of courses per department, implements inline view: T. To be able to refer to a column in inline view: T, we add an alias name: total, to COUNT star function in SELECT clause. Note, a blank, and not comma, between SELECT COUNT star and a name of a column: total. A blank indicates an alias name for the results of counting. A comma between count star and total, would be syntactically incorrect, because a relational table: COURSE, does not have a column: total, and grouping is performed by a column: offered by.

Grouping

For each value of credit points find the total number of courses that have the respective credits

For each department find the total number of credit points offered


SELECT offered_by, SUM(credits)
FROM COURSE
GROUP BY offered_by;

Find the largest number of courses offered by any department


SELECT MAX(total)
FROM (SELECT COUNT(*) total
      FROM COURSE
      GROUP BY offered_by) T;

SELECT statement (2)

Outline

Queries with simple conditions
Queries with Boolean expressions
Set algebra queries
Sorting
Queries about lack of values (NULLs)
Grouping
Grouping with selections

Grouping with selections

Find the names of all departments that offer more than 1 course


SELECT offered_by, COUNT(*)
FROM COURSE
GROUP BY offered_by;


+------------------+----------+
| offered_by       | count(*) |
+------------------+----------+
| Computer Science |        2 |
| Mathematics      |        1 |
| Physics          |        2 |
+------------------+----------+

To express a selection condition on the results of grouping and counting, we use a HAVING clause.

For example, if we would like to find the names of departments, that offer more than one course, then we add to the SELECT statement a HAVING clause, with a condition count star greater than 1.

A HAVING clause compares the results of counting in each group, with a constant. A very common mistake is to use a WHERE clause, instead of a HAVING clause. A WHERE clause expresses a condition evaluated on the values from a single row. A HAVING clause expresses a condition evaluated on the values from an entire group of rows. This is why, the group functions cannot be use in WHERE clauses.

A SELECT statement that implements a query: "find the names of departments, that offer more than one course", is given at the top of the present slide and a sample answer is visualized below.

Grouping with selections

Find the names of all departments that offer more than 1 course


SELECT offered_by, count(*)
FROM COURSE
GROUP BY offered_by
HAVING count(*) > 1;


+------------------+----------+
| offered_by       | count(*) |
+------------------+----------+
| Computer Science |        2 |
| Physics          |        2 |
+------------------+----------+

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapters 5, 7, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.1 - 6.3.4 Data Manipulation, Pearson Education Ltd, 2015
D. Darmawikarta, SQL for MySQL A Beginner’s Tutorial, Chapters 2 - 5, Brainy Software Inc. First Edition: June 2014
How to ... ? Cookbook, How to implement queries in SQL, Recipe 5.1 How to implement SELECT statements with simple Boolean expressions ?
How to ... ? Cookbook, How to implement queries in SQL, Recipe 5.2 How to implement SELECT statements with the set algebra operations ?
How to ... ? Cookbook, How to implement queries in SQL, Recipe 5.3 How to implement SELECT statements with GROUP BY and HAVING clauses ?