Chapter 7, Problem 7.1RQ

Program Plan Intro

Define the following terms: Dynamic SQL, Correlated subquery, embedded SQL, procedure, join, equi-join, self-join, outer join, function, and persistent stored modules (SQL/PSM).

Explanation of Solution

Dynamic SQL: Dynamic SQL is the programming paradigm which allows the programmer to generate SQL statements dynamically during runtime.

Correlated subquery: In SQL correlated subquery is subquery in which the processing of inner query depends on the outer query. While in a normal nested query the inner query is executed first and then the outer query is executed, in case of correlated query inner query is executed once for each row considered by outer query.

Embedded SQL: Embedded SQL is the practice of combining the capability to programming languages and SQL statements to perform database manipulation. With embedded SQL hard-coded SQL statements are included in the programming languages such as C, C++ and JAVA to perform database manipulation or access database.

Procedure: An SQL procedure is the stored SQL code containing multiple SQL statements which can be reused over and over again to perform some redundant task in the database.

Join: An SQL join refers to combination or two or more rows or columns from one or more tables in the database to create a temporary table. There are various kind joins such as equi join, self join, natural join, outer join, inner join etc.

Equi-join: It is a type of join in which the condition of joining is based on the equality between the values in common columns. Common columns are redundant in the resulting table in case of equi-join. For illustration purpose, consider the table below:

Table order
CustID	OrderID
100	502
101	206
102	205
103	1225

Table customer
CustID	CustName
100	John
101	Paul
102	Smith
103	Jason

For the above two tables on performing the equi join by the following SQL query:

SELECT Customer.CustID, Order.CustID,

CustName, OrderID

FROM Customer, Order

WHERE Customer.CustID = Order. CustID

ORDER BY OrderID

The resulting table will contain the following data:

CustID	CustID	CustName	OrderID
100	100	John	502
101	101	Paul	206
102	102	Smith	205
103	103	Jason	1225

Self-join: Self-join is the join in which table rows are matched with other rows of the same table. In short self-join can be termed as joining the table with itself. Self-join can be used to generate a sub table from a given large table. The syntax for performing self-join is as follows:

SELECT B1.column_name, B2.column_name...

FROM table1 X, table1 Y

WHERE B1.common_filed = B2.common_field;

Outer join: It is a join in which resulting table consists of matching rows from the joined table as well as non-matching rows based on the join condition. The syntax of using outer join is as follows:

Select *

FROM table1, table2

WHERE conditions [+];

Function: A function in SQL is a stored subroutine that takes one parameter as input and returns one value. They are similar to procedure except that they can only exercise one input parameter. There are many built in functions in SQL such as SUM, AVG, and COUNT.

Persistent stored modules (SQL/PSM): PSM or persistent stored module is the additional capability added in the SQL in 1999. These capabilities allowed programmer to create and drop modules of codes stored in the database schema across user session. It also gave SQL computational power and flow control capabilities such as IF-THEN, FOR, WHILE statements and loops.