Category Archives: Upwork Databases

Upwork Databases

SQL Test 2016

Published by:

1. Which are not DCL operations?

Answers:

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

2. Which of the following is not a DML command?

Answers:

  1. Set transaction
  2. Explain plan
  3. Update
  4. Grant
  5. Insert
  6. Create
  7. Alter
  8. Revoke

3. Which of the following is not a DDL command?

Answers:

  1. Drop
  2. Create
  3. Alter
  4. Revoke
  5. Explain plan
  6. Insert
  7. Update
  8. Delete

4. Which of the following statements are not true regarding the primary key?

Answers:

  1. There can only be one primary key in a table
  2. An index is automatically generated upon creation of a primary key
  3. A primary key can accept null values
  4. A primary key can be composite
  5. A primary key constraint can be either at the column level or at the table level
  6. There can be more than one primary key in a table

5. Which of the following statements are wrong about primary keys?

Answers:

  1. The primary key field must contain unique values
  2. The primary key field can contain null values
  3. A primary key is the column or set of columns that makes every row in the table unique
  4. A primary key can be created with a maximum of three columns
  5. Each table can have more than one primary keys
  6. Unique key is another name for primary key

6. Which statements are true for views?

Answers:

  1. The definition of a view is stored in data dictionary
  2. Views provide a more secure way of retrieving data
  3. Views are actually Tables and store data in the same manner as Tables

7. Examine the data in the EMPLOYEES table given below:

AST_NAME DEPARTMENT_ID SALARY

ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000

Which of the following Subqueries work?

Answers:

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
  4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

8. Examine the description of the STUDENTS table:

STD_ID NUMBER (4)
COURSE_ID VARCHAR2 (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:

Answers:

  1. SUM(start_date)
  2. AVG(start_date)
  3. COUNT(start_date)
  4. AVG(start_date, end_date)
  5. MIN(start_date)

9. Select all the appropriate options.

Answers:

  1. A table is a multiset of rows
  2. A table is a two-dimensional array of rows and columns
  3. A table is always dependent on other tables
  4. A third normal form table is a table free of redundant data
  5. A table must have a primary key

10. Which of the following field names are correct?

Answers:

  1. EmpNo
  2. 25Block
  3. #AccountID
  4. _CustomerName
  5. Product.Name

11. Which component of an RDBMS validates the syntax of the user’s query?

Answers:

  1. Query Parser
  2. The Database Manager
  3. Query Optimization
  4. Database Administrator

12. What does the term DDL stand for?

Answers:

  1. Data Description Language
  2. Dynamic Data Language
  3. Data Definition Language
  4. Data Derived Language
  5. Descriptive Data Language

13. 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

14. ________ is an operation that displays rows which meet a condition.

Answers:

  1. Restriction
  2. Extraction
  3. Projection
  4. Intersection
  5. Union
  6. Minus
  7. None of the above

15. A table Students has a column called name which stores the names of the students. What will be the correct query to display the names of the students in reverse order?

Answers:

  1. Select name from students reverse;
  2. Select name from students reverse name;
  3. Select name from students order by name descending;
  4. Select name from students order by name reverse;
  5. Select name from students order by name desc;
  6. Select desc name from students;
  7. Select reverse name from students;

16. What items, other than column names can be included in the select clause?

Answers:

  1. Arithmetic expressions
  2. Column aliases
  3. Concatenated columns
  4. None of the above

17. Which of the following statements is true?

(a)The Insert statement creates new rows
(b)The Update statement modifies the table structure

Answers:

  1. only (a) is true
  2. only (b) is true
  3. both (a) and (b) are true
  4. both (a) and (b) are false

18. Which operator will be evaluated first in the following statement:

select (age + 3 * 4 / 2 – 8) from emp

Answers:

  1. +
  2. /
  3. *

19. What are the columns of a table called in a relational model?

Answers:

  1. Attributes
  2. Rows
  3. Tuples
  4. Constraints
  5. Keys
  6. Indexes
  7. Sets
  8. Elements

20. The level of data abstraction which describes how the data is actually stored is?

Answers:

  1. Physical level
  2. Conceptual level
  3. Storage level
  4. File level

21. Which of the following is not a set operator?

Answers:

  1. Union
  2. Union all
  3. Intersect
  4. Minus
  5. Minus all

22. View the following Create statement:

1 Create table Pers
2 (EmpNo Number(4) not null,
3 EName Char not null,
4 Join_dt Date not null,
5 Pay Number)

Which line contains an error?

Answers:

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

23. For which SQL operation is Alter Table used?

Answers:

  1. To add a column
  2. To add an integrity constraint
  3. To modify storage characteristics
  4. To enable/disable or drop an integrity constraint
  5. all of the above

24. < and > are examples of _________ type of operators.

Answers:

  1. Logical
  2. Arithmetic
  3. Assignment
  4. Ternary
  5. Relational
  6. Numeric
  7. Comparison
  8. None of the above

25. Examine the two SQL statements given below:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC

What is true about them?

Answers:

  1. The two statements produce identical results
  2. The second statement returns an error
  3. There is no need to specify DESC because the results are sorted in descending order by default

26. What does MOD() function do?

Answers:

  1. Returns the remainder after division
  2. Modifies the column definition
  3. Modifies the definition of a table
  4. None of the above

27. The names of those departments where there are more than 100 employees have to be displayed. Given two relations, employees and departments, what 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);

28. An association of several entities in a Entity-Relation model is called?

Answers:

  1. Tuple
  2. Record
  3. Relationship
  4. Field

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

Answers:

  1. A view is like a window through which data on 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

30. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

What is the query to determine the names of the Authors who have written more than 1 book?

Answers:

  1. select AuthorName from Authors where AuthorId in (select AuthorId from Books group by AuthorId having count(*)>1)
  2. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId and count(BookId)>1
  3. select AuthorName from Authors, Books where Authors.AuthorId=Books.AuthorId group by AuthorName having count(*)>1
  4. select AuthorName from Authors where AuthorId in (select AuthorId from Books having count(BookId)>1)

31. When a table is dropped using a simple DROP statement, SQL performs some more operations simultaneously, select all the valid operations?

Answers:

  1. Removes all rows from the table
  2. Drops all the table’s indexes
  3. Removes all dependent views
  4. Removes all dependent procedures

32. Which of the following statement is correct regarding table creation?

Answers:

  1. Tables once created cannot be modified to add columns
  2. Constraints can only be given while table creation
  3. One can easily create a table from a given table
  4. When a table is created from another table, all the constraints are copied as well
  5. The width of the columns cannot be modified
  6. Columns cannot be removed from a table

33. A production house has two sales outlets. Both outlets are maintaining their data separately in schemas A and B respectively. The Management wants to see the sale of both outlets in one report. Both outlets are using tables called Sales which have identical structure. Which method you will adopt to create the report?

Answers:

  1. Select * from A.Sales join B.Sales
  2. Select * from A.Sales union all B.Sales
  3. Select * from A.Sales, B.Sales
  4. None of the above

34. What are the programs that execute automatically whenever DML operations are performed on tables called?

Answers:

  1. Triggers
  2. Procedures
  3. Functions
  4. None of the above

35. Which of the following is not a type of constraint?

Answers:

  1. Primary key
  2. Unique
  3. Check
  4. Distinct
  5. Default

36. Consider the query:
SELECT name
FROM Student
WHERE name LIKE ‘_a%’;
Which names will be displayed?

Answers:

  1. Names starting with “a”
  2. Names containing “a” as the second letter
  3. Names starting with “a” or “A”
  4. Names containing “a” as any letter except the first

37. A production house needs a report about the sale where total sale of the day is more than $20,000. Which query should be used?

Answers:

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

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

Answers:

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

39. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;

Answers:

  1. EMPNO, unique value of ENAME and then SALARY are displayed
  2. EMPNO, unique value ENAME and unique value of SALARY are displayed
  3. DISTINCT is not a valid keyword in SQL
  4. No values will be displayed because the statement will return an error

40. What is a rollback of transactions normally used for?

Answers:

  1. Recover from the transaction failure
  2. Update the transaction
  3. Retrieve old records
  4. None of the above

41. The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4)

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

Answers:

  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL
  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL
  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end
  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades
  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL

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

Answers:

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

43. An RDBMS performs the following steps:
1)It calculates the results of the group functions of each group
2)It groups those rows together based on the group by clause
3)It orders the groups based on the results of the group functions in the order by clause
4)It chooses and eliminates groups based on the having clause
5)It chooses rows based on the where clause
Arrange the above steps in the correct order of execution:

Answers:

  1. 4,3,5,1,2
  2. 4,5,3,2,1
  3. 5,2,1,4,3
  4. 5,2,3,4,1
  5. 2,3,1,4,5
  6. 2,3,1,5,4
  7. 1,2,3,4,5
  8. 3,2,1,4,5

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

Answers:

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

45. What 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

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

47. In which type of database is SQL used?

Answers:

  1. Hierarchical
  2. Network
  3. Relational
  4. Object oriented
  5. All of above

48. The concept of data independence is similar to the concept of ________

Answers:

  1. Data type
  2. Abstract data type
  3. Consolidation
  4. Isolation

49. Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

Answers:

  1. The value displayed in the CALC_VALUE column will be lower
  2. The value displayed in the CALC_VALUE column will be higher
  3. There will be no difference in the value displayed in the CALC_VALUE column
  4. An error will be reported

50. Examine the query:-
select (2/2/4) from tab1;
where tab1 is a table with one row. This would give a result of:

Answers:

  1. 4
  2. 2
  3. 1
  4. .5
  5. .25
  6. 0
  7. 8
  8. 24

51. Where 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. To define a table to be operated on by a containing query
  6. All of the above are correct
  7. None of the above is correct

52. In which sequence are queries and sub-queries executed by the SQL Engine?

Answers:

  1. primary query -> sub query -> sub sub query and so on
  2. sub sub query -> sub query -> prime query
  3. the whole query is interpreted at one time
  4. there is no fixed sequence of interpretation, the query parser takes a decision on the fly

53. Are both the statements correct?

(a)where deptno in(2,4,5)
(b)where deptno=2 or deptno=4 or deptno=5

Answers:

  1. True
  2. False

54. What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by

Answers:

  1. 1,2,3,4,5
  2. 1,3,5,4,2
  3. 1,3,5,2,4
  4. 1,3,2,5,4
  5. 1,3,2,4,5
  6. 1,5,2,3,4
  7. 1,4,2,3,5
  8. 1,4,3,2,5

55. There are two tables A and B. You are retreiving data from both tables where all rows from B table and only matching rows from A table should be displayed. Which type of join you will apply between A and B tables?

Answers:

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

56. 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. Stdev 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

57. How many foreign key constraints can a table have?

Answers:

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. None of the above

58. Which statement is correct for FIRST NORMAL FORM?

Answers:

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

59. What is wrong with the following query:

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

Answers:

  1. In the 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 not be in parenthesis
  4. None of the above

60. A company has the following departments:

Marketing , Designing , Production , Packing

What will be the result of the following query?

select * from table where department < ‘Marketing’;

Answers:

  1. The query will return ” Designing , Packing “
  2. The query will return ” Designing , production ,Packing “
  3. The query will return “Packing”
  4. Strings cannot be compared using < operator
  5. The query will return ” Designing “

61. Which of the following is not a numeric group function?

Answers:

  1. Avg
  2. Count
  3. Highest
  4. Max
  5. Stdev
  6. Sum

62. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

What is the query to determine which German books(if any) are more popular than all the French?

Answers:

  1. select bookname from books where language=’German’ and popularityrating = (select popularityrating from books where language=’French’)
  2. select bookname from books where language=’German’ and popularityrating > (select popularityrating from books where language=’French’)
  3. select bookname from books where language=’French’ and popularityrating > (select max(popularityrating) from books where language=’German’)
  4. select bookname from books where language=’German’ and popularityrating > (select max(popularityrating) from books where language=’French’)

63. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

What is the query to determine which is the most popular book written in French?

Answers:

  1. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books where language=’French’)
  2. select bookname from books where language=’French’ and popularityrating = (select max(popularityrating) from books Having language=’French’)
  3. select bookname,max(popularityrating) from books where language=’French’ and max(popularityrating)
  4. select bookname,max(popularityrating) from books where language=’French’ having max(popularityrating)

64. Which of the following constraints cannot be applied at the table level?

Answers:

  1. Primary key
  2. Foreign key
  3. Not null
  4. Check
  5. Unique

65. A construction company is currently executing three projects- hotel construction, residential construction and business towers. The construction company employs both Civil Engineers and Structural Engineers. A Civil Engineer can work on only one project at a time, but each project can accomodate more than one Civil Engineer. On the other hand, a Structural Engineer can work on more than one project and a project could accomodate several Structural Engineers. Define the nature of relationship between (Civil Engineers and Projects) and (Structural Engineers and Projects)

Answers:

  1. one to many, one to one
  2. one to one, one to many
  3. many to one, many to many
  4. many to one, many to one

66. If entity x is existence-dependent on entity y then what is x said to be?

Answers:

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

67. Examine the code given below:

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statements is correct with regard to this code?

Answers:

  1. It returns employees whose salary is 50% more than $23,000
  2. It returns employees who have 50% commission rate or salary greater than $23,000
  3. It returns employees whose salary is 50% less than $23,000
  4. None of the above

68. Consider the following tables:

Books

BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects

SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors

AuthorId
AuthorName
Country

What is the query to determine how many books have been written on each subject. Displaying Name of Subject and count of the Books?

Answers:

  1. select subject,count(*) from books,subjects where books.subjectid=subjects.subjectid group by books.subjectid
  2. select count(*),subject from books,subjects where books.subject.id=subjects.subject.id group by subjects.subject
  3. select subject,count(*) from books,subjects where books.Authorid=subjects.Authorid group by books.subjectid,subjects.subject
  4. select subject,count(*) from books,subjects where books.BookId=subjects.BookId group by books.subjectid,subjects.subject

69. Which of the following statements are true?

Answers:

  1. With DDL you can create and remove tables, schemas, domains, indexes and views
  2. Select, Insert and Update are DCL commands
  3. Grant and Revoke are DML commands
  4. Commit and Rollback are DCL commands

70. Which logical operator can reverse the result?

Answers:

  1. AND
  2. OR
  3. NOT
  4. ANY

71. What is the collection of information stored in a database at a particular moment called?

Answers:

  1. Schema
  2. Instance
  3. Table
  4. Cluster
  5. View
  6. Index
  7. None of the above

72. What is the order of precedence among the following operators?

1 IN
2 NOT
3 AND
4 OR

Answers:

  1. 1,2,3,4
  2. 2,3,4,1
  3. 1,2,4,3
  4. 1,4,3,2
  5. 4,3,2,1
  6. 4,1,2,3
  7. 4,2,1,3
  8. 3,2,1,4

73. Which of the following date function(s) are invalid in Oracle SQL?

Answers:

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

74. Which of the following are aggregate functions in SQL?

Answers:

  1. Avg
  2. Select
  3. Order By
  4. Sum
  5. Union
  6. Group by
  7. Having

75. Which of the following is not a relational operation?

Answers:

  1. Restriction
  2. Extraction
  3. Projection
  4. Intersection
  5. Union

76. The level of abstraction which describes only part of the entire database is called?

Answers:

  1. Conceptual level
  2. View level
  3. Procedural level
  4. None of the above

77. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

What is the query to determine how many books, with a popularity rating of more than 7, have been written on each subject?

Answers:

  1. select subject,count(*) as Books from books,subjects where books.popularityrating > 7 group by subjects.subject
  2. select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
  3. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
  4. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject

78. The overall logical structure of a database can be expressed graphically by:

Answers:

  1. Data Flow Chart
  2. Flow Chart
  3. Directed Chart
  4. Entity-Relationship Diagram
  5. None of the above

79. If E1 and E2 are relational algebra expressions, then which of the following is NOT a relational algebra expression?

Answers:

  1. E1 U E2
  2. E1 / E2
  3. E1 – E2
  4. E1 x E2

80. Which one of the following correctly selects rows from the table myTable that have null in column column1?

Answers:

  1. SELECT * FROM myTable WHERE column1 is null
  2. SELECT * FROM myTable WHERE column1 = null
  3. SELECT * FROM myTable WHERE column1 EQUALS null
  4. SELECT * FROM myTable WHERE column1 NOT null
  5. SELECT * FROM myTable WHERE column1 CONTAINS null

81. A table has following values for its department field:

marketing, production, production, sales, NULL, NULL, Marketing, Null

What will the following query return:

Select distinct(department) from employees;

Answers:

  1. marketing, production, sales
  2. marketing, production, sales, NULL
  3. marketing, production, sales, NULL, NULL
  4. marketing, production, sales, NULL, Marketing
  5. marketing, production, sales, NULL, NULL, Marketing

82. Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen who joined between 1/1/2005 and 1/2/2005 ?

Answers:

  1. select * from Pers where joining_date from ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst’ or ‘Clerk’ or ‘Salesman’
  2. select * from Pers where joining_date between ‘1/1/2005’ to ‘1/2/2005′, job=’Analyst’ or job=’Clerk’ or job=’Salesman’
  3. select * from Pers where joining_date between ‘1/1/2005’ and ‘1/2/2005′ and (job=’Analyst’ or ‘Clerk’ or ‘Salesman’)
  4. None of the above

83. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

What is the query to determine which Authors have written at least 1 book with a popularity rating of less than 5?

Answers:

  1. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
  2. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
  3. select authorname from authors where authorid in (select BookId from books where popularityrating<5)
  4. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

84. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.

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

85. Which of the following statements are incorrect regarding definition of simple and complex views?

Answers:

  1. A simple view is one which derives data from only one table
  2. A simple view is one which contains no functions or groups of data
  3. A complex view is one which derives data from many tables
  4. A complex view is one which may contain functions
  5. A complex view cannot contain groups of data
  6. A complex view is a combination of more than one simple view
  7. A table can either have a simple or complex view, not both

86. What is a cluster?

Answers:

  1. Group of users
  2. Group of indexes
  3. Method of storing tables that are intimately related and often joined together into the same area of the disk
  4. Compressed datafile
  5. Segment of a partitioned table
  6. Group of constraints
  7. Group of databases
  8. Group of roles

87. The purpose of the domain is to constrain the set of valid values. True or false?

Answers:

  1. True
  2. False

88. Consider the following two tables:

1. customers( customer_id, customer_name)
2. branch ( branch_id, branch_name )

What will be the output if the following query is executed:

Select *, branch_name from customers, branch

Answers:

  1. It will return the fields customer_id, customer_name, branch_name with a cartesian join
  2. It will return the fields customer_id, customer_name, branch_id, branch_name with a cartesian join
  3. It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name with a cartesian join
  4. It will return an empty set since the two tables do not have any common field name
  5. It will return an error since * should be for queries involving one table only

89. With regard to statement (a) and statement (b), Which of the following option is correct.

(a)The FLOOR function returns the smallest integer greater or equal to the argument.
(b)The CEIL function gives the largest integer equal to or less that the argument.

Answers:

  1. (a) is true
  2. (b) is true
  3. both (a) and (b) are true
  4. both (a) and (b) are false

90. The Employee table uses alphanumeric characters for their Employee ID field, which contains 7 digits prefixed with 3 characters. The alphanumeric characters refer to the department code. You want to generate a list of all the department codes displayed in capital letters. Which function(s) you will use for this query?

Answers:

  1. CONCAT()
  2. SUBSTR()
  3. UPPER()
  4. LOWER()
  5. REPLACE()

91. Which of the following is not a SQL operator?

Answers:

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

92. Which of the following is not a feature of SQL?

Answers:

  1. SQL is a non-procedural language
  2. SQL processes sets of records rather than a single record at a time
  3. SQL can be used by a range of users including DBA’s, application programmers, management personnel and many other types of end users
  4. Procedures and functions can be written with SQL
  5. SQL provides commands for a variety of tasks including querying data and inserting, updating and deleting data
  6. SQL can retrieve data from more than one table

93. 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

94. The simplest query must include at least________ and _________.

Answers:

  1. A select clause
  2. A where clause
  3. A from clause
  4. A group by clause
  5. A having clause
  6. An order by clause

95. 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

96. Identify the incorrect statement/s regarding constraints.

Answers:

  1. A primary key constraint can be disabled
  2. Information about the constraints on a table can be retrieved from the data dictionary
  3. Information about the columns on which the constraints have been applied can be retrieved from the data dictionary
  4. There can be only one unique key in a table
  5. If a column has null values then the not null constraint cannot be applied to that column
  6. There can be more than one primary key

97. Which of the following is not the benefits of views?

Answers:

  1. Restricting access to database
  2. Allowing users to make simple queries to retrieve the results from complicated queries
  3. Providing data independence for adhoc users and application programs
  4. Faster retrieval of records
  5. Views allow the same data to be seen by different users in different ways

98. _________ is the operation that displays certain columns from the table.

Answers:

  1. Restriction
  2. Intersection
  3. Join
  4. Union
  5. Projection
  6. Selection
  7. Extraction
  8. SubQuery

3,961 total views, 17 views today

Upwork Databases

PostgreSQL Test 2016

Published by:

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

1,182 total views, 2 views today

Upwork Databases

Postgre SQL RDBMS Test 2016

Published by:

1. Which authentication methods are supported by PostgreSQL?

Answers:

  1. Trust
  2. PAM
  3. LDAP
  4. Radius
  5. Password

2. Which index types are supported by PostgreSQL?

Answers:

  1. A-tree
  2. B-tree
  3. GiST
  4. Hash
  5. GIN
  6. None of the above

3. Out of the following backup approaches, which ones are applicable to PostgreSQL?

Answers:

  1. SQL dump
  2. File system level backup
  3. Real cluste
  4. Continuous archiving
  5. None of the above

4. In the following operation, which ones can trigger a trigger?

Answers:

  1. alte
  2. insert
  3. drop
  4. update
  5. delete

5. Which index types support multicolumn indexes?

Answers:

  1. -tree
  2. GiST
  3. GIN
  4. Hash
  5. None of the above

6. What kind of triggers are offered by PostgreSQL?

Answers:

  1. Per-row triggers
  2. Per-table triggers
  3. Per-database triggers
  4. Per-statement triggers

7. Which of the following statements will cast the integer value 1 to type text?

Answers:

  1. SELECT text(1) AS cast_integer;
  2. SELECT CAST(1, text) as cast_integer;
  3. SELECT 1::text AS cast_integer;
  4. SELECT 1 TO text AS cast_integer;
  5. SELECT CAST(1 AS text) AS cast_integer;

8. How can you configure PostgreSQL autovacuum?

Answers:

  1. With the ALTER autovacuum policy; query in psql
  2. By editing pg_hba.conf
  3. By editing postgresql.conf
  4. By running the autovacuum command line tool
  5. None of the above

9. What library is used by PostgreSQL for encryption?

Answers:

  1. GnuTLS
  2. OpenSSL
  3. OpenLDAP
  4. OpenTLS
  5. None of the above

10. Are the contents of the pg_autovacuum system catalog saved when pg_dumpall is used to backup the database?

Answers:

  1. Yes
  2. No

11. On a UNIX system, what is the best way to prevent all non-local connections to the postmaster?

Answers:

  1. Using ipfilte
  2. Using UNIX sockets
  3. Using TCP port 0
  4. Using an empty pg_hba.conf
  5. None of the above

12. Can deferrable constraints be deferred by a trigger?

Answers:

  1. Yes
  2. No

13. After a PostgreSQL installation, how will you create the database cluster?

Answers:

  1. With created
  2. With initd
  3. With createdbs
  4. With postmaste
  5. None of the above

14. How will you change the TCP port which PostgreSQL will listen to?

Answers:

  1. PostgreSQL does not support TCP
  2. By changing “port” in postgresql.conf
  3. By issuing UPDATE config SET port = <newvalue
  4. TCP port cannot be changed
  5. None of the above

15. Which of the following queries will create a table with two fields, “id” and “name” with “id” as an auto incrementing primary key?

Answers:

  1. create table foo (id int serial primary key auto, name varchar(255));
  2. create table foo (id int auto_increment primary key, name varchar(255));
  3. create table foo (id auto_increment primary key, name varchar(255));
  4. create table foo (id serial primary key, name varchar(255));
  5. None of the above

16. How will you list the available functions from psql?

Answers:

  1. select * from pg_functions;
  2. df
  3. ? functions
  4. select * from pg_procedures;
  5. None of the above

17. Consider the following query:

Create table foo (bar varchar);

What will be the size limit of the bar?

Answers:

  1. 256
  2. 1024
  3. 1
  4. No limit (It will be equivalent to the text)
  5. None of the above

18. What is the well known port number for the postgresql database service?

Answers:

  1. 5000
  2. 541
  3. 5432
  4. 63
  5. None of the above

19. What is true regarding file system backup?

Answers:

  1. It can only be used to backup the whole database
  2. The database must be running for the backup to take place
  3. To restore a file system backup, pg_restore should be used
  4. None of the above
  5. All of the above

20. Which one of the following text search functions does not exist?

Answers:

  1. to_tsvecto
  2. plainto_tsquery
  3. to_tsquery
  4. strip
  5. ts_rewrite
  6. plainto_tsvecto

21. How do you alter a column to forbid null values?

Answers:

  1. alter table foo alter bar set not null;
  2. alter table foo alter bar avoid null;
  3. alter table foo alter bar forbid null;
  4. alter table foo alter bar add not null;
  5. None of the above

22. What is the difference between to_tsvector() and ::tsvector ?

Answers:

  1. to_tsvector () normalizes the string while ::tsvector does not
  2. to_tsvector () can be used in select statements, while ::tsvector cannot
  3. They are equivalent
  4. None of the above

23. What is the storage size of an integer on a 64bit system?

Answers:

  1. 8bytes
  2. 2bytes
  3. 16bytes
  4. 4bytes
  5. 64bytes

24. While creating a trigger, the function it will call may be created after it and attached to it.

Answers:

  1. True
  2. False

25. Which function should be used to highlight the results?

Answers:

  1. ts_highlight
  2. ts_tag
  3. ts_headline
  4. ts_select
  5. None of the above

26. Which PostgreSQL version added the enum datatype?

Answers:

  1. 8.0
  2. 8.1
  3. 7.4
  4. 8.3
  5. PostgreSQL doesn’t have an enum type.

27. When using LIKE to compare strings, what is the wildcard operator (operator which matches zero or more characters)?

Answers:

  1. *
  2. $
  3. %
  4. &
  5. None of the above

28. For proper results, which of the following should contain a tsvector?

Answers:

  1. Lexemes
  2. Tokens
  3. Integers
  4. Sets
  5. Enum

29. What is the command used to import a backup made with pg_dumpall > file.dmp?

Answers:

  1. pg_restore file.dmp
  2. psql -f file.dmp
  3. pg_restoreall file.dmp
  4. postgre –restore -f file.dmp
  5. None of the above

30. Which of the following statements will produce an error?

Answers:

  1. SELECT now()::timestamp;
  2. SELECT now()::int;
  3. SELECT now()::varchar;
  4. SELECT now()::char;
  5. None

31. To backup a database, the postmaster daemon must be halted.

Answers:

  1. True
  2. False

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

SELECT products[1] FROM store_products;

Answers:

  1. True
  2. False

33. SELECT ‘infinity’::timestamp;

Will this statement produce an error?

Answers:

  1. Yes
  2. No

34. What is the difference between tokens and lexemes?

Answers:

  1. Tokens are always in upper case
  2. Lexemes are normalized
  3. A lexeme is a string while a token is an intege
  4. Lexemes are always in upper case
  5. None of the above

35. Which kind of index can be declared unique?

Answers:

  1. Hash
  2. A-tree
  3. B-tree
  4. GIN
  5. GiST

36. SELECT rtrim(‘foobar’, ‘abr’);

The result of this statement is foo.

Answers:

  1. True
  2. False

37. SELECT !!3;

What output will this statement give?

Answers:

  1. true
  2. 3
  3. 6
  4. The statement is invalid.
  5. None of the above.

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

Answers:

  1. True
  2. False

39. While creating a table with a field of the serial type, a sequence will be created.

Answers:

  1. True
  2. False

40. How do you create a table with a field of the int array type?

Answers:

  1. create table foo (bar int array);
  2. create table foo (bar integer[]);
  3. create table foo (bar array int);
  4. create table foo (bar[] int);
  5. None of the above

41. What is the name of the special time input with value 00:00:00.00 UTC?

Answers:

  1. zero
  2. noon
  3. midnight
  4. allballs
  5. None of the above

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

Answers:

  1. insert into example (a, b, c) values (1, 2, 3), (1, 2, 4);
  2. insert into example (a, b, c) values (1, 2, 3), (3, 3, 3);
  3. insert into example (a, b, c) values (1, 1, 1), (3, 3, 3);
  4. insert into example (a, b, c) values (1, 2, 3), (1, 4, 3);
  5. None will cause an error

43. Which of the following statements will create a table with a multidimensional array as second column?

Answers:

  1. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[[]]);
  2. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[][]);
  3. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text{2});
  4. CREATE TABLE favorite_books (customer_id integer, themes_and_titles text[text]);

44. If max_connections is 10 and 10 connections are currently active, how can you be sure the superuser will be available to connect?

Answers:

  1. You cannot
  2. Leave a superuser connected all the time
  3. Set superuser_reserved_connections in postgresql.conf
  4. Kill postmaster and connect to it
  5. None of the above

45. How will you rank text search results?

Answers:

  1. With the ORDER BY operator
  2. With the ts_rank function
  3. Search results are automatically ranked
  4. Search results cannot be ranked
  5. None of the above

46. Which of the following statements will create a table special_products which is a child of the table store_products?

Answers:

  1. CREATE TABLE special_products (quality int) INHERITS store_products;
  2. CREATE TABLE special_products (quality int) EXTENDS store_products;
  3. CREATE TABLE special_products (quality int) EXPANDS store_products;
  4. CREATE TABLE special_products (quality int) FROM store_products;
  5. CREATE TABLE special_products (quality int) WITH PARENT store_products;

47. Which of the following statements will create a table?

Answers:

  1. SELECT INTO products_backup FROM special_products;
  2. SELECT FROM special_products * INTO products_backup;
  3. SELECT * INTO products_backup LIKE special_products;
  4. SELECT * INTO products_backup FROM special_products;
  5. SELECT COPY special_products INTO products_backup;

48. Which of the following statements will retrieve the number of values stored in an array column?

Answers:

  1. SELECT array_dims(products) FROM store_products;
  2. SELECT products[] FROM store_products;
  3. SELECT dimensions(products) FROM store_products;
  4. SELECT array_dim(products) FROM store_products;
  5. SELECT count(products) FROM store_products;

49. Does PostgreSQL support SSL?

Answers:

  1. Yes
  2. No

50. What is the effect of turning fsync off in postgresql.conf?

Answers:

  1. File synchronization will be deactivated
  2. fsync is not a valid configuration option
  3. PostgreSQL will not enforce write ahead log flush
  4. It will turn off fast synchronization
  5. None of the above

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

Answers:

  1. UPDATE store_products ONLY SET name = ‘Wine’ WHERE id = 2;
  2. UPDATE store_products SET name= ‘Wine’ WHERE id = 2;
  3. UPDATE ONLY store_products SET name = ‘Wine’ WHERE id = 2;
  4. UPDATE JUST store_products SET name = ‘Wine’ WHERE id = 2;
  5. UPDATE store_products NOT special_products SET name = ‘Wine’ WHERE id = 2;

52. What can be stored in a column of type decimal(4,3)?

Answers:

  1. 4 numeric values with up to 3 digits to the right of the decimal point.
  2. A numeric value with up to 7 digits in total, 3 of which can be to the right of the decimal point.
  3. A numeric value with up to 4 digits in total, 3 of which can be to the right of the decimal point.
  4. A numeric value with at least 4 digits, 3 of which must be to the right of the decimal point.

53. What is the ~ operator?

Answers:

  1. POSIX regular expression match operator
  2. XOR operator
  3. NOT operator
  4. Home directory ENV variable
  5. None of the above

54. What interfaces are available in the base distribution of PostgreSQL?

Answers:

  1. Java
  2. PHP
  3. C
  4. Ruby
  5. Perl

55. What is the default ordering when ORDER BY is not specified?

Answers:

  1. By ID
  2. By insertion date
  3. By name
  4. The ordering is unknown if not specified
  5. None of the above

56. A table can have only one primary key column.

Answers:

  1. True
  2. False

57. What command will correctly restore a backup made with the following command?
pg_dump -Fc dbname > filename

Answers:

  1. psql -f filename dbname
  2. psql -Fc filename dbname
  3. pg_restore filename dbname
  4. pg_restore -d dbname filename
  5. None of the above

58. How do you select a single random row from a table?

Answers:

  1. SELECT random() * FROM tab LIMIT 1;
  2. SELECT * FROM tab ORDER BY random() LIMIT 1;
  3. SELECT * FROM tab RANDOM LIMIT 1 ;
  4. SELECT RANDOM 1 from tab;
  5. None of the above

59. PostgreSQL triggers can be written in C directly.

Answers:

  1. True
  2. False

1,068 total views, 2 views today

Upwork Databases

Oracle SQL 9i Test 2016

Published by:

1. Which of the following constitute the correct guidelines for naming database tables?

Answers:

  1. Must begin with either a number or letter
  2. Must be 1-30 characters long
  3. Should not be an Oracle Server reserved word
  4. Must contain only A-Z, a-z, 0-9, _,*, and #
  5. Must contain only A-Z, a-z, 0-9, _, $, and #
  6. Must begin with a letter

2. Which of the following are DML statements?

Answers:

  1. COMMIT
  2. MERGE
  3. UPDATE
  4. DELETE
  5. CREATE
  6. DROP

3. An outer join is used when:

Answers:

  1. The tables being joined have NOT NULL columns
  2. The tables being joined have only matched data.
  3. The columns being joined have NULL values
  4. The tables being joined have only unmatched data
  5. The tables being joined have both matched and unmatched data

4. Which of the following components are required to run iSQL*plus on PC?

Answers:

  1. SQL*PLUS installed on the PC
  2. HTTP Server
  3. Oracle net on PC
  4. iSQL*PLUS Server

5. Which of the following constitute the attributes of /SQL*Plus?

Answers:

  1. /SQL*Plus commands cannot be abbreviated
  2. /SQL*Plus commands are accessed from a browser
  3. /SQL*Plus commands are used to manipulate data in tables
  4. /SQL*Plus commands manipulate table definitions in the database
  5. /SQL*Plus is the Oracle proprietary interface for executing SQL statements

6. Which component is a literal in the following select statement?
Select ‘Emp name :’||ename from emp where deptno=20;

Answers:

  1. ename
  2. 20
  3. Emp name:
  4. ||

7. Which of the following statements is a complete transaction?

Answers:

  1. DELETE employees;
  2. DESCRIBE employees;
  3. ROLLBACK TO SAVEPOINT C;
  4. GRANT SELECT ON employees TO SCOTT;
  5. ALTER TABLE employees SET UNUSED COLUMN sal;

8. Which of the following tasks can be performed by using the TO_CHAR function?

Answers:

  1. Convert ’10’to 10
  2. Convert 10 to ’10’
  3. Convert ‘TEN’ to 10
  4. Convert a date to a character expression
  5. Convert a character expression to a date

9. What kinds of commands can you enter at the command prompt in 9i Sql Plus?

Answers:

  1. PL/SQL Blocks
  2. SQL*Plus commands
  3. Security commands
  4. SQL commands

10. Which of the following statements are correct with regard to WHERE and HAVING clauses?

Answers:

  1. A WHERE clause can be used to restrict both rows and groups
  2. A WHERE clause can be used to restrict rows only
  3. A HAVING clause can be used to restrict both rows and groups
  4. A HAVING clause can be used to restrict groups only
  5. A HAVING clause CANNOT be used in Subqueries

11. Examine the description of the STUDENTS table:

STD_ID NUMBER (4)
COURSE_ID VARCHAR2 (10)
START_DATE DATE
END_DATE DATE

The aggregate functions valid on the START_DATE columns are:

Answers:

  1. SUM(start_date)
  2. AVG(start_date)
  3. COUNT(start_date)
  4. AVG(start_date, end_date)
  5. MIN(start_date)

12. Examine the data in the EMPLOYEES table given below:

LAST_NAME �DEPARTMENT_ID SALARY
ALLEN 10 3000
MILLER 20 1500
King 20 2200
Davis 30 5000

Which of the following Subqueries work?

Answers:

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY
  2. partment_id);
  3. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY
  4. partment_id);
  5. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
  6. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
  7. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

13. You have to calculate the value 12* salary* commission_pct for all the employees in the EMP table. Which of the following statements ensures that a value is displayed in the calculated column for all the employees?

Answers:

  1. SELECT last_name, 12 * salary* commission_pct FROM emp;
  2. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;
  3. SELECT last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;
  4. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

14. Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

Answers:

  1. The value displayed in the CALC_VALUE column will be lower
  2. The value displayed in the CALC_VALUE column will be higher
  3. There will be no difference in the value displayed in the CALC_VALUE column
  4. An error will be reported

15. Which of the following is an iSQL*Plus command?

Answers:

  1. INSERT
  2. UPDATE
  3. SELECT
  4. DESCRIBE
  5. DELETE

16. Examine the code given below:
SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statement is correct with regard to this code?
Answers:

  1. It returns employees who have 50% of the salary greater than $23,000:
  2. It returns employees who have 50% commission rate or salary greater than $23,000:
  3. It returns employees who have 50% of salary less than $23,000:
  4. None of the above

17. Examine the two SQL statements given below:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

What is true about them?

Answers:

  1. The two statements produce identical results
  2. The second statement returns a syntax error while first will be executed successfully
  3. The second statement will return top 2 records having maximum salary, while first statement will return all records
  4. There is no need to specify DESC because the results are sorted in descending order by default

18. What will happen if you query the emp table shown below:
select empno,DISTINCT ename,Salary from emp;
Answers:

  1. EMPNO ,unique value of ENAME and then SALARY are Displayed.
  2. EMPNO ,unique value of the two columns, ENAME and salary are displayed.
  3. DISTINCT is not a valid keyword in SQL.
  4. No values will be displayed because the statement will return an error

19. How many join conditions should be there to avoid a Cartesion Join for joining three tables?

Answers:

  1. 1
  2. 2
  3. 3
  4. None of the above

20. Which operator will be evaluated first in the statement:
select (2+3*4/2-8) from dual:

Answers:

  1. +
  2. /
  3. *

21. Which of the following SQL statements returns a numeric value?
Answers:

  1. SELECT ADD_MONTHS(MAX (hire_date), 6) FROM EMP;
  2. SELECT ROUND(hire_date)FROM EMP;
  3. SELECT sysdate-hire_date FROM EMP;
  4. SELECT TO_NUMBER(hire_date + 7)FROM EMP;

22. Which of the following substitution variables should be used to reuse the variable value without prompting the user each time?

Answers:

  1. &
  2. ACCEPT
  3. PROMPT
  4. &&

23. Which of the following SELECT statements will get the result ‘elloworld’ from the string ‘HelloWorld’?

Answers:

  1. SELECT SUBSTR (‘HelloWorld’,1) FROM dual;
  2. SELECT INITCAP(TRIM(‘HellowWorld’, 1,1) FROM dual;
  3. SELECT LOWER (SUBSTR (‘HellowWorld’, 2,1) FROM dual;
  4. SELECT LOWER (TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;

24. You want to list names of the employees who have been with the company for more than five years. Which of the following SQL statements will display the required results?

Answers:

  1. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5
  2. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5
  3. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5
  4. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5

25. Examine the structure of the STUDENTS table given below:

STUDENT_ID �NUMBER �NOT NULL, Primary Key
STUDENT_NAME VARCHAR2 (30)
COURSE_ID VARCHAR2 (10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE

You need to create a report of ten students who achieved the highest ranking in the course INT_SQL and completed the course in the year 1999.

Which of the following SQL statements accomplishes this task?

Answers:

  1. SELECT student_id, marks, ROWNUM “Rank” FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’ORDER BY marks DESC;
  2. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99’AND course_id = ‘INT_SQL’ORDER BY marks;
  3. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’ ORDER BY marks DESC);
  4. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students WHERE finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’ ORDER BY marks DESC)WHERE ROWNUM <= 10 ;
  5. SELECT student_id, marks, ROWNUM “Rank” FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN-99′ AND ’31-DEC-99′ AND course_id = ‘INT_SQL’;

26. Which of the following views should a user query to display the columns associated with the constraints on a table owned by the user?

Answers:

  1. USER_CONSTRAINTS
  2. USER_OBJECTS
  3. ALL_CONSTRAINTS
  4. USER_CONS_COLUMNS
  5. USER_COLUMNS

27. What does the TRUNCATE statement do?

Answers:

  1.  Removes the table
  2. Removes all rows from a table
  3. Shortens the table to 10 rows
  4. Removes all columns from a table

28. Top N analysis requires _____ and _____.

Answers:

  1.  The use of rowed & Only an inline view
  2. a GROUP BY clause & Only an inline view
  3. an ORDER BY clause & An inline view and an outer query
  4. None of the above

29. Which data dictionary view holds information about the column in a view?

Answers:

  1. USER_VIEWS
  2. USER_VIEW_COLUMNS
  3. USER_TAB_COLUMNS
  4. USER_ALL_COLUMNS

30. Which of the following SELECT statements should be used to extract the year from the system date to display it in the format “2001”?

Answers:

  1. SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual;
  2. SELECT TO_DATE(SYSDATE, ‘yyyy’) FROM dual;
  3. SELECT DECODE(SUBSTR(SYSDATE, 8), ‘YYYY’) FROM dual;
  4. SELECT DECODE(SUBSTR(SYSDATE, 8), ‘year’) FROM dual;
  5. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),’yyyy’) FROM dual;

31. You want to display the titles of books that meet the following criteria:

1. Purchased before Feb 21, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.

Which of the following statements should you use?

Answers:

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-FEB-2002′ ORDER BY purchase_date;
  2. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date < ’21-FEB-2002′ ORDER BY purchase date ASC;
  3. SELECT book_title FROM books WHERE price < 500 OR > 900 AND purchase_date DESC;
  4. SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-FEB-2002′ ORDER BY purchase_date DESC;

32. Which of the following SQL statements accepts user input for the columns to be displayed, table name, and the WHERE condition?

Answers:

  1. SELECT &1, “&2″FROM &3 WHERE last_name = ‘&4’;
  2. SELECT &1, ‘&2’ FROM &3 WHERE ‘&last_name = ‘&4”;
  3. SELECT &1, &2 FROM &3 WHERE last_name = ‘&4’;
  4. SELECT &1, ‘&2’ FROM EMP WHERE last_name = ‘&4’;

33. Where is the GROUP BY clause statement placed in a SELECT statement that includes a WHERE clause?

Answers:

  1. Immediately after the SELECT clause
  2. Before the WHERE clause
  3. After the ORDER BY clause
  4. After the WHERE clause

34. Evaluate the SQL statement given below:

SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;

Which of the following values are displayed?
Answers:

  1. 46 and 45.93
  2. 50 and 45.93
  3. 50 and 45.9
  4. 45 and 45.93
  5. 45.95 and 45.93

35. Which of the following data types stores data outside the Oracle database?
Answers:

  1. UROWID
  2. BFILE
  3. BLOB
  4. NCLOB
  5. EXTERNAL

36. Which of the following statements are correct with regard to NULL values?

Answers:

  1. Only =and !=operator can be used to search for NULL values in a column
  2. In an ascending order sort, NULL values appear at the bottom of the result set
  3. Both a and b
  4. Neither a nor b

37. Which of the following operations cannot be performed using the ALTER TABLE statement?

Answers:

  1. Rename table
  2. Rename column
  3. Drop column
  4. Drop NOT NULL Constraint

38. Which of the following constraints can be defined only at the column level?

Answers:

  1. UNIQUE
  2. NOT NULL
  3. CHECK
  4. PRIMARY KEY
  5. FOREIGN KEY

39. Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

Answers:

  1. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
  2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
  3. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
  4. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));

40. Which of the following shows the correct use of the Trunc command on a date?

Answers:

  1. TRUNC=To_Date(’09-Jan-02,DD-MON-YY,’YEAR’,”Date” from Dual;
  2. Select TRUNC(To_Date(’09-Jan-02,DD-MON-YY,YEAR’)) “DATE” from Dual;
  3. Date =TRUNC(To_DATE(’09-Jan-02′,’DD-MON-YY’),’YEAR’),’YEAR)”DATE: from DUAL;
  4. SELECT TRUNC(TO_DATE(’12-Feb-99′,’DD-MON-YY’), ‘YEAR’) “Date ” FROM DUAL;

41. Which type of join will you write to perform an outer join of tables A and B that returns all rows from B-:

Answers:

  1. Any outer join
  2. A left outer join
  3. A cross join
  4. A right outer join
  5. An inner join

42. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which of the following statements will accomplish this task?

Answers:

  1. ALTER TABLE students ADD PRIMARY KEY student_id;
  2. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
  3. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
  4. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  5. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

43. Which of the following SQL statements should be used to remove a view called EMP_DEPT_VU from the schema?

Answers:

  1. DROP emp_dept_vu;
  2. DELETE emp_dept_vu;
  3. REMOVE emp_dept_vu;
  4. DROP VIEW emp_dept_vu;
  5. DELETE VIEW emp_dept_vu;
  6. REMOVE VIEW emp_dept_vu;

44. _______ operator can be used with a multiple row subquery.

Answers:

  1. =
  2. LIKE
  3. BETWEEN
  4. NOT IN
  5. Is

45. The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4, 3)

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

Answers:

  1. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
  2. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  3. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
  4. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
  5. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

1,130 total views, 4 views today

Upwork Databases

Oracle PL/Sql 10g Test 2016

Published by:

1. Which two among the following programming constructs can be grouped within a package?

Answers:

  1. Cursor
  2. Constant
  3. Trigger
  4. Sequence
  5. View

2. Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?

Answers:

  1. It persists across transactions within a session
  2. It persists from session to session for the same user
  3. It does not persist across transaction within a session
  4. It persists from user to user when the package is invoked
  5. It does not persist from session to session for the same user

3. Which of the following is not a legal declaration?

Answers:

  1. declare v number(4);
  2. declare x,y varchar2(10);
  3. declare birthdate date not null;
  4. declare Sex boolean:=1;

4. The oracle server implicitly opens a cursor to process:

Answers:

  1. A Sql select statement
  2. A PL/SQL Select statement
  3. DML Statements
  4. DDL Statements

5. Which two statements out of the following regarding packages are true?

Answers:

  1. Both the specification and body are required components of a package
  2. The package specification is optional, but the package body is required
  3. The package specification is required, but the package body is optional
  4. The specification and body of the package are stored separately in the database

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

Answers:

  1. Change the trigger type to a BEFORE DELETE
  2. Take out the COUNT function because it is not allowed in a trigger
  3. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger
  4. Change the trigger to a statement-level trigger by removing FOR EACH ROW

7. Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?

Answers:

  1. Declare
  2. Exception
  3. Begin
  4. End

8. Which of the following statements is true?

Answers:

  1. Stored functions can be called from the SELECT and WHERE clauses only
  2. Stored functions do not permit calculations that involve database links in a distributed environment
  3. Stored functions cannot manipulate new types of data, such as longitude and latitude
  4. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application

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

Answers:

  1. DROP gen_email;
  2. REMOVE gen_email;
  3. DELETE gen_email;
  4. DROP FUNCTION gen_email;

10. A table has to be dropped from within a stored procedure. How can this be implemented?

Answers:

  1. A table cannot be dropped from a stored procedure
  2. Use the DROP command in the procedure to drop the table
  3. Use the DBMS_DDL packaged routines in the procedure to drop the table
  4. Use the DBMS_DROP packaged routines in the procedure to drop the table.

11. In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?

Answers:

  1. Row Level Lock
  2. Page Level lock
  3. Column Level lock
  4. Exclusive Lock

12. If user defined error condition exists,Which of the following statements made a call to that exception?

Answers:

  1. Trap
  2. Raise
  3. call
  4. call Exception

13. Which of the following are identified by the “INSTEAD OF” clause in a trigger?

Answers:

  1. The view associated with the trigger
  2. The table associated with the trigger
  3. The event associated with the trigger
  4. The package associated with the trigger
  5. The statement level or for each row association to the trigger

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

Answers:

  1. FOR EACH ROW trigger on the EMP table
  2. Statement-level trigger on the EMP table
  3. FOR EACH ROW trigger on the AUDIT_TABLE table
  4. Statement-level trigger on the AUDIT_TABLE table

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

Answers:

  1. CAL_TAX
  2. INSERT_EMP
  3. UPDATE_SAL
  4. DELETE_EMP
  5. UPDATE_EMP

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

Answers:

  1. Row_value
  2. Column_value
  3. Raise_application
  4. Exception_init

17. An internal LOB is _____.

Answers:

  1. A table
  2. A column that is a primary key
  3. Stored in the database
  4. A file stored outside of the database, with an internal pointer to it from a database column.

18. What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?

Answers:

  1. Only one
  2. All that apply
  3. All referenced
  4. None

19. The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:

Answers:

  1. Concurrency Control
  2. Program Control
  3. PL/SQL Control
  4. Locking

20. Which table should be queried to determine when the procedure was last compiled?

Answers:

  1. USER_PROCEDURES
  2. USER_PROCS
  3. USER_OBJECTS
  4. USER_PLSQL_UNITS

21. Which cursor dynamically allows passing values to a cursor while opening another cursor?

Answers:

  1. Implicit Cursor
  2. User Defined Cursor
  3. Parameterized Cursor
  4. Explicit Cursor

22. When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:

Answers:

  1. PGA
  2. SGA
  3. Redo Log Buffer
  4. Data base buffer cache

23. Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?

Answers:

  1. Pragma
  2. Raise
  3. Trap
  4. Exception_init

24. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

Answers:

  1. The validity of the SQL statement is established
  2. An area of memory is established to process the SQL statement
  3. The SQL statement is run and the number of rows processed is returned
  4. The area of memory established to process the SQL statement is released

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

Answers:

  1. PROCEDURE combine;
  2. PROCEDURE combine (p_no NUMBER);
  3. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER);
  4. PROCEDURE combine_all(p_num_val VARCHAR2, p_char_val NUMBER);

26. Which part of a database trigger determines the number of times the trigger body executes?

Answers:

  1. Trigger type
  2. Trigger body
  3. Trigger event
  4. Trigger timing

27. Which table should be queried to check the status of a function?

Answers:

  1. USER_PROCS
  2. USER_OBJECTS
  3. USER_PROCEDURES
  4. USER_PLSQL_UNITS

28. Which of the following statements is true regarding stored procedures?

Answers:

  1. A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters
  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification
  3. A stored procedure must have at least one executable statement in the procedure body
  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters

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

Answers:

  1. DML trigger
  2. INSTEAD OF trigger
  3. Application trigger
  4. This is an invalid trigger

30. Which code is stored in the database when a procedure or function is created in SQL*PLUS?

Answers:

  1. Only P-CODE
  2. Only SOURCE code
  3. Both SOURCE CODE and P-CODE
  4. Neither SOURCE CODE or P-CODE

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

Answers:

  1. 0
  2. 1
  3. 2
  4. 98
  5. 100

32. What can be done with the DBMS_LOB package?

Answers:

  1. Use the DBMS_LOB.WRITE procedure to write data to a BFILE
  2. Use the DBMS_LOB.BFILENAME function to locate an external BFILE
  3. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE
  4. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed

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

Answers:

  1. AFTER UPDATE ON PLAYER
  2. AFTER SALARY UPDATE OF PLAYER
  3. AFTER UPDATE(SALARY) ON PLAYER
  4. AFTER UPDATE OF SALARY ON PLAYER

34. How can migration be done from a LONG to a LOB data type for a column?

Answers:

  1. Use the DBMS_MANAGE_LOB.MIGRATE procedure
  2. Use the UTL_MANAGE_LOB.MIGRATE procedure
  3. Use the DBMS_LOB.MIGRATE procedure
  4. You cannot migrate from a LONG to a LOB data type for a column
  5. Using ALTER TABLE statement

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

Answers:

  1. g_comm has a value of 15 at 9:06am for Smith
  2. g_comm has a value of 15 at 9:06am for Miller
  3. g_comm has a value of 20 at 9:06am for both Miller and Smith
  4. g_comm has a value of 15 at 9:03 am for both Miller and Smith

36. The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are INVALID.Which command can be issued to recompile both procedures?

Answers:

  1. COMPILE PROCEDURE CHECK_SAL;
  2. COMPILE PROCEDURE UPD_SAL;
  3. ALTER PROCEDURE CHECK_SAL compile
  4. ALTER PROCEDURE UPD_sAL compile

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

Answers:

  1. TRUE
  2. FALSE
  3. NULL
  4. none

38. Which package construct must be declared and defined within the packages body?

Answers:

  1. Exception
  2. Boolean Variable
  3. Public Procedure
  4. Private Procedure

39. What happens when rows are found using a FETCH statement?

Answers:

  1. The cursor opens
  2. The cursor closes
  3. The current row values are loaded into variables
  4. Variables are created to hold the current row values

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

Answers:

  1. 0
  2. 3
  3. TRUE
  4. NULL
  5. FALSE

41. Which two statements among the following, regarding oracle database 10g PL/SQL support for LOB migration, are true?

Answers:

  1. Standard package functions accept LOBs as parameters
  2. Standard package function do not accept LOBs as parameters
  3. Implicit data conversion is supported for converting LOB to RAW
  4. Implicit data conversion is not supported for converting varchar to LOB

42. Which command is used to disable all triggers on the EMPLOYEES table?

Answers:

  1. Multiple triggers on a table in one command cannot be disabled
  2. ALTER TRIGGERS ON TABLE employees DISABLE;
  3. ALTER employees DISABLE ALL TRIGGERS;
  4. ALTER TABLE employees DISABLE ALL TRIGGERS;

43. Which table and column can be queried to see all procedures and functions that have been marked invalid?

Answers:

  1. USER_ERRORS table,STATUS column
  2. USER_OBJECTS table,STATUS column
  3. USER_ERRORS table,INVALID column
  4. USER_OBJECTS table,INVALID column

44. SQL%ISOPEN always evaluates to false in case of a/an:

Answers:

  1. Explicit Cursor
  2. Implicit Cursor
  3. Paramertized Cursor
  4. Cursor with Arguments

45. Which datatype does the cursor attribute ‘%ISOPEN’ return?

Answers:

  1. BOOLEAN
  2. INTEGER
  3. NUMBER
  4. VARCHAR2

46. Which of the following is a benefit of using procedures and functions?

Answers:

  1. Procedures and Function increases the number of calls to the database
  2. Procedures and Function are reparsed for multiple users by exploiting shared SQL area
  3. Procedures and Function avoid reparsing for multiple users by exploiting shared SQL areas
  4. Testing of procedures and functions requires the database to be restarted, to clear out shared SQL areas and future access

47. All packages can be recompiled by using an Oracle utility called:

Answers:

  1. Dbms_Output
  2. Dbms_Lob
  3. Dbms_utility
  4. Dbms_Error

48. Which type of variable should be used to assign the value TRUE, FALSE?

Answers:

  1. Constant
  2. Scalar
  3. Reference
  4. Composite

49. In which type of trigger can the OLD and NEW qualifiers can be used?

Answers:

  1. Row level DML trigger
  2. Row level system trigger
  3. Statement level DML trigger
  4. Row level application trigger

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

Answers:

  1. 1
  2. 10
  3. None
  4. A value equal to the number of rows in the EMP table

1,817 total views, 6 views today

Upwork Databases

MySQL Test 2016

Published by:

1. Which of the following are true in case of Indexes for MYISAM Tables?

Answers:

  1. Indexes can have NULL values
  2. BLOB and TEXT columns can be indexed
  3. Indexes per table cannot be more than 16
  4. Columns per index cannot be more than 16

2. Below is the table “messages,” please find proper query and result from the choices below.

Id Name Other_Columns
————————-
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1

Answers:

  1. select * from (select * from messages GROUP BY id DESC) AS x ORDER BY name Result: 3 A A_data_3 5 B B_data_2 6 C C_data_1
  2. select * from messages where name =Desc Result: 1 A A_data_1 2 A A_data_2 3 A A_data_3
  3. select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1
  4. Answer A and B

3. How can an user quickly rename a MySQL database for InnoDB?

Answers:

  1. He cannot rename any MySQL database
  2. By using: RENAME DATABASE db_old_name TO db_new_name
  3. By using: RENAME DATABASE TO db_new_name
  4. By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name

4. Is it possible to insert several rows into a table with a single INSERT statement?

Answers:

  1. No
  2. Yes

5. Consider the following tables:

books

——

bookid

bookname

authorid

subjectid

popularityrating (the popularity of the book on a scale of 1 to 10)

language (such as French, English, German etc)

Subjects

———

subjectid

subject (such as History, Geography, Mathematics etc)

authors

——–

authorid

authorname

country
Which is the query to determine the Authors who have written at least 1 book with a popularity rating of less than 5?

Answers:

  1. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
  2. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
  3. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
  4. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

6. The Flush statement cannot be used for:

Answers:

  1. Closing any open tables in the table cache
  2. Closing open connections
  3. Flushing the log file
  4. Flushing the host cache

7. Consider the query:

SELECT name

FROM Students

WHERE name LIKE ‘_a%’;
Which names will be displayed?

Answers:

  1. Names starting with “a”
  2. Names containing “a” as the second lette
  3. Names starting with “a” or “A”
  4. Names containing “a” as any letter except the first

8. Which of the following is the best MySQL data type for currency values?

Answers:

  1. SMALLINT
  2. DECIMAL(19,4)
  3. VARCHAR(32)
  4. BIGINT

9. What are MySQL Spatial Data Types in the following list?

Answers:

  1. GEOMETRY
  2. CIRCLE
  3. SQUARE
  4. POINT
  5. POLYGON

10. Examine the two SQL statements given below:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC

What is true about them?

Answers:

  1. The two statements produce identical results
  2. The second statement returns an error
  3. There is no need to specify DESC because the results are sorted in descending order by default
  4. None of the above statments is correct

11. Which of the following will raise MySQL’s version of an error?

Answers:

  1. SIGNAL
  2. RAISE
  3. ERROR
  4. None of these.

12. Which query will return values containing strings “Pizza”, “Burger”, or “Hotdog” in the database?

Answers:

  1. SELECT * FROM fiberbox WHERE field REGEXP ‘Pizza|Burger|Hotdog’;
  2. SELECT * FROM fiberbox WHERE field LIKE ‘%Pizza%’ OR field LIKE ‘%Burger%’ OR field LIKE ‘%Hotdog%’;
  3. SELECT * FROM fiberbox WHERE field = ‘%Pizza%’ OR field = ‘%Burger%’ OR field = ‘%Hotdog%’;
  4. SELECT * FROM fiberbox WHERE field = ‘?Pizza?’ OR field = ‘?Burger?’ OR field = ‘?Hotdog?’;

13. Which datatype is used to store binary data in MySQL?

Answers:

  1. BLOB
  2. BIGINT
  3. INT
  4. Both BLOB and BIGINT

14. Which of the following will reset the MySQL password for a particular user?

Answers:

  1. UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’username’;
  2. UPDATE mysql.user SET Password=’password’ WHERE User=’username’;
  3. UPDATE mysql.user SET Password=RESET(‘password’) WHERE User=’username’;
  4. None of the above.

15. Which of the following is the best way to modify a table to allow null values?

Answers:

  1. ALTER TABLE table_name MODIFY column_name varchar(255) null
  2. ALTER TABLE table_name MODIFY column_name VARCHAR(255)
  3. ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL
  4. ALTER table_name MODIFY column_name varchar(255) null

16. Which of the following will dump the whole MySQL database to a file?

Answers:

  1. mysql -e “select * from myTable” -u myuser -pxxxxxxxxx mydatabase > mydumpfile.txt
  2. mysql -e “select * from myTable” mydatabase > mydumpfile.txt
  3. SELECT * from myTable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’
  4. None of the above.

17. Which of the following statements is true regarding character sets in MySQL?

Answers:

  1. The default character set of MySQL is UTF-8.
  2. lang.cnf sets the default character set for MySQL databases.
  3. SET CHARSET utf8 will set the character set of data to be imported to UTF-8.
  4. None of these.

18. Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)?

Answers:

  1. Using subqueries
  2. Using variables in a MySQL query
  3. Using self-join
  4. MySQL also supports ROW_NUMBER()

19. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)

Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)

Authors
——–
AuthorId
AuthorName
Country

Which query will determine how many books have a popularity rating of more than 7 on each subject?

Answers:

  1. select subject,count(*) as Books from books,subjects where books.popularityrating > 7
  2. select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
  3. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
  4. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject

20. Which of the following statements are true about SQL injection attacks?

Answers:

  1. Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections.
  2. Parametrized queries do not make code less vulnearable to SQL injections.
  3. SQL injections are not possible, if only emulated prepared statements are used.
  4. Usage of later versions of MySQL, validation, and explicit setting of the charset of user input are valid measures to decrease vulnerability to SQL injections.

21. Which of the following is an alternative to Subquery Factoring (ex. the ‘WITH’ clause in MS SQL Server)?

Answers:

  1. The ‘IN’ clause
  2. Using temporary tables and inline views
  3. The ‘INNER JOIN’ clause
  4. Using subqueries

22. Suppose a table has the following records:

+————–+————-+—————-+
| Item | Price | Brand |
+————–+————-+—————-+
| Watch | 100 | abc |
| Watch | 200 | xyz |
| Glasses | 300 | bcd |
| Watch | 500 | def |
| Glasses | 600 | fgh |
+————–+————-+—————-+

Which of the following will select the highest-priced record per item?

Answers:

  1. select item, brand, price from items where max(price) order by item
  2. select * from items where price = max group by item
  3. select item, brand, max(price) from items group by item
  4. select * from items where price > 200 order by item

23. Which of the following will restore a MySQL DB from a .dump file?

Answers:

  1. mysql -u<user> -p < db_backup.dump
  2. mysql -u<user> -p<password> < db_backup.dump
  3. mysql -u<user> -p <password> < db_backup.dump
  4. mysql -u<user> -p<password> > db_backup.dump

24. Which of the following will show when a table in a MySQL database was last updated?

Answers:

  1. Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’
  2. Creating an on-update trigger to write timestamp in a custom table, then querying the custom table
  3. Getting the “last modified” timestamp of the corresponding database file in the file system
  4. None of these.

25. Which of the following results in 0 (false)?

Answers:

  1. “EXPERTRATING” LIKE “EXP%”
  2. “EXPERTRATING” LIKE “Exp%”
  3. BINARY “EXPERTRATING” LIKE “EXP%”
  4. BINARY “EXPERTRATING” LIKE “Exp%”
  5. All will result in 1 (true)

26. Which of the following relational database management systems is simple to embed in a larger program?

Answers:

  1. MySQL
  2. SQLite
  3. Both
  4. None

27. What is true about the ENUM data type?

Answers:

  1. An enum value may be a user variable
  2. An enum may contain number enclosed in quotes
  3. An enum cannot contain an empty string
  4. An enum value may be NULL
  5. None of the above is true

28. What will happen if two tables in a database are named rating and RATING?

Answers:

  1. This is not possible as table names are case in-sensitive (rating and RATING are treated as same name)
  2. This is possible as table names are case sensitive (rating and RATING are treated as different names)
  3. This is possible on UNIX/LINUX and not on Windows platform
  4. This is possible on Windows and not on UNIX/LINUX platforms
  5. This depends on lower_case_table_names system variable

29. How can a InnoDB database be backed up without locking the tables?

Answers:

  1. mysqldump –single-transaction db_name
  2. mysqldump –force db_name
  3. mysqldump –quick db_name
  4. mysqldump –no-tablespaces db_name

30. What does the term “overhead” mean in MySQL?

Answers:

  1. Temporary diskspace that the database uses to run some of the queries
  2. The size of a table
  3. A tablespace name
  4. None of the above

31. Consider the following select statement and its output:

SELECT * FROM table1 ORDER BY column1;
Column1

——–

1

2

2

2

2

2

3
Given the above output, which one of the following commands deletes 3 of the 5 rows where column1 equals 2?

Answers:

  1. DELETE FIRST 4 FROM table1 WHERE column1=2
  2. DELETE 4 FROM table1 WHERE column1=2
  3. DELETE WHERE column1=2 LIMIT 4
  4. DELETE FROM table1 WHERE column1=2 LIMIT 3
  5. DELETE FROM table1 WHERE column1=2 LEAVING 1

32. Consider the following queries:

create table foo (id int primary key auto_increment, name int);
create table foo2 (id int auto_increment primary key, foo_id int references foo(id) on delete cascade);

Which of the following statements is true?

Answers:

  1. Two tables are created
  2. If a row in table foo2, with a foo_id of 2 is deleted, then the row with id = 2 in table foo is automatically deleted
  3. Those queries are invalid
  4. If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

33. What is NDB?

Answers:

  1. An in-memory storage engine offering high-availability and data-persistence features
  2. A filesystem
  3. An SQL superset
  4. MySQL scripting language
  5. None of the above

34. Which of the following statements are true?

Answers:

  1. Names of databases, tables and columns can be up to 64 characters in length
  2. Alias names can be up to 255 characters in length
  3. Names of databases, tables and columns can be up to 256 characters in length
  4. Alias names can be up to 64 characters in length

35. Which of the following statements is used to change the structure of a table once it has been created?

Answers:

  1. CHANGE TABLE
  2. MODIFY TABLE
  3. ALTER TABLE
  4. UPDATE TABLE

36. What does DETERMINISTIC mean in the creation of a function?

Answers:

  1. The function returns no value
  2. The function always returns the same value for the same input
  3. The function returns the input value
  4. None of the above

37. Which of the following statements grants permission to Peter with password Software?

Answers:

  1. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
  2. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
  3. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
  4. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’

38. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;

Answers:

  1. EMPNO, unique value of ENAME and then SALARY are displayed
  2. EMPNO, unique value ENAME and unique value of SALARY are displayed
  3. DISTINCT is not a valid keyword in SQL
  4. No values will be displayed because the statement will return an error

39. Which of the following is the best way to disable caching for a query?

Answers:

  1. Add the /*!no_query_cache*/ comment to the query.
  2. Flush the whole cache with the command: FLUSH QUERY CACHE
  3. Reset the query cache with the command: RESET QUERY CACHE
  4. Use the SQL_NO_CACHE option in the query.

40. What is the maximum size of a row in a MyISAM table?

Answers:

  1. No limit
  2. OS specific
  3. 65,534
  4. 2’147’483’648
  5. 128

41. Can you run multiple MySQL servers on a single machine?

Answers:

  1. No
  2. Yes

42. Which of the following formats does the date field accept by default?

Answers:

  1. DD-MM-YYYY
  2. YYYY-DD-MM
  3. YYYY-MM-DD
  4. MM-DD-YY
  5. MMDDYYYY

43. State whether true or false:
In the ‘where clause’ of a select statement, the AND operator displays a row if any of the conditions listed are true. The OR operator displays a row if all of the conditions listed are true.

Answers:

  1. True
  2. False

44. What is the name of the utility used to extract NDB configuration information?

Answers:

  1. ndb_config
  2. cluster_config
  3. ndb –config
  4. configNd
  5. None of the above

45. Which one of the following must be specified in every DELETE statement?

Answers:

  1. Table Name
  2. Database name
  3. LIMIT clause
  4. WHERE clause

46. Which of the following are not Numeric column types?

Answers:

  1. BIGINT
  2. LARGEINT
  3. SMALLINT
  4. DOUBLE
  5. DECIMAL

47. Which of the following statements is true regarding multi-table querying in MySQL?

Answers:

  1. JOIN queries are faster than WHERE queries.
  2. WHERE queries are faster than JOIN queries.
  3. INNER queries are faster than JOIN queries.
  4. WHERE & INNER offer the same performance in terms of speed.

48. What is wrong with the following statement?

create table foo (id int auto_increment, name int);

Answers:

  1. Nothing
  2. The id column cannot be auto incremented because it has not been defined as a primary key
  3. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);

49. Consider the following table definition:

CREATE TABLE table1 (
column1 INT,
column2 INT,
column3 INT,
column4 INT
)

Which one of the following is the correct syntax for adding the column, “column2a” after column2, to the table shown above?

Answers:

  1. ALTER TABLE table1 ADD column2a INT AFTER column2
  2. MODIFY TABLE table1 ADD column2a AFTER column2
  3. INSERT INTO table1 column2a AS INT AFTER column2
  4. ALTER TABLE table1 INSERT column2a INT AFTER column2
  5. CHANGE TABLE table1 INSERT column2a BEFORE column3
  6. Columns are always added after the last column

50. Examine the data in the employees table given below:
last_name department_id salary

ALLEN 10 3000

MILLER 20 1500

King 20 2200

Davis 30 5000
Which of the following Subqueries will execute well?

Answers:

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
  4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

51. What privilege do you need to create a function?

Answers:

  1. UPDATE
  2. CREATE ROUTINE
  3. SELECT
  4. CREATE FUNCTION
  5. No specific privilege

52. What is wrong with the following query:

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

Answers:

  1. In the 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 not be in parenthesis
  4. None of the above

53. Which of the following is a correct way to show the last queries executed on MySQL?

Answers:

  1. First execute SET GLOBAL log_output = ‘TABLE’; Then execute SET GLOBAL general_log = ‘ON’; The last queries executed are saved in the table mysql.general_log
  2. Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log
  3. Execute VIEW .mysql_history
  4. Restart MySQL using the following line tail -f /var/log/mysql/mysql.log

54. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.

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

55. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

Answers:

  1. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
  2. SELECT * FROM Persons SORT REVERSE ‘FirstName’
  3. SELECT * FROM Persons ORDER BY -‘FirstName’
  4. SELECT * FROM Persons ORDER BY FirstName DESC

56. You want to display the titles of books that meet the following criteria:

1. Purchased before November 11, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.
Which of the following statements should you use?

Answers:

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date;
  2. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< ‘2002-11-11’ ORDER BY purchase date ASC;
  3. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
  4. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date DESC;

57. State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine

Answers:

  1. True
  2. False

58. Consider the following table structure of students:

rollno int

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 students group by course;
  4. Select course from students group by course having count(*) > 5;
  5. Select course from students group by course where count(*) > 5;
  6. Select course from students where count(group(course)) > 5;
  7. Select count(course) > 5 from students;
  8. None of the above

59. MySQL supports 5 different int types. Which one takes 3 bytes?

Answers:

  1. TINYINT
  2. MEDIUMINT
  3. SMALLINT
  4. INT
  5. BIGINT

60. Which of the following is the correct way to determine duplicate values?

Answers:

  1. SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
  2. SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
  3. SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
  4. SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1

61. Examine the query:-

select (2/2/4) from tab1;

where tab1 is a table with one row. This would give a result of:

Answers:

  1. 4
  2. 2
  3. 1
  4. .5
  5. .25
  6. 8
  7. 24

62. Which of the following commands will list the tables of the current database?

Answers:

  1. SHOW TABLES
  2. DESCRIBE TABLES
  3. SHOW ALL TABLES
  4. LIST TABLES

63. Which of the following is not a MySQL statement?

Answers:

  1. ENUMERATE
  2. EXPLAIN
  3. KILL
  4. LOAD DATA
  5. SET

64. When running the following SELECT query:

SELECT ID FROM (
SELECT ID, name FROM (
SELECT *
FROM employee
)
);

The error message ‘Every derived table must have its own alias’ appears.
Which of the following is the best solution for this error?

Answers:

  1. SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) );
  2. SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) );
  3. SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
  4. SELECT ID AS FIRST_ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) );

65. Which of the following is not a Table Storage specifier in MySQL?

Answers:

  1. InnoDB
  2. MYISAM
  3. BLACKHOLE
  4. STACK

66. The REPLACE statement is:

Answers:

  1. Same as the INSERT statement
  2. Like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted
  3. There is no such statement as REPLACE

67. If you try to perform an arithmetic operation on a column containing NULL values, the output will be:

Answers:

  1. NULL
  2. An error will be generated
  3. Cannot be determined

68. Which of the following is the best way to insert a row, and to update an existing row, using a MySQL query?

Answers:

  1. Use MERGE statement
  2. Use INSERT … ON DUPLICATE KEY UPDATE statement
  3. Use ADD UNIQUE statement
  4. Use REPLACE statement

69. How will you change “Hansen” into “Nilsen” in the LastName column in the Persons Table?

Answers:

  1. UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
  2. UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
  3. SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
  4. SAVE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’

70. Which one of the following correctly selects rows from the table myTable that have NULL in column column1?

Answers:

  1. SELECT * FROM myTable WHERE column1 IS NULL
  2. SELECT * FROM myTable WHERE column1 = NULL
  3. SELECT * FROM myTable WHERE column1 EQUALS NULL
  4. SELECT * FROM myTable WHERE column1 NOT NULL
  5. SELECT * FROM myTable WHERE column1 CONTAINS NULL

71. Is the FROM clause necessary in every SELECT statement?

Answers:

  1. Yes
  2. No

72. Which command will make a backup on the whole database except the tables sessions and log?

Answers:

  1. mysqldump db_name sessions log > backup.sql
  2. mysqldump db_name | grep -vv -E “sessions|log” > backup.sql
  3. mysqldump db_name –ignore-table db_name.sessions db_name.log > backup.sql
  4. mysqldump db_name –except-table=db_name.sessions –except-table=db_name.log > backup.sql

6,051 total views, 6 views today

Upwork Databases

MS SQL Server 2005 Test 2016

Published by:

1. 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

2. What is the function of “DBCC CHECKDB” command?

Answers:

  1. It checks the allocation of all the objects in the specified database
  2. It checks the structural integrity of all the objects in the specified database
  3. It checks for consistency in and between system tables in the specified database
  4. It checks the consistency of disk space allocation structures of a specified database

3. You have a table “engineers” with the following table structure:
enggid int(4)
name varchar(50)
salary int(4)

You want to select the top 2 engineers in the decreasing order of their salaries, starting with the maximum salary. Which of the following SQL queries will fetch this data?

Answers:

  1. SELECT TOP 2 * FROM engineers ORDER BY max(salary) DESC
  2. SELECT TOP 2 * FROM engineers ORDER BY salary DESC, GROUP BY salary
  3. SELECT TOP 2 * FROM engineers GROUP BY salary DESC
  4. SELECT TOP 2 * FROM engineers ORDER BY salary DESC
  5. SELECT TOP 2 [name], salary FROM engineers ORDER BY salary DESC

4. SQL SERVER 2005 provides a new feature to enable partitioning of the data in the database. But only those database objects can be partitioned which store data in the database. In accordance with the above statement, which of the following database objects can be partitioned?

Answers:

  1. Functions
  2. Triggers
  3. Table
  4. Indexes
  5. Assemblies

5. Which of the following are the SQL Transaction statements?

Answers:

  1. REVERT STATEMENT
  2. ROLLBACK STATEMENT
  3. START STATEMENT
  4. COMMIT STATEMENT
  5. END STATEMENT

6. Which of the following operators perform pattern matching?

Answers:

  1. %
  2. _ (underscore)
  3. =
  4. ==
  5. is =

7. Which of the following statements regarding “views” are incorrect?

Answers:

  1. A view has a data of its own
  2. Data can be inserted into the table using its own view
  3. Data can be updated in the table using its own view
  4. A table can be altered by altering its corresponding view
  5. A View is derived from another table

8. You have a table named ’employees’, having the following structure.
empid int(4)
deptname varchar(50)
salary int(4)

And a view is created as follows:

create view viemployees as select * from employees
You want to insert a new row into the table having the following values:
empid=1010, deptname=HR, salary=10000.

Which of the following are the correct insert SQL queries?

Answers:

  1. insert into employees values (1010, HR, 10000)
  2. insert into employees values (1010, ‘HR’, 10000)
  3. insert into viemployees values (1010, ‘HR’, 10000)
  4. insert into viemployees (empid, deptname, salary) values (1010, HR, 10000)
  5. insert into employees (empid, deptname, salary) set values (1010, ‘HR’, 10000)

9. Examine the data in the EMPLOYEES table given below:

LAST_NAME DEPARTMENT_ID SALARY
Allen 10 3000
Miller 20 1500
King 20 2200
Davis 30 5000

Which of the following sub-queries work?

Answers:

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id)
  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id)
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id)
  4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)
  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY))

10. Which of the following are not Database Models?

Answers:

  1. Hierarchical model
  2. Network model
  3. Composite model
  4. Relational model
  5. Recursive model

11. Which functions does the following SQL QUERY perform?

DBCC CHECKIDENT (‘table_name’, RESEED, new_reseed_value)

Answers:

  1. It checks the current identity field value of the specified table
  2. It sets the identity field value to the new reseed value
  3. It sets the identity field value to 1
  4. None of the above

12. You have a table named “employees” with the following table structure:

empid int(4)
deptname varchar(50)
salary int(4)

The following select query is executed on this table:

SELECT * FROM employees ORDER BY deptname DESC, salary DESC

What will be the output of the above query?

Answers:

  1. The above SQL query will give an error
  2. Records will be shown with the salary field displayed in the descending order
  3. Records will be shown with the deptname field in the ascending order and salary in the descending order
  4. Records will be shown with the deptname field and the salary field in the descending order
  5. Records will be shown in a jumbled manner

13. Consider the following tables:
Customers
– – – – – – – –
Customerid
Customername
Address

Orders
– – – – –
Orderid
Customerid
Orderdate
Comments
What will be the query to fetch Customername and 1st Orderdate for order placed by each customer?

Answers:

  1. Select Customers.Customername,(Select Min(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  2. Select Customers.Customername,(Select Max(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  3. Select Customers.Customername,(Select Orderdate From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  4. Select Customers.Customername,Min(Orders.Orderdate) From Orders,Customers Where Customers.Customerid=Orders.Customerid
  5. None of these

14. Which edition of the SQL SERVER 2005 will best meet the following scenario?

A company has an online shopping website named www.shoppingxyz.com. It also has to handle its line of business portion. Its main server supports 4 CPUs and a multi-core processor.

Answers:

  1. SQL SERVER 2005 Enterprise Edition
  2. SQL SERVER 2005 Standard Edition
  3. SQL SERVER 2005 Workgroup Edition
  4. SQL SERVER 2005 Developer Edition
  5. SQL SERVER 2005 Express Edition

15. State whether True or False.

While using the Select query, the IN clause is a better choice than the EXIST clause.

Answers:

  1. True
  2. False

16. Consider the following table structure of employees:

empid int(4)
deptname varchar(50)
salary int(4)

Which query will be used to display the Department (deptname) that is giving the maximum salary?

Answers:

  1. select deptname from employees where salary =max(salary)
  2. select deptname from employees where salary =max(salary) group by deptname
  3. select deptname from employees where salary = (select salary from employees group by salary)
  4. select deptname from employees where salary =(select max(salary) from employees)
  5. select deptname from employees where salary in (select salary from employees group by salary)
  6. select deptname from employees where max(salary) in (select salary from employees group by salary)

17. State whether True or False.

Many-to-One is a type of relationship in RDBMS.

Answers:

  1. False
  2. True

18. State whether True or False.

You can send emails through the Database engine.

Answers:

  1. False
  2. True

19. State whether True or False.

The ‘Having’ clause can not be used when there is a ‘Where’ clause in the statement already.

Answers:

  1. True
  2. False

20. What exactly is a Foreign key?

Answers:

  1. A Foreign key is a column or a set of columns that uniquely identifies rows in a table
  2. A Foreign key checks the number of columns in a table which have non-null values
  3. A Foreign key is used to match columns in other tables
  4. A Foreign key checks the number of rows in a table which have non-null values

21. Which of the following is not a relational operation?

Answers:

  1. Restriction
  2. Extraction
  3. Projection
  4. Intersection
  5. Union

22. Which of the following statements regarding Trigger and Stored Procedures is correct?

Answers:

  1. Triggers are fired manually and Stored Procedures are fired automatically
  2. Triggers can call Stored Procedures but Stored Procedures cannot call Triggers
  3. Both Stored Procedures and Triggers can pass parameters
  4. None of These

23. A table of employees has the following values for its salary field:

10000, 11000, 12000, 10000, 14000, 12000, 13000, 10000, 14000, 10000

What will the following query return?
Select distinct (salary) from employees

Answers:

  1. 10000, 14000, 12000, 10000, 11000
  2. 10000, 11000, 12000, 10000, 14000, 12000
  3. 10000, 11000, 14000, 12000, 13000, 10000
  4. 10000, 11000, 12000, 13000, 10000, 14000
  5. 10000, 11000, 12000, 14000, 13000

24. Which one of the following statements regarding “views” is incorrect?

Answers:

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

25. What will the following query do?

Delete Orders

Answers:

  1. It will remove the Orders Table from the database
  2. It will remove up to 1000 rows from the Orders Table
  3. It will remove all rows from the Orders Table
  4. It will remove all the rows as well as the indexes and constraints from the Orders Table
  5. It will produce error on execution

26. The – (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.

Answers:

  1. logical
  2. arithmetical
  3. assignment
  4. unary

27. You have a table “engineers” with the following table structure:

enggid int(4)
deptname varchar(50)
salary int(4)

You want to display the minimum and maximum salaries of the individual departments. Which of the following queries will fetch the desired results?

Answers:

  1. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers
  2. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by deptname
  3. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by salary
  4. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers order by deptname
  5. None of these

28. Which of the following are correct?

Answers:

  1. With DDL, you can create and remove tables, schemas, domains, indexes and views
  2. Select, Insert and Update are DCL commands
  3. Grant and Revoke are DML commands
  4. Commit and Rollback are DCL commands

29. We have a table named “Customer” which has a column named “Customerid” having datatype Integer. We need to fetch the output with column title: ‘Customer id is (value of customerid from the table)’. What will be the query?

Answers:

  1. Select ‘Customer id is’ + customerid From Customer
  2. Select ‘Customer id is’ + CAST(customerid AS varchar) From Customer
  3. Select ‘Customer id is’ + CAST(customerid AS integer) From Customer
  4. Both a and b
  5. None of these

30. Which of the following statements is wrong?

Answers:

  1. Truncate is faster than Delete
  2. Truncate is a DDL command
  3. Truncate retains the identity of the table
  4. Truncate cannot be rolled back
  5. Truncate removes all rows from the table

31. The table Comments has an auto incremented column called “Commentid” and a “UserComments” column, both of which store the comments of the users visiting the site. What will be the correct query to display the latest comments first?

Answers:

  1. Select usercomments From Comments Order by commentid
  2. Select usercomments From Comments Order by commentid desc
  3. Select usercomments From Comments Order by usercomments
  4. Select usercomments From Comments Order by usercomments desc

32. Consider the following table:

Order
– – – – –
Orderid
Customerid
Orderdate
Comments

Select ISNULL (Comments, ‘No comments’) AS comments From Order

What will be the output of the above query?

Answers:

  1. The output will always be “No comments” irrespective of the value in the Comments field
  2. The output will be null in all the rows
  3. The output will be “No comments”, when the database entry is Null
  4. The output will be “No comments”, when the database entry is Not Null
  5. This query is erroneous

33. What is the maximum storage capacity of XML data type in SQL SERVER 2005?

Answers:

  1. 1GB
  2. 2GB
  3. 4GB

34. Columns are called _____________ of a table in a relational model.

Answers:

  1. Tuples
  2. Elements
  3. Objects
  4. Attributes
  5. Constraints

35. Which is the best method to handle errors and deadlocks?

Answers:

  1. Errors and deadlocks can be handled by using stored procedures
  2. Errors and deadlocks can be handled by using cursors
  3. Errors and deadlocks can be handled by using Try and Catch constructs
  4. Errors and deadlocks can be handled by using inline queries

36. Which of the following is not a Data type in the SQL SERVER 2005?

Answers:

  1. ntext
  2. nvarchar(n)
  3. xml
  4. nsmallmoney

37. Which command is used to create User defined Data Type in the SQL SERVER 2005?

Answers:

  1. sp_addtype
  2. Create Type
  3. Create Data Type
  4. None of these

38. Which of the following mechanisms is used by “ENCRYPTION by passphrase” to encrypt the data in the SQL SERVER 2005?

Answers:

  1. DES (Data Encryption Standard)
  2. AES (Advanced Encryption Standard)
  3. user defined password

39. Which one of the following statements about Indexes is wrong?

Answers:

  1. Indexes are created in an existing table to locate rows more quickly and efficiently
  2. It is possible to create an index on one or more columns of a table
  3. A table is scanned when index is not available
  4. Indexes can only be implemented on integer datatypes

40. What will be the output of the following SQL query?

SET SHOWPLAN_TEXT ON
GO

SELECT * FROM abcsite.abctable
GO

Answers:

  1. It will show the query plan and the estimated cost of running
  2. It will show all the rows of the “abctable” table
  3. It will show all the rows of the “abctable” table and the table structure
  4. It will show the identities and Constraints applied on this table

41. __________ is a container that holds tables, views, procedures, functions and so on.

Answers:

  1. Instance
  2. Cluster
  3. Metadata
  4. Schema
  5. None of the above

42. What exactly is a Super key?

Answers:

  1. A Super key is a column or a set of columns that uniquely identifies rows in a table
  2. A Super key checks the number of columns in a table which have non-null values
  3. A Super key is used to match columns in other tables
  4. A Super key checks the number of rows in a table which have non-null values

43. State whether True or False.

Triggers can be created in Assemblies.

Answers:

  1. True
  2. False

44. Which one of the following statements is correct?

Answers:

  1. You can create DDL triggers
  2. You can create DML triggers
  3. Both a and b are correct
  4. Neither a nor b is correct

45. Starting from the slowest, arrange the following operators in the order in which they produce results in “SQL WHERE” query.

1. >, >=, <, <=
2. <>
3. =
4. LIKE

Answers:

  1. 1,4,3,2
  2. 4,1,2,3
  3. 3,2,1,4
  4. 2,4,1,3

46. Why are Joins generally better than the Correlated sub-query?

Answers:

  1. The Correlated sub-query scans the whole table
  2. Joins pick up the rows according to the condition of the queries
  3. The Correlated sub-query creates a nested loop
  4. The Correlated sub-query makes the statement complex

47. Consider the following table “Students”:

Students
– – – – – –
Name Hobbies

John Driving
Miller Fishing
David Writing
Robert Singing

What will be the output of the following query?

Select Substring(Hobbies,3) From students Where name= “john”

Answers:

  1. Dri
  2. ivi
  3. iving
  4. ving
  5. It will give an error

48. What exactly is the Full-Text Catalog?

Answers:

  1. The Full-Text catalog is an index for searching specific keywords
  2. The Full-Text catalog is a file which contains full-text indexes
  3. The Full-Text catalog is basically an inbuilt function for checking the validation of a text
  4. The Full-Text catalog is a system trigger for checking text validation

49. The commands GRANT and REVOKE are examples of :

Answers:

  1. DDL (Data Definition Language)
  2. DML (Data Manipulation Language)
  3. TCL (Transaction Control Language)
  4. DCL (Data Control Language)

50. We are creating Stored Procedure in Database and using “sp_” prefix in its name. Which of the following statements is correct in accordance with the performance of the Stored Procedure execution?

Answers:

  1. The prefix will speed up the execution as it is in cache and will always get executed with high priority
  2. The prefix will slow down the execution as the prefix is reserved for system stored procedures
  3. The performance will not be affected as “sp_” is just a prefix for a stored procedure
  4. The prefix will speed up the execution as prefix “sp_” is used for stored procedures by default

51. Which of the following commands will be used to see the structure of the table named ‘salary’?

Answers:

  1. sp_helptext salary
  2. sp_define salary
  3. sp_help salary
  4. define salary

52. Which of the following operators will be evaluated first in the following statement?

Select (salary+40^2*30/5) from employees

Answers:

  1. +
  2. *
  3. /
  4. ^

53. State whether True or False.

The “text” and “ntext” data type can store character based data. These data types can be used in joins.

Answers:

  1. True
  2. False

54. Which of the following is not a type of Database backup in the SQL SERVER 2005?

Answers:

  1. Complete backup
  2. Differential backup
  3. Transaction Log backup
  4. Partial backup
  5. File(s) and Filegroup(s) backup

55. Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?

Answers:

  1. Join
  2. Group By
  3. Order By
  4. Union All
  5. Where

56. You have a database named ‘marketnikkie’, the backup of which is stored at ‘D:marketnikkie.bak’ location on your server. Due to some electric fluctuations, the database gets corrupt. Which of the following is the correct query to restore your database?

Answers:

  1. RESTORE DATABASE marketnikkie FROM DISK = ‘D:marketnikkie.bak’
  2. RESTORE DATABASE FROM DISK = ‘D:marketnikkie.bak’
  3. RESTORE DATABASE marketnikkie FROM = ‘D:marketnikkie.bak’
  4. RESTORE marketnikkie FROM DISK = ‘D:marketnikkie.bak’
  5. RESTORE DATABASE marketnikkie.mdf FROM DISK = ‘D:marketnikkie.bak’

57. Which among the following are the new datatypes introduced in the SQL Server 2005?

Answers:

  1. varchar (max)
  2. varbinary (max)
  3. nvarchar (max)
  4. nvarbinary (max)
  5. image
  6. uniqueidentifier
  7. xml

58. Which of the following is not a Method call of .NET class for Assemblies in the SQL Server?

Answers:

  1. Init
  2. Accumulate
  3. Aggregate
  4. Merge
  5. Terminate

59. You have two tables emp_1 and emp_2, having the following data:

Table emp_1

Name Date
Robert 1/5/2008
John 1/6/2008
Michael 1/7/2008
Rachael 1/10/2008
Joey 1/10/2008

Table emp_2

Name Date
Lewis 1/05/2008
David 1/07/2008
George 1/11/2008
Jerry 1/12/2008
Monica 1/12/2008

The following query is run in the Query Analyzer:

SELECT Date FROM emp_1
EXCEPT
SELECT Date FROM emp_2

What will be the result of the query?

Answers:

  1. 1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008
  2. 1/6/2008, 1/10/2008, 1/10/2008
  3. 1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008, 1/12/2008
  4. 1/6/2008, 1/10/2008
  5. The query will give an error as EXCEPT is only concerned with varchar values

60. State whether True or False.

The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.

Answers:

  1. True
  2. False

61. Which of the following is the new sample database in the SQL SERVER 2005?

Answers:

  1. Northwind
  2. pubs
  3. AdventureWorks
  4. master
  5. tempdb

62. Which of the following SQL queries correctly selects the rows from the table “employees” that have NULL value in the “salary” column?

Answers:

  1. SELECT * FROM employees WHERE salary=null
  2. SELECT * FROM employees WHERE salary equals null
  3. SELECT * FROM employees WHERE salary is null
  4. SELECT * FROM employees WHERE salary contains null

63. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?

Answers:

  1. View Level
  2. Procedural Level
  3. Physical Level
  4. Logical Level
  5. Conceptual Level

64. Which of the following “insert queries” will be inserted in the XML field?

The creation query of the table is:
CREATE TABLE Branches(recordID int, description XML)

Answers:

  1. INSERT Into Branches(recordID, description) VALUES(1, ‘<log><application>Sales</application><description>The connection timed out.</description></log>’)
  2. INSERT UniversalLog(recordID, description) VALUES(1, ‘database unavailable’)
  3. INSERT Into Branches(recordID, description) VALUES(1, ‘<log><application>Sales<description>The connection timed out’)
  4. XML cannot be inserted into database
  5. XML can be inserted but syntax in all queries is wrong

65. Which of the following is not an Aggregate function?

Answers:

  1. AVG()
  2. LEN()
  3. COUNT()
  4. SUM()
  5. MAX(column)

66. Consider the following query:

Select name from employees where name like “%_n_”

Which names will be displayed?

Answers:

  1. Names starting with n
  2. Names containing n
  3. Names containing n as the second letter
  4. Names containing n as the second last letter
  5. Names having 3 ns

67. You want to select a row using cursors in the table ‘qlist’. Which of the following statements DECLARES a cursor ‘qselect’ to perform the desired operation?

Answers:

  1. DECLARE qselect CURSOR ON SELECT * FROM qlist
  2. DECLARE qselect CURSOR FOR SELECT * FROM qlist
  3. DECLARE qlist CURSOR ON SELECT * FROM qselect
  4. DECLARE qselect CURSOR AS SELECT * FROM qlist

68. Which query will be used for a particular column of a table, if ‘Reading’ is to be replaced by ‘Writing’?

Answers:

  1. Update [Table Name] set [Field Name]= (replace(‘Reading’,’Writing’))
  2. Update [Table Name] set [Field Name]= (replace(‘Writing’,’ Reading’))
  3. Update [Table Name] set [Field Name]= (replace([Field Name],’ Writing’))
  4. Update [Table Name] set [Field Name]= (replace([Field Name],’ Reading ‘))
  5. Update [Table Name] set [Field Name]= (replace([Field Name],’Reading’,’Writing’))
  6. Update [Table Name] set [Field Name]= (replace([Field Name],’Writing ‘,’Reading’))

69. What is wrong with the following query in accordance with performance?

SELECT * FROM [Table_Name] WHERE LOWER(Field_Name) = ‘name’

Answers:

  1. The required keyword (‘name’) is already in the LOWER case
  2. The query requires more time to convert to the LOWER case
  3. T-Sql is not case sensitive. Hence, the LOWER keyword is not required
  4. Instead of ‘=’ the operator, ‘LIKE’ should be used

70. How is the following query incorrect?

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

Answers:

  1. In the 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 not be in parenthesis
  4. None of the above

71. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?

Answers:

  1. real
  2. rowversion
  3. type_var
  4. sql_variant
  5. monetary

72. Which of the following is not a logical operator?

Answers:

  1. between
  2. any
  3. some
  4. like
  5. with

73. Which of the following methods is used to backup the Transaction log on to a secondary database in order to backup and recover data in a synchronized manner?

Answers:

  1. Using the backup of Transaction log (ldf) file
  2. Taking Backup using flat files
  3. Using Log Shipping
  4. Taking Database Snapshot of Transaction log

74. Which of the following statements is/are wrong?

Answers:

  1. Both Primary key and Unique key enforce uniqueness
  2. Primary keys create a clustered index by default
  3. Both Primary key and Unique key don’t allow NULL values
  4. Unique keys create a non-clustered index by default
  5. It is possible to have more than one Primary key in a table

75. Which of the following is a database object?

Answers:

  1. Tables
  2. Stored Procedures
  3. Indexes
  4. UDF(User Defined Functions)
  5. All of the above

76. State whether True or False.

A User Defined Function ‘findsum’ is to be created as follows:
Create function findsum(@x int, @y int)
As
Begin
Return(@x+@y)
End

But the SQL SERVER is unable to create the desired function and gives an error. The error can be removed by specifying the return type of the function.

Answers:

  1. False
  2. True

77. Which of the following statements is correct?

Answers:

  1. Modifications made in a table will be faster if the number of indexes is more
  2. Modifications made in a table will be slower if the number of indexes is more
  3. The number of indexes does not affect the modification process of a table

1,536 total views, 5 views today

Upwork Databases

MS Access 2003 Test 2016

Published by:

1. A query that uses the BETWEEN syntax for two numbers is identical to:

Answers:

  1. >= and <=
  2. > and <
  3. >= and <
  4. > and <=
  5. No such synta

2. How can the duplicate data on a report be hidden?

Answers:

  1. By Setting the HideDuplicates property to Yes
  2. By Setting the DuplicateRecords property to No
  3. By Setting the DuplicateRecords property to No and setting the GrpKeepTogether property to Per Page
  4. Duplicate Data should be handled at the time of data entry and cannot be controlled while reporting

3. Which of the following is the easiest and fastest method to manipulate records on a subform?

Answers:

  1. Using a Recordset Type Property
  2. Using a RecordSetClone Property
  3. Using a RecordSelectors Property
  4. Using a References Property

4. How do you start an Access application so that the user can’t see or use the database container?

Answers:

  1. Set the Access Option to hide the navigation pane, and press CTRL+F11 special keys.
  2. Set the Access Option to hide the navigation pane, and press ALT+F1 special keys.
  3. Set the Access Option to hide the navigation pane, and turn off special keys to prevent F11.
  4. Set the Access Option to hide the navigation pane, and press CTRL+G special keys .

5. How is data in a single record kept together on a page (for a report) or on a column (for a form)?

Answers:

  1. By setting the KeepTogether property to Yes
  2. By setting the SinglePagePrint property to Yes
  3. By setting the DataTogether property to Yes
  4. This feature is currently not available in Access

6. How do you customize the text on a form or report’s window frame or tab?

Answers:

  1. TabIndex Property
  2. Text Property
  3. Form Property
  4. Caption Property

7. What is the function of a Format Painter?

Answers:

  1. It copies a Table Design to the buffer for later use
  2. It copies a control’s format to another control
  3. It helps to format a report so that it looks tidy and presentable
  4. It copies a report design to the buffer for later use

8. Under the Advanced options for the Access system, there is an option entitled Open Databases Using Record Level Locking. Which of the following are true when this option is checked?

Answers:

  1. Microsoft Access locks the row that is being updated
  2. Microsoft Access locks the page that is being updated
  3. Microsoft Access locks the table that is being updated
  4. Microsoft Access locks the database that is being updated

9. Which of the following is not a section of a report?

Answers:

  1. Summary
  2. Detail
  3. Group Footer
  4. Report Footer

10. The primary key index does not allow ________ data in a field.

Answers:

  1. numeric
  2. character
  3. date
  4. duplicate

11. What is the file extension of an Access Project?

Answers:

  1. .adp
  2. .xml
  3. .mdb
  4. .mdf

12. State whether True or False.

When a table from an Access database is exported to another database format, Microsoft Access exports the table data and data definitions, as well as its properties (including constraints, relationships, and indexes).

Answers:

  1. True
  2. False

13. A company has the following departments:
Finance, Accounts, Human Resources, Administration

There is a table named Company having a column named Department, and the above values are stored in that column.

What will be the result of the following query?

Select Department from Company where Department < ‘Finance’

Answers:

  1. The query will return “Accounts, Human Resource”
  2. The query will return “Accounts, Administration ,Human Resource”
  3. The query will return “Administration”
  4. Strings cannot be compared using the < operator
  5. The query will return “Accounts, Administration”

14. If a relationship has the ‘Cascade Update Related Fields’ option on, which of the following is true?

Answers:

  1. Data in linked fields cannot be updated.
  2. When a parent record is updated, all child records in the linked table are updated.
  3. Null or blank values cannot be entered into the table.
  4. Data in the linked tables can be updated, but new data cannot be added.

15. Which of the following options most clearly defines the meaning of a bound HTML Control on a Data Access Page?

Answers:

  1. A Control that is bound to the HTML code residing online on a Web Server
  2. A Control that displays information in the HTML code which is later used for publishing data on the internet
  3. A Control that is bound to the HTML code showcasing the data from a particular field of a table
  4. A Control that computes user defined expressions and stores the result in a table in the form of the HTML code.

16. In Access 2003, one can control the user access to data, through permissions. The Update data permission allows a user to:

Answers:

  1. view and modify but not insert or delete data
  2. view, modify, insert and delete data
  3. view, modify and insert but not delete data
  4. view but not modify, insert or delete data

17. Which of the following is not done when you compact a database?

Answers:

  1. Restructuring table records and objects, and then stores them in successive memory blocks.
  2. Databases linked to the compacted database are compacted and repaired.
  3. Restores table records into their primary key order, if a primary key exists in the table
  4. Reduces the physical size of your database by making an exact duplicate of the database.

18. Under the Advanced options in Access 2003, there is a Default Open Mode Group option which includes the options Shared and Exclusive. Which of the following is true when the Shared option is selected?

Answers:

  1. All users have the query owner’s permission to view or run the query
  2. Only the query owner can save changes to the query and other networked users cannot use the open database simultaneously
  3. It enables other network users to use the open database simultaneously
  4. Only the query owner can change the ownership of the query

19. Which query will you use to count the number of records in a result set?

Answers:

  1. Select count(*) as total from students
  2. Select count(amount) as total from students
  3. Select total from students
  4. Select * from students

20. The Database Splitter is one of the database utilities. Which of the following is true about the Database Splitter?

Answers:

  1. This utility splits a Microsoft Access database into three files
  2. This utility breaks up the information in the tables into a compacted format for faster retrieval
  3. This utility segregates the tables from the queries, forms, reports, macros, and modules
  4. All of the above

21. In a report with grouping, how can you display the count of records in each group?

Answers:

  1. Add a text box to the group’s Page Footer, and its control source to “=Count(*)”
  2. Add a text box to the group’s Footer section, and its control source to “=[RecordCount]”
  3. Add a text box to the group’s Report Footer, and its control source to “=[RecordCount]”
  4. Add a text box to the group’s Footer section, and its control source to “=Count(*)”

22. Which of these functions determines if a phrase exists in a longer string?

Answers:

  1. InStr
  2. Len
  3. Trim
  4. Left

23. What does an Access Data Projects (ADP) offer that an MDB/ACCDB doesn’t?

Answers:

  1. It can perform heterogeneous queries across a variety of data sources
  2. It is tied directly to one SQL Server database and can create and modify SQL Server objects
  3. It supports temporary tables or links to other data sources beyond the single SQL Server database

24. If a user has a database opened in the Exclusive mode, what will happen if another user attempts to open the same database?

Answers:

  1. The second user will receive the ‘Permission denied’ message
  2. The second user can make changes to the file that has been opened by the first user, and the first user will receive the “File is being modified by another user” message
  3. The second user receives the ‘File already in use’ message
  4. Both users will receive the ‘Permission denied’ message

 

948 total views, 1 views today

Upwork Databases

Data Warehousing Test 2016

Published by:

1. Which of the following are the modes of OLAP?

Answers:

  1. MOLAP
  2. ROLAP
  3. KOLAP

2. OLAP queries can be characterised as on-line transactions that do not:

Answers:

  1. Access small amounts of data
  2. Analyse the relationships between many types of business elements e.g. sales, products, regions, and channels
  3. Compare aggregated data over hierarchical time periods e.g. monthly, quarterly, yearly
  4. Present data in different perspectives e.g. sales by region vs. sales by channels by product within each region
  5. Respond quickly to user requests, so that users can pursue an analytical thought process without being stymied by the system

3. Normalisation is:

Answers:

  1. The process of organising data in accordance with the rules of a relational database
  2. The process of cleansing the data
  3. The process of integrating the data into the datawarehouse from legacy systems
  4. The process of compressing the data
  5. The process of eliminating invalid data before it is introduced into the data warehouse

4. Which of the following would not be considered as a variable affecting the design of an OLAP system?

Answers:

  1. Query demand
  2. Source of data
  3. Number of dimensions
  4. Atomic data volume
  5. Data volatility

5. A slice is:

Answers:

  1. A subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions not in the subset
  2. A subset of a multi-dimensional array corresponding to multiple values for one or more members of the dimensions not in the subset
  3. A subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions in the subset
  4. A subset of a multi-dimensional array corresponding to multiple values for one or more members of the dimensions in the subset
  5. A subset of a multi-dimensional array not corresponding to a single value for one member of the dimensions not in the subset

6. Which of the following techniques can be used to improve query performance?

Answers:

  1. Denormalization
  2. Partitioning
  3. Summarization
  4. Denormalization and Partitioning
  5. Denormalization, Partitioning and Summarization

7. The term OLAP was coined by:

Answers:

  1. Date
  2. Codd
  3. IBM
  4. Oracle
  5. Microsoft

8. The main objects used by OLAP programs are:

Answers:

  1. Multidimensional cubes
  2. Metadata
  3. RDBMS tables
  4. Fact tables
  5. Pivot tables

9. Granularity refers to the:

Answers:

  1. Validity of the data stored in a data warehouse
  2. The level of detail of the facts stored in a data warehouse
  3. The timeliness of the data stored in a data warehouse
  4. The redundancy of the data stored in a data warehouse
  5. Compactness of the data stored in a data warehouse

10. Which of the following queries would be correlated with a Data warehouse?

Answers:

  1. What is the current account balance of this customer?
  2. How many customers have not paid their balances on time?
  3. What is the total number of customers in the middle region?
  4. Which product line sells best in middle region and how does this correlate to demographic data?
  5. Which customer makes the maximum purchases?

11. Data Volatility describes:

Answers:

  1. The degree to which data and data structures change over time
  2. The redundancy of the data
  3. The volume of the data
  4. The compactness of the data
  5. The validity of the data

12. A data warehouse is a “subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision-making process”. The term non-volatile means that:

Answers:

  1. The data is refreshed often
  2. The data is backed up often
  3. The data is deleted often
  4. The data is rarely changed
  5. The data is of low volume

13. Which of the following is not true regarding an OLTP system?

Answers:

  1. OLTP is generally regarded as unsuitable for data warehousing
  2. OLTP systems can be repositories of facts and historical data for business analysis
  3. The purpose of an OLTP system is to run day-to-day operations
  4. The Data Model of an OLTP system is normalised
  5. OLTP offers large amounts of raw data

14. A data warehouse includes data from various sources including legacy systems. Legacy systems implies:

Answers:

  1. Systems that have been developed at different times by different people for a variety of purposes
  2. Systems which are no longer useful
  3. Systems whose data is outdated
  4. Systems whose technology is outdated
  5. Systems whose data is corrupt

15. A multi-dimensional data set is sparse if:

Answers:

  1. The data to be analysed is less in volume
  2. If a relatively high percentage of the possible combinations (intersections) of the members from the data set’s dimensions contain missing data
  3. If a relatively high percentage of the possible combinations (intersections) of the members from the data set’s dimensions contain invalid data
  4. If a relatively high percentage of the possible combinations (intersections) of the members from the data set’s dimensions contain valid data
  5. If a relatively high percentage of the possible combinations (intersections) of the members from the data set’s dimensions contain outdated data

16. A data warehouse is a “subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision-making process”. The data within the warehouse is integrated in that:

Answers:

  1. Users from all departments help to create the database
  2. Data from various departments is collected into the warehouse
  3. The final product is a fusion of various legacy system information into a cohesive set of information
  4. Every user has access to the data in the warehouse
  5. It contains the data of the enterprise in its entirety

17. Normalization applied to the dimension tables of a star schema is known as:

Answers:

  1. Snowflaking
  2. Synchronization
  3. Slicing and Dicing
  4. Replication
  5. Data transformation

18. Replication refers to the:

Answers:

  1. Physical copying of data from one database to another
  2. Cleansing of the data
  3. Integration of data from various sources into the data warehouse
  4. Analysis of the data
  5. Recovery of data

19. ROLAP stands for:

Answers:

  1. Recyclic On-line Analytical Processing
  2. Relational On-line Analytical Processing
  3. Reduced On-line Analytical Processing
  4. Rotated On-line Analytical Processing
  5. Redundant On-line Analytical Processing

20. In a star schema, a table which contains data about one of the dimensions is called a:

Answers:

  1. Fact table
  2. Meta table
  3. Data Dictionary
  4. Pivot table
  5. Dimension table

21. Which of the following would not be an application of Data Mining in the banking field?

Answers:

  1. Detect patterns of fraudulent credit card use
  2. Ascertaining the number of transactions made in a day
  3. Determine credit card spending by customer groups
  4. Find hidden correlation between different financial indicators
  5. Predict the customers likely to change their credit card affiliation

22. A Star Schema is a database design that consists of:

Answers:

  1. A fact table
  2. Dimension tables
  3. Pivot tables
  4. A fact and pivot tables
  5. A fact table and one or more dimension tables

23. HOLAP stands for:

Answers:

  1. Hierarchical On-line Analytical Processing
  2. Hybrid On-line Analytical Processing
  3. Horizontal On-line Analytical Processing
  4. Hyper On-line Analytical Processing
  5. HyperCube On-line Analytical Processing

24. A structure that stores multi-dimensional information, having one cell for each possible combination of dimensions is known as:

Answers:

  1. Table
  2. Section
  3. Partition
  4. Cube
  5. Repository

25. Which Data Mining function/technique is used to analyse a collection of records over a period of time?

Answers:

  1. Classification
  2. Associations
  3. Sequential/Temporal patterns
  4. Clustering
  5. Segmentation

26. Which technique of Data Mining involves developing mathematical structures with the ability to learn?

Answers:

  1. Clustering and Segmentation
  2. Neural Networks
  3. Fuzzy Logic
  4. Linear Regression Analysis
  5. Rule based Analysis

27. A datawarehouse should be able to implement advanced query functionality. This means :

Answers:

  1. The RDBMS must provide a complete set of analytic operations including core sequential and statistical operations
  2. The RDBMS must not have any architectural limitations
  3. The RDBMS server must support hundreds, even thousands, of concurrent users while maintaining acceptable query performance
  4. Query performance must not be dependent on the size of the database, but rather on the complexity of the query
  5. The warehouse must ensure local consistency, global consistency, and referential integrity

28. The modification of data as it is moved into the data warehouse is:

Answers:

  1. Data Transformation
  2. Replication
  3. Synchronization
  4. Data migration
  5. Normalization

29. Which of the following statements is incorrect regarding Data Mining?

Answers:

  1. It is the process of turning data into information
  2. It is a collection of many techniques
  3. It is a replacement for OLAP
  4. It is based on machine generated hypothesis
  5. It is used in Decision Support, Prediction, Forecasting and Estimation

30. Which of the following would be the only similarity between a datawarehouse and OLTP system?

Answers:

  1. Purpose
  2. Structure of data
  3. Type of data
  4. Condition of data
  5. Data model

31. Which of the following is not true regarding the process of Data Mining?

Answers:

  1. Software techniques are used for finding patterns and regularities in sets of data
  2. It is the computer that is responsible for finding the patterns by identifying the underlying rules and features in the data
  3. Data mining analysis tends to work from the data up
  4. The best techniques are those developed with an orientation towards small volumes of data
  5. The analysis process starts with a set of data, uses a methodology to develop an optimal representation of the structure of the data, during which time knowledge is acquired

32. Data quality management refers to the fact that:

Answers:

  1. Ad-hoc analysis must not be slowed or inhibited by the performance of the data warehouse RDBMS
  2. The warehouse must ensure local consistency, global consistency, and referential integrity
  3. The RDBMS server must support hundreds, even thousands, of concurrent users while maintaining acceptable query performance
  4. The server must include tools that co-ordinate the movement of subsets of data between warehouses
  5. The RDBMS must provide a complete set of analytic operations including core sequential and statistical operations

33. Which of the following stage is concerned with the extraction of patterns from the data?

Answers:

  1. Selection
  2. Pre-processing
  3. Transformation
  4. Data Mining
  5. Interpretation and Evaluation

34. The Metadata of the data warehouse should at least contain:

Answers:

  1. The structure of the data
  2. The algorithm used for summarisation
  3. The mapping from the operational environment to the data warehouse and the algorithm used for summarisation
  4. The structure of the data and the algorithm used for summarisation
  5. The structure of the data, the algorithm used for summarisation and the mapping from the operational environment to the data warehouse and the algorithm used for summarisation

35. Which of the following features are required by OLAP applications?

Answers:

  1. Multidimensional views of data
  2. Calculation-intensive capabilities
  3. Time intelligence
  4. Multidimensional views of data and Calculation-intensive capabilities
  5. Calculation-intensive capabilities and Time intelligence

36. Data Mining is also known as

Answers:

  1. Data Extraction
  2. Data Cleansing
  3. Data Archiving
  4. Knowledge Discovery in Databases (KDD)
  5. Data Preservation

37. Given the following steps between raw data and extracted knowledge, arrange them in the correct order:

1 Data mining
2 Transformation
3 Selection
4 Pre-processing
5 Interpretation and Evaluation

Answers:

  1. 3,4,2,1,5
  2. 4,3,2,1,5
  3. 4,2,1,3,5
  4. 4,1,2,3,5
  5. 3,4,1,2,5

38. The data warehouse is typically a large database on a high performance SMP system. Here SMP stands for:

Answers:

  1. Symmetric Multi-Processing
  2. Superior Multi-Processing
  3. Systematic Massive Processing
  4. Symmetric Massive Processing
  5. Systematic Multi-Processing

39. The main impetus behind data warehousing was:

Answers:

  1. To discover means to reduce the data volumes
  2. To make OLTP systems work faster
  3. To reduce human interaction with database systems
  4. To access corporate knowledge repositories based on huge databases to make sound business decisions
  5. To standardise the database products used

40. SQL stands for:

Answers:

  1. Structured Query Language
  2. Systematic Query Language
  3. Structured Query Logic
  4. Structured Queuing Logic
  5. Standard Query Logic

41. Changing the view of the data to a higher level of aggregation is known as:

Answers:

  1. Implosion
  2. Drill down
  3. Drill up
  4. Synchronisation
  5. Summarisation

42. The movement of data from one environment to another is known as:

Answers:

  1. Data Migration
  2. Normalization
  3. Replication
  4. Data Mining
  5. Data Cleansing

43. In which component of the enterprise is the data re-organised for analysis and information extracted from the data?

Answers:

  1. The Data Warehouse
  2. The Data Mart
  3. The Data Mine
  4. The operational RDBMS
  5. Metadata

44. A means of extending the data accessible to the end user beyond that which is stored in the OLAP server is know as :

Answers:

  1. Consolidation
  2. Multi Dimensional Analysis
  3. Drill Down
  4. Navigation
  5. Reach through

45. The logical organisation of data in a database is called:

Answers:

  1. Normalisation
  2. Schema
  3. View
  4. Fact table
  5. Dimension

46. Which of the following type of data is most likely to be stored on some form of mass storage ?

Answers:

  1. Metadata
  2. Highly summarised data
  3. Lightly summarised data
  4. Current detail data
  5. Older detail data

47. The requirement that the datawarehouse RDBMS server must support hundreds and thousands of concurrent users while maintaining an acceptable query performance is known as:

Answers:

  1. Terabyte Scalability
  2. Load Performance
  3. Mass User Scalability
  4. Data Quality Management
  5. Query Performance

48. Metadata does not include:

Answers:

  1. The actual data
  2. A description of tables and fields in the warehouse, including data types and the range of acceptable values
  3. A similar description of tables and fields in the source databases, with a mapping of fields from the source to the warehouse
  4. A description of how the data has been transformed, including formulae, formatting, currency conversion, and time aggregation
  5. Information that is needed to support and manage the operation of the data warehouse

49. The main objective of Data Mining is:

Answers:

  1. The safe storage of data
  2. Elimination of errors from the data
  3. Deleting data that is no longer important to the organization
  4. The extraction of implicit, previously unknown, and potentially useful information from data
  5. To help in the generation of reports for the management

50. In the Discovery model of Data Mining, the emphasis is on which of the following?

Answers:

  1. The system automatically discovering important information hidden in the data
  2. The user who is responsible for formulating the hypothesis and issuing the query on the data to affirm or negate the hypothesis
  3. Volume of the data being examined
  4. Timeliness of the data
  5. Speed with which the data is examined

51. In a star schema, the central table which contains the individual facts being stored in the database is called a:

Answers:

  1. Fact table
  2. Meta table
  3. Data Dictionary
  4. Pivot table
  5. Dimension table

52. Which of the following rules would be considered the central core of OLAP?

Answers:

  1. Multidimensional Conceptual View
  2. Intuitive Data Manipulation
  3. Accessibility
  4. Batch Extraction vs Interpretative
  5. Transparency

53. Changing the view of the data to a greater level of detail is known as:

Answers:

  1. Explosion
  2. Drill down
  3. Drill up
  4. Exploration
  5. Aggregation

54. A multidimensional cube records a set of data derived from:

Answers:

  1. Fact tables
  2. Pivot tables
  3. Dimensions
  4. Fact tables and Dimensions
  5. Fact tables and Pivot tables

55. Which Data Mining technique partitions the database so that each partition or group is similar according to some criteria or metric ?

Answers:

  1. Clustering and Segmentation
  2. Induction
  3. Neural Networks
  4. Data Visualisation
  5. Linear Regression Analysis

56. Which of the following is not associated with data warehousing?

Answers:

  1. Transaction processing
  2. Information retrieval and analysis
  3. Multi-dimensional data model
  4. Query processing
  5. Transformed and summarised data

57. Which of the following is an architecture for OLAP?

Answers:

  1. MOLAP
  2. ROLAP
  3. KOLAP
  4. MOLAP and ROLAP
  5. MOLAP, ROLAP and KOLAP

58. Under OLAP terminology, slice and dice refers to:

Answers:

  1. The user-initiated process of navigating by calling for page displays interactively, through the specification of slices via rotations and drill down/up
  2. Restricting the view of database objects to a specified subset
  3. A means of extending the data accessible to the end user beyond that which is stored in the OLAP server
  4. Computing all of the data relationships for one or more dimensions
  5. Applying calculations to input data at the time the request for that data is made

59. In the Verification model of Data Mining, the emphasis is on which of the following?

Answers:

  1. The system automatically discovering important information hidden in the data
  2. The user who is responsible for formulating the hypothesis and issuing the query on the data to affirm or negate the hypothesis
  3. Volume of the data being examined
  4. Timeliness of the data
  5. Speed with which the data is examined

60. The applications of Data Mining would not include:

Answers:

  1. Discovering buying-patterns for cross selling
  2. Financial market prediction
  3. Discovering errors made during data entry
  4. Discovering which customer is most profitable
  5. Credit assessment

61. A data warehouse is a “subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision-making process”. The data within the warehouse is integrated in such a way that:

Answers:

  1. Users from all departments help to create the database
  2. It contains the data of the enterprise in its entirety
  3. The final product is a fusion of various legacy system information into a cohesive set of information
  4. Every user has access to the data in the warehouse

1,275 total views, 3 views today

Upwork Databases

Data Structures Test 2016

Published by:

1. Which graph traversal algorithm uses a queue to keep track of the vertices which need to be processed?

Answers:

  1. Breadth-first search
  2. Depth-first search

2. A simple graph with n vertices and k components can have at the most _______.

Answers:

  1. n edges
  2. n-k edges
  3. (n-k)(n-k-1)/2 edges
  4. (n-k)(n-k+1)/2 edges

3. What is the minimum number of edges which must be removed from a complete bipartite graph of six nodes K(6) so that the remaining graph is a planar?

Answers:

  1. 2
  2. 3
  3. 4
  4. 6

4. Which feature of heaps allows them to be efficiently implemented using a partially filled array?

Answers:

  1. Heaps are binary search trees
  2. Heaps are complete binary trees
  3. Heaps are full binary trees
  4. Heaps contain only integer data

5. What happens if you make a recursive call without making the problem smaller?

Answers:

  1. The operating system detects the infinite recursion because of the “repeated state”
  2. The program keeps running until you press Ctrl-C
  3. The results are non-deterministic
  4. The run-time stack overflows, halting the program

6. Tree algorithms typically run in time O(d) . What is d?

Answers:

  1. The depth of the tree
  2. The number of divisions at each level
  3. The number of nodes in the tree
  4. The total number of entries in all the nodes of the tree

7. Here is a code for an integer variable n

while (n > 0)
{
n = n/10; // Use integer division
}
What is the worst case scenario analysis for the above loop?

Answers:

  1. O(1)
  2. O(log n)
  3. O(n)
  4. O(n2)

8. Suppose we have a circular array implementation of a queue, with ten items in the queue stored at data[2] through data[11]. The CAPACITY is 42. Where does the push member function place the new entry in the array?

Answers:

  1. data[1]
  2. data[2]
  3. data[11]
  4. data[12]

9. Which of the following sorting algorithms yield approximately the same worst-case and average-case running time behavior in O(n*log(n))?

Answers:

  1. Bubble sort and selection sort
  2. Heap sort and merge sort
  3. Quick sort and radix sort
  4. Tree sort and Median-of-3 quicksort

10. The operation for adding an entry to a stack is traditionally called ________.

Answers:

  1. add
  2. append
  3. insert
  4. push

11. For a complete binary tree with depth d, the total number of nodes is:

Answers:

  1. 2d+1
  2. 2d
  3. 2d-1
  4. 2d2

12. Which of the following is false?

Answers:

  1. A binary search begins with the middle element in the array
  2. A binary search continues halving the array either until a match is found or until there are no more elements to search
  3. If the search argument is greater than the value located in the middle of the binary, the binary search continues in the lower half of the array

13. Which of the following applications may use a stack?

Answers:

  1. A parentheses balancing program
  2. Keeping track of local variables at run time
  3. Syntax analyzer for a compiler
  4. All of the above

14. What is the value of the post-fix expression 6 3 2 4 + – *?

Answers:

  1. Something between -15 and -100
  2. Something between -5 and -15
  3. Something between 5 and 15
  4. Something between 15 and 100

15. The minimum number of interchanges needed to convert the array 89,19,14,40,17,12,10,2,5,7,11,6,9,70 into a heap with the maximum element at the root is:

Answers:

  1. 0
  2. 1
  3. 2
  4. 3

16. Suppose T is a complete binary tree with 14 nodes. What would be the minimum possible depth of T?

Answers:

  1. 0
  2. 3
  3. 4
  4. 5

17. In which data structure do the insertion and deletion take place at the same end?

Answers:

  1. Linked list
  2. Tree
  3. Stack
  4. Linked list of stack

18. What is the formulae to find maximum number of nodes n in a perfect binary tree?

Answers:

  1. 2h + 1 – 1
  2. 2h + 1
  3. 2h
  4. 2h + 1 + 1

19. A chained hash table has an array size of 512. What is the maximum number of entries that can be placed in the table?

Answers:

  1. 511
  2. 512
  3. 1024
  4. There is no maximum limit

20. In which dynamically created linked list can the first node be recovered after moving to the second node?

Answers:

  1. Simple linked list
  2. Circular linked list
  3. Doubly linked list
  4. Both b and c

21. What is the best definition of a collision in a hash table?

Answers:

  1. Two entries are identical except for their keys
  2. Two entries with different data have exactly the same key
  3. Two entries with different keys have exactly the same hash value
  4. Two entries with exactly the same key have different hash values

22. What is the pre-order traversal equivalent of the following algebraic expression?

[a+(b-c)]*[(d-e)/(f+g-h)]

Answers:

  1. abc-+de-fg+h-/*
  2. *+a-bc/-de-+fgh
  3. a+*b-/c-d-e+fgh
  4. *+a-bc-/d+e-fgh

23. A sparse matrix can be a lower-triangular matrix when____.

Answers:

  1. all the non-zero elements lie only on the leading diagonal
  2. all the non-zero elements lie above the leading diagonal
  3. all the non-zero elements lie below the leading diagonal
  4. None of the above

24. A graph in which all nodes are of an equal degree is known as:

Answers:

  1. Multigraph
  2. Non – regular graph
  3. Regular graph
  4. Complete graph

25. What is the maximum number of statements that may be recursive calls in a single function declaration?

Answers:

  1. 1
  2. 2
  3. n (n is the argument)
  4. There is no fixed maximum

26. Which additional requirement is placed on an array so that binary search may be used to locate an entry?

Answers:

  1. The array elements must form a heap
  2. The array must have at least 2 entries
  3. The array must be sorted
  4. The array’s size must be a power of two

27. What is the worst-case scenario for heapsort to sort an array of n elements?

Answers:

  1. O(log n)
  2. O(n)
  3. O(n log n)
  4. O(n2)

28. The recurrence relation T(n)=mT(n/2)+an2 is satisfied by___

Answers:

  1. T(n)=O(nm)
  2. T(n)=O(m*log(m))
  3. T(n)=O(n*log(m))
  4. T(n)=O(m*log(n))

29. If ‘data’ is a circular array of CAPACITY elements and ‘last’ is an index in that array, what is the formula for the index after ‘last’?

Answers:

  1. (last % 1) + CAPACITY
  2. last % (1 + CAPACITY)
  3. (last + 1) % CAPACITY
  4. last + (1 % CAPACITY)

30. Consider the node of a complete binary tree whose value is stored in data[i] for an array implementation. If this node has a right child, where will the right child’s value be stored (the array’s first index is 0)?

Answers:

  1. data[i+1]
  2. data[i+2]
  3. data[2*i + 1]
  4. data[2*i + 2]

31. In a complete binary tree, the parent of any node k can be determined by ________.

Answers:

  1. 2k
  2. 2k+1
  3. K/2
  4. 2K-1

32. Consider a linked list of n elements which is pointed by an external pointer. What is the time taken to delete the element which is a successor of the pointed element by a given pointer?

Answers:

  1. O(1)
  2. O(log2n)
  3. O(n)
  4. O(n*log2n)

33. Suppose X is a B-tree leaf containing 41 entries and has at least one sibling. Which of the statements would be true in this case?

Answers:

  1. Any sibling of X is also a leaf
  2. Any sibling of X contains at least 41 entries
  3. The parent of X has exactly 42 entries
  4. X has at least 41 siblings

34. In a complete binary tree of n nodes, how far are the most distant two nodes? Assume each in the path counts 1. Assume log(n) is log base 2.

Answers:

  1. about log(n)
  2. about 2*log(n)
  3. about 3*log(n)
  4. about 4*log(n)

35. In a graph G, F is a spanning forest of G if
(i)F is a subgraph of G containing all the nodes of G
(ii)F is an order forest containing trees T1,T2,…Tn
(iii)Ti contains all the nodes that are reachable in G from the root Ti and are contained in Tj for some j<i..

Which of the above conditions is/are true?

Answers:

  1. (i),(ii)
  2. (ii),(iii)
  3. (i),(iii)
  4. (i),(ii) and (iii)

36. Which information is not saved in the activation record when a function call is executed?

Answers:

  1. Current depth of recursion
  2. Formal parameters
  3. Location where the function should return when done
  4. Local variables

37. The linked list implementation of sparse matrices is superior to the generalized dope vector method because it is __________.

Answers:

  1. conceptually easier and completely dynamic
  2. efficient if the sparse matrix is a band matrix
  3. efficient in accessing an entry
  4. all of these

38. Which situation occurs frequently if the selected hash function is poor?

Answers:

  1. Overflow
  2. Underflow
  3. Collision
  4. None of the above

39. The post-order traversal of a binary tree starts with:

Answers:

  1. Post-order traversal of the left sub tree
  2. Post-order traversal of the right sub tree
  3. Post-order traversal of the root
  4. Post-order traversal of the lowest node

40. One difference between a queue and a stack is:

Answers:

  1. Queues require dynamic memory but stacks do not
  2. Stacks require dynamic memory but queues do not
  3. Queues use two ends of the structure but stacks use only one
  4. Stacks use two ends of the structure but queues use only one

41. What is the minimum number of nodes in a full binary tree with depth 3?

Answers:

  1. 4
  2. 8
  3. 11
  4. 15

42. Using which traversal in a sorted binary insertion tree can a sorted array of numbers be obtained?

Answers:

  1. Pre-order traversal
  2. Post-order traversal
  3. In order traversal
  4. Top-down traversal

43. Where does the push member function place the new entry on the linked list in the linked list implementation of a queue?

Answers:

  1. At the head
  2. At the tail
  3. After all other entries that are greater than the new entry
  4. After all other entries that are smaller than the new entry

44. Which term is used to describe an O(n) algorithm?

Answers:

  1. Constant
  2. Linear
  3. Logarithmic
  4. Quadratic

45. What is the minimum number of nodes in a complete binary tree with depth 3?

Answers:

  1. 4
  2. 8
  3. 11
  4. 15

46. What is true of the complete bipartite graphs K(3,3) and K(2,4)?

Answers:

  1. Both are planar
  2. Neither is a planar
  3. Both are isomorphic
  4. None of these

47. If X is the adjacency matrix of a graph G with no self loops, the entries along the principle diagonal of X are ______.

Answers:

  1. all zeros
  2. all ones
  3. both zeros and ones
  4. different

48. Consider a linked list implementation of a queue with two pointers: front and rear. The time needed to insert element in a queue of length n is:

Answers:

  1. O(1)
  2. O(log2n)
  3. O(n)
  4. O(n*log2n)

49. What is the worst-case scenario for mergesort to sort an array of n elements?

Answers:

  1. O(log n)
  2. O(n)
  3. O(n log n)
  4. O(n2)

50. Consider a hashing function that resolves collision by quadratic probing. Assume that the address space is indexed from 1 to 8. If a collision occurs at position 4, the location which will never be probed is:

Answers:

  1. 4
  2. 5
  3. 8
  4. 2

51. In which data structure is the concept of rotation used?

Answers:

  1. Binary search tree
  2. Circular queue
  3. AVL tree
  4. Circular linked list

52. You have implemented a queue with a circular array keeping track of the first, the last, and the count (the number of items in the array). Suppose the first is zero, and the last is CAPACITY-1, what can you say about the count?

Answers:

  1. The count must be zero
  2. The count must be CAPACITY
  3. The count can be zero or CAPACITY, but no other value can occur
  4. None of the above

53. A procedure that calls itself in a program is called _______.

Answers:

  1. Repeat
  2. Loop
  3. Recursion
  4. Tree

54. State whether True or False.

For all possible inputs, a linear algorithm to solve a problem must perform faster than a quadratic algorithm to solve the same problem.

Answers:

  1. True
  2. False

55. If a max heap is implemented using a partially filled array called data, and the array contains n elements (n > 0), where is the entry with the greatest value?

Answers:

  1. data[0]
  2. data[n-1]
  3. data[n]
  4. data[2*n + 1]

56. What is the worst-case scenario for quicksort to sort an array of n elements?

Answers:

  1. O(log n)
  2. O(n)
  3. O(n log n)
  4. O(n2)

57. Which of the following lines of the code will delete two successive nodes of a single linked linear list(with more than two nodes)? Here ‘LINK[X]’ denotes the address field of node X.

Answers:

  1. LINK[X]:=LINK[LINK[X]]
  2. X:=LINK[LINK[X]]
  3. LINK[LINK[X]]:=X
  4. LINK[X]:=LINK[LINK[LINK[X]]]

58. A non- planar graph with the minimum number of vertices has:

Answers:

  1. 10 edges, 5 vertices
  2. 9 edges, 6 vertices
  3. 6 edges, 4 vertices
  4. 9 edges, 5 vertices

59. A circuit which is a connected graph and which includes every vertex of the graph is known as_____.

Answers:

  1. Euler
  2. Unicursal
  3. Hamiltonian
  4. Clique

60. A one dimensional array A has indices 1…75. Each element is a string and takes up three memory words. The array is stored starting at location 1120 decimal. The starting address of A[49] is:

Answers:

  1. 1267
  2. 1164
  3. 1264
  4. 1169

61. A simple graph in which there exists an edge between every pair of vertices is called a/an _________.

Answers:

  1. incomplete graph
  2. complete graph
  3. Euler graph
  4. planner graph

62. Let A be a sorted array of n=10 elements. Assume that only one comparison is required to determine whether the target is equal to, less than, or greater than A[i]. Which of the following denotes the average successful time of finding an arbitrary element x in A using the binary search?

Answers:

  1. 1.6
  2. 2.9
  3. 4.2
  4. 5.5

63. Which operations require linear time for their worst-case behavior in the linked-list version of a queue?

Answers:

  1. front
  2. push
  3. empty
  4. None of these operations require linear time

64. Which of the following statements about binary trees is false?

Answers:

  1. Every Node must have at least two children
  2. Every non-empty tree has exactly one root node
  3. Every node has at the most two children
  4. None of the above

65. How many real links are required for a sparse matrix having 10 rows, 10 columns and 15 non-zero elements? (Pick the nearest answer)

Answers:

  1. 15
  2. 20
  3. 50
  4. 100

66. In a graph G having the cut set matrix C(G) and an incidence matrix A(G), the rank of C(G) would be____

Answers:

  1. The same as that of A(G)
  2. More than that of A(G)
  3. Less than that of A(G)
  4. Independent of the rank of A(G)

67. Which of the operations is simpler in the doubly linked list than it is in the simple linked list?

Answers:

  1. Insertion
  2. Deletion
  3. Both a and b
  4. None of the above

68. Which of the following formulae in big-Oh notation best represents the expression n2+35n+6?

Answers:

  1. O(n3)
  2. O(n2)
  3. O(n)
  4. O(42)

69. The operation for removing an entry from a stack is traditionally called _______.

Answers:

  1. delete
  2. peek
  3. pop
  4. remove

70. Which queue allows insertion and deletion at both ends?

Answers:

  1. Simple queue
  2. Circular queue
  3. Dequeue
  4. Special queue

71. What kind of initialization needs to be done for a chained hash table?

Answers:

  1. None
  2. The key at each array location must be initialized
  3. The head pointer of each chain must be set to NULL
  4. Both B and C must be carried out

72. The number of nodes in the largest maximal independent set of the complete bipartite graph K(4,2) is_____ .

Answers:

  1. 2
  2. 3
  3. 4
  4. 6

73. Which of the following techniques is used to resolve collision in hashing?

Answers:

  1. Separate chaining
  2. Open addressing
  3. Linear probing
  4. All of the above

74. You have implemented a queue with a linked list keeping track of a front pointer and a rear pointer. Which of these pointers will you change during an insertion into a NONEMPTY queue?

Answers:

  1. Neither of them changes
  2. Only front_ptr changes
  3. Only rear_ptr changes
  4. Both change

75. Which of the following operations is performed more efficiently by the doubly linked list than by the linear linked list?

Answers:

  1. Deleting a node the location of which is given
  2. Searching an unsorted list for a given item
  3. Inserting a node after the node with a given location
  4. Traversing the list to process each node

76. Four characters are placed in a queue in the following order: D, C, B, and A. If they are removed one at a time, what will be the order of their removal?

Answers:

  1. ABCD
  2. ABDC
  3. DCAB
  4. DCBA

77. What is the meaning of the statement: “Entries in a stack are ‘ordered'”?

Answers:

  1. A collection of stacks can be sorted
  2. Stack entries may be compared with the ‘<‘ operation
  3. The entries must be stored in a linked list
  4. There is a first entry, a second entry, and so on

78. A given connected graph G is a Euler graph if and only if all vertices of G are of ______.

Answers:

  1. the same degree
  2. even degrees
  3. odd degrees
  4. different degrees

79. Which of the following data structures has a balanced condition?

Answers:

  1. AVL Tree
  2. Doubly Linked List
  3. Double Ended Queue
  4. Stack

80. A matrix is called sparse when______

Answers:

  1. most of its elements are non-zero
  2. most of its elements are zero
  3. all of its elements are non-zero
  4. None of the above

81. Which of the following operations in the simple linked list will modify the beginning of the linked list?

Answers:

  1. Deletion of the first node
  2. Insertion after the last node
  3. Insertion after the first node
  4. None of the above

82. You have implemented a queue with a circular array keeping track of the first item, the last item, and the count (the number of items in the array). Suppose the address of the first is zero, and that of the last is CAPACITY-1, what can you say about the count?

Answers:

  1. The count must be zero
  2. The count must be CAPACITY
  3. The count can be zero or CAPACITY, but no other value can occur
  4. None of the above

83. What is the worst-case scenario for the binary search for finding a single item in an array?

Answers:

  1. Constant time
  2. Logarithmic time
  3. Linear time
  4. Quadratic time

84. A binary tree, all the levels of which except possibly the last have the maximum number of nodes and all the nodes at the last level appear as far left as possible, is known as:

Answers:

  1. Full binary tree
  2. 2-Tree
  3. Threaded tree
  4. Complete binary tree

85. What is the pre-order traversal equivalent of the following algebraic expression?

[a+(b-c)]*[(d-e)/(f+(g-h))]

Answers:

  1. abc-+de-fg+h-/*
  2. *+a-bc/-de+f-gh
  3. a+*b-/c-d-e+fgh
  4. *+a-bc-/d+e-fgh

86. In the linked representation of a sparse matrix, the head node for a column list stores_____

Answers:

  1. a pointer to the next column head node
  2. a pointer to the first node in the column list
  3. column number
  4. All of the above

87. The hashing function which dynamically adapts to changes in the table being accessed is called ________.

Answers:

  1. Real
  2. Linear
  3. Partial
  4. Virtual

88. What will happen if in data structure a pop operation on the stack causes the stack pointer to move past the origin of the stack?

Answers:

  1. Overflow
  2. Underflow
  3. Null
  4. Garbage collection

89. If h is the depth of the tree, which formula will be used to find the maximum number of nodes n in a perfect binary tree?

Answers:

  1. 2h + 1 – 1
  2. 2h + 1
  3. 2h
  4. 2h + 1 + 1

90. What is the minimum number of edges and vertices possible in a non- planar graph?

Answers:

  1. 10 edges, 5 vertices
  2. 9 edges, 6 vertices
  3. 6 edges, 4 vertices
  4. 9 edges, 5 vertices

91. Which of the following is the worst-case scenario for operations on heaps?

Answers:

  1. Neither insertion nor removal is better than linear
  2. Insertion is better than linear, but removal is not
  3. Removal is better than linear, but insertion is not
  4. Both insertion and removal are better than linear

92. Which of these are standard operations of Stack Data Structure?

Answers:

  1. Push, delete
  2. Insert, pop
  3. Put, extract
  4. Push, pop

93. Suppose we have a circular array implementation of a queue, with ten items in the queue stored at data[2] through data[11]. The CAPACITY is 42. Where does the enqueue member function place the new entry in the array?

Answers:

  1. data[1]
  2. data[2]
  3. data[11]
  4. data[12]

94. A binary search tree is generated by inserting the following integers in the order: 50,15,62,5,20,58,91,3,8,37,60,24. How many nodes are in the left and right subtrees, respectively?

Answers:

  1. (4,7)
  2. (7,4)
  3. (8,3)
  4. (3,8)

95. What is the worst-case scenario for the binary search for finding a single item in an sorted array?

Answers:

  1. Constant time
  2. Logarithmic time
  3. Linear time
  4. Quadratic time

96. What is the maximum depth of recursive calls a function may make?

Answers:

  1. 1
  2. 2
  3. n (where n is the argument)
  4. There is no fixed maximum

97. Consider this binary search tree.

Which will be the new root if you remove the root and replace it with something from the left subtree?

Answers:

  1. 1
  2. 2
  3. 4
  4. 5

98. The number of distinct simple graphs with up to three nodes is _______.

Answers:

  1. 15
  2. 10
  3. 7
  4. 9

99. In a selection sort algorithm, the number of passes required to perform the sort are ______.

Answers:

  1. N
  2. N-1
  3. N-2
  4. N^2

1,884 total views, 5 views today