SQL & PL pgSQL Exam for PostgreSQL Developers - shaysalomon12/Data-Engineer GitHub Wiki

  1. You have to answer 22 questions:
    • Questions 1-10: choose 8.
    • Questions 11-24: choose 12.
    • Questions 25-26 are mandatory.
  2. Questions 1-24 are 3 points each.
  3. Questions 25-26 are 20 points each.
  4. You have 120 minutes to complete this exam.
  5. Return your answers in a text file.

We have the following tables:

EMPLOYEES

image

DEPARTMENTS

image

1. Which statement about a FOREIGN KEY constraint is true?

A. Duplicate values are not allowed in a foreign key.
B. Null values are not allowed in a foreign key.
C. Foreign key can be designated only to a combination of columns.
D. A foreign key must match an existing value in the parent table or be NULL.

2. Which statement about DISTINCT keyword is true?

A. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the FROM keyword.
B. You can only specify one column after the DISTINCT qualifier.
C. The DISTINCT qualifier affects all the selected columns (it may be more than one), and the result represents a distinct combination of the columns.
D. By default PostgreSQL eliminates duplicate rows. DISTINCT keyword is only used in group functions.

3. Evaluate this query:

1 | SELECT ename "Emp name", sal salary, deptno AS "Dept Num"
2 | FROM employees
3 | WHERE salary > 4000
4 | ORDER BY "Dept Num";

Which line contains an error?

A. 1
B. 2
C. 3
D. 4

4. You query the database with the following command:

SELECT SUBSTR ('HELLO WORLD', 3, 3);

What is the result?

A. RL
B. LLO
C. LO
D. LE

5. Which SQL statement retrieves the average salary per job in a department?

-- A.
SELECT deptno, AVG(sal)
FROM employees GROUP BY job;

-- B.
SELECT job, AVG(sal)
FROM employees GROUP BY deptno;

-- C.
SELECT deptno, job, AVERAGE (sal)
FROM employees GROUP BY deptno, job;

-- D.
SELECT deptno, job, AVG (sal)
FROM employees GROUP BY job ,deptno;

6. Consider the following code snippet:

create or replace procedure do_loop()
language plpgsql as
$$
DECLARE
   a numeric(2) ;
BEGIN
   FOR a IN REVERSE 20 .. 10 LOOP
   raise notice '%',a; 
END LOOP;
END;
$$;
 
CALL do_loop();

What will be the last output line?

A. 11
B. 10
C. 29
D. 30

7. Triggers are written to be executed in response to any of the following events:

A. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
B. A database definition (DDL) statement (CREATE, ALTER, or DROP).
C. Whenever a designated event occurs and the WHEN condition associated with the trigger.
D. All of the above.

8. The EMPLOYEES table containes 14 rows. The DBA created another table with the same structure called GOOD_EMPLOYEES

What will happen if he will execute the following command:

INSERT INTO good_employees
SELECT * FROM employees
WHERE LENGTH(ename) > 5;

A. All employees whose names are 5 characters long will be selected.
B. Rows will be entered from EMPLOYEES into GOOD_EMPLOYEES only if there are more then 5 rows.
C. We will receive an error because the word VALUES is missing.
D. Rows will be entered from EMPLOYEES into GOOD_EMPLOYEES for all employees whose names are longer than 5 characters.

9. Which statement would you use to query the database for employee name and department number of all employees in department 10 and 30 in alphabetic order by employee name?

-- A.  
SELECT ename, deptno
FROM employees
WHERE deptno IN (10,30)
ORDER BY ename;
 
-- B.  
SELECT ename, deptno
FROM employees
WHERE deptno =10
AND deptno =30
ORDER BY ename;
 
-- C.  
SELECT ename, deptno
FROM employees
WHERE deptno =10
OR deptno =30
ORDER BY ename;
 
-- D.  
SELECT ename, deptno
FROM employees
WHERE deptno BETWEEN 10 AND 30
ORDER BY deptno;
 
-- E.   A + C

10. You wrote the following select statement:

SELECT empno, ename , sal + 300 FROM employees;

The column’s heading in pgAdmin, in the result will be:

A. empno , ename , ?column?
B. Empno , Ename , Sal
C. EMPNO , ENAME , SAL+ 300
D. The statement would cause an error since alias is missing

11. What is the use of User Defined Exceptions?

A. To capture compilation errors of the PL/pgSQL program.
B. To capture PostgreSQL syntax errors.
C. To capture logical run-time errors defined by the programmer.
D. Answers B and C are correct.

12. A programmer wrote the following command from pgAdmin:

CALL Procedure_name;

What will this command do?

A. It will create a PL/pgSQL block in the database and execute it.
B. It will execute a PL/pgSQL block which has to be already stored in the database.
C. It will create a PL/pgSQL block in the database and will execute it only if it is valid.
D. It will execute a PL/pgSQL block which is already stored in the database. Only if it does not exist, It will also create it.

13. What is the best way to insure that an employee will not receive a salary raise of more than 5000?

A. Define a CHECK constraint on the SALARY column.
B. Check it in the application code whenever a salary is updated.
C. Create a BEFORE trigger to avoid this situation.
D. Create an AFTER trigger to avoid this situation.

14. Which of the following statements is incorrect regarding PL/pgSQL programs?

A. In each PL/pgSQL program you must state at least the BEGIN and END clauses.
B. In each PL/pgSQL program you must not specify a DECLARE clause if there are no local variables declared in the block.
C. In each PL/pgSQL program you must specify the INTO clause for each INSERT, UPDATE and DELETE command.
D. If you want to handle run-time errors you must specify the EXCEPTION clause.

15. Examine the following PL/pgSQL block:

DO $$
DECLARE
    BEGIN
        FOR  I  IN 15..15 LOOP
        raise notice 'External Loop ,%',I;
         
            FOR  j IN 10..1 LOOP
                raise notice 'Internal Loop, %',j;
            END LOOP;
 
        END LOOP;
    END;
$$

How many times will each loop execute:

A. The inner loop 15 times and the outer loop 0 times.
B. None of the loops will execute.
C. The inner loop 0 times and the outer loop 15 times.
D. The inner loop 0 times and the outer loop once.

16. In order to return all records from Employees table from a PL/pgSQL function we should:

A. Define a variable of employees%ROWTYPE type.
B. Use RETURNS RECORD.
C. USE RETURNS SETOF number.
D. You cannot declare a PL/pgSQL variable that will contain the entire employees table.
E. Use RETURNS TABLE.

17. In order to write to a log table the before and after values of each update in a specific table, we should:

A. Use a BEFORE trigger.
B. Use a STATEMENT trigger.
C. Use a FOR EACH ROW trigger.
D. Perform a select from the table inside the trigger.

18. Which of the following statements is incorrect regarding a PL/pgSQL function?

A. The function must return a value.
B. You can use the function inside a SELECT statement in pgAdmin.
C. The function must receive parameters (At least one).
D. The function can be an independent object in the database.
E. Usually, all the function's parameters will be of IN type.

19. Which of the following will be compiled before execution?

A. A function that was created in the database, but never executed before.
B. A procedure that does not exists in the database.
C. An anonymous block.
D. A procedure that was successfully created in the past, but became invalid.
E. Answers C and D are correct.

20. What will the following function return?

CREATE FUNCTION sal_emp()
  RETURNS integer AS $$
DECLARE
  v_sal integer;
BEGIN
  SELECT sal
  INTO v_sal
  FROM employees;
 
  RETURN v_sal;
END
$$ LANGUAGE plpgsql;
 
select sal_emp();

A. The sum of all salaries for all employees.
B. The last record of employees table.
C. The salary of all employees in employees table.
D. It will result with run time error.
E. The salary of the first employee in employees table.

21. Examine the following PL/pgSQL function:

 1| CREATE OR REPLACE FUNCTION count_dept(p_deptno numeric)
 2|  RETURNS integer
 3| AS $$
 4| DECLARE
 5|   v_count integer;
 6|   sql varchar;
 7| BEGIN
 8|   sql := 'SELECT count(*) FROM employees WHERE deptno = $1';
 9|   EXECUTE sql USING v_count INTO p_deptno;
10|  
11|  RETURN v_count;
12|  END
13|  $$ LANGUAGE plpgsql;
14|   
15|  select count_dept(10);

Which line contains a logical error?

A. Line 8.
B. Line 11.
C. Line 2.
D. Line 9
E. There is no error in the function code.

22. What would be the output of the following code?

CREATE or replace FUNCTION fx(x IN numeric, y IN numeric)
RETURNS numeric
language plpgsql as
$$
DECLARE
    a numeric;
    b numeric;
    c numeric;
    z numeric;
BEGIN
   IF x > 2*y THEN
      z:= x;
   ELSE
      z:= 2*y;
   END IF;
 
   RETURN z;
END;
$$
 
DO
$$
DECLARE
    a numeric := 23;
    b numeric := 47;
    c numeric;
 
BEGIN
    select fx(a, b) INTO c;
    raise notice '%',c;
END;
$$

A. 46
B. 47
C. 94
D. 23

23. What is wrong in the following code snippet?

CREATE OR REPLACE FUNCTION totalCustomers
language plpgsql as
$$
DECLARE
    total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;
   RETURN total;
END;
$$

A. It doesn’t have the RETURN clause in function declaration.
B. The RETURN statement is wrong.
C. Function definition should not use the IS keyword
D. Nothing wrong.

24. What will be the output of the following code snippet?

DO
$$
DECLARE
   a numeric (2) := 21;
   b numeric (2) := 10;
BEGIN
    
   IF ( a <= b ) THEN
      raise notice 'a = %',a;
   END IF;
 
   IF ( b >= a ) THEN
      raise notice 'a = %',a;
   END IF;
    
   IF ( a <> b ) THEN
      raise notice 'b = %',b;
    
   END IF;
 
END;
$$

A. b = 2
B. a = 21
C. b = 10
D. a = 21, b = 10

25. Examine the Employees table and Write a SELECT statement which will display for each manager:

  • His Name (the employee who is a manager).
  • His manager ID, with the heading "Manager Code".
  • His department name and department location.
  • How many employees work under him.
  • The average salary of his employees.

Display only managers who have more than one employee.

Sort the report according to average salary of his employees, where the highest average salary will be displayed first.

26. Create a procedure that calculates new Salary for employees:

The procedure will receive 2 parameters:

  1. Department id (deptno)
  2. Percentage raise

And return 1 parameter: Total sum of all raise amounts.

The procedure will also:

  1. Calculate the new salary for each employee which belongs to the "department id" given as input, based on the "percentage raise" given also as input.
  2. Print to screen the following columns for each employee in that department:
  • enam
  • job
  • mgr
  • hiredate
  • new salary (after the raise)