Skip to content
Thoroughbred Software InternationalThoroughbred Software InternationalThoroughbred DocumentationDocs

Sample Queries

Q4S01

Section titled “Q4S01”

Selecting columns from a file and using a numeric output mask.

Request in English:

List all employees and their salaries.

Query Framer:

Files (Links):
Q4EMPLOY
Columns:
EMPLOYEE-NAME, SALARY (Mask: $##,## 0)

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,## 0"
FROM Q4EMPLOY
Table Displayed:
Employee Name Salary
Douglas $14,500
Roland $28,750
Bell $32,000
Parker $35,000
Watson $33,000
Alexander $30,000
Drummond $28,500
Johnson $28,500
Mason $30,000
Brady $20,000
Perkins $20,000
Allen $30,000
Davis $14,500

Q4S02

Section titled “Q4S02”

Selecting columns from a file and specifying column headings.

Request in English:

List all employees and their salaries, using the specified column headings.

Query Framer:

Files (Links):
Q4EMPLOY

Columns: EMPLOYEE-NAME (Heading: Name), SALARY (Heading: Salary; Mask: $##,## 0)

EQL Command:

SELECT EMPLOYEE-NAME "Name", SALARY "Salary"
:"$##,##0"
FROM Q4EMPLOY
Table Displayed:
Name Salary
Douglas $14,500
Roland $28,750
Bell $32,000
Parker $35,000
Watson $33,000
Alexander $30,000
Drummond $28,500
Johnson $20,000
Mason $30,000
Brady $20,000
Perkins $20,000
Allen $30,000
Davis $14,500

Q4S03

Section titled “Q4S03”

Selecting specific rows using WHERE, with rows matching (=) a value.

Request in English:

List the names and salaries of employees who earn $20,000.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: SALARY = 20000

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE SALARY = 20000
Table Displayed:
Employee Name Salary
Johnson $20,000
Brady $20,000
Perkins $20,000

Q4S04

Section titled “Q4S04”

Selecting specific rows using WHERE, with rows greater than (>) a value.

Request in English:

List the names and salaries of employees who earn more than $20,000.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: SALARY > 20000

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE SALARY > 20000
Table Displayed:
Employee Name Salary
Roland $28,750
Bell $32,000
Parker $35,000
Watson $33,000
Alexander $30,000
Drummond $28,500
Mason $30,000
Allen $30,000

Q4S05

Section titled “Q4S05”

Selecting specific rows using WHERE, with rows matching a partial value (substring).

Request in English:

List the names and salaries of all programmers.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: JOB-DESCRIPTION(1, 4) = “Prog”

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION(1,4) = "Prog"
Table Displayed:
Employee Name Salary
Roland $28,750
Parker $35,000
Drummond $28,500

Q4S06

Section titled “Q4S06”

Selecting specific rows using WHERE, with rows matching a partial value (LIKE).

Request in English:

List the names and salaries of all sales representatives.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: JOB-DESCRIPTION LIKE “Sales Rep*”

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "Sales Rep*"
Table Displayed:
Employee Name Salary
Alexander $30,000
Johnson $20,000
Mason $30,000
Brady $20,000
Perkins $20,000
Allen $30,000

Q4S07

Section titled “Q4S07”

Selecting specific rows using WHERE, with rows matching a partial value (LIKE).

Request in English:

List the names and salaries of only regional sales representatives.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: JOB-DESCRIPTION LIKE “*Regional*”

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "*Regional*"
Table Displayed:
Employee Name Salary
Alexander $30,000
Mason $30,000
Allen $30,000

Q4S08

Section titled “Q4S08”

Selecting specific rows using WHERE, with rows matching more than 1 value (AND).

Request in English:

List the names and salaries of programmers who earn more than $30,000.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: JOB-DESCRIPTION LIKE “Prog*” AND SALARY > 30000

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "Prog*" AND SALARY > 30000

Table Displayed:

Employee Name
Salary
Parker
$35,000

Q4S09

Section titled “Q4S09”

Selecting specific rows using WHERE, with rows meeting either of 2 values (OR).

Request in English:

List the names and salaries of all employees who earn more than $30,000 and of all programmers.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: JOB-DESCRIPTION(1,4) = “Prog” OR SALARY
> 30000

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION(1,4) = "Prog" OR SALARY > 30000
Table Displayed:
Employee Name Salary
Roland $28,750
Bell $32,000
Parker $35,000
Watson $33,000
Drummond $28,500

Q4S10

Section titled “Q4S10”

Selecting specific rows using WHERE, when rows are BETWEEN 2 values.

Request in English:

List the names and salaries of employees who earn between $20,000 and $30,000.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: SALARY BETWEEN 20000 AND 30000

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE SALARY BETWEEN 20000 AND 30000
Table Displayed:
Employee Name Salary
Roland $28,750
Alexander $30,000
Drummond $28,500
Johnson $20,000
Mason $30,000
Brady $20,000
Perkins $20,000
Allen $30,000

Q4S11

Section titled “Q4S11”

Selecting specific rows using WHERE, with rows matching a partial value (LIKE).

Request in English:

List the names and salaries of employees whose names begin with a
P and have rk as the second

and third letters.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: EMPLOYEE-NAME LIKE “P?rk*”

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE EMPLOYEE-NAME LIKE "P?rk*"
Table Displayed:
Employee Name Salary
Parker $35,000
Perkins $20,000

Q4S12

Section titled “Q4S12”

Selecting specific rows using WHERE, with rows matching a partial value (LIKE).

Request in English:

List the names and salaries of employees whose names begin with a
P , M , or W .
Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Rows Where: EMPLOYEE-NAME LIKE “[PMW]*”

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0"
FROM Q4EMPLOY
WHERE EMPLOYEE-NAME LIKE "[PMW]*"
Table Displayed:
Employee Name Salary
Parker $35,000
Watson $33,000
Mason $30,000
Perkins $20,000

Q4S13

Section titled “Q4S13”

Selecting specific rows using WHERE, with rows matching values IN a list.

Request in English:

List the names, salaries, and bonus amounts of employees who received a bonus of $1,500 or $1,250.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY
(Mask: $##,## 0),BONUS
Rows Where: BONUS IN (1500, 1250)

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0",BONUS
FROM Q4EMPLOY
WHERE BONUS IN (1500, 1250)
Table Displayed:
Employee Name Salary Bonus
Roland $28,750 1500
Parker $35,000 1250
Drummond $28,500 1500

Q4S14

Section titled “Q4S14”

Selecting specific rows using WHERE, with rows matching values NOT IN a list.

Request in English:

List the names, salaries, and bonus amounts of employees who did not receive a bonus of $1,500 or $1,250.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY
(Mask: $##,## 0),BONUS
Rows Where: BONUS NOT IN (1500, 1250)

EQL Command:

SELECT EMPLOYEE-NAME, SALARY :"$##,##0", BONUS
FROM Q4EMPLOY
WHERE BONUS NOT IN (1500, 1250)
Table Displayed:
Employee Name Salary Bonus
Douglas $14,500
Bell $32,000 2800
Watson $33,000 1800
Alexander $30,000 18000
Johnson $20,000 8500
Mason $30,000 17500
Brady $20,000 6750
Perkins $20,000 8800
Allen $30,000 19500
Davis $14,500

Q4S15

Section titled “Q4S15”

Selecting a calculated column.

Request in English:

List the names and gross pay (including bonus) of marketing representatives.

Query Framer:

Use EQL for calculated columns.

EQL Command:

SELECT EMPLOYEE-NAME, SALARY + BONUS :"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "Marketing*"
Table Displayed:
Employee Name Salary + Bonus
Bell $34,800
Watson $34,800

Q4S16

Section titled “Q4S16”

Selecting a calculated column using an expression alias.

Request in English:

List the names and gross pay (including bonus) of non-regional sales representatives.

Query Framer:

Use EQL for calculated columns.

EQL Command:

SELECT EMPLOYEE-NAME, SALARY+BONUS
        AS GROSS-PAY:"$##,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "Sales*"
        AND JOB-DESCRIPTION NOT LIKE "*Regional*"
Table Displayed:
Employee Name Gross Pay
Johnson $28,500
Brady $26,750
Perkins $28,800

Q4S17

Section titled “Q4S17”

Selecting a calculated column and specific rows using an expression alias.

Request in English:

List the names and gross pay (including bonus) of employees who earned $35,000 or more.

Query Framer:

Use EQL for calculated columns.

EQL Command:

SELECT EMPLOYEE-NAME, SALARY + BONUS
        AS GROSS-PAY :"$##,##0"
FROM Q4EMPLOY
WHERE GROSS-PAY >= 35000
Table Displayed:
Employee Name Gross Pay
Parker $36,250
Alexander $48,000
Mason $47,500
Allen $49,500

Q4S18

Section titled “Q4S18”

Producing a TOTAL of numeric columns using an expression alias.

Request in English:

Show the effect of an
across-the-board salary increase of 7% for employees, listing the current salary,

amount of salary increase, the new salary, and the grand total for each of these figures.

Query Framer:

Use EQL for calculated columns.

EQL Command:

TOTAL SALARY, PAY-RAISE, NEW-PAY;
SELECT EMPLOYEE-NAME, SALARY :"###,###"", SALARY *.07
        AS PAY-RAISE :"###,###",SALARY * 1.07 AS
        NEW-PAY :"###,###"
FROM Q4EMPLOY
Table Displayed:
Employee Name Salary Pay Raise New Pay
Douglas 14,500 1,015 15,515
Roland 28,750 2,013 30,763
Bell 32,000 2,240 34.240
Parker 35,000 2,450 37,450
Watson 33,000 2.310 35,310
Alexander 30,000 2,100 32,100
Drummond 28,500 1,994 30,495
Johnson 20,000 1,400 21,400
Mason 30,000 2,100 32,100
Brady 20,000 1,400 21,400
Perkins 20,000 1,400 21,400
Allen 30,000 2,100 32,100
Davis 14,500 1,015 15,515
336,250 23,538 359,788

Q4S19

Section titled “Q4S19”

Displaying rows in ORDER BY a specific column.

Request in English:

List the names and salaries of all employees in alphabetical order.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, SALARY (Mask: $##,## 0)
Order Rows By: SORT1 EMPLOYEE-NAME

EQL Command:

SELECT EMPLOYEE-NAME, SALARY: "$##,##0"
FROM Q4EMPLOY
ORDER BY EMPLOYEE-NAME
Table Displayed:
Employee Name Salary
Alexander $30,000
Allen $30,000
Bell $32,000
Brady $20,000
Davis $14,500
Douglas $14,500
Drummond $28,500
Johnson $20,000
Mason $30,000
Parker $35,000
Perkins $20,000
Roland $28,750
Watson $33,000

Q4S20

Section titled “Q4S20”

Displaying rows in descending, or reverse, ORDER BY a specific column.

Request in English:

List the names and bonus amounts of all employees, going from the employee who received the biggest bonus amount to the one who received the smallest.

Query Framer:
Files (Links): Q4EMPLOY
Columns: EMPLOYEE-NAME, BONUS (Mask: $##,## 0)
Order Rows By: SORT2 BONUS(D)

EQL Command:

SELECT EMPLOYEE-NAME, BONUS :"$##,##0"
FROM Q4EMPLOY
ORDER BY BONUS(D)
Table Displayed:
Employee Name Bonus
Allen $19500
Alexander $18000
Mason $17500
Perkins $8800
Johnson $8500
Brady $6750
Bell $2800
Watson $1800
Roland $1500
Drummond $1500
Parker $1250
Douglas $0
Davis $0

Q4S21

Section titled “Q4S21”

Displaying rows in ORDER BY 2 columns.

Request in English:

Show all job titles in alphabetical order, giving the range of salaries for each position going from the lowest salary to the highest.

Query Framer:
Files (Links): Q4EMPLOY
Columns: JOB-DESCRIPTION, SALARY
(Mask: $##,## 0), EMPLOYEE-NAME
Order Rows By: SORT3 JOB-DESCRIPTION + SALARY

EQL Command:

SELECT JOB-DESCRIPTION, SALARY :"$##,##0", EMPLOYEE-
NAME
FROM Q4EMPLOY
ORDER BY JOB-DESCRIPTION + SALARY
Table Displayed:
Job Description Salary Employee Name
Bookkeeper $14,500 Douglas
Bookkeeper $14,500 Davis
Marketing Rep. $32,000 Bell
Marketing Rep. $33,000 Watson
Programmer $28,500 Drummond
Programmer $28,750 Roland
Programmer $35,000 Parker
Sales Rep. $20,000 Johnson
Sales Rep. $20,000 Brady
Sales Rep. $20,000 Perkins
Sales Rep. (Regional) $30,000 Alexander
Sales Rep. (Regional) $30,000 Mason
Sales Rep. (Regional) $30,000 Allen

Q4S22

Section titled “Q4S22”

Producing a TOTAL of a numeric column.

Request in English:

List all salaries and give the grand total.

Query Framer:
Files (Links): Q4EMPLOY
Columns: SALARY (Mask: $###,## 0)
Column Totals: SALARY

EQL Command:

TOTAL SALARY;
SELECT SALARY :"$###,##0"
FROM Q4EMPLOY

Table Displayed:

Salary
$ 14,500
$ 28,750
$ 32,000
$ 35,000
$ 33,000
$ 30,000
$ 28,500
$ 20,000
$ 30,000
$ 20,000
$ 20,000
$ 30,000
$ 14,500
$336,250

Q4S23

Section titled “Q4S23”

Producing the SUM of a numeric column.

Request in English:

Only show the total of all salaries.

Query Framer:

Use EQL to produce the SUM for all rows.

EQL Command:

SELECT SUM(SALARY) :"$###,##0"
FROM Q4EMPLOY

Table Displayed:

Sum (Salary)
$336,250

Q4S24

Section titled “Q4S24”

Producing the SUM of a numeric column while selecting only specific rows to be included in the SUM.

Request in English:

Show the total of salaries paid to the bookkeepers.

Query Framer:

Use EQL to produce the SUM for all selected rows.

EQL Command:

SELECT SUM(SALARY) :"$###,##0"
FROM Q4EMPLOY
WHERE JOB-DESCRIPTION LIKE "Bookkeeper*"

Table Displayed:

Sum (Salary)
$29,000

Q4S25

Section titled “Q4S25”

Summarizing a group of records: selecting a group (GROUP BY) and producing a SUM for each group.

Request in English:

List the total of salaries in each department.

Query Framer:
Files (Links): Q4EMPLOY
Columns: DEPT-CODE, SALARY (Mask: $###,## 0)
Group Rows By: DEPT-CODE

EQL Command:

SELECT DEPT-CODE, SUM(SALARY) :"$###,##0"
FROM Q4EMPLOY
GROUP BY DEPT-CODE
Table Displayed:
Dept. Code Sum (salary)
01 $29,000
02 $92,250
03 $60,000
05 $30,000
06 $30,000
07 $30,000
09 $33,000
11 $32,000

Q4S26

Section titled “Q4S26”

Summarizing a group of records: selecting a group (GROUP BY) and producing a SUM for each group (with 2 files).

Request in English:

List the total salary for each department, showing departments by name.

Query Framer:
Files (Links): Q4DEPT, Q4EMPLOY
Columns: DEPT-NAME, SALARY (Mask: $###,## 0)
Group Rows By: DEPT-NAME

EQL Command:

SELECT DEPT-NAME, SUM(SALARY) :"$###,##0"
FROM Q4DEPT, Q4EMPLOY
GROUP BY DEPT-NAME
Table Displayed:
Dept. Name Sum (Salary)
Accounting $ 29,000
Engineering $ 92,250
Marketing $ 65,000
Sales $150,000

Q4S27

Section titled “Q4S27”

Selecting columns from 2 related files.

Request in English:

List departments alphabetically, showing the employees within each department.

Query Framer:
Files (Links): Q4DEPT, Q4EMPLOY
Columns: DEPT-NAME, EMPLOYEE-NAME
Order Rows By: SORT1 DEPT-NAME

EQL Command:

SELECT DEPT-NAME, EMPLOYEE-NAME
FROM Q4DEPT, Q4EMPLOY
ORDER BY DEPT-NAME
Table Displayed:
Dept. Name Employee Name
Accounting Douglas
Accounting Davis
Engineering Roland
Engineering Parker
Engineering Drummond
Marketing Watson
Marketing Bell
Sales Johnson
Sales Brady
Sales Perkins
Sales Alexander
Sales Mason
Sales Allen

Q4S28

Section titled “Q4S28”

Selecting columns from 2 related files.

Request in English:

List department locations alphabetically, showing the employees and their jobs within each department.

Query Framer:
Files (Links): Q4DEPT, Q4EMPLOY
Columns: DEPT-LOCATION, EMPLOYEE-NAME,
JOB-DESCRIPTION
Order Rows By: SORT2 DEPT-LOCATION

EQL Command:

SELECT DEPT-LOCATION, EMPLOYEE-NAME, JOB-DESCRIPTION
FROM Q4DEPT, Q4EMPLOY
ORDER BY DEPT-LOCATION
Table Displayed:
Dept. Employee Job
Location Name Description
Atlanta Alexander Sales Rep (Regional)
Chicago Mason Sales Rep (Regional)
London Watson Marketing Rep
New York Douglas Bookkeeper
New York Davis Bookkeeper
New York Roland Programmer
New York Parker Programmer
New York Drummond Programmer
New York Johnson Sales Rep
New York Brady Sales Rep
New York Perkins Sales Rep
San Francisco Allen Sales Rep (Regional)
Toronto Bell Marketing Rep

Q4S29

Section titled “Q4S29”

Producing the SUM of a calculated column from one file while selecting specific rows matching the value from a second file.

Request in English:

List the total of gross pay (including bonus) for employees who work in New York.

Query Framer:

Use EQL to produce the SUM of a calculated column.

EQL Command:

SELECT SUM(SALARY + BONUS) :"$###,##0"
FROM Q4DEPT, Q4EMPLOY
WHERE DEPT-LOCATION LIKE "New York*"

Table Displayed:

Sum (Salary + Bonus)
$209,550

Q4S30

Section titled “Q4S30”

Producing a list ordered by one Data Name then ordered by a second Data Name.

Request in English:

Provide a telephone list of customers within each state and sorted by customer name.

Query Framer:
Files (Links): UTCUST
Columns: CUST-NAME, STATE, PHONE
(Mask: ### ###-####)
Order Rows By: STATE, CUST-NAME

EQL Command:

SELECT CUST-NAME, STATE, PHONE :"### ###-####"
FROM UTCUST
ORDER ROWS BY STATE, CUST-NAME
Table Displayed:
Customer Name State Telephone
Hartford Recreation Office Connecticut 203 340-3434
Warren Baseball Club Connecticut 203 774-2415
Dayton Little Devils Baseball New Jersey 732 433-4300
Fords Little League All-Stars New Jersey 732 345-9292
Hackensack Hackers New Jersey 201 452-3544
Paramus Little League New Jersey 201 236-4001
Princeton Amateur League New Jersey 609 359-9090
Southside Raiders New Jersey 201 345-3240
Westfield Recreation New Jersey 732 232-1500
Blue Diamond Baseball New York 718 230-2424
Lakewood Little League New York 516 934-0221
Team Sports New York 212 423-4555

Q4S31

Section titled “Q4S31”

Producing a list from two files which is ordered by one Data Name then ordered by a second Data Name.

Request in English:

Provide a list of invoices for each customer listing the most current invoice first.

Query Framer:

Files (Links):UTSALHSH, UTCUST
Columns:CUST-NAME, INVOICE-NUMBER, INVOICE-DATE, NET-SALE-AMOUNT (Mask: $#######.00)
Order Rows By:CUST-NAME, INVOICE-DATE (Descending order)

EQL Command:

SELECT CUST-NAME, INVOICE-NUMBER, INVOICE-DATE,
        NET-SALE-AMOUNT :"$#######.00"
FROM UTSALHSH, UTCUST
ORDER ROWS BY CUST-NAME, INVOICE-DATE(D)
Table Displayed:
Customer Name Invoice Number Invoice date Net Sales Amount
Warren Baseball Club B00132 05/15/98 709.80
B00130 03/14/98 523.05
B00100 01/05/98 385.70
Lakewood Little League B00147 04/18/98 342.10
B00136 03/24/98 314.55
B00119 02/23/98 1012.60
B00115 02/14/98 165.85
B00104 01/15/98 553.40
Southside Raiders B00148 04/19/98 164.40
B00129 03/10/98 620.95
B00116 02/15/98 368.30
Blue Diamond Baseball B00143 01/11/98 224.30
B00138 03/29/98 199.70
B00124 03/02/98 374.10
B00123 02/28/98 493.50
B00111 02/02/98 470.05
B00106 01/19/98 218.85
Team Sports B00144 04/13/98 664.05
B00133 03/16/98 612.55
B00131 03/14/98 404.10
B00122 02/27/98 284.85
B00112 02/05/98 165.85
B00101 01/05/98 700.25
Westfield Recreation B00145 04/15/98 260.35
B00134 03/18/98 552.90
B00127 03/05/98 650.40
B00113 02/06/98 658.10
B00102 01/06/98 664.05
Dayton Little Devils Baseball B00128 03/08/98 811.05
B00117 02/19/98 448.20
Customer Name
Invoice Number Invoice date Net Sales Amount
Hartford Recreation Office
B00146 04/18/98 420.00 B00139 03/30/98 668.25 B00126 03/05/98 770.60 B00114 02/08/98 382.15 B00107 01/21/98 112.90
Paramus Little League
B00140 04/05/98 291.30 B00125 03/04/98 925.20 B00108 01/24/98 500.50
Fords Little League All-Stars
B00141 04/05/98 347.55 B00109 01/29/98 158.30
Princeton Amateur League
B00142 04/07/98 387.75 B00135 03/21/98 300.20 B00120 02/25/98 840.35 B00110 02/01/98 307.65 B00103 01/12/98 709.25
Hackensack Hackers
B00137 03/24/98 394.70 B00121 02/27/98 509.90 B00118 02/21/98 656.65 B00105 01/18/98 167.85

Q4S32

Section titled “Q4S32”

Producing a list using a mask for specified columns.

Request in English:

Provide a list of open receivables and payments toward each invoice.

Query Framer:

Files (Links):
UTOPENAR

Columns: INVOICE-NUMBER, DUE-DATE, CUST-CODE, NET-AMOUNT (Mask: $#######.00), TOTAL-PAYMENTS (Mask: $#######.00)

EQL Command:

SELECT INVOICE-NUMBER,DUE-DATE,CUST-CODE,NET-AMOUNT
      :"$#######.00",TOTAL-PAYMENTS:"$#######.00"
FROM UTOPENAR
Table Displayed: Total
Invoice Number Due Date Customer Code Net Amount Payments
B00102 02/05/98 0006 $664.05 $398.43
B00105 01/18/98 0012 $167.85 $ .00
B00111 04/03/98 0004 $470.05 $282.03
B00115 02/14/98 0002 $165.85 $ .00
B00117 02/19/98 0007 $448.20 $268.92
B00118 02/21/98 0012 $656.65 $393.99
B00123 04/29/98 0004 $493.50 $ .00
B00128 03/08/98 0007 $811.05 $486.63
B00129 04/09/98 0003 $620.95 $372.57
B00135 06/19/98 0011 $300.20 $ .00
B00136 03/24/98 0002 $314.55 $ .00
B00137 03/24/98 0012 $394.70 $ .00
B00138 05/28/98 0004 $199.70 $ .00
B00139 05/29/98 0008 $668.25 $ .00
B00140 06/04/98 0009 $291.30 $ .00
B00141 05/06/98 0010 $347.55 $ .00
B00142 07/06/98 0011 $387.75 $ .00
B00143 06/10/98 0004 $224.30 $ .00
B00144 07/12/98 0005 $664.05 $ .00
B00145 05/15/98 0006 $260.35 $ .00
B00146 06/17/98 0008 $420.00 $ .00
B00147 04/18/98 0002 $342.10 $ .00
B00148 05/19/98 0003 $164.40 $ .00

Q4S33

Section titled “Q4S33”

Request in English:

Provide a list of invoices and the current balance by customer.

Query Framer:

Use EQL to produce the SUM of a calculated column.

EQL Command:

DETAIL
TOTAL SUM(NET-AMOUNT-TOTAL-PAYMENTS);
SELECT CUST-NAME,INVOICE-NUMBER,SUM(NET-AMOUNT-
        TOTAL-PAYMENTS)"Invoice|Balance"
FROM UTOPENAR,UTCUST
GROUP BY CUST-NAME
Table Displayed:
Customer Invoice Invoice
Name Number Balance
Lakewood Little League 3000 700.00
700.00
Warren Baseball Club
1000 70.00

Warren Baseball Club 2000 300.00

370.00

Q4S34

Section titled “Q4S34”

Producing a list that allows the user to determine the specific group to display.

Request in English:

Provide a customer list with telephone for any sales-rep. You determine the sales-rep at run time.

Query Framer:

Use EQL to produce the SUM of a calculated column.

EQL Command:

INPUT "Enter Sales Rep:"INTO TEMP-REP(3);
SELECT CUST-CODE,CUST-NAME,PHONE
FROM UTCUST,UTREP
WHERE REP-CODE=TEMP-REP

User Prompt:

Enter Sales Rep: 003

Table Displayed:
Customer Code Customer Name Telephone
0001 Warren Baseball Club 203 774-2415
0008 Hartford Recreation Office 203 340-3434
0009 Paramus Little League 201 236-4001

Q4S35

Section titled “Q4S35”

Producing a list, which resizes a column.

Request in English:

Provide a customer list of open AR balances. Only display the first 20 characters of the customer name.

Query Framer:

Files (Links):
UTCUST

Columns: CUST-CODE, CUST-NAME (only first 20 characters), OPEN-AR-BALANCE (Mask: $######.00)

EQL Command:

SELECT CUST-CODE,CUST-NAME(1,20),
        OPEN-AR-BALANCE:"$######.00"
FROM UTCUST
Table Displayed:
Customer Code Customer Name Open AR Balance
0001 Warren Baseball Club
0002 Lakewood Little League $822.50
0003 Southside Raiders $412.78
0004 Blue Diamond Baseball $1105.52
0005 Team Sports $664.05
0006 Westfield Recreation $525.97
0007 Dayton Little Devils $503.70
0008 Hartford Recreation $1088.25
0009 Paramus Little League $291.30
0010 Fords Little League $347.55
0011 Princeton Amateur League $687.95
0012 Hackensack Hackers $825.21

Q4S36

Section titled “Q4S36”

Producing a list, which fits a specified criteria.

Request in English:

Provide a list of invoices for March of 1998.

Query Framer:
Files (Links): UTSALHSH
Columns: INVOICE-NUMBER, CUST-CODE, INVOICE-DATE, NET-SALE-AMOUNT
(Mask: $#######.00)
Rows Where: INVOICE-DATE(YYMM) = “9803”

EQL Command:

SELECT INVOICE-NUMBER,CUST-CODE,INVOICE-DATE
      NET-SALE-AMOUNT:"$#######.00"
FROM UTSALHSH
ROWS WHERE INVOICE-DATE(YYMM)="9803"
Table Displayed:
Invoice Number Customer Code Invoice Date Net Sales Amount
B00124 0004 03/02/98 $374.10
B00125 0009 03/04/98 $925.20
B00126 0008 03/05/98 $770.60
B00127 0006 03/05/98 $650.40
B00128 0007 03/08/98 $4811.05
B00129 0003 03/10/98 $620.95
B00130 0001 03/14/98 $523.05
B00131 0005 03/14/98 $404.10
B00132 0001 03/15/98 $709.80
B00133 0005 03/16/98 $612.55
B00134 0006 03/18/98 $552.90
B00135 0011 03/21/98 $300.20
B00136 0002 03/24/98 $314.55
B00137 0012 03/24/98 $394.70
B00138 0004 03/29/98 $199.70
B00139 0008 03/30/98 $668.25

From the Query-IV Reference, version 8.9.1. Open this page in the PDF