24-01 Pro Oracle SQL Development

Relational databases, based on principles described by E. F. Codd in 1970, have been significantly shaped by Oracle Corporation, which released the first commercial SQL database in 1979 under the leadership of co-founder Larry Ellison. Oracle has since maintained a dominant position in the database market.

Despite its success, the Oracle database has some outdated and confusing features for developers, such as unconventional join syntax, treating empty strings as null, a 30-byte name limit (now extended to 128 bytes), and quirky behaviors in SQL*Plus. Oracle’s adherence to standards has been criticized, but it is also acknowledged that many of its early decisions predated the establishment of those standards.

Oracle has a history of continuously adding features, sometimes in response to industry trends, which has led to a wide variety of capabilities being integrated into the database over time, including multiversion concurrency control, PL/SQL, object-relational and Java support, and cloud integration, among others. However, not all of these features have been successful or well-received, with some being criticized for moving the database in the wrong direction.

Despite this, Oracle’s comprehensive feature set allows it to function like a Swiss Army knife for database solutions, catering to a broad range of technological trends without requiring a different database for each new innovation. The company is expected to continue introducing important new features, but users should approach these with caution, staying informed through resources like the "New Features" chapter of the Oracle manual, rather than immediately adopting them.

The relational model, foundational to the Oracle database, was introduced by E. F. Codd in 1970. It emphasizes simplicity and the use of tables, rows, and columns—referred to in theoretical terms as relations, tuples, and attributes. The model advocates for eliminating redundancy and complexity through normalization and use of primary keys to maintain data integrity and prevent update anomalies. The relational model simplifies database design by focusing on relationships between tables, which are handled through constraints and joins. While tables are a common and seemingly simple data structure, creating more tables rather than complicating existing ones is the recommended approach for handling complex data.

Implementing the relational model comes with challenges, as it requires the database to manage the physical data structures and access algorithms. Oracle, as a database management system, does much of this heavy lifting, though users must still make trade-offs regarding performance and storage. Codd acknowledged that some data redundancy is inevitable and suggested the future development of metadata systems to prevent inconsistencies, a solution that is not yet fully automated. For now, database administrators and developers must remain vigilant and disciplined to avoid and manage data inconsistencies.

The provided text highlights the importance of practicality over purity in the use of relational databases and the SQL language. It addresses common criticisms of the relational model and suggests practical workarounds:

In summary, the text argues for a balanced approach to using relational databases, where theoretical ideals are tempered with practical solutions to real-world problems. It encourages accepting certain imperfections like NULLs and denormalization as part of effective database design and operation.

The text discusses the significance of SQL (Structured Query Language) as the predominant language for interacting with relational databases. It touches on the history of SQL, the importance of understanding relational operations, and the enduring relevance of SQL despite the existence of alternative query languages.

Key points include:

In conclusion, SQL is seen as a high-level language that is unlikely to be replaced in the near future for querying relational data. The text emphasizes the language’s adaptability, its acceptance as a programming language, and the impracticality of visual query builders for complex tasks.

This section discusses the broad array of database technologies available and the capabilities of Oracle databases, emphasizing that Oracle is a robust and versatile database management system (DBMS) that can handle a variety of database workloads, including both online transaction processing (OLTP) and data warehousing (DW).

Key points include:

The provided text is a summary and guide on writing SQL statements, with a focus on the importance of understanding ANSI join syntax, inline views, nulls, and various types of joins in Oracle SQL. The key points are:

This summary omits the visual aids and specific code examples provided for each type of join but captures the main educational points regarding the importance and use of null handling and joins in SQL.

The text discusses two approaches to Oracle database development: the common shared database server approach and the less common private database for each developer approach. The author advocates for private database development for most projects due to the increased control it offers developers, despite acknowledging that shared database development may still be suitable for certain situations with small teams or stable applications. Although private database development may initially seem daunting due to concerns about environment, security, and deployment, the author assures that such challenges can be overcome, as demonstrated by many successful organizations. The chapter focuses on private database development and suggests that having full control over an imperfect copy of the production environment is preferable to having little control over a perfect copy.

The text discusses the benefits of using private Oracle databases for development compared to shared databases and outlines methods to set up private databases.

Benefits of Private Databases:

Setting up Private Databases:

In conclusion, the text advocates for the use of private databases in development environments to enhance productivity, security, and innovation while providing various ways to set up these private databases, from simple local installations to more advanced options like cloud services and containers.

The provided text emphasizes the importance of frequent database deployments and the use of simple, disciplined approaches to manage deployment scripts. The key points are:

In summary, the text advocates for disciplined, frequent database deployments using simple tools like SQLPlus and manual script creation. This approach ensures reliability, maintainability, and leverages the full features of the database system.

This text emphasizes the importance of using version control for managing database schemas and code, rather than relying on databases for this purpose. It argues that all code should have a single source of truth, which is best maintained through text files in version control systems like Git or Subversion. These systems offer flexibility and tools for branching, merging, and resolving conflicts, which are not provided by databases.

The author advises that development should start by cloning a repository or pulling the latest files from the file system, and any modifications should be made from the file system, not directly on the database. Modern Integrated Development Environments (IDEs) support this workflow and can make development more organized.

The author also recommends manually creating and saving changes to files, cautioning against relying on automated tools that generate changes based on database states. These tools often produce code that is difficult to maintain and may not align with the developer’s intent or style. Instead, changes should be carefully crafted by developers themselves.

In summary, the text advocates for managing database objects and code within a version-controlled environment, where changes are made manually, to ensure better quality, maintainability, and collaboration among developers.

This chapter focuses on ways to democratize Oracle schema development to empower team members, reduce dependency on others for permission, and foster experimentation. It emphasizes the need to address power imbalances between database developers and administrators, improve transparency, and lower barriers to entry in the workplace.

Key points include:

Overall, the chapter underscores the importance of fostering an inclusive and collaborative environment in Oracle development to enhance efficiency and creativity.

The provided text discusses the disadvantages of using the old join syntax in SQL compared to the more modern ANSI join syntax. The old syntax separates tables from their join conditions, making code hard to read and debug, and it increases the risk of accidental cross joins (Cartesian products), which can lead to poor performance and incorrect results. It also mentions that the old syntax is nonstandard, as it predates the SQL-92 standardization of join syntax, making code less portable between different database systems.

The text suggests embracing the ANSI join syntax, which explicitly specifies the type of join and the join condition together, leading to more readable and maintainable code. While the old syntax is still valid and occasionally useful, it is recommended to be used sparingly, with an acknowledgement that there are rare cases where it may be necessary or perform better. Examples are provided to illustrate the differences between the old and ANSI join syntaxes, and the text emphasizes the importance of writing queries in a way that makes them easier to read, maintain, and debug.

The provided text discusses the importance of minimizing context within SQL queries to improve their understandability and maintainability. It argues that excessive context can lead to complex dependencies that make SQL code harder to debug and work with. The text particularly advises against the overuse of correlated subqueries and common table expressions (CTEs), which can increase the context and complexity of SQL statements.

Correlated subqueries, which are subqueries that reference columns from the outer query, can create indirect links that complicate the query structure. While they might not pose a significant issue in smaller queries, in larger ones, they can make it difficult to understand the query in smaller parts.

Common table expressions, while useful in certain scenarios, are also criticized for unnecessarily increasing the context of a query. They can make it harder to isolate and test portions of the query independently, as the whole query remains interconnected.

The text acknowledges that both correlated subqueries and CTEs have their place and can sometimes lead to performance improvements or cleaner code. However, it emphasizes that if the goal is to create modular and simple SQL code, alternative techniques should be used that minimize intra-query dependencies.

The section discusses strategies for writing readable and powerful SQL statements by conceptualizing queries as nested sets, using chunking to manage complexity, and applying functional programming principles.

Sets: - Visualizing SQL statements as sets helps with both performance and readability. - Sets are collections of objects, which can contain other sets, allowing for a nested representation of information. - Relational sets in SQL differ from mathematical sets as they are not always distinct and need to be represented as simple rows and columns for practical use. - Joins combine multiple sets into tabular data, avoiding storing sets within single columns to facilitate filtering and joining.

Chunking: - Chunking involves grouping small pieces of information into meaningful units to manage complex ideas with limited short-term memory (typically 7 ± 2 items). - In SQL, chunking can be applied by breaking down a query into smaller, understandable parts and joining these parts together. - A modified rule of 3 ± 1 is suggested for SQL due to its interconnected nature. - Nesting sets in SQL allows for simplification of code, with each level of a statement containing a manageable number of chunks.

Functional Programming: - Functional programming involves writing deterministic functions that always return the same output for the same input without side effects. - While SQL is not inherently a functional language, its simplicity allows for the implementation of functional programming concepts. - SQL statements should deal with relational data as inputs and outputs, avoiding program state to reduce side effects and bugs. - Data in SQL should be passed through nested sets, minimizing dependencies and context by avoiding global variables, bind variables, and correlated subqueries.

In summary, to improve SQL readability and manageability, one should think in terms of sets, use chunking to break down complex queries, and adopt functional programming ideas to minimize side effects and dependencies.

Inline views, or subqueries in the FROM clause, are a method of structuring SQL queries by breaking them into smaller, independent sections, making the code easier to write and understand. They are different from regular subqueries and correlated subqueries, with the latter referencing columns from the outer query. Inline views behave like tables or views, can be nested within each other, and offer the benefits of a functional programming approach by passing and returning sets.

Inline views can make the SQL code larger due to the need for additional joins when splitting complex queries into simpler chunks, but they help manage complexity. For instance, joining ten tables at once leads to an overwhelming number of possible combinations, making it hard to visualize and understand, but splitting them into two groups of five reduces the complexity significantly.

The use of inline views is exemplified by a query to find the most popular rocket fuels per year, broken down into three separate inline views for launches, launch vehicle engines, and engine fuels. These independent chunks simplify the overall query, despite sometimes being so simple that they only filter data without joining tables. This approach helps to accommodate the cognitive limits of query writers and users, ensuring that queries remain within a manageable scope of understanding. The next steps involve putting these pieces together using ANSI join syntax and considering style and performance implications of building large, nested queries.

The provided SQL query is designed to identify the top three most common rocket fuels used per year. It achieves this by constructing a complex query with several nested inline views that progressively build upon each other:

The query uses ANSI join syntax and window functions. It is recommended to explore this query in an IDE to understand how each section contributes to the final result.

The initial results show that historically common rocket fuels have been variants of kerosene and UDMH. In more recent years, liquid hydrogen has gained popularity. The data might not be perfect and could require some grouping to refine the results, but it is still considered valid for the purpose of the query.

The code and other examples can be found in a GitHub repository, and it is intended for use with Oracle database version 12.2 or higher. The book from which this code is taken suggests that while it’s not necessary to run every example while reading, this particular complex query is worth experimenting with in an IDE for a better understanding of its structure and debugging process.

The provided text explains the concepts of operators, functions, expressions, and conditions in Oracle SQL. Operators are symbols that perform an action and return a value, while functions are called with arguments to return a value. An expression is a combination of literals, functions, and operators that yields a value. Conditions are specific types of expressions that return a Boolean value and are often used in clauses like WHERE.

Understanding these elements is crucial because they have different applications and cannot always be used interchangeably. For instance, a WHERE clause requires a condition, not just any expression.

The text also stresses the importance of recognizing when something is missing or overly complex in SQL code. It suggests that excessive type conversions might indicate the need for a simplified approach, such as using the TRUNC function to handle dates without conversion.

Regarding precedence rules, the text advises following conventional mathematical order for operators and understanding that in conditions, AND has higher precedence than OR. However, clarity should be prioritized over strict adherence to precedence rules, and parentheses should be used to make expressions easier to read.

Simplification of syntax can also be achieved through inline views, which can make long, complex expressions more manageable and easier to debug. However, one should balance the use of inline views to avoid unnecessary fragmentation of the code.

Finally, the text mentions that discrete math principles, like De Morgan’s laws, can help simplify complex logical conditions. It also suggests creating truth tables for complex scenarios to visualize all possible combinations of conditions and their outcomes, aiding in better understanding and debugging of SQL queries.

The text explains the use of CASE and DECODE expressions in SQL for adding conditional logic, similar to IF statements. CASE is generally preferred because it is more readable, available in both SQL and PL/SQL, and handles NULLs predictably. Both expressions utilize short-circuit evaluation, stopping once a condition is met.

An example provided applies CASE and DECODE to the fizz buzz problem, where numbers 1 to 100 are evaluated for divisibility by 3, 5, or both, outputting "fizz", "buzz", or "fizz buzz" accordingly. The CASE expression is shown to be more versatile, allowing different types of conditions, not just equality.

There are two types of CASE expressions: searched and simple. The searched case is more powerful and used in the fizz buzz example, while the simple case is shorter and compares against a list of values.

The text also highlights a peculiarity of DECODE where it treats NULL as equal to NULL, which is not standard behavior in SQL comparisons. An example is given where DECODE returns "A" when comparing two NULLs, which is a special case to remember.

The provided text discusses various aspects and types of SQL joins, emphasizing their importance in database operations and their role in evaluating database capabilities. The text then goes on to describe specific join types, their uses, and implications for database performance and query design.

Summary of Key Points:

The text asserts that a deep understanding of joins is essential for database proficiency, and it is crucial to be able to join tables effectively. The specific join types and their appropriate uses are covered in greater detail, along with their implications for query performance and design.