This website uses cookies to ensure you have the best experience. Learn more

Chapter 8 Advanced Sql Essay

2100 words - 9 pages

Chapter 8 Advanced SQL

Chapter Objectives

This chapter continues what was covered in Chapter 7. While Chapter 7 dealt with single table queries, Chapter 8 discusses joins. Other topics are also included, such as triggers, stored procedures, functions, Embedded SQL, Dynamic SQL, and Persistent Stored Modules. This chapter also contains a detailed discussion of transaction integrity as well as the SQL-99 enhancements and extensions to SQL. An overview of data dictionaries is also included. Chapter 7 is obviously a prerequisite for this chapter.
Specific student learning objectives are included at the beginning of the chapter. From an instructor's point of view, the objectives of ...view middle of the document...

You may also want to discuss the various DBA views available and show these to the students during your lecture.
3. When discussing multiple table queries, always emphasize that there is more than one way to write a query. For example, show the students a query using a join and then the same query using subqueries.
4. Emphasize the cases when a subquery is needed and cannot be substituted with a join. A good example of this would be the case where one needs to find all customers who never purchased a product (using a subquery with the NOT IN qualifier).
5. Develop an exercise for the students to explore the effects of a trigger. Have them create and populate some tables, write an insert trigger for one of the tables that might impact other tables. You could then have the students insert some records and see the results. It is important to show the effects of triggers through examples that the students can try out followed by problems that they would have to solve by writing triggers. The same can be said for stored procedures.
6. The discussion on SQL/PSM might be a good place to introduce PL/SQL before introducing triggers and stored procedures.

Answers to Review Questions

1. Define each of the following key terms:
a. Dynamic SQL. The process of making an application capable of generating specific SQL code on the fly, as the application is processed.
b. Correlated subquery. This type of subquery is processed outside in, rather than inside out. That is, the inner query is executed for each row in the outer query, and the inner query depends in some way on values from the current row in the outer query.
c. Embedded SQL. The process of including hard-coded SQL statements in a program written in another language, such as C or Java.
d. Procedure. A collection of procedural and SQL statements that are assigned a unique name within the schema and stored in the database.
e. Join. The most frequently used relational operation, which brings together data from two or more related tables into one result table.
f. Equi-join. A join in which the joining condition is based on equality between values in the common columns. It produces a table of rows composed of columns from two other tables, where common columns appear (redundantly) in the result table.
g. Natural join. A join that returns all requested results, but values of the common columns are included only once. Natural joins are the same as equi-joins, except that duplicate columns are eliminated. The natural join is the most commonly used form of join operation;
h. Outer join. A join in which rows that do not have matching values in common columns are nevertheless included in the result table. Outer joins return all the values in one of the tables included in the join, regardless of whether a match exists in the other table(s) or not.
i. Function. A stored subroutine that returns one value and has only input parameters.
j. Persistent stored...

Other Papers Like Chapter 8 Advanced Sql

Db Testing Essay

5118 words - 21 pages learn about the most common and vexing defects related to SQL databases and the best tools available to support your testing efforts. Copyright Sammamish Software Services 2003. All rights Reserved 1 8/26/2004 The Data Access Layer Testing at the data access layer is the point at which your application communicates with the database. ! In this presentation we’ll discuss why tests at this level are vital to improve not only your overall

Introducing Transaction Log Files Essay

2172 words - 9 pages portion of the transaction log required for recovery and backup will vary with the recovery model chosen. Backup and restore strategies are covered in Chapter 8. How the Transaction Log Works SQL Server 2000 uses a buffer cache, which is an in-memory structure, into which it retrieves data pages from disk for use by applications and users. Each modification to a data page is made to the copy of the page in the buffer cache. A modified

Unit 1 and Part of 2

1038 words - 5 pages advanced features for more experienced programmers. 8. Ruby on Rails Like Java or the C language, Ruby is a general purpose programming language, though it is best known for its use in web programming, and Rails serves as a framework for the Ruby Language. Ruby on Rails has many positive qualities including rapid development, you don’t need as much code, and there are a wide variety of 3rd party libraries available. It’s used from companies ranging

It222 Unit 9 Assignment

559 words - 3 pages advanced command-line and scripting interface included in Windows Server 2008. ____G___ 4.This volume houses the boot files for a Windows Server 2008 computer. ____E___ 5. This describes the process of removing tombstoned objects from the NTDS.DIT file. ____A___ 6. You will need to perform this operation if you have inadvertently deleted one or more Active Directory objects. ____F___ 7. This is a graphical user interface that will allow you

Mr Mhlongo, Will Use This Site for Research Purpose and Other Reasons

3943 words - 16 pages I (VB.NET & ASP.NET) DEV201I DEVELOPMENT SOFTWARE II (PL/SQL ORACLE) DEV3M1I DEVELOPMENT SOFTWARE III: ADV TOPICS DEV3M2I DEVELOPMET SOFTWARE III: PROJECT INY3M1I ADVANCED DATABASES (PL/SQL ORACLE) INY3M2I PROJECT MANAGEMENT TEP101I TECHNICAL PROGRAMMING I (JAVA) TEP201I TECHNICAL PROGRAMMING II (C++) ASK131U ACCOUNTING SKILLS I INT2M1I INTERNET FUNDAMENTALS (XML & SOAP Web Services) WEB201I WEB MANAGEMENT

Visual Basic Preview

5065 words - 21 pages ............................................................................ 43 Hiding and Showing Forms in Visual Basic .............................................................. 46 Chapter 8. 8.1 8.2 8.3 8.4 8.5 8.6 Creating a Visual Basic Application Containing Multiple Forms .................................... 46 Understanding Modal and Non-modal Forms ............................................................... 48 Writing Visual Basic Code to Display a Non-Modal Form

Logical Design

655 words - 3 pages retrieved on April 21, 13 from http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/ Ricardo, C. (2012). Databases Illuminated, Second Edition. Sudbury, MA. Jones & Bartlett Learning (Chapter 2 pg. 50-58/Chapter 8 pg. 287-289)

Lab Validation Report

5252 words - 22 pages  real-­‐time  data  transactions  and  analytic  workloads.  Designed  for  use  cases  that  require   instant  access  to  both  real-­‐time  and  historical  data,  MemSQL  is  based  on  a  distributed  relational  database   architecture  with  an  analytics  engine  that  runs  on  SQL,  the  most  popular  database

Transaction Management Ch 10

4346 words - 18 pages Diagram [pic] Using this database, write the SQL code to represent each one of the following transactions. Use BEGIN TRANSACTION and COMMIT to group the SQL statements in logical transactions. a. On May 11, 2008, customer ‘10010’ makes a credit purchase (30 days) of one unit of product ‘11QER/31’ with a unit price of $110.00; the tax rate is 8 percent. The invoice number is 10983, and this invoice has only one product line

Hostel Management System

2259 words - 10 pages CHAPTER 1 INTRODUCTION 1.1 Problem definition We have got nine hostels in our university, which consist of four boy’s hostel and five girl’s hostel. All these hostels at present are managed manually by the hostel office. The Registration form verification to the different data processing are done manually. Thus there are a lot of repetitions which can be easily avoided. And hence there is a lot of strain on the person who are running the

Ouuop

384 words - 2 pages Anna Lewis Advanced Accounting Seminar II DQs Chapter 2: 8 First Morgan Company will find the fair value of Jennings, Inc and the fair value of the stock shares. The excess if recorded as goodwill. Because dissolution will occur, Jennings’ asset and liability accounts are transferred to Morgan and entered at fair value with excess recorded to goodwill (Doupnik, Hoyle & Schaefer, 2011). Chapter 2:9 A bargain purchase gain will be

Related Essays

Sql Server 2008 Week 1 Essay

798 words - 4 pages True or False 1. T 2. F 3. T 4. F 5. T 6. T 7. T 8. F 9. T 10. T 11. T 12. T 13. F 14. T 15. T 16. F 17. F 18. T 19. T 20. T 1. 3  Major Revisions and 11 minor. The latest SQL standard was adopted in July 2003 and is often called SQL:2003. 2. SQL Express SQL Server Express supports most of the features and functionality of SQL Server. The following

Biometrics Essay

1931 words - 8 pages Scanning. v. DNA Analysis. b. Behavioral verification i. Typing. ii. Signature. iii. Voice. 4. Identification. 5. Verification. 6. Advantages 7. Limitations 8. Conclusion. 9. References Introduction Biometrics is an advanced technology for superb security and authentication .The very term "biometric” it represent that "bio" means

Computer Tricks Essay

3238 words - 13 pages traditional network firewalls. The following are the various types of Web application threats covered by this chapter: 1. Cross-site scripting 2. SQL injection 3. Command injection 4. Cookie/session poisoning 5. Parameter/form tampering 6. Buffer overflow 7. Directory traversal/forceful browsing 8. Cryptographic interception 9. Authentication hijacking 10. Log tampering 11. Error message interception attack 12. Obfuscation application 13

Asignment Essay

3234 words - 13 pages server manages other users’ transactions and prevents contention between transactions that request the same data. See Also: Chapter 9, "Process Architecture" for more information about Oracle configuration 3. 4. 5. 6. 7. 8. 9. Overview of Oracle Utilities Oracle provides several utilities for data transfer, data maintenance, and database administration, including Data Pump Export and Import, SQL*Loader, and LogMiner. See Also