Intermediate SQL Book – Intermediate SQL

This Book forms the bridge between the bo ok in which the SQL language was introduced, and the coverage of the data definition language (DDL).

It is possible to express a range of complex queries using the data manipulation language (DML) previously introduced. The earlier book showed how fairly simple queries can be constructed using the select-list, the WHERE clause to filter rows out of a query result, and the ORDER BY clause to sort information.

This book completes the coverage of the DML facilities of SQL, and will considerably increase the range of queries you are able to write. The final SQL book will then address aspects of SQL relating to the updating of data and the manipulation of the logical structures, i.e. tables that contain data.

Grouping and summarising information

Information retrieved from an SQL query can very easily be placed into separate groups or categories by use of the GROUP BY clause. The clause is similar in format to ORDER BY, in that the specification of the words GROUP BY is followed by the data item or items to be used for forming the groups. The GROUP BY is optional. If it appears in the query, it must appear before the

ORDER BY if the ORDER BY is present.

Table of contents

• Objectives

• Introduction

• Context

• Grouping and summarising information

– A very common error with GROUP BY

– The HAVING clause

• Writing queries on more than one table – JOIN

– Avoiding ambiguously named columns

– Outer JOINs

∗ LEFT OUTER JOIN

∗ RIGHT OUTER JOIN

∗ FULL OUTER JOIN

– Using table aliases

– SELF JOINS

– Summary of JOINs

• Nested queries

– The depth and breadth of nested queries

• The UNION operator

• The INTERSECT operator

• The MINUS operator

• ANY or ALL operator

• Correlated sub-queries

• Interactive queries

• Activities

– Activity 1: JOINs

– Activity 2: GROUP BY

– Activity 3: Nested queries

• Review questions

• Discussion topic

• Additional content