J, LocalCurrencyAmountt is misspelled: J. The best way for students to understand SQL is by using it. Students can create databases in Microsoft Access with basic tables, relationships, and data from the material in the book. An Access version of WPC is also available there.
There is a useful teaching technique which will allow you to demonstrate the SQL queries in the text using Microsoft SQL Server if you have it available. This file contains all the queries shown in the Chapter 2 text. Highlight the query you want to run and click the Execute Query button to display the results of the query. An example of this is shown in the following screenshot. Microsoft Access does not support all SQL and newer constructs.
The correct solutions for these questions were obtained using Microsoft SQL Server , and solutions are shown for Access as well. For those students who are used to procedural languages, they may have some initial difficulty with a language that does set processing like SQL.
These students are accustomed to processing rows records rather than sets. It is time. If you can explain it in terms of traditional control break logic sort rows on a key then process the rows until the value of the key changes , they will have less trouble. In many cases, they will not know that Microsoft Access generates SQL code when you create a query in design view. It is worth letting them know this is done and even showing them the SQL created for and underlying a Microsoft Access query.
It has been our experience that a review of a Cartesian Product from an algebra class is time well spent. The following example will work. Assume you create four tables with five columns each and rows each. This happens because the JOIN is not qualified. If they understand Cartesian products then they will understand how to fix a JOIN where the results are much too large. The topic of reserved words and delimiters is discussed in more detail in Chapters 7 and 8. However, now is a good time to introduce it to your students.
See solutions to Review Question Screen shot solutions to all the queries in this chapter come from Microsoft Access. Note that some of them are from Access and some from Access the differences for the purposes of this chapter are entirely cosmetic font and other colors. What is an online transaction processing OLTP system? What is a business intelligence BI system? What is a data warehouse? An OLTP system is typically one in which a database is used to store information about daily operational aspects of a business or other enterprise, such as sales, deposits, orders, customers, etc.
A business intelligence BI system is a system used to support management decisions by producing information for assessment, analysis, planning and control. BI systems typically use data from a data warehouse, which is a database typically combining information from operational databases, other relevant internal data, and separately-purchased external data.
What is an ad-hoc query? An ad-hoc query is a query created by the user as needed, rather than a query programmed into an application. What does SKU stand for? What is an SKU? SKU stands for stock keeping unit. An SKU is a an identifier used to label and distinguish each item sold by a business. Summarize how data were altered and filtered in creating the Cape Codd data extraction. We also note that the OrderTotal column includes tax, shipping, and other charges that do not appear in the data extract.
The structure of the table is:. Thus one SKU may be associated once with each specific order number, but may also be associated with many different order numbers as long as it appears only once in each order. Using the Microsoft Access Relationship window, the relationships are shown in Figure and look like this:. Using an underline to show primary keys and italics to show foreign keys, the tables and their relationships are shown as:.
Summarize the background of SQL. That version is called SQL There is a later version called SQL3 that has some object-oriented concepts, but SQL3 has not received much commercial attention. What is SQL? How does it relate to the SQL statements in this chapter?
It is the version of SQL supported by most commonly used relational database management systems. Why is SQL described as a data sublanguage? A data sublanguage consists only of language statements for defining and processing a database.
To obtain a full programming language, SQL statements must be embedded in scripting languages such as VBScript or in programming languages such as Java or C. What does DML stand for? What are DML statements? DML stands for data manipulation language. DML statements are used for querying and modifying data. What does DDL stand for? What are DDL statements? DDL stands for data definition language.
DDL statements are used for creating tables, relationships. In this framework:. All data manipulation is expressed in SQL in these products. The eight tables in the revised Cape Codd database schema are shown in Figure You may have already created this database as suggested in Chapter 2 and used it to run the SQL queries discussed in the chapter.
Whenever possible, all results were obtained by. As explained in the text, some queries cannot be run in Microsoft Access , and for those queries the correct result was obtained using Microsoft SQL Server Solutions to Review Questions 2.
Specifically, why did we include it? Otherwise, a two table query would be required. Sort the results in ascending order by WarehouseID. Note that, as discussed in Chapter 2, Microsoft Access uses wildcard characters that differ from the SQL standard. Include meaningful column names in the result. To correct this, we use an SQL statement with the un-aliased computation:.
In your answer to Review Question 2. Otherwise there would be ambiguity in the SQL statement and the results would differ according to which clause was applied first.
Do not use the IN keyword. Use the IN keyword. Since we want the query output for warehouses that are not Atlanta or Bangor or Chicago as a set, we must ask for warehouses that are not in the group Atlanta and Bangor and Chicago. This happens because each OR eliminates only one warehouse, but that warehouse still qualifies for inclusion in the other OR statements. Do not be concerned with removing leading or trailing blanks. Use a subquery. Warehouse—the query will NOT work without them.
Explain why you cannot use a subquery in your answer to question 2. In a query that contains a subquery, only data from fields in the table used in the top-level query can be included in the SELECT statement. If data from fields from other tables are also needed, a. In question 2. Therefore, we had to use a join. Explain how subqueries and joins differ. If data from fields from other tables are also needed, a join must be used. See the answer to question 2.
In Chapter 8, correlated subqueries will be discussed, and correlated subqueries do not have an equivalent join structure—you must use subqueries. Run this statement.
SKU, I. QuantityOnHand, I. SKU, CS May Data Warehousing. Author s. Paulraj Ponniah Ph. First published April. Ponniah has published three successfulbooks and, as Adjunct Professor, continues to teach college coursesin data warehousing and database design.
Shopbop Designer Fashion Brands. Other editions — View all Data Warehousing Fundamentals: Cutting-edge content and guidance from a data warehousingexpert—now expanded to reflect field trends Data warehousing has revolutionized the way businesses in a widevariety of industries perform analysis and make strategicdecisions.
He also teaches database and data warehousing courses. Request permission to reuse content from this title. This practical Second Edition highlights the areas ofdata warehousing and business intelligence where high-impacttechnological progress has been made.
Learn more about Enterprise Learning Solutions. ComiXology Thousands of Digital Comics. If this is a republication request please include details of the new fundamentale in which the Wiley content will appear.
New to This Edition This second edition builds on areas in which significant technology progress has been made since the first edition. The Complete Guide to Dimensional Modeling.
This is a comprehensive text on data warehousing. Top Reviews Most recent Top Reviews. Sold by itemspopularsonlineaindemand and ships from Amazon Fulfillment. Data warehousing has revolutionized the way businesses in a wide variety of industries perform analysis and make strategic decisions.
This should include, the Wiley title sand the specific portion of the content you wish to re-use e. Specifically written for professionals responsible fordesigning, implementing, or maintaining data warehousing systems, Data Warehousing Fundamentals presents agile, thorough, andsystematic development principles for the IT professional andanyone working or researching in information management.
Amazon Advertising Find, attract, and engage customers. Data Warehouses and Data Marts. How to Provide Metadata. I do not have a IT background yet this book covers fundamenfals principles in a way the layman can understand.
Description Cutting-edge content and dtaa from a data warehousing expert—now fundsmentals to reflect field trends Data warehousing has revolutionized the way businesses in a wide variety of industries perform analysis and make strategic decisions.
The book also contains review questions and exercises for each chapter, appropriate for self-study or classroom work, industry examples of real-world situations, and several appendices with valuable information. Page 1 of 1 Start over Page 1 of 1. ITprofessionals eager to get into the field will gain a clearunderstanding of techniques for data extraction from sourcesystems, data cleansing, data transformations, data warehousearchitecture and infrastructure, and the various methods forinformation delivery.
User Review — Flag as inappropriate This book is the worst I have read on the subject.
0コメント