Deal of the Day

Sunday 3 July, 2011

Few SQL Questions


Hi Guy, 
This time I am back with Few SQL Questions....

What will runsqlstm will do?
If we want to execute set of SQL statement then we can write all the SQL statement to be including with the source.
Type as SQL
Only insert, update and delete and no select is allowed.
Then use STRSQLSTM to execute the program
RUNSQLSTM   SRCFILE (LIB/TEST) SRCMBR (SQL01) COMMIT (*NONE)


What is the advantage of Opnqryf over SQLRPG?
Opnqryf
sqlrpg
Opnqryf will come along with os/400 system and no need to have any additional package needed to execute it.
We need to have sqlrpg installed in as/400 system which involves additional cost to the programmers
Opnqryf is faster as compared to sqlrpg
It is slower
Opnqryf Is nothing but a dynamic logical files which will be created and the records are queried and finally the logical files are deleted and OPNQRYF provides that facility
SQLRPG is imbedding sql statements directly within SQL Statement

What is STRSQL use for?
To start into SQL.

How is embedded SQL used in RPG ?
/EXEC SQL
+SQL statements
+--------------------
+--------------------
/END EXEC

At which specs U can use an SQL command in RPGLE?
C-Spec

What is SQL ?
An application that uses an embedded SQL interface requires a precompiler to convert the SQL statements into code, which is then compiled, bound to the database, and executed. In contrast, a DB2 CLI application does not require precompilation or binding, but instead uses a standard set of functions to execute SQL statements and related services at runtime. This difference is important because, traditionally, precompilers have been specific to a database product, which effectively ties your applications to that product. DB2 CLI enables you to write portable applications that are independent of any particular database product. This independence means a DB2 CLI application does not have to be recompiled or rebound to access different database products, but rather selects the appropriate one at runtime. DB2 CLI can execute any SQL  statement that can be prepared dynamically in embedded SQL. This is guaranteed because DB2 CLI doesn't actually execute the SQL statement itself, but passes it to the DBMS for dynamic exec

Give examples of DCL, DDL, DML  SQL commands.
DATA CONTROL LANGUAGE(DCL)- grant , revoke.                                                                                                                                                                        
DATA DEFINITION LANGUAGE(DDL)- create, alter , drop.
DATA MANIPULATION LANGUAGE(DML)- select, insert, update, delete.

What are the column or aggregate functions available in SQL?
AVG for average, COUNT for counting the number of rows in the result table, MAX which returns the maximum value in the column,  MIN which returns the  minimum value, SUM which gives the sum total of the column.

Explain the usage of the terms GROUP BY, HAVING, ORDER BY.
GROUP BY - the column function will calculate its result based on the individual groups created by GROUP BY specification creating one result for each group. HAVING - is used to specify the conditions each row of each returned group must satisfy. 
ORDER BY - used to sort the output of a query using the column names or position of column in the list of columns named in the select expression.

What does the LIKE and IN and BETWEEN search operators allow you to do?
They search for rows where the column specified satisfies one among a number of values.
     LIKE - to select rows based on a similarity of partial strings.
                 ( - for a single unknown char and % for 0 to any no. of unknown char)
     IN - to select rows where the column values are one among a specific list of values.
     BETWEEN - to select rows having column values within the  given  limits. 

What are the DB2 objects that can be created using CREATE statement?
TABLE, INDEX, VIEW, SYNONYM, ALIAS, STOGROUP, DATABASE, TABLESPACE.

What are the DB2 objects you require before creating a Table?
Tablespace and Database are required before creating a table.

What is a CURSOR and how is it operated for read, update, delete?
A cursor is a named control structure used to make a selected set of rows available to a program one row at a time for read or update.
DECLARE CURSOR, OPEN CURSOR, FETCH CURSOR, CLOSE CURSOR are used to operate the cursor. The results table will be created during the execution of the
OPEN CURSOR statement. The values are fetched from the table row into the host variables and the cursor  position remains on the row until the next FETCH or CURSOR CLOSE statement. During this time ,the cursor position could be used to update or delete this row from the table using the WHERE CURRENT OF clause. 

What is the difference between Dynamic SQL and Embedded SQL?
Dynamic SQL statements are prepared and executed within a program  while a  program is being executed. The SQL source is contained in the host variables rather than being hard coded into the program and may change from execution to execution.
Embedded SQL statements are hard coded within the application program and are prepared during the program preparation process before the program is executed.

What is the difference between cascade and restrict with ref. to DELETE statement? What is a primary key and a foreign key?
Cascade and Restrict are part of the delete rule when specifying the referential constraints between two tables. Cascade on Delete - deletes all dependent rows from the dependent table while deleting a row in the parent table. The Restrict rule fails the  delete request if a dependent row exists.
A primary key is the unique identifier of the rows in a table.
A foreign key is a column or combination of columns in a table whose values are required to match with those of the primary key in some other table.

What is the difference between a base table, view, synonym, alias, index?
Base table - table that physically exists and has physical stored records ,can be  updated.
View - an alternative representation of data contained in one or more tables including all or some of the columns from the constituent tables , cannot be updated, automatically dropped when source tables are dropped.
Synonym - alternate name for a table or view, accessible only by creator of synonym, cannot be updated unless authorized to update base table.
Alias - similar to synonym , but accessible by all users who have access to the source tables represented by the alias, can be defined for local or remote objects before they exist, remains intact after the object it represents has been dropped.
Index - set of row identifiers or pointers that are logically ordered by the values of a column that has been specified as being an index, provides faster access to data and can enforce uniqueness on the row in a table.   

What is the difference between JOIN and UNION?
JOIN - relational operation that allows retrieval of data from two or more tables based on matching column values, results in addition of columns of the different constituent tables with the same key values.  
UNION - used to combine the results of two or more Select statements into a single results table, results in the addition of rows of the different tables.

What are Subqueries ?  What is SQLCA , where is it given in a COBOL pgm. ?
Subquery  is a query that is written as a part of another query’s  WHERE clause.
SQLCA ( SQL Communication Area )  is made up of a series of variables that are updated  after each SQL statement is executed and contains the sqlcode , it is given in the working-storage section using an SQL INCLUDE statement.

When do the following SQL codes occur?
+000  successful  execution.
-904  unavailable resource
-811  embedded SQL returning more than one rows
-100  row not found
-925  commit not valid

What is an alias?
It is an alternate name that can be used in SQL statements to refer to a table or view in the same or a remote DB2 subsystem.

What is meant by dynamic SQL?
Dynamic SQL are SQL statements that are prepared and executed within a program while the program is executing. The SQL source is contained in host variables rather than being “hard coded” into the program. The SQL statement may change from execution to execution.

What is meant by embedded SQL?
They are SQL statements that are embedded within an application program and are prepared during the program preparation process before the program is executed. After it is prepared the statement itself does not change (although values of the host variables specified within the statement might change).

What is meant by an index?
An index is a set of row identifiers (RIDs) or pointers that are logically ordered by the values of a column that has been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in the table.

What is a join?
A join is a relational operation that allows retrieval of data from two or more tables based on matching column values.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

2 comments:

  1. Hi Sumit,
    Congratulations for your blog, this article is excelent.

    I have a question: do you know a way to create Stored Procedures on AS400 without using iSeries Navigator - Run SQL script ?

    From command line, i tried to use:
    SBMJOB CMD(STRQSH CMD('db2 -tvf /home/StoredProdecure.sql > /home/StoredProdecure.log'))
    but it didn't work... it only works for create index on tables...

    As commented previously, i need a way to compile the stored procedures from command line, as it takes more time to open iseries navigator and run the script... i have to compile many stored procedures everyday...

    Thanks in advance for your help.

    Alejandro Padron,
    Mexico.

    ReplyDelete
  2. Hi Alex,
    Thanks for the appreciation.
    For SQL Procedures you can refer to my blog @ http://iseriesblogs.blogspot.com/2011/07/sql-stored-procedures.html

    I hope the blog answers you queries. If you still has any question, do feel to write me.

    Thanks and Regards,
    -Sumit

    ReplyDelete