Module 5: Summaries & SubQueries

CMPS 160: Databases

Introduction

In this module, we will learn how to utilize aggregate functions in SQL (Structured Query Language). The functions can be utilized for calculating sums of data, searching for duplicate values in an existing table, locating a definite result for a specific query loop.

We will learn about data types and functions. How data can be stored in any of the character, numeric, date/time, and large object data types. In this module, we will learn how to cast, convert and format values within a database.

Database functions allow you to locate and perform calculations on specific areas of data in a database. Think about how you use functions in Microsoft Excel to add up cells to obtain a sum value. Functions in a database work similar.

Module Objectives

  • Learn how to create summary queries.
  • Describe the differences between the HAVING and WHERE clause
  • Learn about the WITH ROLLUP operator.
  • Learn about the use of the GROUPING and IF functions with the WITH ROLLUP operator.
  • Understand the use of the aggregate functions.
  • Describe the way subqueries can be used in the WHERE, HAVING, FROM and SELECT clauses of a statement.
  • Learn the difference between a correlated subquery and noncorrelated subquery.

Learning Resources

  • Module 5 Readings: Chapter 6 & 7
  • Module 5 Slides: Chapter 6 & 7

Learning Activities

  • Module 5 Assignment: Chapter 6 & 7: Pages 339 & 379 of the textbook.

For Further Study

Leave A Reply

Your email address will not be published.