The best example I have is doing adhoc queries on a production system. Consider the case where you cannot deploy schema level PL/SQL. I would also add a caveat that if the procedure is big and gnarly, I would rather compile it in the schema than trying to debug it in the middle of a giant query. There are not that many tasks that are so difficult in SQL that this condition exists, but there are some where procedural code is just a better answer. The best case I have for deploying inline methods in production code is when the logic is specific to that single query, and is not easily and cleanly done directly in SQL. Yet in the same release we were given PRAGMA UDF which allows optimizing schema level PL/SQL functions as inline. The primary reason (allegedly) that Oracle provided for defining the PL/SQL code inline is to improve performance by avoiding context switching. Tim Hall of Oracle Base fame has a good primer on it WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1). The Oracle documentation for it that I have found so far is sparse. The capability to define PL/SQL functions and procedures inside an Oracle SQL query (and even the query portion of DML statements) was added in Oracle version 12.1. Write your own queries that retrieve multiple columns in a precise order from a table, and that restrict the rows retrieved from those columns using “BETWEEN”, “IN”, and references to text strings.Declaring PL/SQL in an Oracle SQL WITH Clause To see some examples of these likely errors, examine: (a) rows in the trsnact table that have “0” in their orgprice column (how could the original price be 0?), (b) rows in the skstinfo table where both the cost and retail price are listed as 0.00, and (c) rows in the skstinfo table where the cost is greater than the retail price (although occasionally retailers will sell an item at a loss for strategic reasons, it is very unlikely that a manufacturer would provide a suggested retail price that is lower than the cost of the item).Įxercise 6. When the “amt” is different than “sprice” then the quantity purchased was more than 1Įxercise 5: Even though the Dillard’s dataset had primary keys declared and there were not many NULL values, there are still many strange entries that likely reflect entry errors. What did you learn about how the values in “amt”, “quantity”, and “sprice” relate to one another? Examine instances of transaction table where “amt” is different than “sprice”. Examine lists of distinct values in each of the tables.Įxercise 4. Pay particular attention to the skuinfo table.Įxercise 3. Look at examples of data from each of the tables. Use HELP and SHOW to confirm that the relational schema provided to us for the Dillard’s dataset shows the correct column names and primary keys for each table.Įxercise 2. These are my personal codes and criticisms and additions are welcomeĮxercise 1. The SQL queries here were written as part of assignments in Coursera course. )įor reference, the full list of tables from the Dillard’s dataset are Hence there are 7 tables that will be looked at here. (Note: there was an additional table provided in the course which gave more data to the SKSTINFO and that table is the SKSTINFO_FIX. The full actual and details of the tables in the database can be found on the University of Arkansas website UA_DILLARDS that contains sales transaction data for 453 Dillard’s Department stores between August 2004 and July 2005. This is the database of Dillard’s Department stores, specifically, the As part of “ Managing Big Data with MySQL” by Duke University on Cousera, I dealt with real world data that comprise hundreds to millions of entries/rows. Analysing ‘Big Data’ from a database with real world data requires retrieving data from the database by writing relevant queries.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |