Programming Concepts & OCI Guide PDF

## Programming Concepts **PL/SQL** - **Data Types**: Records are composite data types. A record can be based on a table, user definition, or cursor. Therefore, all given options are valid. - **Keywords**: The four keywords that define a PL/SQL block are DECLARE, BEGIN, EXCEPTION, and END. BEGIN an...

## Programming Concepts **PL/SQL** - **Data Types**: Records are composite data types. A record can be based on a table, user definition, or cursor. Therefore, all given options are valid. - **Keywords**: The four keywords that define a PL/SQL block are DECLARE, BEGIN, EXCEPTION, and END. BEGIN and END are mandatory for a PL/SQL block to exist. - **Error Handling**: The DELETE operation removes elements from any mentioned position in a collection, whereas the TRIM operation deletes elements from the end of a collection. Therefore, the statement is false. - **Return Statements**: The operation of a RETURN statement in a Procedure is similar to its functionality in a Function. Therefore, the statement is false. - **Procedures**: The Package Body holds the private declarations of Procedures and Functions. - **CURSOR Operations**: The FETCH statement is used to retrieve data from a cursor's SELECT statement when utilizing explicit cursors to process data from a database. - **CURSOR Attributes**: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT are Cursor Attributes. %COUNT is not a Cursor Attribute. - **Packages**: A package in Oracle database to encapsulate a set of related components for better organization and modularity. The mandatory elements are Package Specification and Package Body. - **Conditional Logic**: The CASE control structure is used to implement conditional logic on various conditions. - **PL/SQL Applications**: PL/SQL applications can run on any operating system and platform that supports the Oracle database. It is, therefore, a platform-independent language. - **Implicit Cursor and Explicit Cursor**: When fetching data from the database, an Explicit cursor enables the user to perform all operations, such as OPEN, FETCH, and CLOSE. **Oracle Cloud Infrastructure (OCI)** - **Selecting a Region:** When selecting an OCI region for your deployment, you should primarily consider choosing the region closest to the majority of your users for lower latency and higher performance. - **Fault Domain:** The primary role of a fault domain is to protect against unexpected hardware failures within an availability domain. **SQL Queries** - **Correlated Subquery**: When using a correlated subquery in SQL, it is true that the inner SELECT statement executes first, followed by the outer SELECT statement. - **INTERSECT Operation**: When creating SQL queries using the INTERSECT operator to retrieve data from multiple tables, you need to ensure that the number of columns and data types are identical for all the SELECT statements in the query. - **UNION Operation**: The UNION set operator will get the maximum output. - **Database User**: A database user can access a table if and only if the user is the table's owner: This is false. **Concepts** - **3-Tier Architecture**: The 3-tier architecture is used in database systems. - **Triggers**: Triggers are executed on you and don't throw any error even if nothing executes. There are two types of triggers: Statement Level and Row Level trigger. - **Data Types**: For storing student names from different clubs, the SET datatype will ensure that there are no duplicates. **Other** - **Procedures and Functions**: While developing an application, it is possible to alter the table to add a primary key later, even when data is present. - **Autonomous Database**: When considering implementing an autonomous database, we are looking into different workload types to choose solutions, like "Automation Transaction Processing" and "Automation Data Warehouse. - **APEX Components**: The FORM component of APEX would facilitate user registration in an application. - **Data Integrity**: Foreign key constraint ensures that when the data in the parent key is updated, all the rows in the child table that depend on the updated parent keys are also updated. This is false. - **Database Objects**: Indexes are the objects that should be located within the database to optimize and ensure the faster retrieval of data from tables. - **Package Body**: The Package Body is the section that holds the private declarations of procedures and functions. This document provides a guide for understanding various programming concepts, particularly PL/SQL and Oracle Cloud Infrastructure (OCI), which is helpful in your programming journey.