Subject: Informix Questions
From: [email-masked] (Stuart Litel)
Date: Sat, 4 Nov 1995 20:51:32 -0800 (PST)

STUART LITEL
                              Post Office Box 40
                              Newtonville, MA 02160
                              (617) 527 - 4551
                              FAX (617) 964 - 4262
                              [email-masked]

Attached is a copy of an Informix Programmers Test I compiled a
while back for one of my customers.  The original idea was to
test prospective Informix programmers and see if their knowledge
would help the company.

Recently I started talking to the people at Prentice Hall  /
Informix Press to write a book on the subject.  I am still in
discussions with them, and have not fully decided if I will
write such a book.

I am giving this test away, in hopes of convincing me that such
a book is necessary.  In exchange for this test, I would
appreciate any help.  If you can think of any questions to add
to the test, I would appreciate your forwarding (with the
answers of course) them to me.  All questions will remain my
property, but if I do write such a book, I will TRY to
acknowledge any individual who send me at least 10 questions
that I do use.  I would like to keep all the questions in the
following four categories, and need help especially with the
latter two.

1) Informix SQL questions
2) Informix 4GL questions
3) Informix New Era questions
4) Informix Database Administrator type questions (On-Line / Standard Engine)

Please try not to send me any questions that are specific to
certain versions of Informix.  For example something that is true
in the RDS version and not in the compiled Informix version or
true in Informix 5.X but not in Informix 7.X.

Please send me your questions in any of the following ways.
1) Via  E-Mail at: [email-masked]
2) Via Fax: (617) 964 - 4262
3) Via U.S.P.S. mail: Stuart Litel, P.O. Box 40 Newtonville, MA 02160

Thank you for all your help, in I hope this test will assist you
in your needs.  Oh yes, you may freely reproduce this test and
use as needed, but please leave it intact.  Just copy the test
as is, and  use as needed.

One last request - if you do find this test useful, kindly send me a
Thank You note, so I know that is it being used and it was useful.

Thank You!

Stuart Litel

About the author: Over 10 years ago I started using Informix 4GL / SQL
where I worked at a company that was a beta tester for the products
including RDS/Debugger and Turbo (now known as "on-line").  Presently
I serve as an Indepent Consultant on all areas of Informix Products
and quite possibly maybe the longest Informix programmer/user in
the country.   I also serve on the Steering/Executive Committee (and Founder)
of the Informix New England(USA) User Group.  I am always available for
servies and can always be reached via e-mail or telephone.  I believe
I have also written over 3 million lines of Informix 4GL code and
work with the complete line of products from RDS/Compiled, Debugger,
Standard/On-Line engines upto the most recent versions.

- ------------------------- cut here for test --------------------------------

                     INFORMIX 4GL Programming Test

1) Explain what the Informix "Construct" statement does?

2) What is the difference between the "Input by name" and
"input prog_rec from screen_rec"?

3) In a 4GL report what does "order external" do?

4) What will happen in the following code sample - find at least
two mistakes...

        define p_array[100] of smallint
        define x                        smallint
        #NOTE assume the next two lines are correct and xyz is
        # a smallint field
        declare abc_curs cursor for
                select xyz from abc
        foreach abc_curs into p_array[x]
        end foreach

5) Explain briefly what the following Informix built-in
functions do:

        1) errorlog(char-expr)
        2) infield(field-name)
        3) scr_line()
        4) arg_val(integer_expr)

6) Explain what will be displayed on the lines below in bold for
the value of x:

database abc
globals
        define  x       smallint
end globals

main
        define x                smallint
        display x                               #I AM A BOLD LINE
        let x = 2
        display x                               #I AM A BOLD LINE
        call funct1()
        display x                               #I AM A BOLD LINE
        call funct2()
        display x                               #I AM A BOLD LINE
        call funct3() returning x
        display x                               #I AM A BOLD LINE
end main

function funct1()
        define x        smallint
        let x = 3
end function

function funct2()
        let x = 4
end function

function funct3()
        let x = 5
        return x
end function

7) How do you "force the cursor" in the input array statement to
go down on line or bounce to the next row?

8) What is int_flag? How and when is it set?

9) What is the prepare statement used for?

10) What is the difference in the following code

        #       SAMPLE #1
        define x                smallint
        declare abc_curs cursor for
                select field from table
        open abc_curs
        while true
                fetch abc_curs into x
                if (status = 100) then
                        exit while
                end if
                display x
        end while

        #       SAMPLE #2
        define x                smallint
        declare abc_curs cursor for
                select field from table
        foreach abc_curs into x
                display x
        end foreach

11) When using ring menus, explain how to make a different key
other than the first key the "choice" key? For example I have a
ring menu that is hEllo and I want to make the "E" key the key
that will pick this choice.

12) When using ring menus explain how to Hide Options to certain
users?

13) What is the difference between the "format" and "picture"
attribute in a .per screen file?

For the next few questions (#14 - # 19) - use the following two
tables

Person Table                            State table
first_name      char(20)                state                   char(2)
last_name       char(20)                description             char(20)
address         char(30)
Job_title       char(30)
city            char(30)
state           char(2)
zip             char(5)
age             smallint

14) Write an SQL query that will find all the people in the
person table that do not live in a state that begins with the
letter "M"

15) Write an SQL query that will give a list of all the first
and last names of the people in the person table and the state
long description that "goes with" or "joins" them from the state
table.

16) Write an SQL query that will give a list of all the first
and last names of the people in the person table and the long
description that "goes with" or "joins" them and the state is or
is not in the state table. For example John Smith lives in NH
(New Hampshire) and Jane Doe lives in XX and there is no state
XX, but you want to show both John Smith with New Hampshire and
Jane Doe with no state.

17) Write a single query that will give a list of the average
age of all the people in the Person table broken down by state
and in order of the youngest to oldest age.

18) Write a query that will give you a count of the people who
live in each state, and the results should be printed on in
alphabetic order by state descrion. (i.e. Alabama 10, Alaska 5,
etc...)

19) Write a single query that will give you a list of people in
the Person table that live in states where the state is NOT IN
the State table.

20) What is the difference in "Select Distinct" and "Select
Unique" sql statement?

21) What is the difference on the following two statements:
        a) Lock table Person in exclusive mode
        b) Lock table Person in share mode

22) Explain what the Informix 4GL "on key" statement does?

23) What happens in the following code - what will be displayed
by the last line?

        define x        smallint
        let x = 2
        case
        when x = 2
                let x = 4
        when x = 4
                let x = 5
        when x = 6
                let x = 8
        otherwise
                let x = 10
        end case
        display x

24) Explain how to change the "Accept" key in Informix to "F10"
(the Accept key by default is the Esc or Escape key)?

25) What is the "Options input wrap" do in 4gl?

Feel free to copy this test and distribute, but please leave the following
on all copies of the test:
Copyright (c) 1994-95  Stuart Litel, P.O. Box 40, Newtonville, MA 02160
(617) 527-4551, FAX:(617) 964-4262, E-Mail: [email-masked]

- ---------------------------------- cut here for answers -----------

1) The construct statement allows the programmer to do a "Query
by Form"

2) The Input by name will bounce from field to field in order
that the fields were defined in the program_rec and the Input
from statement will bounce from field to field in the order the
fields are listed in the ".per" file.

3) The order external will be used when there was an "order by"
used in selecting the data passed into a report so the group
(before and after group) functions will be aware the data was
pre-sorted.

4) The "x" is never initialized to "1".  All informix arrays
must start at 1.  Also the program will blow up in the foreach
statement when the 101 row is retrieved (assuming the let x=1 is
put in)

5)
    errorlog() - allows text to be written to an errorlog file that
    was started and defined in the startlog() function.

    infield()  - checks to validate if the user's cursor is
    presently in a certain field.

    scr_line() - is the current screen line the user has the cursor
    in while in an array function.

    arg_val() - retrieves arguments passed in on a command line.

6) The first "x" will display "zero" (0) if you are using the
RDS version.  In the compiled version it will display some
unknown number and may even blown up!  The next three displays
will always display "2", "2" and "5" in that order.

7) Add a field to the screen and program record that will be a
character one (char(1)) field. Make this field the last column.
 In the .per file setup the field with the following attributes
"invisible" and "noentry"  and when you want to "bounce" to the
next row, simply say "next field" and the field name of the
"noentry" field.

8) The variable will be set to "TRUE" or "1" every time the
"Interrupt" key id pressed.  The only way to set it back to
FALSE or 0 is the user must do this in the program.  It is never
set back automatically except when a program is started.

9) For executing either a cursor or line of Informix 4GL/SQL
code at a later time in the program.

10) Nothing - they will do the same.

11) If you have a ring menu choice that is HELLO and want user
to press "O" to activate simple use the following:

        command key ("O") "hellO" "This will run the hellO functionality"

12) Simply add the following line immediately after the "Menu"
statement:

        hide option "hellO"

13) The "format" attribute is for displaying data and the
"picture" attribute is for input restrictions of data.

14) select * from person where state not matches "M*"

15)     select first_name, last_name, description
        from person, state
        where @state.state = person.state
NOTE: If using 4GL the "@" is a MUST!

16) select first_name, last_name, description
        from person, state outer
        where @state.state = person.state

17) select state, avg(age)
        from person
        group by state
        order by avg(age) desc

18) select state.description, count(*) from person, state where
state.state = person.state group by 1, order by 1

19) select * from person where state not in (select state from
state)

20) Nothing - they do the same thing.

21) Lock table in share mode will allow users to "view: only
data in the table but NO UPDATES.  Lock table in exclusive mode
will lock out all users from viewing and updating the data in
the table.

22) While in an input, prompt, or display array statement, the
on key statement will allow you to execute specific
functionality based on a function or control key sequence.

23) 4

24) Options accept key F10

25) When the last field is reached in either a construct or
input statement it will bounce back to the first field.