0845 450 6120

SQL Programming Language Introduction

This is an introductory level course which focuses on standard SQL. Whenever features outside of the standard are required, this course covers these features in two of the most commonly used database products, Oracle Database and Microsoft SQL Server. This comprehensive course provides a good foundation for the product specific Learning Tree courses covering Oracle and SQL Server.

What is SQL?

Structured Query Language, or SQL, is the language used by all relational database management systems to build, manipulate and access database contents. The name is somewhat misleading - while "queries" are the most extensive and complex part of the language, SQL is also used for updating and building databases. SQL is standardised through various versions of the ANSI/ISO SQL standard. However, none of the database products follow the standard 100%. They all have dialectic variations to the language. In order to work with a particular database, the programmer will need to be aware of product-specific features for that particular database system, in addition to the common functionality provided in the standard.

Who will benefit from this course?

This course is valuable for a wide range of professionals including:

  • Developers, analysts, administrators, managers, programmers, consultants and others who design and write applications using relational databases
  • Experienced data processing professionals working with traditional database systems (hierarchical or network), who are now moving to relational databases
  • IT managers and project leaders who need a general overview of the features and functionality of SQL

Which database system is used for the course exercises?

The exercises in this course use two database management systems: Oracle Database 12c and Microsoft SQL Server 2014. The examples and exercises in the course will also work on earlier versions of the two products. Attendees have a choice between these two systems for the hands-on exercises. All examples and exercises are designed to work on both systems and the differences between the dialects will be pointed out during class.

Reset

Learning Objectives

  • Write SQL code based on ANSI/ISO standards to build Microsoft SQL Server or Oracle database structures
  • Update database content with SQL and transaction handling
  • Retrieve data with filter conditions and from multiple tables using various types of join
  • Process data with row and aggregate functions

Hands-On Experience:

  • Creating and modifying tables, constraints and indexes
  • Modifying table contents
  • Retrieving data from tables
  • Applying conditions to filter retrieved rows
  • Joining multiple tables
  • Applying row and aggregate functions

Pre-Requisites

Experience working with a relational database and a familiarity with basic programming concepts are helpful but not required. Familiarity with fundamentals of database concepts is useful but not strictly required.

Prior SQL knowledge is not required. If you have a background in one particular dialect of SQL, you may be interested in learning how that dialect differs from the standard and from other dialects.

Course Content

SQL Overview

  • Outlining SQL as the cornerstone of database activity
  • Applying the ANSI/ISO standards
  • Describing the fundamental building blocks: tables, columns, primary keys and foreign keys

Building the Database Schema

Creating tables and columns

  • Building tables with CREATE TABLE
  • Modifying table structure with ALTER TABLE
  • Adding columns to an existing table
  • Removing tables with DROP TABLE

Protecting data integrity with constraints

  • Guaranteeing uniqueness with primary key constraints
  • Enforcing integrity with foreign key constraints
  • Imposing business rules with check constraints

Improving performance with indexes

  • Expediting data retrieval with indexes
  • Recommending guidelines for index creation

Manipulating Data

Modifying table contents

  • Adding table rows with INSERT
  • Changing row content with UPDATE
  • Removing rows with DELETE

Applying transactions

  • Controlling transactions with COMMIT and ROLLBACK
  • Deploying BEGIN TRANSACTION in SQL Server

Working with the SELECT Statement

Writing Single Table queries

  • Retrieving data with SELECT
  • Specifying column expressions
  • Sorting the result with ORDER BY
  • Handling NULL values in expressions

Restricting rows with the WHERE filter

  • Testing for equality or inequality
  • Applying wildcard characters
  • Avoiding NULL value pitfalls

Querying Multiple Tables

Applying the ANSI/ISO standard join syntax

  • Matching related rows with INNER JOIN
  • Including nonmatched rows with OUTER JOIN
  • Creating a Cartesian product with CROSS JOIN

Combining results with set operators

  • Stacking results with UNION
  • Identifying matching rows with INTERSECT
  • Utilising EXCEPT to find nonmatching rows

Employing Functions in Data Retrieval

Processing data with row functions

  • Conditional formatting with the CASE expression
  • Utilising the CASE expression to simulate IF tests
  • Dealing with NULL values

Performing analysis with aggregate functions

  • Summarising data using SUM, AVG and COUNT
  • Finding the highest/lowest values with MAX and MIN
  • Defining the summary level with GROUP BY
  • Applying filter conditions with HAVING

Constructing Nested Queries

Applying subqueries in filter conditions

  • Correlated vs. noncorrelated subqueries
  • Testing the existence of rows

Including subqueries in expressions

  • Placing subqueries in the column list
  • Creating complex expressions containing subqueries
  • Handling subqueries that return no rows

Developing In-Line and Stored Views

Breaking down complex problems

  • Selecting data from a query result set
  • Subqueries in the FROM clause

Creating views in a database

  • Building reusable code
  • Updateable vs. non-updateable views

Privacy Notice

In order to provide you with the service requested we will need to retain and use your contact information in accordance with our Privacy Notice. If you choose to provide us with this information you explicitly consent to us using the information as necessary to provide the request service to you. If you do not agree please do not proceed to request the service from us.

Marketing Permissions

Would you like to receive our newsletter and other information on products and services which we think will be of interest to you by email. We will always treat your information with care and in accordance with our Privacy Notice. You are free to withdraw this permission at any time.

 

Online Courses

You may prefer an online course if you are looking for a flexible and cost-effective solution. Online courses allow you to study at your own pace, at a time that suits you.

We have the following eLearning options available:

Virtual Classroom

Virtual classrooms provide all the benefits of attending a classroom course without the need to arrange travel and accomodation. Please note that virtual courses are attended in real-time, commencing on a specified date.

Virtual Course Dates

Our Customers Include