Upwork POSTGRESQL TEST 2018

Hello Freelancer, Get the POSTGRESQL TEST of Latest version 2018. We are ready here to provide your desire upwork test answer. We already tested all exam test information Available include on here. So Dear, Why Late? Read the below full exam test and get more information from our website.

1. Which of the following are supported by PostgreSQL?

Answers:

1. Multi version concurrency control transactions
2. Multi-user support
3. Declarative SQL queries
4. Query optimization

2. Point out the incorrect statement regarding group functions:

Answers:

1. Group functions act on a group of rows
2. Group functions return one result for all the rows operated upon
3. Group functions ignore the null values
4. Stddev and variance are examples of group functions
5. One cannot combine group and single value functions in a query
6. Sum is not a group function

3. Which of the following date function(s) are invalid?

Answers:

1. NEXT_DAY
2. NEXT_MONTH
3. MONTHS_BETWEEN
4. DAYS_BETWEEN

4. Which of the following functionalities is supported by the pg_ctl script?

Answers:

1. status
2. start
3. stop
4. restart
5. invoke
6. all of the above

5. In which of the following ways can a value in an array column be modified?

Answers:

1. Element modification
2. Columnar modification
3. Slice modification
4. Complete modification

6. Which of the following are valid in a declare block?

Answers:

1. intCustomerId int4 := 67;
2. intCustomerId int4 = 67;
3. intCustomerId int4;
4. intCustomerId int4; intCustomerId:= 67

7. For which of the following languages does PostgreSQL provide an API interface?

Answers:

1. Python and Perl
2. C/C++ and Java
3. PHP and Ruby
4. Small talk

8. Data validation can be implemented at the definition stage through:

Answers:

1. Check constraints with specified values
2. Referential constraints, by creating a foreign key for another table
3. Default value of a column
4. Null constraints

9. Which of the following statements is not correct about creating a new operator?

Answers:

1. Name and functionname clause must be specified
2. LEFTARG or RIGHTARG must be defined
3. Overloading is not possible in operators
4. COMMUTATOR attribute must be specified

10. 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?

Answers:

1. EmpNo
2. EName
3. Join_dt
4. Pay

11. How can data be accessed by users who do not have direct access to the tables?

Answers:

1. By creating views
2. By creating triggers
3. By creating stored procedures
4. None of the above

12. Which of the following statements are incorrect regarding referential integrity?

Answers:

1. A foreign key can refer to a primary key
2. A foreign key can refer to a unique key
3. The on delete cascade clause will work only if there is a reference to a primary key
4. The referred key can either be in the same table or in some other table
5. A foreign key can be composite
6. Referential integrity can only be applied while creating the table

13. Which of the following statements regarding views are incorrect?

Answers:

1. A view is like a window through which data in tables can be viewed or changed
2. A view is derived from another table
3. A view cannot be derived from another view
4. A view is stored as a select statement only
5. A view has no data of its own
6. A view is another name for a table

14. Which of the following statements is correct?

Answers:

1. PostgreSQL is written in C
2. PostgreSQL can dynamically load compiled code for C on the fly
3. All the users are allowed to use CREATE FUNCTION to link to a C function
4. All of the above

15. Which statements hold true for Partition Tables?

Answers:

1. A table may be split into several independent pieces
2. All pieces must have the same size
3. A partition may be analyzed and exported
4. A partition cannot be truncated
5. When one partition goes offline, other partitions still remain available

16. Which of the following is correct regarding VACUUM?

Answers:

1. It deletes the temporary data and recovers the disk space
2. The table identifier must be provided
3. A VACUUM statement will clean up each table in the presently connected database
4. All of the above

17. 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?

Answers:

1. Equi-join
2. Inner Join
3. Outer Join
4. Self Join
5. Correlated Subquery

18. 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?

Answers:

1. Use cascade constraints clause with drop table
2. Disable the foreign key of the detail table
3. Drop the primary and foreign key constraints of both the tables
4. None of the above

19. 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?

Answers:

1. Alter table t add primary key(c1);
2. Alter table t add constraint pk primary key(c1);
3. Alter table t add (constraint pk primary key(c1));
4. Alter table t add pk constraint primary key(c1);

20. Which of the following are not DCL operations?

Answers:

1. Insert
2. Grant
3. Delete
4. Update
5. Revoke
6. Commit
7. Rollback

21. Which of the following holds true for functions in PostgreSQL?

Answers:

1. The parameters are positional
2. Functions may be overloaded
3. A variation of the SQL99 CREATE FUNCTION command is supported
4. All of the above

22. Choose the correct statements for a trigger function:

Answers:

1. It is created using the CREATE FUNCTION command
2. It should be defined as accepting single arguments
3. It returns a value of integer datatype
4. It returns a value of integer or opaque datatype

23. 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. Which select statement will you use?

Answers:

1. select * from Pers where joining_date from #1/1/2005# to #31/12/2005#, job=Analyst or clerk or salesman
2. select * from Pers where joining_date between #1/1/2005# to #31/12/2005#, job=Analyst or job=clerk or job=salesman
3. select * from Pers where joining_date between #1/1/2005# and #31/12/2005# and (job=Analyst or clerk or salesman)
4. None of the above

24. Which of the following is correct for the postmaster -n debugging option?

Answers:

1. It stops the postmaster from re-initializing shared data structures
2. It allows a developer to collect a core dump from each backend process manually
3. It causes the postmaster to use the SIGSTOP signal to stop backend processes
4. None of the above

25. 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?

Answers:

1. Alter table t drop primary key
2. Alter table t drop constraint pk
3. Drop primary key constraint on table t
4. Alter table t delete primary key

26. Which of the following variable declarations is not correct?

Answers:

1. cust_id INTEGER;
2. meter_name VARCHAR(30);
3. net_amt FLOAT(2);
4. update_time timestamp;

27. What do you understand by the following PL/pgSQL declaration?

c_phone customer.contact_no%TYPE;

Answers:

1. This is declaring a new variable c_phone with undefined type
2. This is declaring a new variable c_phone with same type as contact_no column
3. This is declaring a new variable c_phone with variable data type
4. None of the above is correct

28. Which of the following date and time constants are not defined in PostgreSQL?

Answers:

1. today
2. current
3. epoch
4. current_day
5. yesterday

29. 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 descending order:

Answers:

1. Select * from Products order by CurrentStock,ProductGroup
2. Select * from Products order by CurrentStock DESC,ProductGroup
3. Select * from Products order by ProductGroup,CurrentStock
4. Select * from Products order by ProductGroup,CurrentStock DESC
5. None of the above

30. Which of the following can be used to uniquely identify a row?

Answers:

1. Primary Key
2. Unique Key
3. Foreign Key
4. All of the above

31. Which clause should be used to display the rows of a table in ascending order of a particular column?

Answers:

1. Where
2. Order By
3. Group By
4. Having
5. First Group By and then Having
6. Like
7. Between

32. Which of the following holds true if you have installed PL/pgSQL in the PgDatabase?

Answers:

1. For all the subsequent databases that are created with PgDatabase as their template, the user must install PL/pgSQL
2. All the subsequent databases that are created with PgDatabase as their template, will have PL/pgSQL installed
3. For all the subsequent databases that are created with PgDatabase as their template, the user may or may not install PL/pgSQL
4. None of the above are correct

33. Which of the following is not defined in the PL/pgSQL?

Answers:

1. %Type
2. %Coltype
3. %Rowtype
4. ALIAS

34. A handler named ‘plpgsqlHandle()’ is created for installing PL/pgSQL. For creating the handler on ‘SwineDB’ database you would issue a command:

Answers:

1. SwineDB=# CREATE LANGUAGE ‘pl/pgsql’ HANDLER plpgsqlHandle SwineDB-# LANCOMPILER ‘Install PL/pgSQL’; CREATE
2. SwineDB=# CREATE LANGUAGE ‘pl/pgsql’ EXECUTE plpgsqlHandle SwineDB-# LANCOMPILER ‘Install PL/pgSQL’; CREATE
3. SwineDB=# CREATE LANGUAGE ‘plpgsql’ HANDLER plpgsqlHandle SwineDB-# LANCOMPILER ‘Install PL/pgSQL’; CREATE
4. SwineDB=# CREATE LANGUAGE ‘plpgsql’ EXECUTE plpgsqlHandle SwineDB-# LANCOMPILER ‘Install PL/pgSQL’; CREATE

35. Normalization divides tables in a more useful and meaningful manner. Which statement is correct for the FIRST NORMAL FORM?

Answers:

1. It includes only tables that do not have composite primary keys
2. It must have data stored in a two-dimensional table with no repeating groups
3. Every non-key column is non transitively dependent upon its primary key
4. None of the above

36. What is the default location of the standard elog?

Answers:

1. /var/log/messages
2. $PGDATA/serverlog
3. Any of the above
4. None of the above

37. What does the pg_dump command do?

pg_dump CustomerDatabase

Answers:

1. It copies the database to specified location
2. It creates a list of SQL commands used to create db from scratch
3. None of the above

38. What does the RAISE statement do in PL/pgSQL?

Answers:

1. It logs the error to the logging utility
2. It displays the error to stderr
3. Both of the above
4. None of the above

39. 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 tables A and B?

Answers:

1. Inner join
2. Left outer join
3. Right outer join
4. Self join

40. 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 Products table is:
ProductID
ProductName
CurrentStock
MinimumStock

Two possible queries are:

Statement 1: select * from products where currentStock>MinimumStock+50
Statement 2: select * from products where currentStock-50>MinimumStock

Select an option which is more suitable for these queries:

Answers:

1. Only statement 1 is correct
2. Only statement 2 is correct
3. Both statements 1 and 2 are correct
4. Both statements 1 and 2 are incorrect

41. Which of the following holds true when you have defined a function with “isstrict” attribute?

Answers:

1. This will allow you to pre-evaluate a call to a function
2. This will not allow you to pre-evaluate a call to a function if one of its arguments is NULL
3. The function will always return a NULL value if more than one of its arguments is NULL
4. The function will always return a NULL value whenever any of its arguments is NULL

42. Which of the following is true about PostgreSQL clients?

Answers:

1. The graphical client application is psql
2. The command line driven client is PgAccess
3. psql client is installed by default
4. PgAccess installation requires specification of the with-pgtcl option

43. Will the following function compile and execute?

DECLARE
intValue int4;
BEGIN
intValue := 20 * 20;
return intValue;
END;

Answers:

1. It will not compile
2. It will compile but will give a runtime error
3. It will compile but will not produce any output
4. It will compile and will produce 400 as output

44. In which order are primary queries and their sub-queries interpreted:

Answers:

1. prime query followed by sub query followed by sub sub query and so on
2. lowest sub query followed by sub query followed by prime query
3. The whole query is interpreted at one time
4. There is no fixed sequence of interpretation of a query

45. Which of the following copy commands will work in PostgreSQL?

Answers:

1. COPY
2. copy
3. COPY .. TO
4. All of the above

46. 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?

Answers:

1. select * from orders where sum(amount) > 50000
2. select * from orders where sum(amount) > 50000 order by OrderDate
3. select * from orders group by OrderDate where sum(amount) > 50000
4. select * from orders group by OrderDate having sum(amount)>50000

47. Which of the following trigger function variables is not defined?

Answers:

1. TG_NARGS
2. NEW
3. TG_NAME
4. TG_WHERE
5. OLD
6. None of the above

48. Is function overloading available in PL/pgSQL?

Answers:

1. True
2. False

49. Can you define variables in PostgreSQL pl/pgSQL whose value cannot be null?

Answers:

1. No, you cannot
2. Yes, you can use ‘NOT NULL’ after datatype
3. Yes, you can use ‘NON NULLABLE’ after datatype
4. Yes, that is the default case, for setting it to hold null values ‘NULLABLE’ is used after datatype

50. 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);

Answers:

1. = should be replaced by in operator
2. Order by clause in the subquery should be preceded with a group by clause
3. Order by clause in the subquery can be used only if the where and group by clauses have been applied
4. Group by clause should be applied to the outer query
5. An order by clause is not allowed in a subquery
6. There is no error

51. 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 courses in which the number of students enrolled is more than 5?

Answers:

1. Select course from students where count(course) > 5;
2. Select course from students where count(*) > 5 group by course;
3. Select course from student’s group by course;
4. Select course from student’s group by course having count(*) > 5;

52. Choose the correct statement regarding WAL in PostgreSQL:

Answers:

1. It increases the reliability of the database
2. It logs the entries to the database automatically
3. It is detrimental for database performance
4. There is no such term defined in PostgreSQL

53. Which of the following functions is not available in PostgreSQL?

Answers:

1. intfrombit
2. bittoint4
3. to_number
4. to_timestamp

54. What is the first step in installing PL/pgSQL in PostgreSQL?

Answers:

1. Use ‘CREATE FUNCTION’ to create the procedural call handler
2. Use ‘CREATE LANGUAGE’ SQL command
3. Either one of the above can be used

55. Which of the following is correct with regard to Password Authentication?

Answers:

1. pg_shadow table stores the password as plain text
2. pg_user table stores the password as encrypted text
3. Only superusers have access to the system password storing table
4. All of the above

56. 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 will you use to create a combined sales report for both the outlets?

Answers:

1. Select * from SVA.Sales join SVD.Sales
2. Select * from SVA.Sales union all SVD.Sales
3. select * from SVA.Sales,SVD.Sales
4. None of the above

57. Which of the following help PostgreSQL avoid unnecessary locking of records?

Answers:

1. Multi Lock
2. MCVR
3. MVCC
4. MVSQL

58. Which of the following programming structures is not available in PL/pgSQL?

Answers:

1. IF THEN ELSE
2. LOOP
3. WHILE
4. DO WHILE
5. FOR

59. When should sub queries be used?

Answers:

1. To define the set of rows to be inserted in a table
2. To define the set of rows to be included in a view
3. To define one or more values to be assigned to existing rows
4. To provide values for conditions in the Where clause
5. All of the above are correct

60. What is the default variable for the PROMPT3?

Answers:

1. ‘>>> ‘
2. ‘>> ‘
3. ‘%/%R%# ‘
4. ‘%/%R%# ‘

61. What does the following update statement do?

Update OrderTable set OrderDiscount=OrderDiscount*1.10

Answers:

1. It increases OrderDiscount of the first row by 10%
2. It increases OrderDiscount of all rows by 10%
3. It does nothing without the where clause
4. It generates an error due to the lack of a where clause

62. Which of the following files controls the host based authentication in PostgreSQL?

Answers:

1. pg_host_auth.conf
2. pghba.conf
3. pg_hba.conf
4. pghostauth.conf

63. A PL/pgSQL code block is defined with DECLARE, BEGIN and END. How many such sub blocks can be nested within a block?

Answers:

1. None
2. 2
3. 8
4. Unlimited

64. What is a cluster?

Answers:

1. A group of users
2. A group of indexes
3. A method of storing tables that are intimately related and often stored together into the same area of the disk
4. A compressed datafile
5. A segment of a partitioned table
6. A group of constraints
7. A group of databases
8. A group of roles

65. Which of the following security features is inbuilt in PostgreSQL?

Answers:

1. Stunnel
2. SSL on compiling with -with-ssl
3. SSH/OpenSSH
4. None of the above

66. Choose the correct statement:

Answers:

1. All PL/pgSQL expressions in a function, except dynamic queries, are only prepared once during the lifetime of the PostgreSQL backend process
2. All PL/pgSQL expressions in a function are only prepared once during the lifetime of the PostgreSQL backend process
3. All PL/pgSQL expressions in a function are prepared for each execution time during the lifetime of the PostgreSQL backend process
4. None of the above is correct

67. If entity x is existence-dependent on entity y, then what is x called?

Answers:

1. Dominant entity
2. Subordinate entity
3. Primary entity
4. Secondary entity

68. 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 trust

Answers:

1. Line 1 is a valid host based client config entry
2. Line 2 is a valid host based client config entry
3. Line 1 and Line 2 both are valid host based client config entries
4. Both Line 1 and Line 2 are invalid host based client config entries

69. Which of the following statements are true for views?

Answers:

1. A view is stored in the data dictionary
2. Views provide a more secure way of data retrieval
3. Views are actual tables and store data at another location
4. All of the above are true

70. What is wrong in this query:

Select * from Orders where OrderID=(select OrderID from OrderItems where ItemQty>50)

Answers:

1. In a sub query, * should be used instead of OrderID
2. The sub query can return more than one row, so, = should be replaced with in
3. The sub query should be used first
4. None of the above

71. Which of the following is not an SQL operator?

Answers:

1. Between .. and ..
2. Like
3. In
4. Is null
5. Having
6. Not in

72. Which of the following clauses are not allowed in a single row sub-query?

Answers:

1. From
2. Where
3. Group by
4. Having
5. Order by

73. 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
Employeename
Salary
Deptno

Department
———
Deptno
Departname

Answers:

1. Select departname from department where deptno in (select deptno from employee group by deptno having count(*) > 100);
2. Select departname from department where deptno in (select count(*) from employee group by deptno where count(*) > 100);
3. Select departname from department where count(deptno) > 100;
4. Select departname from department where deptno in (select count(*) from employee where count(*) > 100);

74. The primary key indexing technique does not allow:

Answers:

1. Duplicate data in a field
2. Multiple attributes
3. Sets of relations
4. Many to Many relation

75. You want to manipulate some value in the database upon updation in a trigger function. Which line of the code would be appropriate?

Answers:

1. IF TG_RELID = ”UPDATE” THEN –Modify the database value END IF;
2. IF TG_NAME = ”UPDATE” THEN –Modify the database value END IF;
3. IF TG_OP = ”UPDATE” THEN –Modify the database value END IF;
4. IF TG_ARGV = ”UPDATE” THEN –Modify the database value END IF;

76. Which of the following statements is correct with regard to PostgreSQL?

Answers:

1. It is an Object Oriented Database Management System
2. It is an Object Relational Database Management System
3. It is a Relational Database Management System
4. It is an Object Based Database Management System

77. Which of the following geometric types is not defined in PostgreSQL?

Answers:

1. path
2. box
3. hexagon
4. polygon

78. 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:

Answers:

1. DECLARE order_id ALIAS FOR $1; mytimestamp timestamp; BEGIN mytimestamp := ”now”; UPDATE orders SET orderid=order_id, lastmodified = mytimestamp; RETURN mytimestamp; END; ‘ LANGUAGE ‘plpgsql’;
2. DECLARE order_id ALIAS FOR $1; mytimestamp timestamp; BEGIN mytimestamp := ‘now’; UPDATE orders SET orderid=order_id, lastmodified = mytimestamp; RETURN mytimestamp; END; ‘ LANGUAGE ‘plpgsql’;
3. DECLARE order_id ALIAS FOR $1; BEGIN mytimestamp := ”now”; UPDATE orders SET orderid=order_id, lastmodified = ”now”; RETURN mytimestamp; END; ‘ LANGUAGE ‘plpgsql’;
4. DECLARE order_id ALIAS FOR $1; BEGIN mytimestamp := ”now”; UPDATE orders SET orderid=order_id, lastmodified = ‘now’; RETURN mytimestamp; END; ‘ LANGUAGE ‘plpgsql’;

79. You have defined a function “Calculate()” in the template1 database. What will happen when you create a new database “ManagementDB” there?

Answers:

1. The function will be moved to the new database
2. The function will be cloned to the new database
3. The function will remain with the template database only
4. Any of the above can happen

80. Which of the following is not a single value function?

Answers:

1. Round
2. Floor
3. Avg
4. Sqrt
5. Tan

81. Which component of a DBMS verifies the syntax of the users query?

Answers:

1. Parser
2. The database manager
3. Query optimization
4. Database administrator

82. Every Boyce-Codd Normal Form(BCNF) is in:

Answers:

1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. None of the above

83. Which of the following values is not defined to indicate the level of the raise event?

Answers:

1. DEBUG
2. ERROR
3. NOTICE
4. EXCEPTION

84. Which character function should be used to return a specified portion of a character string?

Answers:

1. CONCAT
2. LENGTH
3. SUBSTR
4. INITCAP

Finally no more words require about the POSTGRESQL TEST  information in this session of this content. If you are require knowing more, Please ask to us via our contact us form or comment box. Please make sure that, you don’t send Personal information via the Comment box. Thanks for Being with us.