NUS Extension

Professional & Management




2 Days


Click on the selected date to apply now or download registration form to apply later.

Intake 01
3 - 4 Aug 09

Intake 02
5 - 6 Oct 09

Intake 03
3 - 4 Dec 09




S$800 (Inclusive of GST)


NUS Extension (#12-01 Park Mall)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Exploiting Databases in Excel Using SQL*XL

Extraction of data from databases is often considered a difficult task even to many Excel power users as the databases can come from many heterogeneous sources. Many of such users have to rely on their technical support staff to handle such tasks and ended often at their mercies to have their work done. The domain of exploiting of databases from Excel can now be conducted through both the spreadsheet cells just like a cell value or cell function through SQL*XL which seamlessly reside on the Excel application. However, some knowledge of SQL is required to be able to retrieve, synthesise and analyze the data for reporting and decision-making.

This introductory SQL training course is designed for participants new to writing SQL queries in Excel using SQL*XL. A typical participant will need to learn SQL to build applications or to generate business reports in Excel. For the most part, the SQL learned in the course is applicable to all major databases and the knowledge is therefore transferable to any other relational database management system within an organisation.

This training course will come with teaching notes and reference materials.


Course Objective

  • Understand how relational databases work
  • Learn to use SQL to retrieve data from any database in Excel
  • Learn to synthesise and output reports within Excel


Course Prerequisites

There are no formal prerequisites for this course except a fundamental knowledge of Excel. This course is useful for anyone who works with external relational databases and wishes to learn SQL to enhance on their data analysis, synthesis and reporting in Excel, or those who just wish to learn the fundamentals of SQL.


Course Outline

Relational Database Basics

  • Brief history of SQL
  • Overview of relational databases
  • Anatomy of a relational database

Acquiring & Exporting Data

  • Methods of accessing data
  • Working with Access
  • Working with other DAO Databases

Excel and SQL*XL Basics

  • Connect from Excel to a database
  • Export: Query data into Excel

Creating a Simple Query

  • Introducing SELECT
  • Translating your request into SQL
  • Eliminating duplicate rows
  • Sorting information

Advanced SELECTs

  • Calculated Fields
  • Aggregate Functions and Grouping
  • Built-in Data Manipulation Functions

Filtering Data

  • Refining result using WHERE
  • Defining search conditions
  • Using multiple coditions

Subqueries, Joins and Unions

  • Subqueries
  • Joins
  • Unions

Summarising and Grouping Data

  • Simple totals
  • Grouping data
  • Filtering grouped data

 

 

top