> Quizzes > Quizzes 2020 -- Oracle / Postgresql -- Best Pratices / How to

In the page Prices and Donations, you can find how to have the solutions of the quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.

If it is not specified, the commands must be executed connected as a database "superuser".
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the database version
- it is not warranted to be error-free in all the database environments

April - Oracle virtual column

Level (*) : 2
Topic (*) : DBA

You are in Oracle 18c and you want to create a table T1 with 3 colums:
 - c_1 : it must be a virtual column valued with as 'c_2 || '-' || c_3'
 - c_2 : varchar2(10)
 - c_3 : varchar2(10)
Write the SQL to create that table.
And write a query reading the Oracle dictionary to see that C__1 is a virtual column.

March - Oracle JSON

Level (*) : 2
Topic (*) : DBA/Progr

You are in Oracle 18c and you have a table called FILMS with a JSON column:
 CREATE TABLE   films  (
  ,nome	VARCHAR2(40)
  ,cod_stato	VARCHAR2(9)
  ,anno		VARCHAR2(9)
  ,js_soldi	VARCHAR2(255) 
  ,CONSTRAINT films_js_chk CHECK (js_soldi IS JSON)

The "js_soldi" column contains keys such as "gros_ita_eur", "gros_w_usd", "costo_usd", etc.
Here there are some of its rows:
  ---------- ------------------------------------------------------
  Tolo Tolo  {"gros_ita_eur" : 46174075}
  Avatar     {"gros_ita_eur" : 65666319, "gros_w_usd" : 2789958507}

You have to find a query in order to get the 3 highest grossing films:
In the table, this info has the key called "gros_w_usd" (you must divide its value by 1000000 to have the result in million dollars).

Example of the result:
  --------------- -----------------
             2798 Avengers: Endgame
             2790 Avatar
             2187 Titanic

February - Postgres JSONB and Unix time stamp

Level (*) : 2
Topic (*) : Postgres/Progr

In your db, there is a table called CUST.
It is like this:
                    Table "public.cust"
    Column   |  Type   | Collation | Nullable | Default 
   id        | integer |           | not null | 
   name      | text    |           |          | 
   birthinfo | jsonb   |           |          | 
     "cust_pk" PRIMARY KEY, btree (id)

In it, there is data like this:
   id |   name     |             birthinfo                        
    1 | Loris Assi | {"city": "Augusta", "date": 203644800000, "pr": "SR"}

The date information is in Unix time stamp format (milliseconds from January 1st, 1970).
You have to find a query in order to convert that date in 'yyyymmdd' format.

Then the query result should be like this:
   id |    name    | birthdate 
    1 | Loris Assi | 19760615

January - Postgres backup error

Level (*) : 2
Topic (*) : Postgres

Using Barman, we launch a backup of a Postgres cluster database called "c3".
We receive the following error:
 barman@dblor:~$ barman backup c3
 Starting backup using postgres method for server c3 in /u1/barman/c3/base/20191219T105926
 Backup start at LSN: 16/FF34BB88 (0000000100000016000000FF, 0034BB88)
 Starting backup copy via pg_basebackup for 20191219T105926
 ERROR: Backup failed copying files.
 DETAILS: data transfer failure on directory '/u1/barman/c3/base/20191219T105926/data'
 pg_basebackup error:
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: could not initiate base backup: SSL SYSCALL error: EOF detected
 pg_basebackup: removing contents of data directory "/u1/barman/c3/base/20191219T105926/data"

Which could be the reason of that error?
And what you can do in order to avoid it?

1: easy solution
2: medium solution
3: hard (or long) solution

quiz usually suitable for Oracle DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for Oracle DBAs and for Programmers
Postgres: regarding the Postgresql database


This website is not authorized by Oracle Corporation and it is not connected to the official Oracle website.
Some words used here are registered trade marks of their owners.