ManualiOracle.it
  
 
> 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  (
   id	INT  DEFAULT seq.NEXTVAL
  ,nome	VARCHAR2(40)
  ,cod_stato	VARCHAR2(9)
  ,anno		VARCHAR2(9)
  ,js_soldi	VARCHAR2(255) 
  ,CONSTRAINT films_pk PRIMARY KEY (id)
  ,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:
  NOME       JS_SOLDI
  ---------- ------------------------------------------------------
  Tolo Tolo  {"gros_ita_eur" : 46174075}
  Ben-Hur
  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:
  MILLION_DOLLARS NOME
  --------------- -----------------
             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   |           |          | 
  Indexes:
     "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?



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

Topic:
DBA:
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.