Advanced SQL  «Prev  Next»
Lesson 1

SQL Course Introduction

Characteristics of SQL

The Structured Query Language is a special-purpose programming language designed for managing data held in a (RDBMS)relational database management system. Originally based upon relational algebra and tuple relational calculus, SQL consists of
  1. (DDL) data definition language and
  2. (DML) data manipulation language.
The scope of SQL includes (CRUD) create, read, update, and delete data .
SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks." Despite not entirely adhering to the relational model as described by Codd, it became the most widely used database language.
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Since then, the standard has been enhanced several times with added features. Despite these standards, the SQL code is not completely portable among different database systems.


Characteristics of SQL Database Engines?

SQL databases tend to be mysterious when it comes to the information that is stored within them.
The SQL Database Engine is the core service for storing, processing, and extracting data. The SQL Database Engine provides access and rapid transaction processing to meet the requirements of applications. The SQL Database Engine can be used to create relational databases for
  1. online transaction processing or
  2. online analytical processing data.

It is likely that you have a number of applications that put information into your various SQL database engines.
From this information set, you need to be able to retrieve meaningful information and that is where this course comes in.
This hands-on class focuses on retrieving information from your systems in a way that is more insightful in terms of getting
  1. what you need from the system,
  2. working with multiple tables, and
  3. doing it easily.

Making Low-Impact DDL Changes

If you only have one database available, you need to be careful when making changes to your tables and indexes. When you create or modify an object, you must first acquire a DDL lock on the object to prevent other types of access against the object. Because of this locking issue, you will need to avoid changing objects while users are accessing them. In addition, changing an object will invalidate the stored procedures that reference the object.
Although you should ideally make changes to objects at times when users are not accessing them, your maintenance time windows may not be sufficiently large enough to support that requirement. You should always plan for enough time 1) to make the change, 2) test the change, and 3) recompile any objects that the change impacts.

Course goals

In this course, we will cover:
  1. Advanced uses of the SELECT statement
  2. The DISTINCT keyword
  3. Views
  4. String, arithmetic, and date functions

March 13, 2024, 1:01 pm