SQL Book Chapter Text

Introduction

The goal of writing this book is to teach just enough SQL to enable GoldMine users to get answers to questions they know are inside the “Treasure Box” as my 4 year old son Josh calls it.

I have often been asked by GoldMine users questions like:

• Who are the contacts I haven’t been in touch with for over 6 months?
• Who should be added to my Christmas list ?
• Did the change to my last mass email improve sales?
• How can I rank my customers by sales volume?
• What customers are costing me lots of time but not buying much?
• How do I find duplicate email addresses?
• Contacts with email addresses that returned as undeliverable
• What contacts responded to a mailing and purchased the advertised product

SQL queries can easily answer these questions

I’m a (insert job title here), isn’t this stuff the IT department should be handling? I have two responses. The first is that you are really an information worker by definition if you use GoldMine and the better you can handle data the better you can do you job. The second is that most companies that deploy GoldMine don’t have IT personnel who know how to extract useful information from GoldMine’s database.

If you picture your business like an engine then data is the raw fuel. You pour in data and your business transforms it into actions taken by sales persons, representatives, customer service employees etc.  GoldMine’s SQL Query window is like a turbocharger that increases efficiency and output of that engine.

Many programs provide a query builder that allows users to extract information while hiding the underlying SQL. GoldMine has never developed such a tool. I speculate that the original architect of GoldMine, Elan Susser, decided that “hand built” SQL SELECT statements (the SQL command which this guide covers) aren’t difficult to construct and can organize information in more ways than a query builder can.

This isn’t always the case, there are many times when a query builder would save time even for someone experienced at writing SQL SELECT statements. That’s why some GoldMine third party tools have added an SQL query builder. They are listed in appendix.

Despite the availability of query builders I believe that information workers (sales and marketing, customer support, management etc) can understand SQL Queries despite their non-technical backgrounds.  Learning how to construct SQL Queries and having a basic understanding of what SQL is will help information workers of all levels.

Reasons to learn how to use the SQL SELECT statement

• Understand underlying technology of SQL
• Save time – don’t have to learn various query builders you will encounter
• Instant access to data in GoldMine

SQL is becoming the universal underlying organizing technology for systems that use data. Microsoft has recently announced it will be integrated into Windows. You may soon be using it to find information in your Windows system.

Reasons to learn how to use the SQL SELECT statement

• Understand underlying technology of SQL
• Save time – don’t have to learn various query builders you will encounter
• Instant access to data in GoldMine
• SQL is becoming the universal underlying organizing technology for systems that use data. Microsoft has recently announced it will be integrated into Windows. You may soon be using it to find information in your Windows system.

What You Need to Learn

To make useful SQL queries you need to learn:

·         The SQL SELECT statement and its clauses

·         Where the data is inside your GoldMine system

What Does the Term SQL Mean?

The acronym SQL is probably the first point of confusion for people new to modern database technology. The SQL standard is much more than a Structured Query Language.  At this point the letters S Q L  are don’t have an exact meaning – SQL now represents a language to:

• Modify a database's structure
• Change system security settings
• Add user permissions on databases or tables
• Query a database for information
• Update the contents of a database

How is it pronounced? IBM originally developed Structured English Query Language (acronym SEQUEL), which was pronounced See-kwull. As the above functions besides querying were added to the SQL standard the “English” was dropped and the acronym became SQL pronounced es-cue-ell. You will hear it pronounced either way.

What exactly is SQL?

SQL is a non-procedural language developed to allow users and programmers of databases a way to manipulate, extract and present data without needing to deal with the mechanics of those actions. The database handles all the underlying mechanics so that the user only needs to specify what needs to happen – not how it happens

Explanation of Computer Terms Everyone Assumes You Already Know

I have often been frustrated by books that assume you know what terms like syntax, function, parameter and procedure mean in the context of the computer language being discussed.

Syntax is the way elements of a computer language are organized into useful commands

Functions are mini-programs that are usually structured as a command word that operates on parameters inside parentheses separated by commas.

Parameters are either literal text or refer to data in columns. They are used by functions.

Functions expect something to work on. It might be some literal text such as “New Sale” or it may be a column you want to refer to.

Literals are actual values such as “New York” rather than a reference to a value in a column

Set based is a term that refers to the way SQL operates on data. SQL is designed to work with many rows of data at once versus file based databases that perform all operations one record at time..

Database Schema refers to the design of the database and how data is placed with in that design

Database Terminology

Old School – file, records and fields

New School – Table, rows and columns

Explanation of GoldMine’s use of SQL and BDE –

GoldMine uses the Borland Database Engine to access both the dBase IV files used by GoldMine Business Contact Manager (formerly know as GoldMine Standard) and the Microsoft SQL Server 7.0 or 2000 databases used by the GoldMine Sales and Marketing version also know as GoldMine FrontOffice.

Many people don’t know that the SQL Query window can be used with GoldMine running on dBase IV files. Almost all the standard SQL SELECT statement elements can be used even though you are not using a(n) SQL Server.

If the GoldMine is running on Microsoft SQL Server then all Microsoft SQL Server SELECT statement elements can be used.

If the GoldMine is running on dBase files then the Borland Database Engine (BDE) SELECT statement elements are used.

Documentation

Basic SQL you don’t need to know for GoldMine – INSERT, UPDATE, DELETE

Inside the GoldMine SQL Query Window only the SELECT statement is allowed. That is the only command we will cover in this text

Where GoldMine stores the data you see on screen

GoldMine stores data in some frankly strange ways. The reason for this requires us to travel back in time to the late 1980’s and early 1990’s. The programmers of GoldMine made many decisions that helped performance and saved disk space. PC’s had limited disk space and were about 150 times slower than they are now. They can’t just change it now because so many reports and third party programs are based on GoldMine’s current database structure. 

Data:

contact1

Contact1 is where the top half the GoldMine contact screen stores its data. The 4 “quadrants” of the top half of the GoldMine contact screen divide the data logically by:

1. Name and title infomation
2. Contact via information
3. Address information
4. Key contact information

Contact1 is also the primary contact table of the GoldMine database. Contact history, details, calendar, additional contacts, referrals, linked documents, opportunities and projects all relate back to a contact1 record. The accountno column is the primary key that relates contact1 to:

1. contact2 – user defined fields
2. conthist – contact history
3. cal – calendar items
4. contsupp – additional contacts, referrals, linked documents, email addresses, websites

contact2

Contact2 holds all userdefined fields plus 15 GoldMine system fields

conthist

Conthist holds all history records which are divided into the following different types

contsupp

I have heard long time GoldMine solution partners refer to the contsupp table as GoldMine’s kitchen sink. It

The RECTYPE value H can be linked to records with the RECTYPE value P. Assigning extended information settings to a profile (assigned to a tab, or extended fields used) creates an H record type to store the settings. The profile record stores a character string in the Phone field that matches the H record’s AccountNo field.

contgrps

cal

opmgr

mailbox

GoldMine system related data:

contudef

lookup

Learn how to write basic SQL SELECT statements

Learn how to write advanced SQL SELECT statements

Real World Use

Solution Library

User your creativity

Stored Procedures – Use the power under the hood of SQL Server

Recommended products

Inaplex

Vineyardsoft

GoldBox

Crystal Reports