Modern Database Management (12th Edition)
Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
bartleby

Concept explainers

Question
Book Icon
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).

Expert Solution & Answer
Check Mark

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.

Want to see more full solutions like this?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!

Chapter 7 Solutions

Modern Database Management (12th Edition)

Ch. 7 - Under what conditions can a UNION clause be used?Ch. 7 - Prob. 7.12RQCh. 7 - Prob. 7.13RQCh. 7 - List for advantages of SQL-invoked routines.Ch. 7 - Prob. 7.15RQCh. 7 - Prob. 7.16RQCh. 7 - Prob. 7.17RQCh. 7 - Prob. 7.18RQCh. 7 - Prob. 7.19RQCh. 7 - Prob. 7.20RQCh. 7 - Prob. 7.21RQCh. 7 - Prob. 7.22RQCh. 7 - Prob. 7.23RQCh. 7 - This chapter discusses the data dictionary views...Ch. 7 - Prob. 7.25PAECh. 7 - Prob. 7.26PAECh. 7 - Prob. 7.27PAECh. 7 - Are based on the class schedule 3NF relations...Ch. 7 - Prob. 7.29PAECh. 7 - Prob. 7.30PAECh. 7 - Prob. 7.31PAECh. 7 - Are based on Figure 7-17. This problem set...Ch. 7 - Prob. 7.33PAECh. 7 - Prob. 7.34PAECh. 7 - Prob. 7.35PAECh. 7 - Prob. 7.36PAECh. 7 - Prob. 7.37PAECh. 7 - Prob. 7.38PAECh. 7 - Prob. 7.39PAECh. 7 - Prob. 7.40PAECh. 7 - Prob. 7.41PAECh. 7 - Prob. 7.42PAECh. 7 - Prob. 7.43PAECh. 7 - Prob. 7.44PAECh. 7 - Write an SQL query to display the order number,...Ch. 7 - Prob. 7.46PAECh. 7 - Prob. 7.47PAECh. 7 - Prob. 7.48PAECh. 7 - Prob. 7.49PAECh. 7 - Prob. 7.50PAECh. 7 - Write an SQL query to list each customer who...Ch. 7 - Prob. 7.52PAECh. 7 - Modify Problem and Exercise 6-60 so that the list...Ch. 7 - Prob. 7.54PAECh. 7 - Prob. 7.55PAECh. 7 - Prob. 7.56PAECh. 7 - Prob. 7.57PAECh. 7 - Prob. 7.58PAECh. 7 - Prob. 7.59PAECh. 7 - Prob. 7.60PAECh. 7 - Prob. 7.61PAECh. 7 - Prob. 7.62PAECh. 7 - Prob. 7.63PAECh. 7 - Rewrite your answer to Problem and Exercise 6-71...Ch. 7 - Display the customer ID, name, and order ID for...Ch. 7 - Prob. 7.66PAECh. 7 - Prob. 7.67PAECh. 7 - Prob. 7.68PAECh. 7 - Prob. 7.69PAECh. 7 - Prob. 7.70PAECh. 7 - Prob. 7.71PAECh. 7 - Write an SQL query to list the order number,...Ch. 7 - Prob. 7.73PAECh. 7 - Prob. 7.74PAECh. 7 - Prob. 7.75PAECh. 7 - Prob. 7.76PAE
Knowledge Booster
Background pattern image
Computer Science
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education