SQL Language
Course 915 - Three days

SQL is an industry-standard language for accessing mainframe, midrange and LAN-based, multiuser relational databases. This course will teach the student how to read and write good SQL for querying, updating and maintaining SQL databases. The course progresses through the elements of the language to build a thorough appreciation and understanding of SQL's capabilities and power. It covers ANSI-standard SQL and some of the common extensions.

Poorly written SQL is frequently a major factor in slow database response. The course highlights the best approach to structuring queries and updates to minimize the processing overhead and maximize performance. This becomes particularly important in client/server or distributed database environments, where locally executed SQL commands might result in remote processing on a host.

 
On Completion, Students will be able to

  • Describe where the SQL language came from and why it is set-based rather than procedural
  • Write SQL statements to create and manipulate database objects and data
  • Describe the performance implications of the way SQL queries are constructed
  • Use the features of the language to safeguard the data and its value to the user
  • Describe where the SQL Standard is heading and how it fits in with other initiatives

Who Should Attend

Anyone wishing to use SQL to access data. This course is also a common prerequisite for those going on our client/server or database courses.


Practical Work

Numerous examples are used throughout the course. You will complete a series of exercises that cover all the important components of the language and allow you to practice your SQL.


Prerequisites

No prerequisite knowledge required at all, typically your skills will include a working familiarity with the Windows 95 environment but no prior knowledge of SQL. The most successful students on this course are those who can type accurately and consistently, and can cope with 'syntax' as SQL is a syntactically fussy language. Typically students will have coded in some other language but it is not necessary to have done so to be successful on this course. The course is constructed with a view to being able to cope with students who may wish to move at a faster or slower pace than others.


Course Content

Introduction to Relational Databases

The need for an SQL standard; The ANSI standards; What is a database?; What is a relational database?; Components of a relational database; The database; Anatomy of a table; Primary and foreign keys; Joins; Components of SQL

Data Manipulation Language

Select operations; Distinct; Virtual columns; Column aliases; Functions; Restricting the rows returned; Multiple conditions; Ordering data; Inserting rows; Updating rows; Deleting rows; Joining tables; Cross, Natural, Inner, Outer, Full, Left and Right; Composite joins; Table aliases; Self joins

Data Definition Language

Data types; Column attributes; Create domain; Create table; Primary and foreign keys; Referential integrity; Assertions; Alter and drop tables; Defining simple views; Views with virtual columns; Restricted-column views; Joined-table views; Restricted-row views; View restrictions

Data Manipulation Language (Advanced)

Table aggregates; Group by and aggregates; The Having clause; Using Sub-queries with selects and inserts; Exists; Co-related Sub-queries

Data Control Language

Identifying users; Setting privileges; Grant and revoke; Schemas and the system catalog

Performance Issues

Table design; Creating and dropping indexes; Choosing columns to index; Tuning SQL; The optimizer; Statistics

SQL in the Client/Server World

Stored procedures; Triggers; Transactions and locks; SQL and other languages; SQL 3; OQL


Recommended Follow-on Courses

Database Analysis and Design