The Graphics Network

Course Outline - SQL

01285 713297 info@tgn.co.uk

Structured Query Language (SQL) has been the workhorse for anything to do with data for many decades and it is still the language of choice for the majority of applications. Over the years I have used it for everything from hand-held scientific instruments to major commercial applications.

I can provide training ranging from a one-day introduction through to a more comprehensive coverage over 3 or 4 days. I can also combine it with my language courses to show how it can be used in bespoke applications. I can also provide training for spatial applications exploiting the geography and geometry capabilities. I use SQL Server most often, but can also train using MySQL, MariaDB, PostgreSQL, SQLite and even Access. The outline below assumes SQL Server over 3 or 4 days.

SQL

Introduction
Relational Databases. IDs and Keys. Primary and Foreign Keys. Entity-Relationship Modelling - ERM. T-SQL. How SQL Server Works. System Databases. SQL Server Management Studio. SQL Server Configuration Manager. Other Tools.
Tables and Data Types
Naming Objects. Designing a Table. Creating a Database. Database Files. Storing Scripts. Data Types - Integers. Non-Integer Numeric Types. Characters and Text. Other Main Data Types. Identity Columns. Setting the Primary Key. Nulls. Altering a Table. Dropping Columns and Tables. User Defined Data Types.
Selecting Data
The Select Statement. Selecting Columns. Ordering. Aliases. Combining Columns. Distinct. Filtering Using Where. Using Not, And and Or. Matching using Like. Between and In.
Joins and Constraints
Relationships. Using Foreign Keys. Inner Joins. Outer Joins. Disambiguation and abbreviation. Foreign Key Constraints. Database Diagrams. Relationship Properties. Cascading on Update or Delete. Default, Unique and Check Constraints. Disabling Constraints.
Calculation
Aggregate Functions. Group By and Having. Having and Where. Rollup, Cube and Grouping Sets. Dates and Times. The Convert Function. String Functions.
Action Queries
Insert Into. Delete. Update. Updating Multiple Rows. Insert ... Select. Select Into.
Sub-Queries
Simple sub-queries. Sub queries using multiple items. Correlated sub-queries. Exists and Not Exists.
Views and Triggers
Creating and Using Views. Views Involving Multiple Tables. Inserting via a View. Triggers. The Trigger SQL. Temporary Tables.
Stored Procedures and Functions
What is a Stored Procedure?. Creating a Stored Procedure. Calling the Procedure. Passing Parameters. Returning a Value. Using an Output Parameter. Scalar-Valued Functions. Table-Valued Functions. A Function as a Parameterized View. Multi-Statement Table-Valued Functions. Error Handling. T-SQL as a Programming Language.
Indexing
Index Structure. Clustered and Non-Clustered Indexes. Query Analysis. Indexes and Primary Keys. Covering a Query.
Transactions and Locks
Using Transactions. Savepoints. Nested Transactions. Transaction Isolation Levels.

Spatial

Introducing Spatial
Spatial support in SQL Server. Geography and geometry types. Coordinate systems. Spatial references and IDs. Well Known Text and Well Known Binary. Standard methods. Points, line strings, rings and polygons. Circular and additional types. Mapping.
Spatial Calculations
Static and instance methods. Variables, methods and properties. Extracting points. Calculating distances. Areas. Intersections and combinations. Centroids and centres. Envelopes and convex hulls. Point reduction. Spatial aggregates.
Spatial Indexes
The importance of spatial indexes. How spatial indexes work. Levels. Creation and configuration. Selecting indexes.