Advanced SQL Book – Advanced SQL

This Book is the final one specifically dedicated to the SQL language, and so it forms an important role in drawing together the information covered in all three of the SQL-related chapters of the module. SQL continues to be an important vehicle for explaining and illustrating concepts in many of the later chapters of the module, and provides a medium through which many relevant

practical exercises can be performed.

Although this chapter is called Advanced SQL, the material covered is not in general more difficult than that of previous chapters. The previous two chapters on SQL have provided a fairly comprehensive coverage of the data manipulation (DML) part of the language, enabling the specification of a wide range of queries. This chapter introduces the mechanisms for creating, changing and removing tables, and for inserting, updating and removing rows from tables.

The mechanisms for performing these actions in SQL are relatively straightfor-ward, but are extremely powerful. Because SQL is a command-level language, these commands do not include the checks that we have grown to expect from a typical Graphical User Interface (GUI), and so they must be used with care.

Table of contents

• Objectives

• Introduction

• Context

• Creating tables in SQL

– Data types

– Defining primary keys

– Defining foreign keys

– Copying data by combining CREATE TABLE and SELECT

– Copying table structures without data

• The ALTER TABLE statement

– Using ALTER TABLE to add columns

– Modifying columns with ALTER TABLE

• Removing tables using the DROP TABLE statement

– Using DROP TABLE when creating tables

• Adding new rows to table with INSERT

• Changing column values with UPDATE

• Removing rows with DELETE

• Creating views in SQL

– Views and updates

• Renaming tables

• Creating and deleting a database

• Using SQL scripts

• Activities

– Activity 1: Data definition language

– Activity 2: Manipulating rows in tables

– Activity 3: Creating and removing views

• Review questions

• Discussion topic

• Additional content and activities