UPWORK TEST » October 2, 2015

Daily Archives: October 2, 2015

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

4,008 total views, 19 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,191 total views, 3 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,075 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,136 total views, 1 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,827 total views, 3 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,083 total views, 11 views today