Assessments

This section contains the answers to the questions throughout the book. Go ahead and check whether you have got them right.

Chapter 1 – Oracle Cloud Fundamentals

1. D is correct. All the aspects are covered by OCI.
2. B is correct. Analytical databases focus on complex data retrieval, aggregations, and analytical queries. The performance of the retrieval process is ensured by the indexing, which is mostly reflected by the B+ trees and bitmap index types.
3. D is correct. ATPs are defined by small online transactions and are covered by the TP and the higher priority TPURGENT.
4. C is correct. The user is not responsible for the platform or the infrastructure. Applications are located in the cloud, and the whole administration is vendor operated and administered.
5. D is correct. The archiver process takes the online logs and copies them into the archive repository before they are rewritten. Log Writer is responsible for transactions by managing the changes and storing them in the online logs. The Process Monitor background process manages sessions and server-client interconnection. System Monitor...

Chapter 2 – Data Loading and Migration Perspectives

1. A is correct. SQL Loader is related to the third-party systems holding the data to be imported. Multiple file types and structures can be used. The essential element in this process is the instruction set defined in a control file, which specifies how to import/load and map the data from the external file, including handling undefined values and other loading instructions.
2. C is correct. Exp and Imp methods are operated on the client side.
3. A is correct. We have used this method to make the log available through Object Storage.
4. C is correct. The pre-authenticated request consists of several elements. B defines the bucket and O defines the object name.

Chapter 3 – Date and Time Standardization Principles

1. A is correct. It takes preferences to the top-level precisions, thus the year is first, followed by the month and day elements.
2. A is correct. T encloses time elements.
3. C is correct. It refers to the Gregorian calendar.
4. D is correct. Unbounded periodicity is expressed by the value -1 after the R designation.

Chapter 4 – Concepts of Temporality

1. A is correct. UTC is used for the normalization reference.
2. C is correct. The client time uses the UTC reference extended by the time zone shift definition, whereas local time applies the time zone directly to the value itself.
3. C is correct. The Gregorian calendar correctly applies the current rules for the leap year definition.
4. If he was 17 the day before yesterday, he must have had his 18th birthday yesterday.
5. In order for him to be 20 next year, he must be 19 this year. So, today must be the beginning of the year – January 1.
6. The timeline is shown in Figure A.1:

Figure A.1 – Solution: time positions

Chapter 5 – Modeling and Storage Principles

1. C is correct. Microsecond precision is used by default, consisting of six decimal places.
2. B is correct. DATE can operate up to second precision, whereas TIMESTAMP uses nanosecond precision.
3. C is correct. DATE and TIMESTAMP cannot hold time zone references. Using TIMESTAMP with a local time zone incorporates the shift between the server and client directly into the value itself during retrieval and processing.
4. A is correct. There is a TIMESTAMP keyword followed by the element value definition. There is no explicit mapping definition, so the order of elements must apply the predefined format.
5. D is correct. SYSDATE and CURRENT_DATE provide the DATE data type. systimestamp provides the database’s time zone and localtimestamp provides the client side’s time.
6. D is correct. Oracle provides the number of days elapsed when two DATE values are subtracted.
7. A is correct. Oracle provides INTERVAL DAY TO...

Chapter 6 – Conversion Functions and Element Extraction

1. B is correct. The format is RM.
2. A is correct. The D and DDD formats provide numerical output. DY is textual but provides only abbreviations. DAY provides the full name.
3. C is correct. By default, the 12-hour format is used, so HH and HH12 are the same. The HHEurope format does not exist. The 24-hour format is referenced by HH24.
4. D is correct. DATE, TIMESTAMP, and INTERVAL types are permitted values for EXTRACT function operations.

Chapter 7 – Date and Time Management Functions

1. A is correct. The query takes the provided date and adds one month, resulting in the last day of February. Since 2023 is not a leap year, February 28, 2023, is the result.
2. A is correct. Any numerical value can be produced, even negative.
3. D is correct. Languages and regions influence the output of the NEXT_DAY function. This is why we implement the own function, which is not influenced by the NLS parameter values.
4. B is correct. The Q parameter value is used for quarter trimming. CC trims based on the whole century.
5. D is correct. The PERSONAL_ID value is an interesting concept of person identification; it is formed by the date of birth and gender applied for the month elements.

Chapter 8 – Delving into National Language Support Parameters

1. A is correct. NLS_DATE_FORMAT is a static parameter, and the instance must be restarted to apply the change stored in SPFILE.
2. B is correct. NLS_TERRITORY references the first day of the week, whether it is a Sunday or a Monday.
3. B is correct. NLS_DATE_LANGUAGE can be defined by the third parameter of the TO_CHAR function.
4. D is correct. For the system (database), the NLS_DATABASE_PARAMETERS data dictionary should be referenced. Session-level values can be obtained by the NLS_SESSION_PARAMETERS data dictionary. The other definitions (options B and C) are not valid data dictionary structures.

Chapter 9 – Duration Modeling and Calculations

1. A is correct. Undefined states cannot be covered; each new state automatically marks the end of the validity of the direct predecessor.
2. B is correct. The right-side open characteristics expresses the first timepoint, which is invalid for the duration spectrum.
3. C is correct. DATE value subtraction produces day granularity.
4. C is correct. INTERVAL DAY TO SECOND is produced.

Chapter 10 – Interval Representation and Type Relationships

1. C is correct. The left border remains the same. The right border is extended by one day to March 1 because 2022 is not a leap year.
2. C is correct. The not_fills relationship type does not exist. The occupies type characterizes the common start or end point or the whole coverage. The excludes category covers before and meets types, which do not overlap.
3. C is correct. If there are no undefined states, each beginning point of the validity ends the validity of the direct predecessor. Thus, no gaps can be present.
4. B is correct. The PERIOD definition takes the name specified after the FOR keyword by extending its name with _START or _END. Therefore, if it is specified as PERIOD FOR VALIDITY, then the name of the PERIOD border attributes in this case would be VALIDITY_START or VALIDITY_END.
5. A is correct. In principle, there are three options – ALL, CURRENT, and ASOF, extended by the QUERY_TIME parameter...

Chapter 11 – Temporal Database Concepts

1. D is correct. The model takes two layers. The first is formed of the primary key and references. Individual object states are temporally oriented and part of the second layer. From the temporal perspective, historical, current, and future valid data can be managed.
2. A is correct. A conventional system stores only current valid data. Any change invokes physical replacement of the original state.
3. C is correct. The aspect of relevance is related to identifying significant changes. Transaction support covers the integrity of transaction rules in a temporal environment. The aspect of correctness relates to extended temporal data integrity, like a temporal ISA hierarchy. The aspect of limited temporal usability is related to the important loss of historical states and future plan management.
4. C is correct. Three dimensions are covered – Insert, Preprocessing, and Load.
5. B is correct. In this case, the best option is...

Chapter 12 – Building Month Calendars Using SQL and PL/SQL

1. B is correct. It is necessary to remove all elements lower than the month itself. This can be done with a truncating operation. Values can be rounded up. The first_day function does not exist and should be user defined. And finally, the EXTRACT function gets only one element, not the DATE value.
2. C is correct. First, increase the original value (sysdate) by one month, then truncate its value. Finally, one day is subtracted, providing midnight of the last day of the month. The last_day function keeps the original time elements. TRUNC(sysdate) + 1 adds one day and removes time elements. The ROUND function cannot provide these results.

Chapter 13 – Flashback Management for Reconstructing the Database Image

1. C is correct. The isolation property of the transaction ensures access to the confirmed changes.
2. C is correct. The System Change Number (SCN) is Oracle’s clock, which is incremented every time a transaction is committed. SCN marks a consistent point in time in the database.
3. B is correct. The FLASHBACK DATABASE command can only be launched in MOUNT mode.
4. A is correct. Syntactically, the AS OF SCN clause is used, followed by the SCN value.

Chapter 14 – Building Reliable Solutions to Avoid SQL Injection

1. We are pleased that you have realized the importance of the correct access and processing of date and time and have applied the principles of reducing the possibility of SQL injection in your solutions.
2. D is correct. You have to specify valid conditions. For option D, the WHERE clause looks like this: employee_id=1 or 1=1.

Chapter 15 – Timestamp Enhancements

1. A is correct. The client time zone is obtained by SESSIONTIMEZONE. The server time zone can be obtained by the DBTIMEZONE function of the STANDARD package. MYTIMEZONE and UTC_SHIFT do not exist in that package.
2. D is correct. The time zone can be stated using the valid name representation or with numerical values using time zone hour (TZH) and minute (TZM).
3. C is correct. The DATE value is transformed into a TIMESTAMP reference and extended by the time zone reference, forming the TIMESTAMP with time zone data type.
4. B is correct. The FROM_TZ function takes two parameters. TIMESTAMP is a constructor, and At time zone is a TIMESTAMP clause extension, not a function. The only valid option is NEW_TIME.
5. D is correct. The valid UTC normalization for the TIMESTAMP value is done by the SYS_EXTRACT_UTC function.

Chapter 16 – Oracle Cloud Time-Zone Reflection

1. A is correct. Option B does not reflect the destination by using ALTER SESSION. The Unset command is not correct (option C). The Deregister clause does not exist either (option D).
2. C is correct. The difference between the database (server) and client time zone is 16 hours. SYSDATE refers to the server time zone and CURRENT_DATE is the client time. Based on the settings, SYSDATE refers to the smaller value, therefore the output value provided by the query is -16. If the SYSDATE_AT_DBTIMEZONE parameter was set to false, the database time zone would not be applied, so the difference would be 10 hours, resulting in a negative value. Therefore, the output of the query would be -10.