Introduction to SQL

2 DAY COURSE - £595 plus VAT

Synopsis

This course will introduce you to relational databases (RDBMSs), and SQL (Structured Query Language) - the language used to create, modify, manage and query them. Using real world examples you will create tables, set permissions, insert, update and delete data, and retrieve your data using standard SQL. You will also learn about the most common features of the proprietary extensions to the standard language supported by the two most common databases - Microsoft SQL Server and Oracle.

Duration

2 days

Cost

£595 plus VAT

Prerequisites

There are no prerequsites for this course

Detailed course contents

Introduction

  • Databases and SQL
  • Types of databases
  • Common applications of databases
  • Popular Databases
  • What is SQL?
  • Subsets of SQL

Tables

  • What is a table?
  • Creating a table
  • Column definitions
  • Common SQL data types
  • Putting it all together
  • Note on syntax
  • Table and column name rules
  • More DDL

Adding records to a table

  • The INSERT Statement
  • Inserting into DATE columns
  • Inserting the current date

Primary keys and foreign keys

  • Primary keys
  • Choosing a primary key
  • Declaring a primary key
  • Surrogate primary keys
  • Support for surrogate primary keys – SQL Server
  • More on SQL Server Identity columns
  • Support for surrogate primary keys - Oracle
  • More on Oracle sequences
  • Foreign Keys
  • Declaring a foreign key
  • Handling deletions

Other types of constraint

  • Adding constraints to a column
  • Making table columns mandatory
  • The NOT NULL constraint
  • CHECK constraints
  • Declaring a check constraint at column level
  • Declaring a check constraint at table level
  • Creating constraints independently
  • Removing constraints

Selecting data from a table

  • The SELECT statement
  • The WHERE clause
  • Conditional selections
  • More conditional selections
  • The LIKE condition
  • The IN condition
  • The BETWEEN condition
  • Case sensitivity
  • Combining conditions with AND and OR
  • Precedence of AND and OR
  • Negating a predicate with NOT
  • SELECT..INTO
  • Creating a table from a query result
  • Sorting results with ORDER BY
  • More on the ORDER BY clause
  • Shortcuts with the ORDER BY clause

Selecting from multiple tables

  • Table Joins
  • Inner Joins
  • Outer Joins
  • Non standard table join syntax
  • Non-equijoins
  • Table aliases
  • Column aliases
  • Linking multiple tables
  • Self joins

Doing more in the SELECT statement

  • The DISTINCT keyword
  • Mathematical operators
  • Mathematical operators can work on more than one column
  • Mathematical functions
  • Handling NULL values
  • Character functions
  • Tip: Overriding case sensitivity in Oracle
  • Initialise capitals
  • Concatenating character values
  • Character functions can be combined
  • Date functions
  • SQL Server date functions
  • Oracle date functions
  • Date arithmetic
  • Tip: Use TRUNC when comparing dates in Oracle
  • Converting datatypes: Oracle
  • Converting datatypes: SQL Server
  • Tip: Use CONVERT when comparing dates in SQL Server
  • Using functions in the WHERE and ORDER BY clauses

Aggregate Functions

  • The GROUP BY clause
  • The HAVING clause
  • Combining clauses

Subqueries,correlated subqueries, unions and inline views

  • Single row subqueries
  • Multi row subqueries
  • Correlated subqueries
  • UNIONs
  • UNION ALL
  • MINUS and EXCEPT
  • Inline views (derived tables)

Updating and deleting

  • The UPDATE statement
  • The DELETE statement
  • The TRUNCATE statement
  • Views
  • Creating a view
  • Manipulating a view

Data access

  • Controlling access with DCL
  • Users
  • Roles
  • The GRANT command
  • The REVOKE command
  • Accessing another user’s objects
  • Easier access with SYNONYM s

Transaction Control

  • Transactions
  • COMMIT ting changes
  • Rolling back changes


Availability

Click on a date to book it

Can't see a suitable date or location?
This course is also available as a one on one course or as a private course.
Click here to register your interest in this course and we will keep you informed when any new dates are added.

FAQs

  • What are the course times?
    • Please arrive for a 9.30am start on the first day of your course. Subsequent days will usually start at 9am but check with your instructor. The course will end at 5pm each day.

  • Do I need to bring anything?
    • No, everything is provided, you only have to bring yourself.

  • How can I pay?
    • Once you have booked you will receive a confirmation email, and shortly after that, an invoice. The invoice is payable within 30 days and payment can be made by bank transfer (BACS etc), cheque or card.

  • Is there a dress code?
    • There is no dress code for any of our courses.

  • Are refreshments provided?
    • Mid-morning and mid-afternoon refreshments will be provided. Lunch is not provided but all our venues are in central locations.

  • What is the cancellation policy?
    • Cancellations received within 7 days of making your booking or more than 21 days before the scheduled start date will not incur any charge. For cancellations received between 7 and 21 days before the scheduled start date a 50 percent charge will apply. For cancellations received less than 7 days before the scheduled start date a 100 percent charge will apply.


Other courses