UpWork (oDesk) & Elance PostgreSQL RDBMS Test Question & Answers

UpWork (oDesk) & Elance PostgreSQL RDBMS Test Question & Answers

July 03, 2015 / No Comments

UpWork (oDesk) & Elance PostgreSQL RDBMS Test Question & Answers are really very important to pass UpWork & Elance test. You will get top score at this skill test exam. If you found any problem or wrong answer please inform me via contact or comments. We will try to solve it in short. This test is extremely valuable to acquire knowledge of PostgreSQL RDBMS. Lets Start test.

Ques : Which authentication methods are supported by PostgreSQL?
Ans  :  Trust
        PAM
        LDAP
        Radius
        Password

Ques : Which index types are supported by PostgreSQL?
Ans  :  B-tree
        GiST
        Hash
        GIN

Ques : Out of the following backup approaches, which ones are applicable to PostgreSQL?
Ans  : SQL dump
       File system level backup
   Continuous archiving

Ques : In the following operation, which ones can trigger a trigger?
Ans  : insert
       update
   delete

Ques : Which index types support multicolumn indexes?
Ans  : B-tree
       GiST
   GIN

Ques : What kind of triggers are offered by PostgreSQL?
Ans  : Per-row triggers
      Per-statement triggers

Ques : Which of the following statements will cast the integer value 1 to type text?
Ans  :  SELECT CAST(1, text) as cast_integer;
        SELECT 1::text AS cast_integer;
 
Ques : Are the contents of the pg_autovacuum system catalog saved when pg_dumpall is used to backup the database?
Ans  : No

Ques : Can deferrable constraints be deferred by a trigger?
Ans  : Yes

Ques : After a PostgreSQL installation, how will you create the database cluster?
Ans  :  With initd

Ques : Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?
Ans  :  No limit (It will be equivalent to the text)

Ques : What is the difference between to_tsvector() and ::tsvector ?
Ans  :  to_tsvector () can be used in select statements, while ::tsvector cannot

Ques : While creating a trigger, the function it will call may be created after it and attached to it.
Ans  : True

Ques :  What is the command used to import a backup made with pg_dumpall > file.dmp?
Ans  :  psql -f file.dmp

Ques : An ISO-8601 time may be entered into a table using the numeric format 012411 instead of 01:24:11.
Ans  : True

Ques : Consider the following empty table:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Which of the following inserts will cause an error?
Ans  : insert into example (a, b, c) values (1, 2, 3), (1, 4, 3);

Ques : What is the effect of turning fsync off in postgresql.conf?
Ans  : File synchronization will be deactivated

Ques : What is the ~ operator?
Ans  : POSIX regular expression match operator

Ques : What is the default ordering when ORDER BY is not specified?
Ans  : The ordering is unknown if not specified

Ques : Which one of the following text search functions does not exist?
Ans  :  plainto_tsvecto

Ques : Which PostgreSQL version added the enum datatype?
Ans  : 8.0

Ques :  While creating a table with a field of the serial type, a sequence will be created.
Ans  :  True

Ques : What can be stored in a column of type decimal(4,3)?
Ans  : 4 numeric values with up to 3 digits to the right of the decimal point.

Ques : What interfaces are available in the base distribution of PostgreSQL?
Ans  : C

Ques : A table can have only one primary key column.
Ans  : True

Ques : What command will correctly restore a backup made with the following command?
pg_dump -Fc dbname > filename
Ans  : psql -f filename dbname

Ques :   How can you configure PostgreSQL autovacuum?
Ans  : By editing postgresql.conf

Ques :  What library is used by PostgreSQL for encryption?
Ans  : None of the above

Ques : On a UNIX system, what is the best way to prevent all non-local connections to the postmaster?
Ans  :  None of the above

Ques : How will you change the TCP port which PostgreSQL will listen to?
Ans  : By changing "port" in postgresql.conf

Ques : Which of the following queries will create a table with two fields, "id" and "name" with "id" as an auto incrementing primary key?
Ans  :  create table foo (id serial primary key, name varchar(255));

Ques : How will you list the available functions from psql?
Ans  :  \df

Ques : What is the well known port number for the postgresql database service?
Ans  : 5432

Ques : What is true regarding file system backup?
Ans  : All of the above

Ques : How do you alter a column to forbid null values?
Ans  : None of the above

Ques : What is the storage size of an integer on a 64bit system?
Ans  : 4bytes

Ques : Which function should be used to highlight the results?
Ans  :  ts_highlight

Ques : When using LIKE to compare strings, what is the wildcard operator (operator which matches zero or more characters)?
Ans  : *

Ques : For proper results, which of the following should contain a tsvector?
Ans  : Lexemes

Ques : Which of the following statements will produce an error?
Ans  :  SELECT now()::int;

Ques : To backup a database, the postmaster daemon must be halted.
Ans  : True

Ques : The following statement will retrieve the second element of the array column products in table store_products.

SELECT products[1] FROM store_products;
Ans  : True

Ques : SELECT 'infinity'::timestamp;

Will this statement produce an error?
Ans  : Yes

Ques :  What is the difference between tokens and lexemes?
Ans  :  A lexeme is a string while a token is an intege

Ques : Which kind of index can be declared unique?
Ans  : Hash

Ques :  SELECT rtrim('foobar', 'abr');

The result of this statement is foo.
Ans  : True

Ques : SELECT !!3;

What output will this statement give?
Answers:
Ans  : 6

Ques : How do you create a table with a field of the int array type?
Ans  : create table foo (bar integer[]);

Ques : create table foo (bar integer[]);
Ans  : None of the above

Ques : Which of the following statements will create a table with a multidimensional array as second column?
Ans  : CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[][]);

Ques : If max_connections is 10 and 10 connections are currently active, how can you be sure the superuser will be available to connect?
Ans  :  Set superuser_reserved_connections in postgresql.conf

Ques : How will you rank text search results?
Ans  : With the ORDER BY operator

Ques : Which of the following statements will create a table special_products which is a child of the table store_products?
Ans  : CREATE TABLE special_products (quality int) INHERITS store_products;

Ques : Which of the following statements will create a table?
Ans  : SELECT * INTO products_backup FROM special_products;

Ques : Which of the following statements will retrieve the number of values stored in an array column?
Ans  : SELECT array_dims(products) FROM store_products;

Ques : Does PostgreSQL support SSL?
Ans  : Yes

Ques : Given a table special_products that inherits from a table store_products, which of the following statements will modify store_products only without affecting its child table?
Ans  : UPDATE ONLY store_products SET name = 'Wine' WHERE id = 2;

Ques : How do you select a single random row from a table?
Ans  : SELECT * FROM tab ORDER BY random() LIMIT 1;

Ques : PostgreSQL triggers can be written in C directly.
Ans  : True

Thanks for watching this test Question & Answers. Please don't forget to leave a comment about this post. You can also find some more effective test question & answers, information, techniques, technology news, tutorials, online earning information, recent news, results, job news, job exam results, admission details & another related services on the following sites below. Happy Working!
News For Todays ARSBD oEtab ARSBD-JOBS DesignerTab

UpWork (oDesk) & Elance Oracle PL Test Question & Answers

UpWork (oDesk) & Elance Oracle PL Test Question & Answers

/ No Comments

UpWork (oDesk) & Elance Oracle PL Test Question & Answers are really very important to pass UpWork & Elance test. You will get top score at this skill test exam. If you found any problem or wrong answer please inform me via contact or comments. We will try to solve it in short. This test is extremely valuable to acquire knowledge of Oracle PL. Lets Start test.

Ques : The oracle server implicitly opens a cursor to process:
Ans  :  A Sql select statement
       DML Statements

Ques : Which two among the following programming constructs can be grouped within a package?
Ans  : Constant
       Sequence

Ques : Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?
Ans  : It persists across transactions within a session
       It persists from session to session for the same user

Ques : Which of the following is not a legal declaration?
Ans  : declare x,y varchar2(10);
      declare Sex boolean:=1;
 
Ques : Which two statements out of the following regarding packages are true?
Ans  : The package specification is required, but the package body is optional
       The specification and body of the package are stored separately in the database
 
Ques : A table has to be dropped from within a stored procedure. How can this be implemented
Ans  : Use the DBMS_DDL packaged routines in the procedure to drop the table

Ques : CREATE OR REPLACE PACKAGE manage_emp IS
tax_rate CONSTANT NUMBER(5,2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION cal_tax (p_sal NUMBER) RETURN NUMBER;
END manage_emp;
/
CREATE OR REPLACE PACKAGE BODY manage_emp IS

PROCEDURE update_sal (p_raise_amt NUMBER) IS
BEGIN
UPDATE emp SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
END;

PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
BEGIN
INSERT INTO emp(empno, deptno, sal) VALUES
(v_id, p_depntno, p_sal);
END insert_emp;

PROCEDURE delete_emp IS
BEGIN
DELETE FROM emp WHERE empno = v_id;
END delete_emp;

PROCEDURE update_emp IS
v_sal NUMBER(10,2);
v_raise NUMBER(10, 2);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = v_id;
IF v_sal < 500 THEN v_raise := .05;
ELSIP v_sal < 1000 THEN v_raise := .07;
ELSE v_raise := .04;
END IF;
update_sal(v_raise);
END update_emp;

FUNCTION cal_tax (p_sal NUMBER)RETURN NUMBER IS
BEGIN
RETURN p_sal * tax_rate;
END cal_tax;
END manage_emp;
/
What is the name of the private procedure in this package?
Ans  : UPDATE_SAL

Ques : An internal LOB is _____.
Ans  : Stored in the database

Ques : The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:
Ans  :  Concurrency Control

Ques : Which table should be queried to determine when the procedure was last compiled?
Ans  : USER_OBJECTS

Ques : Which cursor dynamically allows passing values to a cursor while opening another cursor?
Ans  : Implicit Cursor

Ques : Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?
Ans  : Exception_init

Ques : How can migration be done from a LONG to a LOB data type for a column?
Ans  : Using ALTER TABLE statement

Ques : Which table and column can be queried to see all procedures and functions that have been marked invalid?
Ans  : USER_OBJECTS table,STATUS column

Ques : In which type of trigger can the OLD and NEW qualifiers can be used?
Ans  : Row level DML trigger

Ques : Examine the following trigger:

CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Employee
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM employee;
DMBS_OUTPUT.PUT_LINE( 'There are now' || n || 'employees');
END;

This trigger results in an error after this SQL statement is entered: DELETE FROM Employee WHERE Empno = 7499;
How should the error be corrected?
Ans  : Take out the COUNT function because it is not allowed in a trigger

Ques : Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?
Ans  : Exception

Ques :  Which of the following statements is true?
Ans  :  Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application

Ques :Examine the following code:
CREATE OR REPLACE FUNCTION gen_email (first_name VARCHAR2, last_name VARCHAR2,
id NUMBER)
RETURN VARCHAR2 IS
email_name VARCHAR2(19);
BEGIN
email_name := SUBSTR(first_name, 1, 1) ||
SUBSTR(last_name, 1, 7) ||.@Oracle.com .;
UPDATE employees SET email = email_name
WHERE employee_id = id;
RETURN email_name;
END;
Which of the following statements removes the function?
Ans  : DROP FUNCTION gen_email;

Ques : In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?
Ans  :  Page Level lock

Ques : If user defined error condition exists,Which of the following statements made a call to that exception?
Ans  : Raise

Ques : Which of the following are identified by the "INSTEAD OF" clause in a trigger?
Ans  : The view associated with the trigger

Ques : What type of trigger is created on the EMP table that monitors every row that is changed, and places this information into the AUDIT_TABLE?
Ans  : FOR EACH ROW trigger on the EMP table

Ques : Which procedure is called after a row has been fetched to transfer the value, from the select list of the cursor into a local variable?
Ans  :  Row_value

Ques : What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?
Ans  : Only one

Ques : When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:
Ans  : PGA

Ques : What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?
Ans  : The area of memory established to process the SQL statement is released

Ques : Examine the following package specification:
CREATE OR REPLACE PACKAGE combine_all
IS
v_string VARCHAR2(100);
PROCEDURE combine (p_num_val NUMBER);
PROCEDURE combine (p_date_val DATE);
PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END combine_all;
/
Which overloaded COMBINE procedure declaration can be added to this package specification?
Ans  :  PROCEDURE combine;

Ques : Which part of a database trigger determines the number of times the trigger body executes?
Ans  : Trigger type

Ques : Which table should be queried to check the status of a function?
Ans  : USER_OBJECTS

Ques : Which of the following statements is true regarding stored procedures?
Ans  :  A stored procedure must have at least one executable statement in the procedure body

Ques : Examine the following code:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE LOGON ON employees
BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
(TO_CHAR(SYSDATE, 'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500, 'You may
insert into the EMPLOYEES table only during
business hours.');
END IF;
END;
/
What type of trigger is it?
Ans  : This is an invalid trigger

Ques : Which code is stored in the database when a procedure or function is created in SQL*PLUS?
Ans  : Only P-CODE

Ques : Evaluate the following PL/SQL block:
DECLARE
v_low   NUMBER:=2;
v_upp   NUMBER:=100;
v_count NUMBER:=1;
BEGIN
FOR i IN v_low..v_low LOOP
INSERT INTO test(results)
VALUES (v_count)
v_count:=v_count+1;
END LOOP;
END;
How many times will the executable statements inside the FOR LOOP execute?
Ans  : 1

Ques : What can be done with the DBMS_LOB package?
Ans  : Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE

Ques : Examine the following code:
CREATE OR REPLACE TRIGGER UPD_SALARY
FOR EACH ROW
BEGIN
UPDATE TEAM
SET SALARY=SALARY+:NEW.SALARY
WHERE ID=:NEW.TEAM_ID
END;
Which statement must be added to make this trigger executable after updating the SALARY column of the PLAYER table?
Ans  : AFTER UPDATE ON PLAYER

Ques : Examine the following code:

CREATE OR REPLACE PACKAGE comm_package IS
g_comm NUMBER := 10;
PROCEDURE reset_comm(p_comm IN NUMBER);
END comm_package;

User MILLER executes the following code at 9:01am:
EXECUTE comm_package.g_comm := 15

User Smith executes the following code at 9:05am:
EXECUTE comm_package.g_comm := 20

Which of the following statement is true?
Ans  :  g_comm has a value of 15 at 9:06am for Miller

Ques : The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are INVALID.Which command can be issued to recompile both procedures?
Ans  : ALTER PROCEDURE CHECK_SAL compile

Ques : Examine the following procedure:
PROCEDURE emp_salary
(v_bonus  BOOLEAN,
V_raise BOOLEAN,
V_issue_check in out BOOEAN)
is
BEGIN
v_issue_check:=v_bonus or v_raise;
END;
If v_bonus=TRUE and v_raise=NULL,which value is assigned to v_issue_check?
Ans  : TRUE

Ques : Which package construct must be declared and defined within the packages body?
Ans  :  Private Procedure

Ques : What happens when rows are found using a FETCH statement?
Ans  : The current row values are loaded into variables

Ques : Evaluate the following PL/SQL block:
DECLARE
result BOOLEAN;
BEGIN
DELETE FROM EMPloyee
WHERE dept_id IN (10,40,50);
result:=SQL%ISOPEN;
COMMIT:
END;

What will be the value of RESULT if three rows are deleted?
Ans  : FALSE

Ques : Which two statements among the following, regarding oracle database 10g PL/SQL support for LOB migration, are true?
Ans  : Standard package functions accept LOBs as parameters

Ques : Which command is used to disable all triggers on the EMPLOYEES table?
Ans  : ALTER TABLE employees DISABLE ALL TRIGGERS;

Ques : SQL%ISOPEN always evaluates to false in case of a/an:
Ans  : Implicit Cursor

Ques : Which datatype does the cursor attribute '%ISOPEN' return?
Ans  : BOOLEAN

Ques : Which of the following is a benefit of using procedures and functions?
Ans  : Procedures and Function avoid reparsing for multiple users by exploiting shared SQL areas

Ques : All packages can be recompiled by using an Oracle utility called:
Ans  :  Dbms_utility

Ques : Which type of variable should be used to assign the value TRUE, FALSE?
Ans  : Scalar

Ques : Examine the following code:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
END;
/
An UPDATE command is issued in the EMP table that results in changing 10 rows
How many rows are inserted into the AUDIT_TABLE ?
Ans  : 1

Thanks for watching this test Question & Answers. Please don't forget to leave a comment about this post. You can also find some more effective test question & answers, information, techniques, technology news, tutorials, online earning information, recent news, results, job news, job exam results, admission details & another related services on the following sites below. Happy Working!
News For Todays ARSBD oEtab ARSBD-JOBS DesignerTab