From: slitel@netcom.com (Stuart Litel) Date: Sat, 4 Nov 1995 20:51:32 -0800 (PST) Subject: Informix Questions STUART LITEL Post Office Box 40 Newtonville, MA 02160 (617) 527 - 4551 FAX (617) 964 - 4262 slitel@netcom.com 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: slitel@netcom.com 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: slitel@netcom.com - ---------------------------------- 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.