{"id":30,"date":"2023-01-23T16:47:56","date_gmt":"2023-01-23T16:47:56","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-6-chapter-notes\/"},"modified":"2023-03-24T14:17:52","modified_gmt":"2023-03-24T14:17:52","slug":"module-6-chapter-notes","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-6-chapter-notes\/","title":{"raw":"Module 6 Chapter Notes","rendered":"Module 6 Chapter Notes"},"content":{"raw":"<h2>Chapter 8 Notes for Module 6<\/h2>\n<p class=\"page-title\"><strong>What is a CROSS JOIN?<\/strong><\/p>\nA CROSS JOIN is identical to the PRODUCT relational operator. The CROSS JOIN is also known as the Cartesian product of two tables. For example, if you have two tables, AGENT, with 10 rows and CUSTOMER, with 21 rows, the CROSS JOIN resulting set will have 210 rows and will include all of the columns from both tables. Syntax examples are:\n\nSELECT * FROM CUSTOMER CROSS JOIN AGENT;\n\nor\n\nSELECT * FROM CUSTOMER, AGENT\n\nIf you do not specify a join condition when joining tables, the result will be a CROSS Join or PRODUCT operation.\n<p class=\"page-title\"><strong>What three join types are included in the OUTER JOIN classification?<\/strong><\/p>\nAn OUTER JOIN is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows. (Unmatched rows are those without matching values in the join columns). The SQL standard prescribes three different types of join operations:\n\nLEFT [OUTER] JOIN\n\nRIGHT [OUTER] JOIN\n\nFULL [OUTER] JOIN.\n\nThe LEFT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the\u00a0<i>left<\/i>\u00a0table. (The left table is the\u00a0<i>first<\/i>\u00a0table named in the FROM clause.)\n\nThe RIGHT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the\u00a0<i>right<\/i>\u00a0table. (The right table is the\u00a0<i>second<\/i>\u00a0table named in the FROM clause.)\n\nThe FULL [OUTER] JOIN will yield all rows with matching values in the join columns, plus all the unmatched rows from both tables named in the FROM clause.\n<p class=\"page-title\"><strong>What is a subquery, and what are its basic characteristics?<\/strong><\/p>\nA subquery is a query (expressed as a SELECT statement) that is located inside another query. The first SQL statement is known as the outer query, the second is known as the inner query or subquery. The inner query or subquery is normally executed first. The output of the inner query is used as the input for the outer query. A subquery is normally expressed inside parenthesis and can return zero, one, or more rows and each row can have one or more columns.\n\nA subquery can appear in many places in a SQL statement:\n<ul>\n \t<li>as part of a FROM clause,<\/li>\n \t<li>to the right of a WHERE conditional expression,<\/li>\n \t<li>to the right of the IN clause,<\/li>\n \t<li>in a EXISTS operator,<\/li>\n \t<li>to the right of a HAVING clause conditional operator,<\/li>\n \t<li>in the attribute list of a SELECT clause.<\/li>\n<\/ul>\nExamples of subqueries are:\n\nINSERT INTO PRODUCT\n\nSELECT * FROM P;\n\nDELETE FROM PRODUCT\n\nWHERE V_CODE IN (SELECT V_CODE FROM VENDOR\n\nWHERE V_AREACODE = \u2018615\u2019);\n\nSELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 V_CODE, V_NAME\n\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VENDOR\n\nWHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);\n<p class=\"page-title\"><strong>What are the three types of results a subquery can return?<\/strong><\/p>\nA subquery can return 1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns).\n<p class=\"page-title\"><strong>What is a correlated subquery?<\/strong><\/p>\nA correlated subquery is subquery that executes once for each row in the outer query. This process is similar to the typical nested loop in a programming language. Contrast this type of subquery to the typical subquery that will execute the innermost subquery first, and then the next outer query \u2026 until the last outer query is executed. That is, the typical subquery will execute in serial order, one after another, starting with the innermost subquery. In contrast, a correlated subquery will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery.\n\nFor example, the following subquery will list all the product line sales in which the \u201cunits sold\u201d value is greater than the \u201caverage units sold\u201d value for\u00a0<i>that\u00a0<\/i>product (as opposed to the average for\u00a0<i>all\u00a0<\/i>products.)\n\nSELECT\u00a0\u00a0\u00a0\u00a0\u00a0 INV_NUMBER, P_CODE, LINE_UNITS\n\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LINE LS\n\nWHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LS.LINE_UNITS &gt; (SELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(LINE_UNITS) FROM LINE LA\n\nWHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LA.P_CODE = LS.P_CODE);\n\nThe previous nested query will execute the inner subquery once to compute the average sold units for each product code returned by the outer query.\n<p class=\"page-title\"><strong>What is the difference between a regular subquery and a correlated subquery?<\/strong><\/p>\nA regular, or uncorrelated subquery, executes before the outer query.\u00a0 It executes only once and the result is held for use by the outer query.\u00a0 A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query.\u00a0 Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query.\n<p class=\"page-title\"><strong>What does it mean to say that SQL operators are set-oriented?<\/strong><\/p>\nThe description of SQL operators as set-oriented means that the commands work over entire tables at a time, not row-by-row.\n<p class=\"page-title\"><strong>What does union-compatible mean, and how would you check for this condition?<\/strong><\/p>\nUnion compatible means that the relations yield attributes with identical names and compatible data types. That is, the relation\u00a0<b>A(c1,c2,c3)<\/b>\u00a0and the relation\u00a0<b>B(c1,c2,c3)<\/b>\u00a0have union compatibility if both relations have the same number of attributes, and corresponding attributes in the relations have \u201ccompatible\u201d data types.\u00a0<i>Compatible data types do not require that the attributes be exactly identical<\/i>\u00a0-- only that they are\u00a0<i>comparable<\/i>. For example, VARCHAR(15) and CHAR(15) are comparable, as are NUMBER (3,0) and INTEGER, and so on. Note that this is a practical definition of union-compatibility, which is different than the theoretical definition discussed in Chapter 3.\u00a0 From a theoretical perspective, corresponding attributes must have the same\u00a0<i>domain<\/i>.\u00a0 However, the DBMS does not understand the meaning of the business domain so it must work with a more concrete understanding of the data in the corresponding columns.\u00a0 Thus, it only considers the data types.\n<p class=\"page-title\"><strong>What is the difference between UNION and UNION ALL?<\/strong><\/p>\nUNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice that for two rows to be duplicated, they must have the same values in all columns.\n\nTo illustrate the difference between UNION and UNION ALL, let\u2019s assume two relations:\n\nA (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean)\n\nand\n\nB (ID, Name) with rows (1, River, 2, Lake, and 3, Ocean).\n\nGiven this description,\n\nSELECT * FROM A\n\nUNION\n\nSELECT * FROM\n\nwill yield:\n\n<b>ID<\/b>\u00a0\u00a0\u00a0\u00a0\u00a0<b>Name<\/b>\n\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake\n\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River\n\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean\n\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River\n\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake\n\nwhile\n\nSELECT * FROM A\n\nUNION ALL\n\nSELECT * FROM B\n\nwill yield:\n\nID\u00a0\u00a0\u00a0\u00a0 Name\n\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake\n\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River\n\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean\n\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River\n\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake\n\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean\n<p class=\"page-title\"><strong>Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?<\/strong><\/p>\nMINUS queries are analogous to algebraic subtraction \u2013 it results in the value that existed in the first operand that is not in the second operand.\u00a0 UNION queries are analogous to algebraic addition \u2013 it results in a combination of the two operands.\u00a0 (These analogies are not perfect, obviously, but they are helpful when learning the basics.) Addition and UNION have the commutative property (a + b = b + a), while subtraction and MINUS do not (a \u2013 b \u2260 b \u2013 a).\n<p class=\"page-title\"><strong>What MS Access\/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999?<\/strong><\/p>\nSELECT DATE()-#25-JAN-1999#\n\nNOTE: In MS Access you do not need to specify a FROM clause for this type of query.\n\n<strong>What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?<\/strong>\n\nSELECT \u00a0\u00a0\u00a0\u00a0 SYSDATE \u2013 TO_DATE(\u201925-JAN-1999\u2019, \u2018DD-MON-YYYY\u2019)\n\nFROM \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DUAL;\n\nNote that in Oracle, the SQL statement requires the use of the FROM clause. In this case, you may use the DUAL table. (The DUAL table is a dummy \u201cvirtual\u201d table provided by Oracle for this type of query.\u00a0 The table contains only one row and one column so queries against it can return just one value.)\n<p class=\"page-title\"><strong>What string function should you use to list the first three characters of a company\u2019s EMP_LNAME values?<\/strong><\/p>\nIn Oracle, you use the SUBSTR function as illustrated next:\n\nSELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;\n\nIn SQL Server, you use the SUBSTRING function as shown:\n\nSELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;\n<p class=\"page-title\"><strong>What is an Oracle sequence?<\/strong><\/p>\nAn Oracle sequence is a special type of object that generates unique numeric values in ascending or descending order. You can use a sequence to assign values to a primary key field in a table.\n\nA sequence provides functionality\u00a0<i>similar<\/i>\u00a0to the Autonumber data type in MS Access. For example, both, sequences and Autonumber data types provide unique ascending or descending values. However, there are some subtle differences between the two:\n<ul>\n \t<li>In MS Access an Autonumber is a\u00a0<i>data type<\/i>; in Oracle a sequence is\u00a0<i>a completely independent object<\/i>, rather than a data type.<\/li>\n \t<li>In MS Access, you can only have one Autonumber per table; in Oracle you can have as many sequences as you want and they are not tied to any particular table.<\/li>\n \t<li>In MS Access, the Autonumber data type is tied to a field in a table; in Oracle, the sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.<\/li>\n<\/ul>\nThe syntax used to create a sequence is:\n\nCREATE SEQUENCE CUS_NUM_SQ START WITH 100 INCREMENT BY 10 NOCACHE;\n<p class=\"page-title\"><strong>What is a trigger, and what is its purpose?<\/strong><\/p>\nA trigger is a block of PL\/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.) Triggers are always associated with a table and are invoked before or after a data row is inserted, updated, or deleted. Any table can have one or more triggers.\n\nTriggers provide a method of enforcing business rules such as:\n<ul>\n \t<li>A customer making a credit purchase must have an active account.<\/li>\n \t<li>A student taking a class with a prerequisite must have completed that prerequisite with a B grade.<\/li>\n \t<li>To be scheduled for a flight, a pilot must have a valid medical certificate and a valid training completion record.<\/li>\n<\/ul>\nTriggers are also excellent for enforcing data constraints that cannot be directly enforced by the data model. For example, suppose that you must enforce the following business rule:\n\nIf the quantity on hand of a product falls below the minimum quantity, the P_REORDER attribute must the automatically set to 1.\n\nTo enforce this business rule, you can create the following TRG_PRODUCT_REORDER trigger:\n\nCREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER\n\nBEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN ON PRODUCT\n\nFOR EACH ROW\n\nBEGIN\n\nIF :NEW.P_ONHAND &lt;= :NEW.P_MIN THEN\n\nNEW.P_REORDER := 1;\n\nELSE\n\n:NEW.P_REORDER := 0;\n\nEND IF;\n\nEND;\n<p class=\"page-title\"><strong>What is a stored procedure, and why is it particularly useful?<\/strong><\/p>\nA stored procedure is a named block of PL\/SQL and SQL statements. One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. For example, you can create a stored procedure to represent a product sale, a credit update, or the addition of a new customer. You can encapsulate SQL statements within a single stored procedure and execute them as a single transaction.\n\nThere are two clear advantages to the use of stored procedures:\n<ol>\n \t<li>Stored procedures substantially reduce network traffic and increase performance. Because the stored procedure is stored at the server, there is no transmission of individual SQL statements over the network.<\/li>\n \t<li>Stored procedures help reduce code duplication through code isolation and code sharing (creating unique PL\/SQL modules that are called by application programs), thereby minimizing the chance of errors and the cost of application development and maintenance.<\/li>\n<\/ol>\nFor example, the following PRC_LINE_ADD stored procedure will add a new invoice line to the LINE table and it will automatically retrieve the correct price from the PRODUCT table.\n\nCREATE OR REPLACE PROCEDURE PRC_LINE_ADD\n\n(W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER)\n\nAS\n\nW_LP NUMBER := 0.00;\n\nBEGIN\n\n-- GET THE PRODUCT PRICE\n\nSELECT P_PRICE INTO W_LP\n\nFROM PRODUCT\n\nWHERE P_CODE = W_P_CODE;\n\n-- ADDS THE NEW LINE ROW\n\nINSERT INTO LINE\n\nVALUES(INV_NUMBER_SEQ.CURRVAL, W_LN, W_P_CODE, W_LU, W_LP);\n\nDBMS_OUTPUT.PUT_LINE('Invoice line ' || W_LN || ' added');\n\nEND;\n<p class=\"page-title\"><strong>What is embedded SQL, and how is it used?<\/strong><\/p>\n<i>Embedded SQL<\/i>\u00a0is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion. The program may be a standard binary executable in Windows or Linux, or it may be a Web application designed to run over the Internet. No matter what language you use, if it contains embedded SQL statements it is called the\u00a0<i>host language<\/i>. Embedded SQL is still the most common approach to maintaining procedural capabilities in DBMS-based applications.\n\nFor example, the following embedded SQL code will delete the employee 109, George Smith, from the EMPLOYEE table\n\nEXEC SQL\n\nDELETE FROM EMPLOYEE WHERE EMP_NUM = 109;\n\nEND-EXEC.\n\nRemember that the preceding embedded SQL statement is compiled to generate an executable statement. Therefore, the statement is fixed permanently; that is, it cannot change -- unless, of course, the programmer changes it. Each time the program runs, it deletes the same row.\u00a0<i>In short, the preceding code is good only for the first run; all subsequent runs will more than likely give an error<\/i>. (Employee 109 is deleted the first time the embedded statement is executed. If you run the code again, it tries to delete an employee who has already been deleted.) Clearly, this code would be more useful if you are able to specify a variable to indicate which employee number is to be deleted.\n<p class=\"page-title\"><strong>What is dynamic SQL, and how does it differ from static SQL?<\/strong><\/p>\n<i>Dynamic SQL<\/i>\u00a0is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time. In a dynamic SQL environment, a program can generate the SQL statements (at run time) that are required to respond to ad-hoc queries. In such an environment, neither the programmers nor end users are likely to know precisely what kind of queries are to be generated or how those queries are to be structured. For example, a dynamic SQL equivalent of the example shown in question 19 might be:\n\nSELECT\u00a0\u00a0\u00a0<b>:<\/b>W_ATTRIBUTE_LIST\n\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<b>:<\/b>W_TABLE\n\nWHERE\u00a0\u00a0\u00a0\u00a0<b>:<\/b>W_CONDITION;\n\nNote that the attribute list and the condition are not known until the end user specifies them. W_TABLE, W_ATRIBUTE_LIST and W_CONDITION are text variables that contain the end-user input values used in the query generation. Because the program uses the end user input to build the text variables, the end user can run the same program multiple times to generate different outputs. For example, in one case the end user may one to know what products have a price less than $100; in another case, the end user may want to know how many units of a given product are available for sale at any given moment.\n\nAlthough dynamic SQL is clearly flexible, such flexibility carries a price. Dynamic SQL tends to be much slower that static SQL and it requires more computer resources (overhead). In addition, you are more likely to find different levels of support and incompatibilities between DBMS vendors.","rendered":"<h2>Chapter 8 Notes for Module 6<\/h2>\n<p class=\"page-title\"><strong>What is a CROSS JOIN?<\/strong><\/p>\n<p>A CROSS JOIN is identical to the PRODUCT relational operator. The CROSS JOIN is also known as the Cartesian product of two tables. For example, if you have two tables, AGENT, with 10 rows and CUSTOMER, with 21 rows, the CROSS JOIN resulting set will have 210 rows and will include all of the columns from both tables. Syntax examples are:<\/p>\n<p>SELECT * FROM CUSTOMER CROSS JOIN AGENT;<\/p>\n<p>or<\/p>\n<p>SELECT * FROM CUSTOMER, AGENT<\/p>\n<p>If you do not specify a join condition when joining tables, the result will be a CROSS Join or PRODUCT operation.<\/p>\n<p class=\"page-title\"><strong>What three join types are included in the OUTER JOIN classification?<\/strong><\/p>\n<p>An OUTER JOIN is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows. (Unmatched rows are those without matching values in the join columns). The SQL standard prescribes three different types of join operations:<\/p>\n<p>LEFT [OUTER] JOIN<\/p>\n<p>RIGHT [OUTER] JOIN<\/p>\n<p>FULL [OUTER] JOIN.<\/p>\n<p>The LEFT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the\u00a0<i>left<\/i>\u00a0table. (The left table is the\u00a0<i>first<\/i>\u00a0table named in the FROM clause.)<\/p>\n<p>The RIGHT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the\u00a0<i>right<\/i>\u00a0table. (The right table is the\u00a0<i>second<\/i>\u00a0table named in the FROM clause.)<\/p>\n<p>The FULL [OUTER] JOIN will yield all rows with matching values in the join columns, plus all the unmatched rows from both tables named in the FROM clause.<\/p>\n<p class=\"page-title\"><strong>What is a subquery, and what are its basic characteristics?<\/strong><\/p>\n<p>A subquery is a query (expressed as a SELECT statement) that is located inside another query. The first SQL statement is known as the outer query, the second is known as the inner query or subquery. The inner query or subquery is normally executed first. The output of the inner query is used as the input for the outer query. A subquery is normally expressed inside parenthesis and can return zero, one, or more rows and each row can have one or more columns.<\/p>\n<p>A subquery can appear in many places in a SQL statement:<\/p>\n<ul>\n<li>as part of a FROM clause,<\/li>\n<li>to the right of a WHERE conditional expression,<\/li>\n<li>to the right of the IN clause,<\/li>\n<li>in a EXISTS operator,<\/li>\n<li>to the right of a HAVING clause conditional operator,<\/li>\n<li>in the attribute list of a SELECT clause.<\/li>\n<\/ul>\n<p>Examples of subqueries are:<\/p>\n<p>INSERT INTO PRODUCT<\/p>\n<p>SELECT * FROM P;<\/p>\n<p>DELETE FROM PRODUCT<\/p>\n<p>WHERE V_CODE IN (SELECT V_CODE FROM VENDOR<\/p>\n<p>WHERE V_AREACODE = \u2018615\u2019);<\/p>\n<p>SELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 V_CODE, V_NAME<\/p>\n<p>FROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VENDOR<\/p>\n<p>WHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);<\/p>\n<p class=\"page-title\"><strong>What are the three types of results a subquery can return?<\/strong><\/p>\n<p>A subquery can return 1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns).<\/p>\n<p class=\"page-title\"><strong>What is a correlated subquery?<\/strong><\/p>\n<p>A correlated subquery is subquery that executes once for each row in the outer query. This process is similar to the typical nested loop in a programming language. Contrast this type of subquery to the typical subquery that will execute the innermost subquery first, and then the next outer query \u2026 until the last outer query is executed. That is, the typical subquery will execute in serial order, one after another, starting with the innermost subquery. In contrast, a correlated subquery will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery.<\/p>\n<p>For example, the following subquery will list all the product line sales in which the \u201cunits sold\u201d value is greater than the \u201caverage units sold\u201d value for\u00a0<i>that\u00a0<\/i>product (as opposed to the average for\u00a0<i>all\u00a0<\/i>products.)<\/p>\n<p>SELECT\u00a0\u00a0\u00a0\u00a0\u00a0 INV_NUMBER, P_CODE, LINE_UNITS<\/p>\n<p>FROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LINE LS<\/p>\n<p>WHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LS.LINE_UNITS &gt; (SELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(LINE_UNITS) FROM LINE LA<\/p>\n<p>WHERE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LA.P_CODE = LS.P_CODE);<\/p>\n<p>The previous nested query will execute the inner subquery once to compute the average sold units for each product code returned by the outer query.<\/p>\n<p class=\"page-title\"><strong>What is the difference between a regular subquery and a correlated subquery?<\/strong><\/p>\n<p>A regular, or uncorrelated subquery, executes before the outer query.\u00a0 It executes only once and the result is held for use by the outer query.\u00a0 A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query.\u00a0 Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query.<\/p>\n<p class=\"page-title\"><strong>What does it mean to say that SQL operators are set-oriented?<\/strong><\/p>\n<p>The description of SQL operators as set-oriented means that the commands work over entire tables at a time, not row-by-row.<\/p>\n<p class=\"page-title\"><strong>What does union-compatible mean, and how would you check for this condition?<\/strong><\/p>\n<p>Union compatible means that the relations yield attributes with identical names and compatible data types. That is, the relation\u00a0<b>A(c1,c2,c3)<\/b>\u00a0and the relation\u00a0<b>B(c1,c2,c3)<\/b>\u00a0have union compatibility if both relations have the same number of attributes, and corresponding attributes in the relations have \u201ccompatible\u201d data types.\u00a0<i>Compatible data types do not require that the attributes be exactly identical<\/i>\u00a0&#8212; only that they are\u00a0<i>comparable<\/i>. For example, VARCHAR(15) and CHAR(15) are comparable, as are NUMBER (3,0) and INTEGER, and so on. Note that this is a practical definition of union-compatibility, which is different than the theoretical definition discussed in Chapter 3.\u00a0 From a theoretical perspective, corresponding attributes must have the same\u00a0<i>domain<\/i>.\u00a0 However, the DBMS does not understand the meaning of the business domain so it must work with a more concrete understanding of the data in the corresponding columns.\u00a0 Thus, it only considers the data types.<\/p>\n<p class=\"page-title\"><strong>What is the difference between UNION and UNION ALL?<\/strong><\/p>\n<p>UNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice that for two rows to be duplicated, they must have the same values in all columns.<\/p>\n<p>To illustrate the difference between UNION and UNION ALL, let\u2019s assume two relations:<\/p>\n<p>A (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean)<\/p>\n<p>and<\/p>\n<p>B (ID, Name) with rows (1, River, 2, Lake, and 3, Ocean).<\/p>\n<p>Given this description,<\/p>\n<p>SELECT * FROM A<\/p>\n<p>UNION<\/p>\n<p>SELECT * FROM<\/p>\n<p>will yield:<\/p>\n<p><b>ID<\/b>\u00a0\u00a0\u00a0\u00a0\u00a0<b>Name<\/b><\/p>\n<p>1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean<\/p>\n<p>1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake<\/p>\n<p>while<\/p>\n<p>SELECT * FROM A<\/p>\n<p>UNION ALL<\/p>\n<p>SELECT * FROM B<\/p>\n<p>will yield:<\/p>\n<p>ID\u00a0\u00a0\u00a0\u00a0 Name<\/p>\n<p>1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean<\/p>\n<p>1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 River<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Lake<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ocean<\/p>\n<p class=\"page-title\"><strong>Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?<\/strong><\/p>\n<p>MINUS queries are analogous to algebraic subtraction \u2013 it results in the value that existed in the first operand that is not in the second operand.\u00a0 UNION queries are analogous to algebraic addition \u2013 it results in a combination of the two operands.\u00a0 (These analogies are not perfect, obviously, but they are helpful when learning the basics.) Addition and UNION have the commutative property (a + b = b + a), while subtraction and MINUS do not (a \u2013 b \u2260 b \u2013 a).<\/p>\n<p class=\"page-title\"><strong>What MS Access\/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999?<\/strong><\/p>\n<p>SELECT DATE()-#25-JAN-1999#<\/p>\n<p>NOTE: In MS Access you do not need to specify a FROM clause for this type of query.<\/p>\n<p><strong>What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?<\/strong><\/p>\n<p>SELECT \u00a0\u00a0\u00a0\u00a0 SYSDATE \u2013 TO_DATE(\u201925-JAN-1999\u2019, \u2018DD-MON-YYYY\u2019)<\/p>\n<p>FROM \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DUAL;<\/p>\n<p>Note that in Oracle, the SQL statement requires the use of the FROM clause. In this case, you may use the DUAL table. (The DUAL table is a dummy \u201cvirtual\u201d table provided by Oracle for this type of query.\u00a0 The table contains only one row and one column so queries against it can return just one value.)<\/p>\n<p class=\"page-title\"><strong>What string function should you use to list the first three characters of a company\u2019s EMP_LNAME values?<\/strong><\/p>\n<p>In Oracle, you use the SUBSTR function as illustrated next:<\/p>\n<p>SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;<\/p>\n<p>In SQL Server, you use the SUBSTRING function as shown:<\/p>\n<p>SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE;<\/p>\n<p class=\"page-title\"><strong>What is an Oracle sequence?<\/strong><\/p>\n<p>An Oracle sequence is a special type of object that generates unique numeric values in ascending or descending order. You can use a sequence to assign values to a primary key field in a table.<\/p>\n<p>A sequence provides functionality\u00a0<i>similar<\/i>\u00a0to the Autonumber data type in MS Access. For example, both, sequences and Autonumber data types provide unique ascending or descending values. However, there are some subtle differences between the two:<\/p>\n<ul>\n<li>In MS Access an Autonumber is a\u00a0<i>data type<\/i>; in Oracle a sequence is\u00a0<i>a completely independent object<\/i>, rather than a data type.<\/li>\n<li>In MS Access, you can only have one Autonumber per table; in Oracle you can have as many sequences as you want and they are not tied to any particular table.<\/li>\n<li>In MS Access, the Autonumber data type is tied to a field in a table; in Oracle, the sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table.<\/li>\n<\/ul>\n<p>The syntax used to create a sequence is:<\/p>\n<p>CREATE SEQUENCE CUS_NUM_SQ START WITH 100 INCREMENT BY 10 NOCACHE;<\/p>\n<p class=\"page-title\"><strong>What is a trigger, and what is its purpose?<\/strong><\/p>\n<p>A trigger is a block of PL\/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.) Triggers are always associated with a table and are invoked before or after a data row is inserted, updated, or deleted. Any table can have one or more triggers.<\/p>\n<p>Triggers provide a method of enforcing business rules such as:<\/p>\n<ul>\n<li>A customer making a credit purchase must have an active account.<\/li>\n<li>A student taking a class with a prerequisite must have completed that prerequisite with a B grade.<\/li>\n<li>To be scheduled for a flight, a pilot must have a valid medical certificate and a valid training completion record.<\/li>\n<\/ul>\n<p>Triggers are also excellent for enforcing data constraints that cannot be directly enforced by the data model. For example, suppose that you must enforce the following business rule:<\/p>\n<p>If the quantity on hand of a product falls below the minimum quantity, the P_REORDER attribute must the automatically set to 1.<\/p>\n<p>To enforce this business rule, you can create the following TRG_PRODUCT_REORDER trigger:<\/p>\n<p>CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER<\/p>\n<p>BEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN ON PRODUCT<\/p>\n<p>FOR EACH ROW<\/p>\n<p>BEGIN<\/p>\n<p>IF :NEW.P_ONHAND &lt;= :NEW.P_MIN THEN<\/p>\n<p>NEW.P_REORDER := 1;<\/p>\n<p>ELSE<\/p>\n<p>:NEW.P_REORDER := 0;<\/p>\n<p>END IF;<\/p>\n<p>END;<\/p>\n<p class=\"page-title\"><strong>What is a stored procedure, and why is it particularly useful?<\/strong><\/p>\n<p>A stored procedure is a named block of PL\/SQL and SQL statements. One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. For example, you can create a stored procedure to represent a product sale, a credit update, or the addition of a new customer. You can encapsulate SQL statements within a single stored procedure and execute them as a single transaction.<\/p>\n<p>There are two clear advantages to the use of stored procedures:<\/p>\n<ol>\n<li>Stored procedures substantially reduce network traffic and increase performance. Because the stored procedure is stored at the server, there is no transmission of individual SQL statements over the network.<\/li>\n<li>Stored procedures help reduce code duplication through code isolation and code sharing (creating unique PL\/SQL modules that are called by application programs), thereby minimizing the chance of errors and the cost of application development and maintenance.<\/li>\n<\/ol>\n<p>For example, the following PRC_LINE_ADD stored procedure will add a new invoice line to the LINE table and it will automatically retrieve the correct price from the PRODUCT table.<\/p>\n<p>CREATE OR REPLACE PROCEDURE PRC_LINE_ADD<\/p>\n<p>(W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER)<\/p>\n<p>AS<\/p>\n<p>W_LP NUMBER := 0.00;<\/p>\n<p>BEGIN<\/p>\n<p>&#8212; GET THE PRODUCT PRICE<\/p>\n<p>SELECT P_PRICE INTO W_LP<\/p>\n<p>FROM PRODUCT<\/p>\n<p>WHERE P_CODE = W_P_CODE;<\/p>\n<p>&#8212; ADDS THE NEW LINE ROW<\/p>\n<p>INSERT INTO LINE<\/p>\n<p>VALUES(INV_NUMBER_SEQ.CURRVAL, W_LN, W_P_CODE, W_LU, W_LP);<\/p>\n<p>DBMS_OUTPUT.PUT_LINE(&#8216;Invoice line &#8216; || W_LN || &#8216; added&#8217;);<\/p>\n<p>END;<\/p>\n<p class=\"page-title\"><strong>What is embedded SQL, and how is it used?<\/strong><\/p>\n<p><i>Embedded SQL<\/i>\u00a0is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion. The program may be a standard binary executable in Windows or Linux, or it may be a Web application designed to run over the Internet. No matter what language you use, if it contains embedded SQL statements it is called the\u00a0<i>host language<\/i>. Embedded SQL is still the most common approach to maintaining procedural capabilities in DBMS-based applications.<\/p>\n<p>For example, the following embedded SQL code will delete the employee 109, George Smith, from the EMPLOYEE table<\/p>\n<p>EXEC SQL<\/p>\n<p>DELETE FROM EMPLOYEE WHERE EMP_NUM = 109;<\/p>\n<p>END-EXEC.<\/p>\n<p>Remember that the preceding embedded SQL statement is compiled to generate an executable statement. Therefore, the statement is fixed permanently; that is, it cannot change &#8212; unless, of course, the programmer changes it. Each time the program runs, it deletes the same row.\u00a0<i>In short, the preceding code is good only for the first run; all subsequent runs will more than likely give an error<\/i>. (Employee 109 is deleted the first time the embedded statement is executed. If you run the code again, it tries to delete an employee who has already been deleted.) Clearly, this code would be more useful if you are able to specify a variable to indicate which employee number is to be deleted.<\/p>\n<p class=\"page-title\"><strong>What is dynamic SQL, and how does it differ from static SQL?<\/strong><\/p>\n<p><i>Dynamic SQL<\/i>\u00a0is a term used to describe an environment in which the SQL statement is not known in advance; instead, the SQL statement is generated at run time. In a dynamic SQL environment, a program can generate the SQL statements (at run time) that are required to respond to ad-hoc queries. In such an environment, neither the programmers nor end users are likely to know precisely what kind of queries are to be generated or how those queries are to be structured. For example, a dynamic SQL equivalent of the example shown in question 19 might be:<\/p>\n<p>SELECT\u00a0\u00a0\u00a0<b>:<\/b>W_ATTRIBUTE_LIST<\/p>\n<p>FROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<b>:<\/b>W_TABLE<\/p>\n<p>WHERE\u00a0\u00a0\u00a0\u00a0<b>:<\/b>W_CONDITION;<\/p>\n<p>Note that the attribute list and the condition are not known until the end user specifies them. W_TABLE, W_ATRIBUTE_LIST and W_CONDITION are text variables that contain the end-user input values used in the query generation. Because the program uses the end user input to build the text variables, the end user can run the same program multiple times to generate different outputs. For example, in one case the end user may one to know what products have a price less than $100; in another case, the end user may want to know how many units of a given product are available for sale at any given moment.<\/p>\n<p>Although dynamic SQL is clearly flexible, such flexibility carries a price. Dynamic SQL tends to be much slower that static SQL and it requires more computer resources (overhead). In addition, you are more likely to find different levels of support and incompatibilities between DBMS vendors.<\/p>\n","protected":false},"author":11,"menu_order":6,"template":"","meta":{"pb_show_title":"","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[49],"contributor":[],"license":[],"class_list":["post-30","chapter","type-chapter","status-publish","hentry","chapter-type-numberless"],"part":19,"_links":{"self":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/users\/11"}],"version-history":[{"count":1,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/30\/revisions"}],"predecessor-version":[{"id":31,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/30\/revisions\/31"}],"part":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/parts\/19"}],"metadata":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/30\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapter-type?post=30"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/contributor?post=30"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/license?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}