A handler named 'plpgsqlHandle()' is created for installing PL/pgSQL. For creating the handler on 'SwineDB' database you would issue a command:
SwineDB=# CREATE LANGUAGE 'pl/pgsql' HANDLER plpgsqlHandle SwineDB-# LANCOMPILER 'Install PL/pgSQL'; CREATE 0.0%
SwineDB=# CREATE LANGUAGE 'pl/pgsql' EXECUTE plpgsqlHandle SwineDB-# LANCOMPILER 'Install PL/pgSQL'; CREATE 0.0%
SwineDB=# CREATE LANGUAGE 'plpgsql' HANDLER plpgsqlHandle SwineDB-# LANCOMPILER 'Install PL/pgSQL'; CREATE 100.0%
SwineDB=# CREATE LANGUAGE 'plpgsql' EXECUTE plpgsqlHandle SwineDB-# LANCOMPILER 'Install PL/pgSQL'; CREATE 0.0%
A PL/pgSQL code block is defined with DECLARE, BEGIN and END. How many such sub blocks can be nested within a block?
None 0.0%
2 0.0%
8 0.0%
Unlimited 100.0%
A steel production company has two sales outlets. Both outlets are maintaining their data separately in servers SVA and SVD. Both outlets use the same structure for the Sales table. Which method wi...
Select * from SVA.Sales join SVD.Sales 0.0%
Select * from SVA.Sales union all SVD.Sales 0.0%
select * from SVA.Sales,SVD.Sales 0.0%
None of the above 100.0%
A wholesale merchant shop needs a report about the sale where total sale of the day is more than $50,000. Which of the following will fulfill this requirement?
select * from orders where sum(amount) > 50000 0.0%
select * from orders where sum(amount) > 50000 order by OrderDate 0.0%
select * from orders group by OrderDate where sum(amount) > 50000 0.0%
select * from orders group by OrderDate having sum(amount)>50000 100.0%
Can you define variables in PostgreSQL pl/pgSQL whose value cannot be null?
No, you cannot 0.0%
Yes, you can use 'NOT NULL' after datatype 100.0%
Yes, you can use 'NON NULLABLE' after datatype 0.0%
Yes, that is the default case, for setting it to hold null values 'NULLABLE' is used after datatype 0.0%
Choose the correct statement regarding WAL in PostgreSQL:
It increases the reliability of the database 0.0%
It logs the entries to the database automatically 100.0%
It is detrimental for database performance 0.0%
There is no such term defined in PostgreSQL 0.0%
Choose the correct statement:
All PL/pgSQL expressions in a function, except dynamic queries, are only prepared once during the lifetime of the PostgreSQL backend process 0.0%
All PL/pgSQL expressions in a function are only prepared once during the lifetime of the PostgreSQL backend process 50.0%
All PL/pgSQL expressions in a function are prepared for each execution time during the lifetime of the PostgreSQL backend process 0.0%
None of the above is correct 50.0%
Choose the correct statements for a trigger function:
It is created using the CREATE FUNCTION command 0.0%
It should be defined as accepting single arguments 100.0%
It returns a value of integer datatype 0.0%
It returns a value of integer or opaque datatype 0.0%
Consider the following structure of the students table: rollno number(4) name varchar(20) course varchar(20) What will be the query to display the c...
Select course from students where count(course) > 5; 0.0%
Select course from students where count(*) > 5 group by course; 0.0%
Select course from student's group by course; 0.0%
Select course from student's group by course having count(*) > 5; 100.0%
Data validation can be implemented at the definition stage through:
Check constraints with specified values 33.0%
Referential constraints, by creating a foreign key for another table 33.0%
Default value of a column 0.0%
Null constraints 33.0%
Every Boyce-Codd Normal Form(BCNF) is in:
First Normal Form 0.0%
Second Normal Form 100.0%
Third Normal Form 0.0%
None of the above 0.0%
Examine the following query: Create table Person (EmpNo Number(4) not null, EName Char not null, Join_dt Date not null, Pay Number) Which of the following field(s) are created correctly?
EmpNo 0.0%
EName 50.0%
Join_dt 50.0%
Pay 0.0%
Food Cart Accounting System (FOCAS) is maintaining products in the products table, and wants to see the products which are 50 or more numbers far from the minimum stock limit. The structure of the ...
Only statement 1 is correct 0.0%
Only statement 2 is correct 0.0%
Both statements 1 and 2 are correct 100.0%
Both statements 1 and 2 are incorrect 0.0%
For which of the following languages does PostgreSQL provide an API interface?
Python and Perl 33.0%
C/C++ and Java 33.0%
PHP and Ruby 33.0%
Small talk 0.0%
How can data be accessed by users who do not have direct access to the tables?
By creating views 50.0%
By creating triggers 0.0%
By creating stored procedures 50.0%
None of the above 0.0%
If entity x is existence-dependent on entity y, then what is x called?
Dominant entity 0.0%
Subordinate entity 0.0%
Primary entity 0.0%
Secondary entity 100.0%
In which of the following ways can a value in an array column be modified?
Element modification 33.0%
Columnar modification 0.0%
Slice modification 33.0%
Complete modification 33.0%
In which order are primary queries and their sub-queries interpreted:
prime query followed by sub query followed by sub sub query and so on 0.0%
lowest sub query followed by sub query followed by prime query 100.0%
The whole query is interpreted at one time 0.0%
There is no fixed sequence of interpretation of a query 0.0%
Is function overloading available in PL/pgSQL?
Normalization divides tables in a more useful and meaningful manner. Which statement is correct for the FIRST NORMAL FORM?
It includes only tables that do not have composite primary keys 0.0%
It must have data stored in a two-dimensional table with no repeating groups 0.0%
Every non-key column is non transitively dependent upon its primary key 0.0%
None of the above 100.0%
Perfect Services provides financial services. You need to display data from the pers table for joining_date from #1/1/2005# to #31/12/2005# and the job should be for Analyst or Clerk or Salesman. W...
select * from Pers where joining_date from #1/1/2005# to #31/12/2005#, job=Analyst or clerk or salesman 0.0%
select * from Pers where joining_date between #1/1/2005# to #31/12/2005#, job=Analyst or job=clerk or job=salesman 0.0%
select * from Pers where joining_date between #1/1/2005# and #31/12/2005# and (job=Analyst or clerk or salesman) 0.0%
None of the above 100.0%
Point out the incorrect statement regarding group functions:
Group functions act on a group of rows 25.0%
Group functions return one result for all the rows operated upon 25.0%
Group functions ignore the null values 25.0%
Stddev and variance are examples of group functions 25.0%
One cannot combine group and single value functions in a query 0.0%
Sum is not a group function 0.0%
Select the appropriate query for the Products table when data should be primarily ordered by ProductGroup. ProductGroup should be displayed in ascending order and CurrentStock should be in descendi...
Select * from Products order by CurrentStock,ProductGroup 0.0%
Select * from Products order by CurrentStock DESC,ProductGroup 0.0%
Select * from Products order by ProductGroup,CurrentStock 0.0%
Select * from Products order by ProductGroup,CurrentStock DESC 100.0%
None of the above 0.0%
The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, which query should be used? Employee --------- Empno Emp...
Select departname from department where deptno in (select deptno from employee group by deptno having count(*) > 100); 100.0%
Select departname from department where deptno in (select count(*) from employee group by deptno where count(*) > 100); 0.0%
Select departname from department where count(deptno) > 100; 0.0%
Select departname from department where deptno in (select count(*) from employee where count(*) > 100); 0.0%
The primary key indexing technique does not allow:
Duplicate data in a field 100.0%
Multiple attributes 0.0%
Sets of relations 0.0%
Many to Many relation 0.0%
There are two tables A and B. You are retrieving data from both tables where all rows from table B and only matching rows from table A should be displayed. Which type of join will you apply between...
Inner join 0.0%
Left outer join 0.0%
Right outer join 100.0%
Self join 0.0%
There is a column c1 in the table t to which a primary key pk is to be added. What will be the correct syntax?
Alter table t add primary key(c1); 50.0%
Alter table t add constraint pk primary key(c1); 50.0%
Alter table t add (constraint pk primary key(c1)); 0.0%
Alter table t add pk constraint primary key(c1); 0.0%
There is a table t upon which a primary key constraint by the name pk is applied. What will be the correct syntax to drop the constraint?
Alter table t drop primary key 0.0%
Alter table t drop constraint pk 100.0%
Drop primary key constraint on table t 0.0%
Alter table t delete primary key 0.0%
What do you infer from the following two lines? 1. host all 192.168.1.10 255.255.255.255 reject 2. host all 127.0.0.1 255.255.255.255 t...
Line 1 is a valid host based client config entry 0.0%
Line 2 is a valid host based client config entry 0.0%
Line 1 and Line 2 both are valid host based client config entries 0.0%
Both Line 1 and Line 2 are invalid host based client config entries 100.0%
What do you understand by the following PL/pgSQL declaration? c_phone customer.contact_no%TYPE;
This is declaring a new variable c_phone with undefined type 0.0%
This is declaring a new variable c_phone with same type as contact_no column 100.0%
This is declaring a new variable c_phone with variable data type 0.0%
None of the above is correct 0.0%
What does the following update statement do? Update OrderTable set OrderDiscount=OrderDiscount*1.10
It increases OrderDiscount of the first row by 10% 0.0%
It increases OrderDiscount of all rows by 10% 100.0%
It does nothing without the where clause 0.0%
It generates an error due to the lack of a where clause 0.0%
What does the pg_dump command do? pg_dump CustomerDatabase
It copies the database to specified location 0.0%
It creates a list of SQL commands used to create db from scratch 100.0%
None of the above 0.0%
What does the RAISE statement do in PL/pgSQL?
It logs the error to the logging utility 0.0%
It displays the error to stderr 0.0%
Both of the above 100.0%
None of the above 0.0%
What is a cluster?
A group of users 0.0%
A group of indexes 0.0%
A method of storing tables that are intimately related and often stored together into the same area of the disk 100.0%
A compressed datafile 0.0%
A segment of a partitioned table 0.0%
A group of constraints 0.0%
A group of databases 0.0%
A group of roles 0.0%
What is the default location of the standard elog?
/var/log/messages 0.0%
$PGDATA/serverlog 0.0%
Any of the above 0.0%
None of the above 100.0%
What is the default variable for the PROMPT3?
'>>> ' 0.0%
'>> ' 100.0%
'%/%R%# ' 0.0%
What is the error in the following query if the students table contains several records? select name from students where name = (select name from students order by name);
= should be replaced by in operator 100.0%
Order by clause in the subquery should be preceded with a group by clause 0.0%
Order by clause in the subquery can be used only if the where and group by clauses have been applied 0.0%
Group by clause should be applied to the outer query 0.0%
An order by clause is not allowed in a subquery 0.0%
There is no error 0.0%
What is the first step in installing PL/pgSQL in PostgreSQL?
Use 'CREATE FUNCTION' to create the procedural call handler 100.0%
Use 'CREATE LANGUAGE' SQL command 0.0%
Either one of the above can be used 0.0%
What is wrong in this query: Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)
In a sub query, * should be used instead of OrderID 0.0%
The sub query can return more than one row, so, = should be replaced with in 100.0%
The sub query should be used first 0.0%
None of the above 0.0%
When should sub queries be used?
To define the set of rows to be inserted in a table 0.0%
To define the set of rows to be included in a view 0.0%
To define one or more values to be assigned to existing rows 0.0%
To provide values for conditions in the Where clause 0.0%
All of the above are correct 100.0%
Which character function should be used to return a specified portion of a character string?
CONCAT 0.0%
LENGTH 0.0%
SUBSTR 100.0%
INITCAP 0.0%
Which clause should be used to display the rows of a table in ascending order of a particular column?
Where 0.0%
Order By 100.0%
Group By 0.0%
Having 0.0%
First Group By and then Having 0.0%
Like 0.0%
Between 0.0%
Which component of a DBMS verifies the syntax of the users query?
Parser 100.0%
The database manager 0.0%
Query optimization 0.0%
Database administrator 0.0%
Which method should be used to drop the master table if its primary key is being referenced by a foreign key in some other table?
Use cascade constraints clause with drop table 50.0%
Disable the foreign key of the detail table 50.0%
Drop the primary and foreign key constraints of both the tables 0.0%
None of the above 0.0%
Which of the following are not DCL operations?
Insert 0.0%
Grant 50.0%
Delete 0.0%
Update 0.0%
Revoke 50.0%
Commit 0.0%
Rollback 0.0%
Which of the following are supported by PostgreSQL?
Multi version concurrency control transactions 25.0%
Multi-user support 25.0%
Declarative SQL queries 25.0%
Query optimization 25.0%
Which of the following are valid in a declare block?
intCustomerId int4 := 67; 33.0%
intCustomerId int4 = 67; 0.0%
intCustomerId int4; 33.0%
intCustomerId int4; intCustomerId:= 67 33.0%
Which of the following can be used to uniquely identify a row?
Primary Key 100.0%
Unique Key 0.0%
Foreign Key 0.0%
All of the above 0.0%
Which of the following clauses are not allowed in a single row sub-query?
From 0.0%
Where 0.0%
Group by 0.0%
Having 0.0%
Order by 100.0%
Which of the following copy commands will work in PostgreSQL?
COPY 0.0%
COPY .. TO 0.0%
All of the above 100.0%
Which of the following date and time constants are not defined in PostgreSQL?
today 0.0%
current 0.0%
epoch 0.0%
current_day 100.0%
yesterday 0.0%
Which of the following date function(s) are invalid?
NEXT_DAY 25.0%
NEXT_MONTH 25.0%
MONTHS_BETWEEN 25.0%
DAYS_BETWEEN 25.0%
Which of the following files controls the host based authentication in PostgreSQL?
pg_host_auth.conf 0.0%
pghba.conf 0.0%
pg_hba.conf 100.0%
pghostauth.conf 0.0%
Which of the following functionalities is supported by the pg_ctl script?
status 25.0%
start 25.0%
stop 25.0%
restart 25.0%
invoke 0.0%
all of the above 0.0%
Which of the following functions is not available in PostgreSQL?
intfrombit 100.0%
bittoint4 0.0%
to_number 0.0%
to_timestamp 0.0%
Which of the following geometric types is not defined in PostgreSQL?
path 0.0%
box 0.0%
hexagon 100.0%
polygon 0.0%
Which of the following help PostgreSQL avoid unnecessary locking of records?
Multi Lock 0.0%
MCVR 0.0%
MVCC 100.0%
MVSQL 0.0%
Which of the following holds true for functions in PostgreSQL?
The parameters are positional 0.0%
Functions may be overloaded 0.0%
A variation of the SQL99 CREATE FUNCTION command is supported 0.0%
All of the above 100.0%
Which of the following holds true if you have installed PL/pgSQL in the PgDatabase?
For all the subsequent databases that are created with PgDatabase as their template, the user must install PL/pgSQL 0.0%
All the subsequent databases that are created with PgDatabase as their template, will have PL/pgSQL installed 100.0%
For all the subsequent databases that are created with PgDatabase as their template, the user may or may not install PL/pgSQL 0.0%
None of the above are correct 0.0%
Which of the following holds true when you have defined a function with "isstrict" attribute?
This will allow you to pre-evaluate a call to a function 0.0%
This will not allow you to pre-evaluate a call to a function if one of its arguments is NULL 0.0%
The function will always return a NULL value if more than one of its arguments is NULL 0.0%
The function will always return a NULL value whenever any of its arguments is NULL 100.0%
Which of the following is correct for the postmaster -n debugging option?
It stops the postmaster from re-initializing shared data structures 100.0%
It allows a developer to collect a core dump from each backend process manually 0.0%
It causes the postmaster to use the SIGSTOP signal to stop backend processes 0.0%
None of the above 0.0%
Which of the following is correct regarding VACUUM?
It deletes the temporary data and recovers the disk space 50.0%
The table identifier must be provided 0.0%
A VACUUM statement will clean up each table in the presently connected database 50.0%
All of the above 0.0%
Which of the following is correct with regard to Password Authentication?
pg_shadow table stores the password as plain text 0.0%
pg_user table stores the password as encrypted text 0.0%
Only superusers have access to the system password storing table 0.0%
All of the above 100.0%
Which of the following is not a single value function?
Round 0.0%
Floor 0.0%
Avg 100.0%
Sqrt 0.0%
Tan 0.0%
Which of the following is not an SQL operator?
Between .. and .. 0.0%
Like 0.0%
In 0.0%
Is null 100.0%
Having 0.0%
Not in 0.0%
Which of the following is not defined in the PL/pgSQL?
%Type 0.0%
%Coltype 100.0%
%Rowtype 0.0%
ALIAS 0.0%
Which of the following is true about PostgreSQL clients?
The graphical client application is psql 0.0%
The command line driven client is PgAccess 0.0%
psql client is installed by default 100.0%
PgAccess installation requires specification of the with-pgtcl option 0.0%
Which of the following programming structures is not available in PL/pgSQL?
IF THEN ELSE 0.0%
LOOP 0.0%
WHILE 0.0%
DO WHILE 100.0%
FOR 0.0%
Which of the following security features is inbuilt in PostgreSQL?
Stunnel 0.0%
SSL on compiling with -with-ssl 100.0%
SSH/OpenSSH 0.0%
None of the above 0.0%
Which of the following statements are incorrect regarding referential integrity?
A foreign key can refer to a primary key 0.0%
A foreign key can refer to a unique key 0.0%
The on delete cascade clause will work only if there is a reference to a primary key 50.0%
The referred key can either be in the same table or in some other table 0.0%
A foreign key can be composite 0.0%
Referential integrity can only be applied while creating the table 50.0%
Which of the following statements are true for views?
A view is stored in the data dictionary 0.0%
Views provide a more secure way of data retrieval 100.0%
Views are actual tables and store data at another location 0.0%
All of the above are true 0.0%
Which of the following statements is correct with regard to PostgreSQL?
It is an Object Oriented Database Management System 0.0%
It is an Object Relational Database Management System 100.0%
It is a Relational Database Management System 0.0%
It is an Object Based Database Management System 0.0%
Which of the following statements is correct?
PostgreSQL is written in C 50.0%
PostgreSQL can dynamically load compiled code for C on the fly 50.0%
All the users are allowed to use CREATE FUNCTION to link to a C function 0.0%
All of the above 0.0%
Which of the following statements is not correct about creating a new operator?
Name and functionname clause must be specified 0.0%
LEFTARG or RIGHTARG must be defined 0.0%
Overloading is not possible in operators 50.0%
COMMUTATOR attribute must be specified 50.0%
Which of the following statements regarding views are incorrect?
A view is like a window through which data in tables can be viewed or changed 0.0%
A view is derived from another table 0.0%
A view cannot be derived from another view 50.0%
A view is stored as a select statement only 0.0%
A view has no data of its own 0.0%
A view is another name for a table 50.0%
Which of the following techniques can be used to obtain a result which is based on comparing one row of a table with another row of the same table?
Equi-join 0.0%
Inner Join 0.0%
Outer Join 0.0%
Self Join 50.0%
Correlated Subquery 50.0%
Which of the following trigger function variables is not defined?
TG_NARGS 0.0%
NEW 0.0%
TG_NAME 0.0%
TG_WHERE 100.0%
OLD 0.0%
None of the above 0.0%
Which of the following values is not defined to indicate the level of the raise event?
DEBUG 0.0%
ERROR 100.0%
NOTICE 0.0%
EXCEPTION 0.0%
Which of the following variable declarations is not correct?
cust_id INTEGER; 0.0%
meter_name VARCHAR(30); 0.0%
net_amt FLOAT(2); 100.0%
update_time timestamp; 0.0%
Which statements hold true for Partition Tables?
A table may be split into several independent pieces 50.0%
All pieces must have the same size 0.0%
A partition may be analyzed and exported 50.0%
A partition cannot be truncated 0.0%
When one partition goes offline, other partitions still remain available 0.0%
Will the following function compile and execute? DECLARE intValue int4; BEGIN intValue := 20 * 20; return intValue; END;
It will not compile 0.0%
It will compile but will give a runtime error 0.0%
It will compile but will not produce any output 0.0%
It will compile and will produce 400 as output 100.0%
You have defined a function "Calculate()" in the template1 database. What will happen when you create a new database "ManagementDB" there?
The function will be moved to the new database 0.0%
The function will be cloned to the new database 100.0%
The function will remain with the template database only 0.0%
Any of the above can happen 0.0%
You want to manipulate some value in the database upon updation in a trigger function. Which line of the code would be appropriate?
IF TG_RELID = ''UPDATE'' THEN --Modify the database value END IF; 0.0%
IF TG_NAME = ''UPDATE'' THEN --Modify the database value END IF; 0.0%
IF TG_OP = ''UPDATE'' THEN --Modify the database value END IF; 100.0%
IF TG_ARGV = ''UPDATE'' THEN --Modify the database value END IF; 0.0%
You want to update the last modified timestamp in the orders table. The correct way to do this in a PL/pgSQL function, when the parameter integer order_id is passed, would be:
DECLARE order_id ALIAS FOR $1; mytimestamp timestamp; BEGIN mytimestamp := ''now''; UPDATE orders SET orderid=order_id, lastmodified = mytimestamp; RETURN mytimestamp; END; ' LANGUAGE 'plpgsql'; 0.0%
DECLARE order_id ALIAS FOR $1; mytimestamp timestamp; BEGIN mytimestamp := 'now'; UPDATE orders SET orderid=order_id, lastmodified = mytimestamp; RETURN mytimestamp; END; ' LANGUAGE 'plpgsql'; 100.0%
DECLARE order_id ALIAS FOR $1; BEGIN mytimestamp := ''now''; UPDATE orders SET orderid=order_id, lastmodified = ''now''; RETURN mytimestamp; END; ' LANGUAGE 'plpgsql'; 0.0%
DECLARE order_id ALIAS FOR $1; BEGIN mytimestamp := ''now''; UPDATE orders SET orderid=order_id, lastmodified = 'now'; RETURN mytimestamp; END; ' LANGUAGE 'plpgsql'; 0.0%