Baze de date - rezolvări teme

Tema nr. 1

Observație!

Scrieți rezolvarea direct în acest document!

1. Circle the programming language meeting the criteria

Criteria

3GL PL/SQL SQL

4GL PL/SQL SQL

Is proprietary to Oracle Corporation PL/SQL SQL

Nonprocedural PL/SQL SQL

Procedural PL/SQL SQL

Is ANSI-compliant PL/SQL SQL

Nota! Pentru a rezolva acest exercitiu am ales sa ingros raspunsul corect.

2. In the following code, identify and highlight examples of these procedural constructs: variable, conditional control, SQL statement

DECLARE

v_first_name varchar2(40);

v_last_name varchar2(40);

v_first_letter varchar2(1);

BEGIN

SELECT first_name, last_name into v_first_name, v_last_name

FROM students

WHERE student_id=105;

v_first_letter := get_first_letter(last_name);

IF 'N' > 'v_first_letter' THEN

DBMS_OUTPUT.PUT_LINE('The last name for: '||first_name||' '||last_name||' is between A and M');

ELSE

DBMS_OUTPUT.PUT_LINE('The last name for: '||first_name||' '||last_name||' is between N and Z');

END IF;

END;

3. Complete the following chart defining the syntactical requirements for a PL/SQL block:

Optional or Mandatory?

Optional or Mandatory? Describe what is included in this section

DECLARE Optional Variabile, exceptii si cursori definiti de utilizator

BEGIN Mandatory Declaratii SQL si PL/SQL

EXCEPTION Optional Au loc actiuni atunci cand apar erori

END Mandatory E obligatoriu sa apara in structura bloc PL/SQL

4. Which of the following PL/SQL blocks execute successfully? For the blocks that fail, explain why they fail.

A.

BEGIN

END;

- nu se executa cu succes pentru ca nu exista nici o instructiune intre BEGIN si END

B.

DECLARE amount INTEGER(10);

END;

- nu se executa deoarece lipseste BEGIN, lucru care este obligatoriu intr-un bloc PL/SQL

C.

DECLARE

BEGIN

END;

- nu se executa pentru ca nu exista nici o instructiune , la fel ca la punctul A

D.

DECLARE amount NUMBER(10);

BEGIN

DBMS_OUTPUT.PUT_LINE(amount);

END;

- acest bloc PL/SQL se executa cu succes

5. Fill in the blanks:

A. PL/SQL blocks that have no names are called an anonymous blocks(blocuri anonime).

B. Procedure(blocuri de tip procedura) and Function(blocuri de tip function) are named blocks and are stored in the database.

6. In Application Express, create and execute a simple anonymous block that outputs “Hello World.”

BEGIN

DBMS_OUTPUT.PUT_LINE(“Hello World”);

END;

7. Create and execute a simple anonymous block that does the following:

• Declares a variable of datatype DATE and populates it with the date that is six months from today

• Outputs “In six months, the date will be: <insert date>.”

DECLARE

data DATE := SYSDATE;

BEGIN

DMBS_OUTPUT.PUT_LINE(ADD_MONTHS(data,6));

END.