Back to course 'CS403: Introduction to Modern Database Systems'

Topic outline

  • Unit 10: The Join Statement

    Collapse all Expand all

    Programmers frequently join data from a number of different tables in order to obtain more information. They also – perhaps even more frequently – build queries to obtain information from more than one table in order to generate better information. In this section, we will learn about SQL Joins, which allow us to create complex queries, combine data from different tables, and obtain a new result set that can provide us with a better understanding of the data and maximize database flexibility.

    Completing this unit should take you approximately 4 hours.

    • Upon successful completion of this unit, the student will be able to:

      • explain the different methods for joining tables; and
      • construct advanced queries of two or more tables using join operations.

    • 10.1: Introduction

      • page icon
        INNER JOIN Page
        Students must
        Mark as done

        This video introduces the JOIN clause using an example of a typical scenario. There are several types of joins, some of which are illustrated in this video.

      • resource icon
        SQL Data Manipulation Language File
        Students must
        Mark as done

        Review chapter 16 for some more examples of the JOIN syntax. Take some time to try out the exercises at the end of the chapter.

      • url icon
        SQL JOINS URL
        Students must
        Mark as done

        It is helpful to visualize the result (that is, the semantics) of SQL statements and clauses. Review this depiction of the results of the types of join clauses using set diagrams.

      • page icon
        Aggregates and JOINs Page
        Students must
        Mark as done

        We use combinations of SQL commands, clauses, and functions to obtain information from a database. Typically, tables are combined to create temporary relations, and we apply select statements with clauses, constraints, and functions to obtain the desired information from a database. Watch this example of a query involving select, inner join, and aggregate.

    • 10.2: Inner Joins

      • page icon
        Inner vs. Outer Joins in SQL Page
        Students must
        Mark as done

        Another type of join in SQL is the outer join. This video explains and illustrates the difference between it and other types of joins.

      • url icon
        SQL INNER JOIN URL
        Students must
        Mark as done

        Review this example of inner joins.

    • 10.3: Outer Joins

      • url icon
        SQL OUTER JOIN URL
        Students must
        Mark as done

        Review this example of an outer join.

      • url icon
        SQL RIGHT JOIN URL
        Students must
        Mark as done

        Review this page, which describes and demonstrates a right join.

      • url icon
        SQL Full Outer Join URL
        Students must
        Mark as done

        This page demonstrates how a full outer join works. It does so by comparing an outer join SQL query with another query that involves a left outer join, a right outer join, and a union. As you'll see, each of these produces the same result.

    • 10.4: The Group By Clause

      • page icon
        GROUP BY Page
        Students must
        Mark as done

        The GROUP BY clause is used with aggregate functions, such as MIN() and MAX(), and with the WHERE, HAVING, and ORDER BY clauses. The examples in this video use full column names, aliases, and column numbers. The HAVING clause filters the results of the GROUP BY and the aggregate functions. The clauses are specified in SQL in a specific order.

      • url icon
        SQL GROUP BY Clause URL
        Students must
        Mark as done

        Read this page, which goes into more detail on the GROUP BY clause.

    • 10.5: Assessment

      • url icon
        SQL Exercises URL
        Students must
        Mark as done

        If you'd like more practice with SQL, attempt some of these exercises.

    • Unit 10 Assessment

      • quiz icon
        Unit 10 Assessment Quiz
        Students must
        Receive a grade

        Take this assessment to see how well you understood this unit.

        • This assessment does not count towards your grade. It is just for practice!
        • You will see the correct answers when you submit your answers. Use this to help you study for the final exam!
        • You can take this assessment as many times as you want, whenever you want.