Sql Tutorial

SQL Tutorial

Table of Contents Introduction SQL Tables Scalable SQL for Windows Definition of Database Tables used by Solomon IV Reading the Reports SQLScope The Menu Bar The View Option Database in Use Conventions Used in this Guide Errors Creating Tables Importing Data Retrieving All Data Retrieving a Single Column Retrieving Multiple Columns Formatting Numbers Ordering Rows Displaying Rows in Descending Order Ordering Multiple Columns Retrieving Specific Rows Multiple Conditions Joining Tables Updating Tables Deleting Rows Inserting Rows

Pervasive SQL If you are interested in learning about Pervasive SQL, this is the site. Our online tutorial provides a basic introduction to Pervasive SQL. It covers the create, import, retrieve, order, update, delete, insert, and join commands.

Introduction

This tutorial is on Pervasive SQL, also known as Scalable SQL, version 3.01 by Btrieve Technologies, Inc. It was developed for users of Solomon IV version 2.0x; however, anyone using Scalable SQL will find it useful. You will need a copy of Scalable SQL to complete the exercises. SQL

SQL -- pronounced sequel -- stands for Structured Query Language. It is the language used to access Scalable SQL for Windows data. With SQL, you can query your database in a variety of ways, using English-like statements. Tables

A table is a set of columns and rows. Each column is referred to as a field. Each value in a field represents a single type of data. For example, a table might have three fields: name, city, and state. The table will consist of three columns: one for name, one for city, and one for state. For every row in the table, the name field contains the name, the city field contains the city, and the state field contains the state. Scalable SQL for Windows

Scalable SQL for Windows contains tables used to store, retrieve, and modify data. As you enter data into Solomon IV, it is stored in Scalable SQL tables. You can retrieve, add to, and modify that data by using Scalable SQL for Windows.

Definition of Database

A database is a collection of related data. It can also be viewed as a collection of related tables. Tables Used by Solomon IV

To find a list of the tables used by Solomon IV, follow the steps outlined below: 1. 2. 3. 4.

Log in to Solomon IV. Click on Help on the Menu bar. Click on Solomon Data Fields on the drop-down menu. Click on Solomon IV Application Database Schema. You will be presented with a screen that lists each module. 5. To see a list of the tables used by a particular module, click on the module name. 6. Then to see a list of the fields in a specific table, click on the table name. You will be presented with a report. 7. To print the report, click on Print. Reading the Reports

SQL reports look similar to the ones shown here. Vendor Training TrnVendor

Add1

String

30

Vendor street address

City

String

30

Vendor city

CurrBal

Float

8

Current outstanding balance

ExpAcc

String

10

Expense account

Name

String

30

Vendor name

State

String

3

Vendor city

Vendid

String

10

Vendor ID

AP Training Documents TrnAPDoc CuryDocBal

Float

8

Current document balance

DocDate

Date

4

Date the document was created

OpenDoc

Logical

2

Is the document unpaid?

OrigDocAmt

Float

8

Amount of the original document

RefNbr

String

10

Reference number

Vendid

String

10

Vendor ID

The first column of the report lists field names. The second column indicates field types. The third column indicates field lengths. The fourth and final column provides a description of the field contents. The tables just described are similar to the ones used by Solomon IV. We will create these tables later and use them in our exercises. Field Type Field types define the kind of data the field can contain. Scalable SQL supports four major field types: String Numeric Date and time Boolean

String -- A string field stores characters. It can also store numbers; however, a number cannot be used in mathematical calculations unless it is first converted to a numeric. Scalable SQL supports the following types of strings: character lstring1 zstring1 note1 lvar 1 Numeric -- A numeric field stores numbers. You can use the field to perform mathematical calculations. Scalable SQL supports the following types of numeric fields: Decimal1 Float1 Integer1 Numeric1 Logical -- A logical field will accept one of only two specified values. Examples are true or false and yes or no. Date and time -- Date and time fields accept the date and time. Length The length column specifies the maximum number of characters the field will accept. SQLScope

SQLScope is the interface used to access and manipulate data stored in a Scalable SQL for Windows database. The following sections describe the interface. 1. To log in to SQLScope:

2. Open SQLScope. 3. In the Database Name field, enter the name of the database you wish to open. Note: For these exercises, use a test database. Type Master in the User Name field. 5. If a password has been assigned, enter the password in the Password field. 6. Click on Login. 4.

The Menu Bar

The Menu bar is located at the top of the SQLScope screen. It begins with the word File and continues with Edit, View, Settings, Database, Run, Window, and Help. You use the menus to give instructions to the software. To use a menu, you point with your mouse at the menu option and click the left mouse button. A dropdown menu will appear. To select an option, highlight the item on the drop-down menu and press Enter. An ellipse after a menu item signifies additional options; if you select that menu item, a dialog box will appear. The View Options

The View options on the Menu bar control how the SQL Scope interface displays to the user. The Lists option displays table names, field names, and templates. The Status Bar displays messages and is located at the very bottom of the screen. Move To buttons display to the left of the SQL text box; use Move To buttons to move around your SQL text screen quickly. Run buttons display to the right of the SQL text box; use Run buttons to run your SQL statement. For these lessons, you should turn on all of these options by following these instructions: 1. Click on View. 2. Click on any item that does not have a checkmark next to it.

Note: You might have to perform this process several times, because only one option can be selected at a time. So click on View and then click on any item that does not have a checkmark next to it. Then click on View again and click on any additional item that does not have a checkmark next to it. Continue until all items are checked. Database in Use

Directly below the Menu bar is the Database field. The name of the current database is displayed in this field. Tables The Tables box lists all of the tables in the database. Clicking on a table name will place the table name in the SQL text box. Fields The Fields box lists all of the fields in a particular table. Clicking on a field name will place the field name in the SQL text box. To see a list of the fields in a table: 1. 2. 3. 4.

Go to the Tables box. Highlight the table of interest. Click on the >> icon. The field names for the table you highlighted will appear in the Field box.

Templates The Templates box provides the syntax for commands. If you forget the syntax for a particular command, click on the command in the template field for a reminder. Refer to your Scalable SQL for Windows manual for further information. The conventions used in

this guide are not the same as those used by Scalable SQL for Windows. Conventions Used in this Guide

This guide uses the following conventions to illustrate SQL syntax.

Regular type

Standard SQL clause.

Bold

User-specified information.

Variable repeated three times

Use as many variables as needed.

...

Use as many variables as needed.

/

Use any one of the items separated by the slash.

<>

Item between brackets is optional.

Errors

SQL consists of commands you type into the SQLScope text box. If you receive an error message after entering a SQL command, check your spelling and syntax. 1

Explanation of these terms is beyond the scope of this course.

Basic SQL Commands If you need an introduction to basic SQL commands, this is the site. Our free online SQL tutorial covers SQL syntax and is an excellent SQL command reference. Take our tutorial and learn the SQL command language.

Creating Tables

You must create your tables before you can enter data into them. Use the Create Table command. Syntax: Create table tablename using filename (fieldname fieldtype(length), fieldname fieldtype(length), fieldname fieldtype(length)); Explanation:    

 

Table names cannot exceed 20 characters. Table names must be unique within a database. Field names must be unique within a table. You may specify the data file to use. If you do not specify a data file, Scalable SQL will create one, using a .dat extension. The list of fields must be enclosed in parentheses. You must specify the field type. Examples: Char -- a character string Float -- a number Date -- a date field Logical -- a logical field

   

You must specify the field length. The field length must be enclosed in parentheses. You must separate field definitions with commas. You must end each SQL statement with a semicolon.

Example:

Create tables TrnVendor and TrnAPDoc. We will use these tables in the exercises that follow: 1. In the SQL text box, type: Create table TrnVendor using 'TVendor.dat' (VendId char(10), Name char(30), Add1 char(30), City char(30), State char(3), CurrBal float(8), ExpAcct char(10)); Create table TrnAPDoc using 'TAPDoc.dat' ( VendId char(10), RefNbr char(10), DocDate date(4), OrigDocAmt float(8), CuryDocBal float(8), OpenDoc logical(2)); 2. Execute the commands. To create the first table, click on First, the Run button located to the right of the SQL text box. 3. You should receive the following message: Scalable SQL informative status = -105. The CREATE statement completed successfully. 4. Click on Next to create the second table. 5. You should receive the following message: Scalable SQL informative status = -105. The CREATE statement completed successfully.

Click on Stop to return to the original screen.

SQL Database Tutorial If you are looking for a SQL database tutorial, this is the site. Our free SQL database tutorial makes learning SQL easy. We provide syntax and examples for each SQL statement we cover. Our SQL tutorial is an excellent SQL quick reference.

Importing Data

You can import data into SQLScope from another data source by using the Insert command. Before completing the following exercises, you must import data into the tables you created. The example given in this section will step you through the process. Syntax: Insert into tablename (fieldname, fieldname, fieldname) Values (@fieldname, @fieldname, @fieldname); Explanation: •

Before you can import, you must create a data file in one of the following formats: o Btreive unformatted (.unf) -- File layout.  Logical, date, and time fields are in binary format.  Each row is preceded by its length, followed by a comma.  Each row is terminated with a carriage return. o Standard Data Format (.sdf) -- File layout.  All data is represented in ASCII characters.  A comma is used to separate fields.  Character fields are enclosed in double quotes.  Each row is terminated with a carriage return.

ASCII  Each row is preceded by a comma or a space.  Each field and each row is a specified length.  Each row is terminated with a carriage return. o DIF (.dif)  Each file is made up of two sections: header and data.  Each row has a standard length.  Each field has a standard length. The Values clause is used to specify substitution variables. Substitution variables must be preceded by an @ sign. Each field in the Insert statement must be in the same order as the corresponding field in the data file. o

• • •

To import data: Choose Import from the File menu to open the Import dialog box. 1. In the File Type box, specify the type of file you want to import. 2. In the Drive box, specify the appropriate drive. 3. In the Directories box, specify the appropriate directory. 4. In the File Name box, specify the name of the file from which to import. 5. If you would like the process to run in the background while you continue working, click on the Background field. 6. Click on Import to begin the import process. Example: Import data into the TrnVendor and TrnAPDoc files. 1. Download the files: Click here to download a zip.file

2. Type the following in the SQL text box: Insert into TrnVendor (Vendid, Name, Add1, City, State, CurrBal, ExpAcct ) Values (@Vendid, @Name, @Add1, @City, @State, @CurrBal, @ExpAcct) 3. Click on File on the Menu bar. 4. Click on Import on the drop-down menu to open the Import dialog box. 5. In the Drive box, specify the drive to which you downloaded the files in step #1. 6. In the Directories box, specify the directory to which you downloaded the files in step #1. 7. In the File Name field, type imvendor.sdf. 8. Click on Import. 9. You should receive the following message: Importing has completed. Records: 30 10.Type the following in the SQL Text box: Insert into TrnAPDoc (Vendid, RefNbr, DocDate, OrigDocAmt, CuryDocBal, OpenDoc) Values (@Vendid, @RefNbr, @DocDate, @OrigDocAmt, @CuryDocBal, @OpenDoc) 1. Click on File on the Menu bar. 2. Click on Import on the drop-down menu to open the Import dialog box.

3. In the Drive box, specify the drive to which you downloaded the files in step #1. 4. In the Directories box, specify the directory to which you downloaded the files in step #1. 5. In the File Name field, type imapdoc.sdf. 6. Click on Import. 7. You should receive the following message: Importing has completed. Records: 50

SQL Select SQL select statements are used to retrieve data from SQL databases. Take our free online SQL tutorial and learn SQL. Among the many commands we teach you are the SQL commands used to select and order data contained in a SQL database.

Retrieving All Data

Select statements are used to retrieve data from SQL tables. The Select statement illustrated below retrieves all of the columns and rows from the named table. Syntax: Select * from tablename; Explanation: 

 

A Select statement is a SQL statement that begins with the word "select." Select statements are used to retrieve data from SQL tables. An asterisk after the word "select" means retrieve all fields (columns).





The name of the table from which you are retrieving data is specified in the From clause. Use a semicolon to signify the end of a SQL statement.

Example: Retrieve all of the data from the TrnVendor table. 1. In the SQL text box, type: Select * from TrnVendor; 2. Execute the command by clicking on First, the Run button located to the right of the SQL text box. The results will display on the screen. 3. Use the horizontal scroll bar to move across the screen. The fields in the Vendor table will display alphabetically. Use the vertical scroll bar to move up and down the screen. 4. Click on Stop to return to the original screen. Results: VendId Name

Add1

City

State

TV001 Wet Off Towels

66 Keep Dry Way

Toledo

OH 1.31300E+001

7230

TV002 The Games All Here

85 Olympic Drive

Warren

NY 4.53170E+002

4110

TV003 BedMakers Linen

97 Comfort Zone

Chicago

IL

5.00000E+002

4120

TV004 Bed Room Furniture, Inc.

4 Hide A Way

Mercer

NJ 3.21700E+002

6080

TV005 Ray Block

90 Detroit Sunscreen Blvd.

MI

1.00123E+003

7230

NJ 9.67240E+002

6510

TV006 Mosquito No 99

Park Ridge

CurrBal ExpAcct

Bite TV007 Spot Out

Repellent Way 77 Sunset Detroit Strip

MI

1.60654E+003

6500

TV008 Big Tree 44 Broad Landscaping Street

Monroe

NY 3.42980E+002

4110

TV009 Hit the Deck 42 Deck Lane

Toledo

OH 2.34500E+001

4030

TV010 The Soda Factory

101 Fizz Up Blvd.

Orlando

FL

4110

TV011

1 Trim Way

Mercer

NJ -1.09230E+002

7230

TV012 Paper People

80 Pulp Fiction Drive

New York

NY 6.17000E+002

6080

TV013 Softer Software

44 User Friendly Dr.

Monroe

NY 9.87367E+003

7230

TV014 Counter Productive

26 Shelf Street

Toledo

OH 4.28900E+001

6510

TV015 No Waste Disposal

5819 Street Road

Park Ridge

NY 1.72098E+003

6500

TV016 Music Maker 54 Note Street

Chicago

IL

4.10914E+003

4110

TV017 Food Four

67 Janice Blvd.

Freeport

MA 6.42980E+002

4110

TV018 Computer Bytes

10 Electric Youngstown WA 3.45090E+002 Dog Street

7230

TV019 Bayshore Consulting

6479 Bay Drive

Bayshore

CA 4.98050E+002

4110

TV020 BayCon Group

1 Tutorial Lane

Burlington

NJ 5.10000E+001

4120

Dallas

TX 2.00000E+001

6080

The Freelance

TV021 Cooperative 101 Operatives Systems Go

0.00000E+000

TV022 Legal Lookup

77 Service Houston Blvd.

TX 3.21516E+003

7230

TV023 World Wide Learning U

11 Boyardee Drive

Freeport

MA 7.71060E+002

6510

TV024 Enterprise Transport

98 New Worlds Blvd.

Freeport

MA 1.23498E+003

6500

TV025 Against the

54 All

Orlando

FL

4110

6.47200E+001

Tide

Detergent Way

TV026 Clampett Oil 4268 Jethro Blvd.

Dallas

TX 0.00000E+000

4030

TV027 Narrow Nest 23 Georgia Drive

Toledo

OH 1.60900E+003

4110

TV028 Distant Horizons

1917 Douglas Way

Chicago

IL

2.69800E+001

7230

TV029 Paltry Play

1818 Johnson Blvd.

Chicago

IL

1.00000E+003

6080

TV030 Make Shift Tilts

4 Holiday Way

Chicago

IL

1.79323E+003

7230

SQL Language The SQL language is used to access data. With SQL, you can query your database in a variety of ways, using English-like statements. Learning SQL is easy. Our online tutorial teaches you SQL commands.

Retrieving a Single Column

You can use SQL to retrieve a single column of data. Syntax: Select fieldname from tablename; Explanation: 



Instead of an asterisk, the name of the field is specified in the Select clause. Field names are not case-sensitive. You can type field names in uppercase or lowercase letters.

Example:

Retrieve the vendor name from each record: 1. In the SQL text box, type: Select Name from TrnVendor; 2. Click on First, the Run button located to the right of the SQL text box. The results will display on the screen. 3. Use the vertical scroll bar to move up and down the screen. 4. Click on Stop to return to the original screen. Results: Name Wet Off Towels The Games All Here BedMakers Linen Bed Room Furniture, Inc. Ray Block Mosquito No Bite Spot Out Big Tree Landscaping Hit the Deck The Soda Factory The Freelance Paper People Softer Software Counter Productive No Waste Disposal Music Maker Food Four Computer Bytes Bayshore Consulting BayCon Group Cooperative Operatives

Legal Lookup World Wide Learning U Enterprise Transport Against the Tide Clampett Oil Narrow Nest Distant Horizons Paltry Play Make Shift Tilts

Learn SQL Want to learn SQL? Our free online SQL tutorial teaches you the SQL commands you need to query your SQL database. We provide examples of SQL syntax so you can learn SQL quickly. Our SQL tutorial is a great SQL command reference.

Retrieving Multiple Columns

You can use SQL to retrieve multiple columns. Syntax: Select fieldname, fieldname, fieldname from tablename; Explanation: 

  

When retrieving multiple columns, specify each field name. A comma must separate field names. The columns will display in the order you select them. Numbers display in scientific notation.

Example:

Retrieve the vendor ID, name, and current balance for each record: 1. In the SQL text box, type: Select VendId, Name, CurrBal from TrnVendor; 2. Execute the command by clicking on First, the Run button located to the right of the SQL text box. The results will display on the screen. 3. Use the vertical scroll bar to move up and down the screen. 4. Click on Stop to return to the original screen Results: VendId

Name

CurrBal

TV001

Wet Off Towels

1.31300E+001

TV002

The Games All Here

4.53170E+002

TV003

BedMakers Linen

5.00000E+002

TV004

Bed Room Furniture, Inc.

3.21700E+002

TV005

Ray Block

1.00123E+003

TV006

Mosquito No Bite

9.67240E+002

TV007

Spot Out

1.60654E+003

TV008

Big Tree Landscaping

3.42980E+002

TV009

Hit the Deck

2.34500E+001

TV010

The Soda Factory

0.00000E+000

TV011

The Freelance

-1.09230E+002

TV012

Paper People

6.17000E+002

TV013

Softer Software

9.87367E+003

TV014

Counter Productive

4.28900E+001

TV015

No Waste Disposal

1.72098E+003

TV016

Music Maker

4.10914E+003

TV017

Food Four

6.42980E+002

TV018

Computer Bytes

3.45090E+002

TV019

Bayshore Consulting

4.98050E+002

TV020

BayCon Group

5.10000E+001

TV021

Cooperative Operatives

2.00000E+001

TV022

Legal Lookup

3.21516E+003

TV023

World Wide Learning U

7.71060E+002

TV024

Enterprise Transport

1.23498E+003

TV025

Against the Tide

6.47200E+001

TV026

Clampet Oil

0.00000E+000

TV027

Narrow Nest

1.60900E+003

TV028

Distant Horizons

2.69800E+001

TV029

Paltry Play

1.00000E+003

TV030

Make Shift Tilts

1.79323E+003

Learning SQL Learning SQL is easy. Our free online SQL tutorial teaches you the SQL commands you need. We provide examples of SQL syntax so you can learn SQL quickly. Our SQL tutorial is a great SQL command reference.

Formatting Numbers

By default, Scalable SQL displays numbers using scientific notation. To change the format, you must add a mask. Syntax: Select fieldname<mask>, fieldname<mask>, fieldname<mask> from tablename; Explanation:   

Number masks tell SQL how to display numbers. The symbols in the table below define the format mask. Enclose number masks in brackets.

Number Masks 9

Display number.

Z

Display number, drop leading zeros.

+

Display a plus in front of positive numbers. Display a negative in front of negative numbers.

-

Display a negative in front of negative numbers. Display nothing in front of positive numbers.

()

Display negative numbers enclosed in parentheses.

.

Display decimal point.

$

Display dollar sign.

,

Use comma to separate thousands.

Example: Retrieve the vendor ID, name, and current balance for all vendors. Format the current balance: 1. In the SQL text box, type: Select VendId, Name, CurrBal [($Z,ZZZ,ZZZ.99)] from TrnVendor;

2. Execute the statement by clicking on First, the Run button located to the right of the SQL text box. The results will display on the screen. 3. Click on Stop to return to the original screen. Results: VendId

Name

CurrBal

TV001

Wet Off Towels

$13.13

TV002

The Games All Here

$453.17

TV003

BedMakers Linen

$500.00

TV004

Bed Room Furniture, Inc.

$321.70

TV005

Ray Block

$1,001.23

TV006

Mosquito No Bite

$967.24

TV007

Spot Out

$1,606.54

TV008

Big Tree Landscaping

$342.98

TV009

Hit the Deck

$23.45

TV010

The Soda Factory

$0.00

TV011

The Freelance

($109.23)

TV012

Paper People

$617.00

TV013

Softer Software

$9,873.67

TV014

Counter Productive

$42.89

TV015

No Waste Disposal

$1,720.98

TV016

Music Maker

$4,109.14

TV017

Food Four

$642.98

TV018

Computer Bytes

$345.09

TV019

Bayshore Consulting

$498.05

TV020

BayCon Group

$51.00

TV021

Cooperative Operatives

$20.00

TV022

Legal Lookup

$3,215.16

TV023

World Wide Learning U

$771.06

TV024

Enterprise Transport

$1,234.98

TV025

Against the Tide

$64.72

TV026

Clampett Oil

$0.00

TV027

Narrow Nest

$1,609.00

TV028

Distant Horizons

$26.98

TV029

Paltry Play

$1,000.00

TV030

Make Shift Tilts

$1,793.23

SQL Tutor Looking for a SQL tutor? Our free online SQL tutorial teaches you SQL. We provide examples of SQL syntax so you can learn SQL quickly. Our SQL tutorial is also a great SQL command reference.

Ordering Rows

Data is stored in Scalable SQL in no particular sequence. If you want to see your data displayed in sequence, you must add an Order By clause to your Select statement. Syntax: Select */fieldname<mask> ... from tablename order by fieldname; Explanation: 

The Order By clause tells SQL you want the specified fields displayed in ascending order (ordered from A to Z, 1 to 100).

Example: Retrieve the city, vendor Id, and name from the TrnVendor table. Order your data by city: 1. In the SQL text box type: Select City, VendId, Name from TrnVendor order by City; 2. Execute the SQL statement.

Results: City

VendId

Name

Bayshore

TV019

Bayshore Consulting

Burlington

TV020

BayCon Group

Chicago

TV003

BedMakers Linen

Chicago

TV016

Music Maker

Chicago

TV028

Distant Horizons

Chicago

TV029

Paltry Play

Chicago

TV030

Make Shift Tilts

Dallas

TV021

Cooperative Operatives

Dallas

TV026

Clampett Oil

Detroit

TV005

Ray Block

Detroit

TV007

Spot Out

Freeport

TV017

Food Four

Freeport

TV023

World Wide Learning U

Freeport

TV024

Enterprise Transport

Houston

TV022

Legal Lookup

Mercer

TV004

Bed Room Furniture, Inc.

Mercer

TV011

The Freelance

Monroe

TV008

Big Tree Landscaping

Monroe

TV013

Softer Software

New York

TV012

Paper People

Orlando

TV010

The Soda Factory

Orlando

TV025

Against the Tide

Park Ridge

TV006

Mosquito No Bite

Park Ridge

TV015

No Waste Disposal

Toledo

TV001

Wet Off Towels

Toledo

TV009

Hit the Deck

Toledo

TV014

Counter Productive

Toledo

TV027

Narrow Nest

Warren

TV002

The Games All Here

Youngstown

TV018

Computer Bytes

SQL Quick Reference

Looking for a SQL quick reference? Our free online SQL tutorial provides examples of SQL commands and SQL syntax. Bookmark our site and use our SQL tutorial as a SQL quick reference.

Displaying Rows in Descending Order

If you would like to see fields displayed in descending order, follow the field name with "desc" in the Order By clause. Syntax: Select */fieldname ... from tablename order by fieldname <desc> ... Explanation: By default, the Order By clause tells SQL you want the field displayed in ascending order. Typing "desc" after the field name in the Order By clause tells SQL you want the data in the field displayed in descending order (Z to A, 100 to 1).





Example: Retrieve the city, vendor ID, and name from the TrnVendor table. Order your data by city in descending order: 1. In the SQL text box, type: Select City, VendId, Name from TrnVendor order by City desc; 2. Execute the SQL statement. Results City

VendId

Name

Youngstown

TV018

Computer Bytes

Warren

TV002

The Games All Here

Toledo

TV027

Narrow Nest

Toledo

TV014

Counter Productive

Toledo

TV009

Hit the Deck

Toledo

TV001

Wet Off Towels

Park Ridge

TV015

No Waste Disposal

Park Ridge

TV006

Mosquito No Bite

Orlando

TV025

Against the Tide

Orlando

TV010

The Soda Factory

New York

TV012

Paper People

Monroe

TV013

Softer Software

Monroe

TV008

Big Tree Landscaping

Mercer

TV011

The Freelance

Mercer

TV004

Bed Room Furniture, Inc.

Houston

TV022

Legal Lookup

Freeport

TV024

Enterprise Transport

Freeport

TV023

World Wide Learning U

Freeport

TV017

Food Four

Detroit

TV007

Spot Out

Detroit

TV005

Ray Block

Dallas

TV026

Clampett Oil

Dallas

TV021

Cooperative Operatives

Chicago

TV030

Make Shift Tilts

Chicago

TV029

Paltry Play

Chicago

TV028

Distant Horizons

Chicago

TV016

Music Maker

Chicago

TV003

BedMakers Linen

Burlington

TV020

BayCon Group

Bayshore

TV019

Bayshore Consulting

SQL Language Reference Looking for a SQL language reference? Our free online SQL tutorial provides examples of SQL commands and SQL syntax. Bookmark our site and use our SQL tutorial as a SQL language reference.

Ordering Multiple Columns

When ordering your data, you can have multiple sort levels. For example, you can order your data by city and then by name within the city. Syntax: Select fieldname, fieldname, fieldname from tablename order by fieldname <desc>, fieldname <desc>, fieldname <desc> Explanation: 





By default, the Order By clause orders the specified fields in ascending order. Typing "desc" after a field name in the Order By clause tells SQL you want the data in the specified field displayed in descending order (Z to A, 100 to 1). The first field name specified is the primary sort order, the second field name specified is the secondary sort order, and so on ...

Example: Retrieve the city, name, and vendor ID from the TrnVendor table. Order your data by city and then by name within city: 1. In the SQL text box, type: Select City, Name, VendId from TrnVendor order by City, Name; 2. Execute the SQL statement.

Results City

Name

VendId

Bayshore

Bayshore Consulting

TV019

Burlington

BayCon Group

TV020

Chicago

BedMakers Linen

TV003

Chicago

Distant Horizons

TV028

Chicago

Make Shift Tilts

TV030

Chicago

Music Maker

TV016

Chicago

Paltry Play

TV029

Dallas

Clampett Oil

TV026

Dallas

Cooperative Operatives

TV021

Detroit

Ray Block

TV005

Detroit

Spot Out

TV007

Freeport

Enterprise Transport

TV024

Freeport

Food Four

TV017

Freeport

World Wide Learning U

TV023

Houston

Legal Lookup

TV022

Mercer

Bed Room Furniture, Inc.

TV004

Mercer

The Freelance

TV011

Monroe

Big Tree Landscaping

TV008

Monroe

Softer Software

TV013

New York

Paper People

TV012

Orlando

Against the Tide

TV025

Orlando

The Soda Factory

TV010

Park Ridge

Mosquito No Bite

TV006

Park Ridge

No Waste Disposal

TV015

Toledo

Counter Productive

TV014

Toledo

Hit the Deck

TV009

Toledo

Narrow Nest

TV027

Toledo

Wet Off Towels

TV001

Warren

The Games All Here

TV002

Youngstown

Computer Bytes

TV018

SQL Command Reference

Looking for a SQL command reference? Our free online SQL tutorial provides examples of SQL commands and SQL syntax. Bookmark our site and use our SQL tutorial as a SQL command reference.

Retrieving Specific Rows

So far, you have been retrieving all of the rows in the table. You can, however, specify which rows you wish to retrieve. For example, you could retrieve only those vendors who are in Chicago. Syntax: Select */fieldname ... from tablename where fieldname =/!=/<>/>/>=/... Explanation: 

You can use any of the following logical operators in your Where clause to restrict the rows you retrieve. Logical Operators =

Equal to

!= or <>

Not equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to



in

Equal to any item in a list

not in

Not equal to any item in a list

between

Between two values, greater than or equal to one and less than or equal to the other

not between

Not between two values

begins with

Begins with specified value

contains

Contains specified value

not contains

Does not contain specified value

is null

Is blank

is not null

Is not blank

like

Like a specified pattern. % means any series of characters. _ means any single character.

not like

Not like a specified pattern. % means any series of characters. _ means many single character.

In the Where clause, when referring to variables in character fields, you must enclose the values in single quotes. Example: where City = 'Chicago'



Variables that refer to numeric fields should not be enclosed in quotes.

Example: where CurrBal > 1200 Examples: Retrieve all vendors located in Chicago. 1. In the SQL text box, type: Select City, Name, VendId from TrnVendor where City = 'Chicago';' 2. Execute the SQL statement. Results: City

Name

VendId

Chicago

BedMakers Linen

TV003

Chicago

Music Maker

TV016

Chicago

Distant Horizons

TV028

Chicago

Paltry Play

TV029

Chicago

Make Shift Tilts

TV030

Retrieve all vendors who are not located in Chicago. Order the results by city. 1. In the SQL text box, type: Select City, Name, VendId from TrnVendor where City <> 'Chicago' order by City; 2. Execute the SQL statement.

Results: City

Name

VendId

Bayshore

Bayshore Consulting

TV019

Burlington

BayCon Group

TV020

Dallas

Cooperative Operatives

TV021

Dallas

Clampett Oil

TV026

Detroit

Ray Block

TV005

Detroit

Spot Out

TV007

Freeport

Food Four

TV017

Freeport

World Wide Learning U

TV023

Freeport

Enterprise Transport

TV024

Houston

Legal Lookup

TV022

Mercer

Bed Room Furniture, Inc.

TV004

Mercer

The Freelance

TV011

Monroe

Big Tree Landscaping

TV008

Monroe

Softer Software

TV013

New York

Paper People

TV012

Orlando

The Soda Factory

TV010

Orlando

Against the Tide

TV025

Park Ridge

Mosquito No Bite

TV006

Park Ridge

No Waste Disposal

TV015

Toledo

Wet Off Towels

TV001

Toledo

Hit the Deck

TV009

Toledo

Counter Productive

TV014

Toledo

Narrow Nest

TV027

Warren

The Games All Here

TV002

Youngstown

Computer Bytes

TV018

Retrieve all vendors in Mercer, New York, or Park Ridge. 1. In the SQL text box, type: Select City, Name, VendId from TrnVendor

where City in ( 'Mercer', 'New York', 'Park Ridge') order by City; 2. Execute the SQL statement. Results: City

Name

VendId

Mercer

Bed Room Furniture, Inc.

TV004

Mercer

The Freelance

TV011

New York

Paper People

TV012

Park Ridge

Mosquito No Bite

TV006

Park Ridge

No Waste Disposal

TV015

Retrieve all vendors whose names begin with "Co." 1. In the SQL text box, type: Select Name, City, VendId from TrnVendor where Name begins with 'Co' order by Name; 2. Execute the SQL statement. Results: Name

City

VendId

Computer Bytes

Youngstown

TV018

Cooperative Operatives

Dallas

TV021

Counter Productive

Toledo

TV014

Retrieve all vendors whose city ends with the letters "do."

1. In the SQL text box, type: Select Name, City, VendId from TrnVendor where City like '%do' order by City; 2. Execute the SQL statement. Results: Name

City

VendId

The Soda Factory

Orlando

TV010

Against the Tide

Orlando

TV025

Wet Off Towels

Toledo

TV001

Hit the Deck

Toledo

TV009

Counter Productive

Toledo

TV014

Narrow Nest

Toledo

TV027

Retrieve all vendors with a current balance between 500 and 1000 dollars. 1. In the SQL text box, type: Select VendId, Name, CurrBal [ZZ,ZZZ.99] from TrnVendor where CurrBal between 500 and 1000 order by CurrBal; 2. Execute the SQL statement. Results: VendId

Name

CurrBal

TV003

BedMakers Linen

500

TV012

Paper People

617

TV017

Food Four

642.98

TV023

World Wide Learning U

771.06

TV006

Mosquito No Bite

967.24

TV029

Paltry Play

1,000

SQL Syntax Need help with your SQL syntax? Our free online SQL tutorial provides examples of SQL commands and SQL syntax. Bookmark our site, learn SQL, and use our SQL tutorial as a SQL command reference.

Multiple Conditions

You can add multiple criteria to your Where clauses by using "and" or "or." Syntax: Select */fieldname<mask> ... from tablename ... where fieldname =/!=/<> ... value and/or fieldname =/!=/<>... value and/or fieldname =/!=/<> ... value order by fieldname <desc> ... Explanation: 



 

The and tells SQL to retrieve the record if both conditions are met. The or tells SQL to retrieve the record if either condition is met. The or is less restrictive and retrieves more records. If multiple ands and ors are used, the ands are evaluated first, and then the ors.



Use parentheses to change precedence (the order of evaluation).

Examples: Retrieve all vendors who are located in Chicago and have a current balance over 1,500 dollars. Order the results by current balance. 1. In the SQL text box, type: Select Name, City, CurrBal [-ZZ,ZZZ.99] from TrnVendor where City = 'Chicago' and CurrBal > 1500 order by CurrBal; 2. Execute the SQL statement. Results: Name

City

CurrBal

Make Shift Tilts

Chicago

1,793.23

Music Maker

Chicago

4,109.14

Retrieve all vendors who are located in Chicago or who have a current balance over 1,500 dollars. 1. In the SQL text box, type: Select Name, City, CurrBal [-ZZ,ZZZ.99] from TrnVendor where City = 'Chicago' or CurrBal > 1500 order by CurrBal;

2. Execute the SQL statement. Name

City

CurrBal

Distant Horizons

Chicago

26.98

BedMakers Linen

Chicago

500.00

Paltry Play

Chicago

1,000.00

Spot Out

Detroit

1,606.54

Narrow Nest

Toledo

1,609.00

No Waste Disposal

Park Ridge

1,720.98

Make Shift Tilts

Chicago

1,793.23

Legal Lookup

Houston

3,215.16

Music Maker

Chicago

4,109.14

Softer Software

Monroe

9,873.67

The next two SQL statements use exactly the same syntax except that the order of precedence is different (note parentheses in second example). Retrieve all vendors from Chicago whose expense account is 4110 or any vendor with a current balance over 1200. 1. In the SQL text box, type: Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct from TrnVendor where City = 'Chicago' and ExpAcct = '4110' or CurrBal < 1200 order by City, ExpAcct; 2. Execute the SQL statement. City

CurrBal

ExpAcct

Bayshore

498.05

4110

Burlington

51.00

4120

Chicago

4,109.14

4110

Chicago

500.00

4120

Chicago

1,000.00

6080

Chicago

26.98

7230

Dallas

0.00

4030

Dallas

20.00

6080

Detroit

1,001.23

7230

Freeport

642.98

4110

Freeport

771.06

6510

Mercer

321.70

6080

Mercer

-109.23

7230

Monroe

342.98

4110

New York

617.00

6080

Orlando

0.00

4110

Orlando

64.72

4110

Park Ridge

967.24

6510

Toledo

23.45

4030

Toledo

42.89

6510

Toledo

13.13

7230

Warren

453.17

4110

Youngstown

345.09

7230

Retrieve all vendors whose expense account is 4110 or who have a current balance over 1200. The vendor must be located in Chicago. 1. In the SQL text box, type: Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct from Vendor where City = 'Chicago' and (ExpAcct = '4110' or CurrBal > 1200) order by City, ExpAcct;

2. Execute the SQL statement. Results: City

CurrBal

ExpAcct

Chicago

0.00

4030

Chicago

4,135.24

4110

Chicago

1,309.95

4110

Chicago

100.00

6010

Chicago

500.00

6040

Chicago

47.00

7010

Chicago

106.00

7190

SQL Join The SQL join command is used to join two or more tables. At times you may need to join two or more tables to retrieve the information you need from your SQL database. Our online SQL tutorial covers joining tables and much more.

Joining Tables So far, you have worked exclusively with a single table -- the TrnVendor table. You might have noticed that each module within Solomon IV consists of several tables. Within the Accounts Payable module, you will find the following tables: APAdjust, APDoc, APHist, APRefNbr, APSetup, APTran, and Vendor. Each table contains specific information. At times, you might need to join two or more tables to retrieve the information you need. For example, if you look at the TrnAPDoc table, you will see that it contains the vendor ID; however, it does not contain the vendor name. If, when retrieving information from the APDoc table, you want to see the vendor name, you have to join the TrnVendor table to the TrnAPDoc table. Syntax: Select */tablename.fieldname<mask> ... from tablename , tablename , tablename where tablename.fieldname = tablename.fieldname and tablename.fieldname = tablename.fieldname and tablename.fieldname = tablename.fieldname order by fieldname <desc>...

Explanation: When you join two or more tables, a good idea is to precede the field names with the table names. This is not mandatory unless the same field name is found in more than one table. If you precede the field name with a table name, place a period between the two names. For example, tablename.fieldname. You must specify which fields are being joined. If you do not specify which fields are being joined, the result is what is commonly referred to as a "Cartesian join" in which all rows in the first table are joined with all rows in the second table. You can give each table name an alias, or alternative table name. When you assign an alias, you can then refer to the table by using its alias.



  



Examples: Retrieve the vendor ID, the vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables. 1. In the SQL text box, type: Select TrnAPDoc.VendId, TrnVendor.Name, TrnAPDoc.OrigDocAmt [-ZZZZZZ.99] from TrnAPDoc, TrnVendor where TrnAPDoc.VendId = TrnVendor.VendId; 2. Execute the SQL statement. Results: TrnAPDoc.VendId

TrnVendor.Name

TrnAPDoc.OrigDocAmt

TV020

BayCon Group

542.98

TV019

Bayshore Consulting

237.60

TV018

Computer Bytes

55.50

TV017

Food Four

55.50

TV016

Music Maker

55.50

TV015

No Waste Disposal

55.50

TV014

Counter Productive

100.00

TV013

Softer Software

355.00

TV012

Paper People

55.50

TV001

Wet Off Towels

55.50

TV010

The Soda Factory

55.50

TV009

Hit the Deck

55.50

TV008

Big Tree Landscaping

55.50

TV007

Spot Out

55.50

TV006

Mosquito No Bite

55.50

TV005

Ray Block

625.00

TV004

Bed Room Furniture, Inc.

55.50

TV003

BedMakers Linen

55.50

TV002

The Games All Here

55.50

TV001

Wet Off Towels

55.50

TV029

Paltry Play

987.78

TV028

Distant Horizons

789.00

TV027

Narrow Nest

893.22

TV026

Clampett Oil

1250.50

TV005

Ray Block

736.99

TV024

Enterprise Transport

652.00

TV023

World Wide Learning U

459.00

TV002

The Games All Here

104.00

TV021

Cooperative Operatives 549.06

TV020

BayCon Group

2566.24

TV019

Bayshore Consulting

20.00

TV018

Computer Bytes

50.00

TV017

Food Four

30.00

TV016

Music Maker

300.00

TV015

No Waste Disposal

35.00

TV014

Counter Productive

5.00

TV013

Softer Software

10.00

TV012

Paper People

20.00

TV001

Wet Off Towels

8.25

TV010

The Soda Factory

108.25

TV009

Hit the Deck

21300.00

TV008

Big Tree Landscaping

300.00

TV007

Spot Out

7000.00

TV006

Mosquito No Bite

400.00

TV005

Ray Block

3600.00

TV004

Bed Room Furniture, Inc.

3600.00

TV003

BedMakers Linen

3000.00

TV002

The Games All Here

1000.00

TV001

Wet Off Towels

6000.00

Retrieve the vendor ID, vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables, using a table alias. 1. In the SQL text box, type: Select a.VendId, b.Name, a.OrigDocAmt [-ZZZZZZ.99] from TrnAPDoc a, TrnVendor b where a.VendId = b.VendId; 2. Execute the SQL statement. Results: a.VendId

b.Name

a.OrigDocAmt

TV020

BayCon Group

542.98

TV019

Bayshore Consulting

237.60

TV018

Computer Bytes

55.50

TV017

Food Four

55.50

TV016

Music Maker

55.50

TV015

No Waste Disposal

55.50

TV014

Counter Productive

100.00

TV013

Softer Software

355.00

TV012

Paper People

55.50

TV001

Wet Off Towels

55.50

TV010

The Soda Factory

55.50

TV009

Hit the Deck

55.50

TV008

Big Tree Landscaping

55.50

TV007

Spot Out

55.50

TV006

Mosquito No Bite

55.50

TV005

Ray Block

625.00

TV004

Bed Room Furniture, Inc. 55.50

TV003

BedMakers Linen

55.50

TV002

The Games All Here

55.50

TV001

Wet Off Towels

55.50

TV001

Wet Off Towels

55.50

TV029

Paltry Play

987.78

TV028

Distant Horizons

789.00

TV027

Narrow Nest

893.22

TV026

Clampett Oil

1250.50

TV005

Ray Block

736.99

TV024

Enterprise Transport

652.00

TV023

World Wide Learning U

459.00

TV002

The Games All Here

104.00

TV021

Cooperative Operatives

549.06

TV020

BayCon Group

2566.24

TV019

Bayshore Consulting

20.00

TV018

Computer Bytes

50.00

TV017

Food Four

30.00

TV016

Music Maker

300.00

TV015

No Waste Disposal

35.00

TV014

Counter Productive

5.00

TV013

Softer Software

10.00

TV012

Paper People

20.00

TV001

Wet Off Towels

8.25

TV010

The Soda Factory

108.25

TV009

Hit the Deck

21300.00

TV008

Big Tree Landscaping

300.00

TV007

Spot Out

7000.00

TV006

Mosquito No Bite

400.00

TV005

Ray Block

3600.00

TV004

Bed Room Furniture, Inc. 3600.00

TV003

BedMakers Linen

3000.00

TV002

The Games All Here

1000.00

TV001

Wet Off Towels

6000.00

Retrieve the vendor ID, vendor name, reference number, and original document amount from the APDoc and Vendor tables for Vendor V00104. Order the results by RefNbr. 1. In the SQL text box, type: Select TrnAPDoc.VendId, TrnVendor.Name, TrnAPDoc.RefNbr, TrnAPDoc.OrigDocAmt [-ZZZZZZ.99] from TrnAPDoc, TrnVendor where TrnAPDoc.VendId = TrnVendor.VendId and TrnAPdoc.VendId = 'TV004' order by TrnAPDoc.RefNbr; 2. Execute the SQL statement.

Results: TrnAPDoc.VendId TrnVendor.Name TrnAPDoc.RefNbr TrnAPDoc.OrigDocAmt TV004

Bed Room Furniture, Inc.

000222

55.50

TV004

Bed Room Furniture, Inc.

000551

3600.00

SQL Update The SQL update command is used to change data in your SQL database. If you are unsure of SQL syntax or you just need help with SQL commands, this is the site. Our online tutorial covers many SQL commands.

Updating Tables

So far, you have looked at several different ways to retrieve and review your data. In this section, you will learn how to update your data. In the following two sections, you will learn about deleting and inserting rows. When you update, delete, and insert, you change the data -- you should perform these operations very cautiously. Before performing any of these operations on a production database, make sure your data is backed up and use the Start Transaction command. If you use the Start Transaction command, all of your changes are temporary until you commit your work and can be rolled back . If you have issued the Start Transaction command, you can undo your changes simply by typing "rollback work." NOTE: The exercises that follow should not be performed on a production database. Use a test or trial database. Syntax: Start transaction;

Update tablename set fieldname = value where fieldname = value; Rollback work; Commit work; Explanation: 









Issue a Start Transaction command before updating your table. This will allow you to roll back the changes, if necessary. If you do not issue a Start Transaction command, you will not be able the roll back your work. If you find that you have updated a row in error, execute the Rollback Work command. When you are satisfied with your changes, issue the Commit Work command. Use a Where clause to specify which rows will be updated. If you do not include a Where clause, all rows will be updated. Remember to end each command with a semicolon.

Example: Change the name of vendor "TV001" to Genie R. Corp., and then roll back the change. Then change the name of vendor "TV001" to Vanix and commit your work. 1. To start the transaction, type: Start transaction; 2. Note the current vendor name, and type:

Select VendId, Name from TrnVendor where VendId = 'TV001'; 3. To update the vendor name, type: Update TrnVendor Set Name = 'Genie R Corp.' Where VendId = 'TV001'; 4. To check the vendor name to see that it has changed, type: Select VendId, Name from TrnVendor where VendId = 'TV001'; 5. To roll back the change, type: Rollback work; 6. To check the vendor name to see that it has reverted to the original, type: Select VendId, Name from TrnVendor where VendId = 'TV001'; 7. To update the vendor name, type: Update TrnVendor Set Name = 'Vanix' Where VendId = 'TV001'; 8. To check the vendor name to see that it has changed, type:

Select VendId, Name from TrnVendor where VendId = 'TV001'; 9. To commit the change, type: Commit work; Note: You can run multiple SQL statements at the same time, as in the following example. To run all of the SQL statements in the previous exercise, type all of the commands in the SQL text box as shown here. Then click on First to execute the first statement and Next to execute each subsequent statement. Start transaction; Select VendId, Name from TrnVendor where VendId = 'TV001'; Update TrnVendor Set Name = 'Genie R Corp.' Where VendId = 'TV001'; Select VendId, Name from TrnVendor where VendId = 'TV001'; Rollback work; Select VendId, Name from TrnVendor where VendId = 'TV001'; Update TrnVendor Set Name = 'Vanix Corp.' Where VendId = 'TV001'; Select VendId, Name from TrnVendor where VendId = 'TV001';

SQL Statement Need help with your SQL statements? Our SQL tutorial provides the help you need to learn SQL. It covers the create, import, retrieve, order, update, delete, insert, and join SQL commands.

Deleting Rows

You can use Scalable SQL to delete rows of data. Syntax: Delete from tablename where fieldname =/<>/ ... value and/or ... fieldname =/<>/ ... value and/or fieldname =/<>/ ... value Explanation: 







 

If you do not include a Where clause, all of the rows in the table will be deleted. Every table has a primary key -- a field or combination of fields that uniquely identify each row in the table. VendId is the primary key for the vendor table. Each vendor is uniquely identified by the vendor Id. RefNbr is the primary key for APDoc. If you want to delete a single row of data, you can refer to the row in the Where clause by using the primary key. When deleting data, use the Start Transaction command so that any errors can be rolled back. Use the Rollback Work command to undo changes. Use the Commit Work command to finalize changes.

Examples:

Delete vendor TV011 by executing the following commands. 1. To view the record, type: Select * from TrnVendor where VendId = 'TV011'; 2. To start the transaction, type: Start transaction; 3. To delete the record, type: Delete from TrnVendor where VendId = 'TV011 '; 4. To check to make sure the records have been deleted, type: Select * from TrnVendor where VendId = 'TV011'; 5. To roll back the deletion, type: Rollback work; Delete all vendors with a current balance over 2000 dollars. 1. To view the records, type: Select Name, CurrBal [-ZZZ,ZZZ.99] from TrnVendor where CurrBal > 2000; 2. To start the transaction, type:

Start transaction; 3. To delete the records, type: Delete from TrnVendor where CurrBal > 2000; 4. To check to make sure the records have been deleted, type: Select Name, CurrBal [-ZZZ,ZZZ.99] from TrnVendor where CurrBal > 2000; 5. To roll back the deletion, type: Rollback work;

SQL Insert The SQL insert command is used to insert data into your SQL database. Our SQL tutorial teaches you SQL commands. It covers the insert, create, join,� import, retrieve, order, update, and delete commands.

Inserting Rows You can insert new rows into a table by using Scalable SQL. Syntax: Insert into tablename ( fieldname, fieldname, fieldname) values ( value, value, value); Explanation:



 

  

When inserting data, use the Start Transaction command so that any errors can be rolled back. You must specify the values to be inserted. When performing an insert, enclose character values in single quotes. Do not enclose numeric values in single quotes. Use the Rollback Work command to undo changes. Use the Commit Work command to finalize changes.

Example: Insert the following vendor into the TrnVendor table. Vendor Id: TV055 Name: Party Games Address: PO Box 136 City: Chicago State: IL Current Balance: 2498.62 1. To start the transaction, type: Start transaction; 2. To insert the records, type: Insert into TrnVendor (VendId, Name, Addr1, City, State, CurrBal ) values ( 'TV055', 'Party Games', 'PO Box 136', 'Chicago', 'IL', 2498.62); 3. To view the record, type: Select VendId, Name, Addr1, City, State, CurrBal [-ZZZ,ZZZ.99]

from TrnVendor where VendId = 'TV055'; 4. To roll back your work, type: Rollback work;

Reference: http://baycongroup.com/tocsql.htm