MacLochlainns Weblog

These pages teach you how to use SQL (Structured Query Language), You use SQL to solve real world problems in relational and object-relational databases. The web pages provide you with a foundation to help you understand and use databases.

SQL specification are defined by American National Standards Institute (ANSI). SQL1 was the first version of the specification formalized by ANSI in 1986. The formal name for SQL1 is SQL-86. SQL1 went through a minor revision but ANSI SQL-89 was accepted by Federal Information Processing Standards (FIPS). SQL2 was a major revision in 1992 and it added a natural join and several other features, including the CASE operator. The CASE operator lets developers perform conditional logic in queries and Data Manipulation Language (DML) commands, like the INSERT, UPDATE, MERGE, and DELETE statements.

SQL3 was a major release that included hierarchical and recursive queries, triggers, and Common Table Expressions (CTEs).

You will learn the basics of good database design, which includes the following:

• Defining single subject fact tables
• Defining mandatory and optional attributes or columns of fact tables
• Defining natural keys of fact tables
• Defining surrogate keys of fact tables
• Defining primary keys of fact tables
• Defining foreign keys of fact tables that let you find related rows in other fact tables
• Defining not null constraints for columns of fact tables
• Defining check constraints for columns of fact tables
• Defining unique constraints for fact tables
• Modeling logical and physical relationships between tables

After introducing the concepts of a database management system, they describe how you model data and work with data. You will learn how to:

• Query data with SELECT statements
• Create tables, views, and procedures with CREATE statements
• Change tables, views, and procedures with ALTER statements
• Add data with INSERT statements
• Change data with UPDATE statements
• Add or change data with the MERGE statements
• Remove data with DELETE statements
• Manage transactions across two or more INSERT, UPDATE, MERGE, or DELETE statementss

The principal focus of these pages will be how you use the Oracle database but they will also show you how to use MySQL and SQL Server where possible.