This module continues the discussion of the SQL data manipulation language (DML) SELECT statement. It introduces various aggregate functions: COUNT, SUM, AVG, VARIANCE, MIN, and MAX, which are used to summarize information from database tuples. This is followed by the GROUP BY/HAVING clause, which allows the application of aggregate functions to subgroups. This module then discusses join queries that allow the user to combine or join data from multiple tables. The inner join queries feature a “where” clause that matches one or multiple columns from two tables. The left outer join, right outer join, and full outer join can be used to keep all the tuples of one or both tables in the result, regardless of whether or not they have matching tuples in the other table. All queries in this module use the Wine database in the online playground and can be executed there.

This module presents more complex SQL queries. It introduces nested queries where a complete SELECT FROM block appears in the WHERE clause of another query. The subquery or inner block is nested in the outer block and there can be multi-level nesting. The query optimizer usually flattens the nested query into multiple queries and executes them sequentially from the innermost to the outermost level. This module also examines the correlated nested query, where the inner block uses one or more columns of the table defined in the outer block. In this case, the query cannot be flattened, and the inner block subquery must be evaluated for each tuple of the table (also used in the inner block). The usage of the operators >= ALL and > ANY is discussed. The former can be used to find the highest or largest values whereas the latter can be used to exclude the lowest or smallest values. All queries in this module use the Wine database in the online playground and can be executed there. Finally, this module examines the DELETE and UPDATE statements that can be used to delete or modify data. It concludes with a brief discussion of SQL views.

This module introduces a couple of extensions to the Relational Database Management Systems (RDBMSs). We will start by reviewing the core components of the relational model and its limitations. Subsequently, the module explores methods for extending relational databases, starting with a thorough review of triggers and stored procedures as pivotal mechanisms for augmenting the activity of RDBMSs. The module concludes by delving into the intricacies of recursive queries, a powerful extension to the SQL language.

This module presents an overview of the NoSQL movement and distributed systems. MongoDB NoSQL database is discussed at the introductory level. MongoDB is intended for storing documents such as resumes, legal documents, books, etc. It does not use any schema or data model, and stores documents as collections — which store a collection of attributes labeled and unordered that represent semi-structured items.

This module continues the discussion of the NoSQL database. The graph theory and Neo4j graph database are discussed at the introductory level. The Neo4j is a graph database that applies graph theory to information storage. It consists of nodes and edges, both of which can store information. Graph databases are particularly useful in modeling social networks such as X (formerly known as Twitter) and Facebook. In a way, a graph database is a hyper-relational database where join tables are replaced by more interesting and semantically meaningful relationships that can be navigated (graph traversal) and/or queried, based on graph pattern matching.