Search for:
Discuss SQL Certificate

Discuss SQL Certificate &-8211; this Article or News was published on this date:2019-05-13 11:20:31 kindly share it with friends if you find it helpful

Discuss SQL Certificate


Advertisements


Oracle&-8217;s Oracle Database 11g: SQL Fundamentals exam is part of the Oracle Certified Oracle Database 11g Administrator track, combining training, experience, and testing to endorse candidates with a strong foundation and expertise in the industry’s most advanced database management system.

This certification is to put you on the short list for winning Oracle SQL-Based projects. An Oracle Technical Certification is a valuable, industry-recognized credential that signifies a proven level of knowledge and skill.




Advertisements

SQL &-8211; Introduction

SQL &-8211; SQL Exams Syllabus

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL Certificate &-8211; Mock Exams

SQL Certificate &-8211; Useful Resources

SQL Certificate &-8211; Discussion

UPSC IAS Exams Notes

Developer&-8217;s Best Practices

Questions and Answers

Effective Resume Writing

HR Interview Questions

Computer Glossary

Who is Who

SQL Certificate Mock Exams

SQL Certificate Mock Exams &-8211; this Article or News was published on this date:2019-05-13 11:20:30 kindly share it with friends if you find it helpful

SQL Certificate Mock Exams


Advertisements


1. What will be the outcome of the following query?

SELECT ROUND(144.23,-1) FROM dual;
  1. 140
  2. 144
  3. 150
  4. 100

2.In which of the following cases, parenthesis should be specified?

  1. When INTERSECT is used with other set operators
  2. When UNION is used with UNION ALL
  3. When MINUS is used for the queries
  4. None of the above

3. Which of the following are DML commands in Oracle Database?

  1. SELECT
  2. GROUP BY
  3. INTERSECT
  4. INSERT

4. Write a query to display employee details (Name, Department, Salary and Job) from EMP table.

  1. SELECT ename, deptno, sal, job FROM emp;
  2. SELECT * FROM emp;
  3. SELECT DISTINCT ename, deptno, sal, job FROM emp;
  4. SELECT ename, deptno, sal FROM emp;

5.What among the following are different types of Views?

  1. Simple views
  2. Complex views
  3. Both A and B
  4. None of the above

6.What is true about the SET operators?

  1. The SELECT clause should have the same number of columns, data types can be different
  2. The SET operators can be used only for combining two queries
  3. The data type of each column in the 2nd query must match the data type of its corresponding column in the first query.
  4. None of the above

7.Which of the following multi-row operators can be used with a sub-query?

  1. IN
  2. ANY
  3. ALL
  4. All of the above

8. When a table can be created?

  1. When the database is not being used by any user
  2. When the database is newly created
  3. It can be created any time, even when a user is using the database
  4. None of the above

9. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

  1. Using SELECT clause
  2. Manually typing each value into the INSERT clause
  3. Using SET operators
  4. None of the above

10. What among the following is true about a View?

  1. Sub-queries can be embedded in a CREATE VIEW statement
  2. A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax
  3. You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement
  4. None of the above

11. Predict the output when below statement is executed in SQL* Plus?

DESC emp
  1. Raises error &-8220;SP2-0042: unknown command &-8220;desc emp&-8221; &-8211; rest of line ignored.&-8221;
  2. Lists the columns of EMP table
  3. Lists the EMP table columns, their data type and nullity
  4. Lists the columns of EMP table along with their data types

12. What will be the outcome of the query given below?

SELECT 100+NULL+999 FROM dual;
  1. 100
  2. 999
  3. NULL
  4. 1099

13. With respect to the given query, if the JOIN used is replaced with NATURAL JOIN, it throws an error. What is the reason for this error?

  1. When the NATURAL JOIN is used, a WHERE clause is mandatory, omitting which gives an error
  2. The ON clause should be replaced with the USING clause
  3. The words NATURAL, JOIN and USING are mutually exclusively in the context of the same join clause
  4. A query can&-8217;t combine the NATURAL JOIN and ON (or USING) clauses while joining.

14.Which of the following syntax models is used in extensively in the software systems worldwide?

  1. ANSI SQL: 1999
  2. Both traditional Oracle syntax and the ANSI SQL: 1999 syntax
  3. Traditional Oracle syntax
  4. All of the options

15.What is true about co-related sub-queries?

  1. The tables used in the main query are also used in a co-related sub-query
  2. The sub-queries which reference a column used in the main query are called co-related sub-queries
  3. The sub-queries which are written without parenthesis are called co-related sub-queries
  4. The sub-queries which mandatorily use different tables than those used in the main query are called co-related sub-queries

16. You issue an UPDATE statement as follows:

UPDATE employees 
SET employee_id   = NULL;
WHERE job_id  = 'CLERK';

What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint)

  1. The first column of the data set will get updated to NULL
  2. The 3rd column of the first row will get updated to NULL
  3. The 3rd column of all the rows will get updated to NULL
  4. And ORA error will be thrown

17.What is true with respect to the query given above?

  1. It gives an ORA error as the mandatory WHERE clause is not present
  2. The JOIN..ON clause can&-8217;t contain more than one condition
  3. The query ignores the last condition and executes without an ORA error
  4. The JOIN..ON clause can be written in the form given above for putting more conditions.

18. Consider the following query.

SELECT e.job_id , e.first_name, d.department_id 
FROM departments D JOIN employees e JOIN BONUS b
USING (job_id );

This query results in an error. What is the reason of the error?

  1. A JOINOUSING can happen only between two tables at a time
  2. USING clause in the query doesn&-8217;t have any column from the department
  3. There is no WHERE clause in the query
  4. None of the above

19. Predict the output of the below query

SELECT 50 || 0001
FROM dual
  1. 500001
  2. 51
  3. 501
  4. 5001

20. You create a table and name it as COUNT. What will be the outcome of CREATE TABLE script?

  1. The table will not be created
  2. The table will be created and an underscore will be added automatically to the name COUNT_
  3. An ORA error will be thrown
  4. The table COUNT will be created without any errors

21. What will be the outcome of the following query?

SELECT *
FROM employees
WHERE salary BETWEEN (SELECT max(salary)
			FROM employees
			WHERE department_id  = 100) 
AND (SELECT min(salary) FROM employees where department_id  = 100); 

This query returns an error. What is the reason for the error?

  1. A GROUP BY clause should be used as the function MAX is used
  2. Both the sub-queries cannot use the same department ID in the same outer query
  3. BETWEEN operator cannot be used with a sub-query
  4. SELECT clause should have columns mentioned and not a asterix (*)

22. Which of the following is not a property of functions?

  1. Perform calculations on data
  2. Convert column data types
  3. Modify individual data items
  4. None of the above

23.What is true with respect to INNER JOINS and OUTER JOINS in Oracle DB?

  1. INNER JOIN returns only the rows that are matched
  2. OUTER JOIN returns only the rows that are not matched
  3. OUTER JOIN returns the rows that are matched as well as those which do not match
  4. None of the above

24. Which of the following can create a view even if the base table(s) does not exist?

  1. NOFORCE
  2. FORCE
  3. OR REPLACE
  4. CREATE VIEW

25. Which of the following ANSI SQL: 1999 join syntax joins are supported by Oracle?

  1. Cartesian products
  2. Natural joins
  3. Full OUTER join
  4. Equijoins

26. What among the following are the pre-requisites for creating a table?

  1. CREATE TABLE privilege
  2. Storage space
  3. Data in the table
  4. None of the above

27. What is the syntax for creating a table?

  1. CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,..] );
  2. CREATE TABLE INTO [schema.] table (column datatype [DEFAULT expr] [,..] );
  3. CREATE TABLE VALUES [schema.] table (column datatype [DEFAULT expr] [,..] );
  4. None of the above

28.You need to display all the non-matching rows from the EMPLOYEES table and the non-matching rows from the DEPARTMENT table without giving a Cartesian product of rows between them. Which of the following queries will give the desired output?

  1. SELECT *
    FROM employees e, department d
    WHERE e.department_id  = d.department_id ; 
    
  2. SELECT *
    FROM employees e NATURAL JOIN department d; 
    
  3. SELECT *
    FROM employees e FULL OUTER JOIN department d
    ON  e.department_id  = d.department_id ; 
    
  4. SELECT *
    FROM employees e JOIN  department d
    ON ( e.department_id  > d.department_id ) ; 

29. Which of the below alphanumeric characters are used to signify concatenation operator in SQL?

  1. +
  2. ||
  3. &-8211;
  4. ::

30.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?

  1. The order of usage of the SET operators can be changed to change the precedence
  2. The equal precedence cannot be changed
  3. Parenthesis can be used to change the precedence
  4. None of the above

31.What will be displayed in the result of this query?

  1. It will display distinct department id(s) contained jointly in EMPLOYEES and DEPARTMENTS table
  2. It will throw ORA error
  3. No rows selected
  4. None of the above

32. Which of the following commands ensures that no DML operations can be performed on a view?

  1. NOFORCE
  2. FORCE
  3. WITH READ ONLY
  4. OR REPLACE

33. What is true about the NOFORCE option in CREATE VIEW statement?

  1. It creates a view even if the base table(s) does not exist.
  2. It creates a view only if the base table(s) exists.
  3. It is the default while creating a view.
  4. None of the above

34. What is true about the OR REPLACE keyword?

  1. Object privileges are lost when a view is created using this keyword
  2. There is no need of re granting the object privileges previously granted on it
  3. Neither of A nor B
  4. None of the above

35. What among the following is a type of Oracle SQL functions?

  1. Multiple-row functions
  2. Single column functions
  3. Single value functions
  4. Multiple columns functions

36. What among the following is a type of single-row function?

  1. VARCHAR2
  2. Character
  3. LONG
  4. NULLIF

37. What is the most appropriate about Multiple Row Functions?

  1. They return multiple values per each row. 
  2. They return one result per group of rows and can manipulate groups of rows. 
  3. They return one result per row and can manipulate groups of rows. 
  4. They return multiple values per a group of row.

38. Which of the following are also called Group functions?

  1. Single row functions
  2. Multi group functions
  3. Multiple row functions
  4. Single group functions.

39. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (*) FROM t_count;
  1. 12
  2. 6
  3. 9
  4. Throws exception because COUNT function doesn&-8217;t works with NULL values

40. Pick the element which you must specify while creating a table.

  1. Column name
  2. Column Data type
  3. Column size
  4. All of the above

41. What can be said about the statement given above?

  1. Alternative names have been given for the view
  2. Giving alternative names is mandatory if any column is derived from a function or an expression
  3. Both A and B
  4. None of the above

42. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (num) FROM t_count;
  1. 12
  2. 6
  3. 9
  4. Throws exception because COUNT function doesn&-8217;t works with NULL values

43. You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?

  1. ON (e.department_id = d.department_id ) should be added
  2. USING (e.department_id ) should be added
  3. WHERE e.department_id in (100,101) should be added
  4. None of the above

44. Which of the following is NOT a GROUP BY extensions in SQL?

  1. GROUP BY
  2. GROUPING SETS
  3. CUBE
  4. ROLLUP

45. What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name; 
  1. It will be no different than the original statement
  2. It will execute successfully giving the same results but change in alias names.
  3. It will throw an ORA error
  4. None of the above

46. What among the following is true about the DELETE statement?

  1. The DELETE statement has to be accompanied by the WHERE clause
  2. It is not mandatory to write a WHERE clause with the DELETE statement
  3. DELETE can remove data from multiple tables at a time
  4. None of the above

47. Assuming the last names of the employees are in a proper case in the table employees, what will be the outcome of the following query?

SELECT employee_id, last_name, department_id  FROM employees WHERE last_name = 'smith';
  1. It will display the details of the employee with the last name as Smith
  2. It will give no result.
  3. It will give the details for the employee having the last name as &-8216;Smith&-8217; in all Lower case.
  4. It will give the details for the employee having the last name as &-8216;Smith&-8217; in all INITCAP case.

48.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer)

  1. A prompt pops up asking the user whether he/she is sure of deleting the rows requested
  2. The rows obeying the condition given in the DELETE statement are removed immediately
  3. The requested rows are removed immediately without any prompt.
  4. None of the above

49.What is true about the query given above?

  1. This query returns an ORA error
  2. It executes successfully but gives no results
  3. Queries from different tables cannot be used with the SET operators
  4. The query executes successfully and gives the results as expected

50.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

  1. The statement would throw an ORA error
  2. The statement would return all the rows in the table
  3. The statement would return NULL as the output result.
  4. The statement would return no rows in the result.

51.What is the default sorting order of the results when UNION ALL operator is used?

  1. Descending
  2. Ascending
  3. Either A or B
  4. All of the above

52. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (ALL num) FROM t_count;
  1. 12
  2. 6
  3. 9
  4. Throws exception because COUNT function doesn&-8217;t works with NULL values

53.What is the maximum level up to which Sub-queries can be nested?

  1. 255
  2. 100
  3. 2
  4. 16

54. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.

SELECT COUNT (DISTINCT num) FROM t_count;
  1. 12
  2. 6
  3. 9
  4. Throws exception because COUNT function doesn&-8217;t works with NULL values

55. Here are few statements about VARIANCE function in SQL.

i. The function accepts multiple numeric inputs and returns variance of all the values

ii. The function accepts a number column and returns variance of all column values including NULLs

iii. The function accepts a number column and returns variance of all column values excluding NULLs

Chose the correct combination from the below options.

  1. i and iii
  2. i and ii
  3. ii
  4. iii

56. Which clause is used to filter the query output based on aggregated results using a group by function?

  1. WHERE
  2. LIMIT
  3. GROUP WHERE
  4. HAVING

57. A user named &-8220;Kevin&-8221; wants to access a table which is owned by another user named &-8220;Jonathan&-8221;. Which of the following will work for Kevin?

  1. Select * from Kevin.employees;
  2. Select * from jonathan.employees;
  3. Either of A or B
  4. None of the above

58.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

  1. Returns rows that match all the values in a list/sub-query
  2. Returns rows that match only some values in a list/sub-query
  3. Returns rows only if all the values match in a list/sub-query
  4. All of the above

59. Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

  1. The rows will get removed without any error
  2. Only the first 10 rows will get removed
  3. The rows cannot be deleted.
  4. None of the above

60.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

  1. Oracle will convert them implicitly and return a VARCHAR2 data type result
  2. Oracle will convert them implicitly and return a NUMBER data type result
  3. An ORA error is thrown
  4. None of the above

61. What is true about a schema?

  1. A schema is owned by a database user and has the same name as that user
  2. Each user owns a single schema
  3. Schema objects include database links
  4. All of the above

62. In which order the values will get inserted with respect to the above INSERT statement?

  1. Location_id , manager_id, department_name , department_id
  2. department_id , department_name , manager_id, location_id
  3. department_id , manager_id, department_name , location_id
  4. department_id , department_name , location_id , manager_id

63. What among the following is true about tables?

  1. A default value is given to a table
  2. A default value can be given to a column of a table during an INSERT statement
  3. Either of A or B
  4. None of the above

65. Which of the below SQL query will display employee names, department, and annual salary?

  1. SELECT ename, deptno, sal FROM emp;
  2. SELECT ename, deptno, sal + comm FROM emp;
  3. SELECT ename, deptno, (sal * 12) Annual_Sal FROM emp;
  4. Annual salary cannot be queried since the column doesn&-8217;t exists in the table

66. What is true about the SUBSTR function in Oracle DB?

  1. It extracts a string of determined length
  2. It shows the length of a string as a numeric value
  3. It finds the numeric position of a named character
  4. It trims characters from one (or both) sides from a character string

67. Which of the following SELECT statements lists the highest retail price of all books in the Family category?

  1. SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
  2. SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
  3. SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
  4. None of the above

68. Which of the following functions can be used to include NULL values in calculations?

  1. SUM
  2. NVL
  3. MAX
  4. MIN

69.Which statements best describes the inference drawn from the questions 34 and 35?

  1. There are duplicate values for job codes
  2. The query executes but results produced are unexpected
  3. There are no duplicate values for departments
  4. None of the above

70. What will be the outcome of the following query?

SELECT length('hi') FROM dual;
  1. 2
  2. 3
  3. 1
  4. hi

Answer:

Answer(1): A. The ROUND function will round off the value 144.23 according to the specified precision -1 and returns 140.

Examine the structure of the EMPLOYEES table as given and answer the questions 2 and 3 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Answer(2): A. Using parenthesis will explicitly change the order of evaluation when INTERSECT is used with other operators.

Answer(3): A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

Answer(4): A.Select the required from the tables each separated by a
comma.

Answer(5): C. Simple and Complex views are two types of views.
Simple views are based on a subquery that references only one table and doesn&-8217;t include group
functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that
retrieves or derives data from one or more tables and can contain functions or grouped data.

Answer(6): C. All the combined should have the same no. of columns
when using SET operators. The corresponding columns in the queries that make up a compound
query must be of the same data type group.

Answer:(7) D. Multiple-row subqueries return more than one row of results.Operators that
can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

Answer(8): C. An index can be created to speed up the query process.
DML operations are always slower when indexes exist. Oracle 11g creates an index for PRIMARY
KEY and UNIQUE constraints automatically. An explicit index is created with the CREATE INDEX
command. An index can be used by Oracle 11g automatically if a query criterion or sort
operation is based on a column or an expression used to create the index.

Answer(9): A. Using the SELECT clause is the most common technique
for inserting rows into tables. It reduces the effort of manually keying in values for each
column.

Answer(10): A. View definition can make use of sub-queries.

Answer(11): C. DESCRIBE is used to show the table structure along
with table columns, their data type and nullity

Answer(12): C. Any arithmetic operation with NULL results in a NULL.

Answer()13: C, D.

Answer(14): C. The ANSI SQL: 1999 syntax though not used as much as
the traditional Oracle syntax, it still is one of the syntaxes that may be used in Oracle SQL

Answer(15): B. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

Answer(16): D. The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the
EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.

Answer(17): D. The WHERE clause can be omitted and the relevant conditions can be accommodated in the JOIN..ON clause itself as shown in the given query

Answer(18): A. Table1 JOIN table2 JOIN table3 is not allowed without the ON clauses for between each JOIN

Answer(19): C. The leading zeroes in the right operand of expression are ignored by Oracle.

Answer(20): A, C. You cannot create a table with the name same as an Oracle Server reserved word.

Answer(21): C. The BETWEEN operator can be used within a sub-query but not with a sub-query.

Answer(22): D. Functions can perform calculations, perform case conversions and type conversions.

Answer(23): A, C. A join can be an inner join,in which the only records returned have a matching record in all tables,or an outer join, in which records can be returned regardless of whether there&-8217;s a matching record in the join.An outer join is created when records need to be included in the results without having corresponding records in the join tables. These records are matched with NULL records so that they&-8217;re included in the output.

Answer(24): B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn&-8217;t created.

Answer(25): D.

Answer(26): A, B. A user must possess the CREATE TABLE privilege and must have sufficient space to allocate the initial extent to the table segment.

Answer(27): A.

Answer(28): C. The FULL OUTER JOIN returns the non-matched rows from both the tables. A full outer join includes all records from both tables, even if no corresponding record in the other table is found.

Answer(29): B.In SQL, concatenation operator is represented by two vertical bars (||).

Answer(30): C. Parenthesis can be used to group the specific queries in order to change the precedence explicitly. Parentheses are preferred over other SET operators during execution.

Answer(31): A. UNION Returns the combined rows from two queries, sorting them and removing duplicates.

Answer(32): C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it&-8217;s important that users can only query data, not make any changes to it.

Answer(33): B, C. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn&-8217;t created.

Answer(34): B. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view&-8217;s previous version should be replaced with the one defined in the new command.

Answer(35): A. There are basically two types of functions &-8211; Single row and Multiple row functions.

Answer(36): B. Character, Date, Conversion, General, Number are the types of Single row functions.

Answer(37): B. Multiple Row functions always work on a group of rows and return one value per group of rows.

Answer(38): C. Group functions are same as Multi row functions and aggregate functions.

Answer(39): A. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and
NULL values.

Answer(40): D. A table must have atleasr one column, its data type specification, and precision (if required).

Answer(41): C. Specifying alias name is good practice to improve the readability of the code and the view queries.

Answer(42): C. COUNT (column) ignores the NULL values but counts the duplicates.

Answer(43): C. The NATURAL JOIN clause implicitly matches all the identical named columns. To add additional conditions the WHERE clause can be used.

Answer(44): A. GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.

Answer(45): B. The sequence of the column alias not matters much as they don&-8217;t carry any behavioral attribute.

Answer(46): B. The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.

Answer(47): B. Provided the last names in the employees table are in a proper case, the condition WHERE last_name = &-8216;smith&-8217; will not be satistified and hence no results will be displayed.

Answer(48): C. As a part of the active or a new transaction, the rows in the table will be deleted.

Answer(49): D. A compound query is one query made up of several queries using different tables.

Answer(50): D.

Answer(51): B. A compound query will by default return rows sorted across all the columns,from left to right in ascending order.The only exception is UNION ALL, where the rows will not be sorted. The only place where an ORDER BY clause is permitted is at the end of the compound query.

Answer(52): C. COUNT(ALL column) ignores the NULL values but counts the duplicates.

Answer(53): A.

Answer(54): B. COUNT (DISTINCT column) counts the distinct not null values.

Answer(55): C. The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering
NULLs.

Answer(56): D. HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

Answer(57): B.

Answer(58): C. &-8216;> ALL&-8217; More than the highest value returned by the subquery. &-8216; ALL&-8217; Less than the lowest value returned by the subquery. &-8216; ANY&-8217; Less than the highest value returned by the subquery. &-8216;> ANY&-8217; More than the lowest value returned by the subquery. &-8216;= ANY&-8217; Equal to any value returned by the subquery (same as IN). &-8216;[NOT] EXISTS&-8217; Row must match a value in the subquery.

Answer(59): C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

Answer(60): C. Oracle does not convert data types implicitly.

Answer(61): D. The user space in a database is known as schema. A schema contains the objects which are owned or accessed by the user. Each user can have single schema of its own.

Answer(62): B. If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order

Answer(63): B. A default value can be specified for a column during the definition using the keyword DEFAULT.

Answer(65): C. Use numeric expressions in SELECT statement to perform basic arithmetic calculations.

Answer(66): A. The SUBSTR(string, x, y) function accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position (x). When position is positive, then the function counts from the beginning of string to find the first character. When position is negative, then the function counts backward from the end of string.

Answer(67): A. Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.

Answer(68): B. NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.

Answer(69): C. As the combination of the job codes and departments is unique, there are no duplicates obtained.

Answer(70): A. the LENGTH function simply gives the length of the string.



Advertisements

SQL &-8211; Introduction

SQL &-8211; SQL Exams Syllabus

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL Certificate &-8211; Mock Exams

SQL Certificate &-8211; Useful Resources

SQL Certificate &-8211; Discussion

UPSC IAS Exams Notes

Developer&-8217;s Best Practices

Questions and Answers

Effective Resume Writing

HR Interview Questions

Computer Glossary

Who is Who

SQL Certificate – Useful Resources

SQL Certificate &-8211; Useful Resources &-8211; this Article or News was published on this date:2019-05-13 11:20:30 kindly share it with friends if you find it helpful

SQL Certificate &-8211; Useful Resources


Advertisements


The following resources contain additional information on SQL Certificate. Please use them to get more in-depth knowledge on this topic.

Useful Links on SQL Certificate

  • ANSI SQL − Database Language SQL (Proposed revised text of DIS 9075) July 1992 (Second Informal Review Draft).

  • Structured Query Language (SQL): − DB2 resource for SQL users.

  • Oracle SQL Developer − Oracle SQL Developer is a free and fully supported graphical tool for database development.

  • SQL at Wikipedia − A small article on SQL, worth to go through it.

  • MySQL Official Website − Here you can download the latest MySQL release, get the MySQL news update. The mailing list is also a great resources for anyone who want to build dynamic websites using MySQL.

  • MySQL and PERL − Its a tutorial from Tutorials Point which explains you how to use MySQL along with PERL and DBI module. Here you will learn all the required MySQL operations alongwith examples.

Useful Books on SQL Certificate

  • Study Guide for 1Z0-051
  • Beginning SQL Joes 2 Pros
  • OCA Oracle Database SQL Expert Exam Guide
  • Learning SQL
  • Sams Teach Yourself SQL in 10 Minutes
  • The Language of SQL
  • What is SQL? Database Learning Basics for Business
  • SQL All-in-One For Dummies
  • Head First SQL: Your Brain on SQL

To enlist your site on this page, please drop an email to [email protected]



Advertisements

SQL &-8211; Introduction

SQL &-8211; SQL Exams Syllabus

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL Certificate &-8211; Mock Exams

SQL Certificate &-8211; Useful Resources

SQL Certificate &-8211; Discussion

UPSC IAS Exams Notes

Developer&-8217;s Best Practices

Questions and Answers

Effective Resume Writing

HR Interview Questions

Computer Glossary

Who is Who

Creating Other Schema Objects Questions

Creating Other Schema Objects Questions &-8211; this Article or News was published on this date:2019-05-13 11:20:29 kindly share it with friends if you find it helpful

Creating Other Schema Objects Questions


Advertisements


1. Which database object among the following provides a layer of abstraction between the users and the data?

  1. Table
  2. Rows
  3. Views
  4. Synonyms

Answer: C, D. Views and Synonyms do not store data themselves. A view is a temporary or virtual table used to retrieve data stored in underlying database tables.

2. Which of the following data base objects can generate serial numbers?

  1. Synonyms
  2. Views
  3. Tables
  4. Sequences

Answer: D. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

3. What is true about views?

  1. They are equal to tables
  2. They store data from one or many tables
  3. We can execute SELECT and other DMLs on Simple views
  4. Views share the same namespace as tables and hence a table and a view cannot have the same name

Answer: C, D. DML operations aren&-8217;t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

4. Why are views useful? (Choose the most appropriate answer)

  1. Because they have shorter names than tables
  2. To prevent users from accessing the columns of tables
  3. To simplify user SQL
  4. All of the above

Answer: B, C. A view is a temporary or virtual table used to retrieve data stored in underlying database tables. The view query must be executed each time the view is used. A view can be used to simplify queries or restrict access to sensitive data.

5. In which of the below scenarios, DML operations on a view are not possible?

  1. View contains GROUP BY clause
  2. Base tables contain NOT NULL columns but not selected in the view query
  3. View query uses ROWNUM pseudocolumn
  4. All of the above

Answer: D. DML operations aren&-8217;t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

6. Where can views get their data from?

  1. Tables from the same schema
  2. Tables from different schema
  3. Both A and B
  4. None of the above

Answer: C.

Consider the given table structure and the following statement and answer the questions 7 to 9 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
CREATE VIEW emp_details AS
SELECT hire_date, job, salary, department_id FROM employees; 

7. You issue the below query. How many columns will the user see as a result of the below query?

SELECT * FROM emp_details WHERE department_id= 100;
  1. 0
  2. 1
  3. 2
  4. 4

Answer: D. Since the view definition is based on four columns from the EMPLOYEES table, a query on a view with all column will show those four columns only.

8. You need to get the department name in addition to the above 4 columns. Which of the following query will give you the required results?

  1. SELECT E.*, dept_name
    FROM departments D join emp_details E
    ON (E.department_id= D.dept_id);
  2. SELECT hire_date, job, salary, dept_name FROM emp_details
  3. This is not possible a view cannot be joined to other tables
  4. None of the above

Answer: A. A view can be joined with other tables or views in a SELECT query.

9. You need to find the maximum salary along with the department name in addition to the 4 columns selected in the view. Which of the following query will give you the required results?

  1. Select dept_name, e.salary
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id);
  2. Select dept_name, max(salary)
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id)
    Group by dept_name;
  3. View cannot appear in queries using group functions
  4. Select dept_name, max(salary)
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id);

Answer: B.

10. What among the following is true about synonyms?

  1. PUBLIC and PRIVATE synonyms can have the same name for the same table
  2. DROP SYNONYM will remove a synonym
  3. DROP PUBLIC SYNONYM can be executed only by a SYSDBA
  4. None of the above

Answer: A, C. A synonym can be a private synonym, which users use to reference objects they own,or a public synonym, which users use to access another user&-8217;s database objects. Only SYSDBA or a user with DBA privileges can create a public synonym.

11. What is true about creating a view? (Choose the most appropriate answer)

  1. A view can only be created from a table
  2. A view can only be created from one table
  3. A view can be created from one or many tables or views
  4. None of the above

Answer: C. A view containing expressions or functions or joining multiple tables is considered a complex view. A complex view can be used to update only one table.

12. Which of the following privileges are required to create views in one&-8217;s own schema?

  1. CREATE TABLE system privilege
  2. CREATE VIEW system privilege
  3. ALTER VIEW system privilege
  4. CREATE ANY VIEW system privilege

Answer: B. CREATE VIEW privilege is required by a user to create a view in its own schema.

13. Which of the following privileges are required to create views in someone else&-8217;s schema?

  1. CREATE ANY VIEW
  2. CREATE VIEW
  3. Both A and B
  4. None of the above

Answer: A. CREATE ANY VIEW privilege is required by a user to create a view in other user&-8217;s schema.

14.Which of the following are supported for an object view or relational view?

  1. LOBs
  2. Object types
  3. REF data types
  4. All of the above

Answer: D.

15. What among the following are different types of Views?

  1. Simple views
  2. Complex views
  3. Both A and B
  4. None of the above

Answer: C. Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn&-8217;t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

16. What is true about a simple view?

  1. DML statements can be issued most of the times against simple views
  2. There is only one source base table
  3. No group functions are used
  4. All of the above

Answer: D. Simple views are based on a subquery that references only one table and doesn&-8217;t include group functions, expressions, or GROUP BY clauses.

17.What is true about a complex view?

  1. DML statements cannot be issued against complex views
  2. Contain multiple base tables
  3. Aggregations cannot be performed
  4. All of the above

Answer: D. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

18.Which keyword combination should be used to implicitly drop a view (if it exists) and create a new view with the same name?

  1. CREATE VIEW
  2. REPLACE VIEW
  3. CREATE OR REPLACE VIEW
  4. None of the above

Answer: C. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view&-8217;s previous version should be replaced with the one defined in the new command.

19.How is a view stored in the data dictionary?

  1. As a WHERE clause
  2. As a CREATE statement
  3. As an UPDATE statement
  4. As a SELECT statement

Answer: D.

20.Which of the following can contain single-row functions?

  1. Inline Views
  2. Simple Views
  3. Complex Views
  4. Composite Views

Answer: A, B. Single-row functions can be used in Inline as well as Simple views.

21.Which of the following can contain a group of data?

  1. Composite View
  2. Simple View
  3. Complex View
  4. None of the above

Answer: C. Complex view can use group function in the query.

22.What among the following is true about a View?

  1. Sub-queries can be embedded in a CREATE VIEW statement
  2. A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax
  3. You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement
  4. None of the above

Answer: A. View definition can make use of sub-queries.

23.Which of the following can create a view even if the base table(s) does not exist?

  1. NOFORCE
  2. FORCE
  3. OR REPLACE
  4. CREATE VIEW

Answer: B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn&-8217;t created.

24.Which of the following commands ensures that no DML operations can be performed on a view?

  1. NOFORCE
  2. FORCE
  3. WITH READ ONLY
  4. OR REPLACE

Answer: C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it&-8217;s important that users can only query data, not make any changes to it.

25.What is true about the NOFORCE option in CREATE VIEW statement?

  1. It creates a view even if the base table(s) does not exist.
  2. It creates a view only if the base table(s) exists.
  3. It is the default while creating a view.
  4. None of the above

Answer: B, C. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn&-8217;t created.

26.What is true about the OR REPLACE keyword?

  1. Object privileges are lost when a view is created using this keyword
  2. There is no need of re granting the object privileges previously granted on it
  3. Neither of A nor B
  4. None of the above

Answer: B. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view&-8217;s previous version should be replaced with the one defined in the new command.

27.What is true with respect to accessing the below view? (Assume the table structure given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
CREATE VIEW salVU100
AS SELECT employee_id  ID_NUMBER, last_name NAME, salary*12 ANNUAL_SAL
FROM employees E 
WHERE department_id= 100; 
  1. The view has to be accessed by the original column names defined in the base table
  2. The view has to be accessed by the aliases given in the view query
  3. View is a simple view
  4. None of the above

Answer: B, C. View must refer the column alias if the view definition contains alias for the columns.

28.What is true with respect to accessing the below view? (Assume the table structure given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
CREATE VIEW salVU100 (ID_NUMBER, NAME, ANNUAL_SAL)
AS SELECT employee_id , last_name, salary*12 
FROM employees E 
WHERE department_id= 100; 
  1. It is not mandatory that the number of aliases match the no. of expressions in the sub-query
  2. It is mandatory that the no. of aliases listed must match the no. of expressions selected in the sub-query
  3. It is mandatory to give aliases while creating a view
  4. None of the above

Answer: B. If the alias are specified in the view header, same number of columns must be selected in the SELECT query.

29. Consider the following statement and the given table structure:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
CREATE OR REPLACE VIEW empVU100
(ID_NUMBER, NAME, ANNUAL_SAL, DEPT_ID)
AS 
SELECT employee_id , first_name ||' '|| last_name, salary, department_id
FROM employees
WHERE department_id= 100; 

What is true about the column aliases as in the above query?

  1. Column aliases are listed in a random order as the columns in the sub-query
  2. Column aliases are listed in the same order as the columns in the sub-query
  3. Column aliases are mandatory while using the CREATE OR REPLACE keyword
  4. We cannot use concatenation when we use the CREATE OR REPLACE

Answer: B.

Consider the following statement and answer the questions 30 to 34 that follow:

CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) 
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name; 

30.What can be said about the statement given above?

  1. Alternative names have been given for the view
  2. Giving alternative names is mandatory if any column is derived from a function or an expression
  3. Both A and B
  4. None of the above

Answer: C. Specifying alias name is good practice to improve the readability of the code and the view queries.

31.What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name; 
  1. It will be no different than the original statement
  2. It will execute successfully giving the same results but change in alias names.
  3. It will throw an ORA error
  4. None of the above

Answer: B. The sequence of the column alias not matters much as they don&-8217;t carry any behavioral attribute.

32.Determine the output of the below DELETE statement.

DELETE FROM dept_sum_vu; 
  1. It will delete the view
  2. It will remove all the rows from the view but the structure of the view will remain the same
  3. It will throw an ORA error
  4. None of the above

Answer: C. The view DEPT_SUM_VU is a complex view. DML operations cannot be performed on a complex view.

33.Suppose you modify the query given above to the following:

CREATE OR REPLACE VIEW dept_sum_vu(name, sal) 
AS 
SELECT d.dept_name, e.salary 
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
Where rownum  10; 

What will be the impact of the modification?

  1. The view can be updated to update the values in EMPLOYEES and DEPARTMENTS tables
  2. Data in EMPLOYEES and DEPARTMENTS tables cannot be deleted through view
  3. Data in EMPLOYEES and DEPARTMENTS tables can be inserted through view
  4. A column can be added to EMPLOYEES table through the view

Answer: B. DML operations cannot be performed on complex views. DEPT_SUM_VU is a complex view as it joined multiple tables. DDL operations are not possible on views.

34.Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

  1. The rows will get removed without any error
  2. Only the first 10 rows will get removed
  3. The rows cannot be deleted.
  4. None of the above

Answer: C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

35.When can the rows from a view be removed?

  1. Deletion of rows through a view is not possible
  2. It should be a simple view
  3. It should be a complex view
  4. None of the above

Answer: B. DML operations are possible only on simple views.

36.When can the data in a view not be modified?

  1. When there are group expressions
  2. When there is a GROUP BY clause
  3. When ROWNUM is used in the view query
  4. All of the above

Answer: D. UPDATE is not possible on a view containing group functions, pseudocolumns or DISTINCT keyword.

37. The JOB_HISTORY table is owned by a user &-8220;Andy&-8221;. Andy grants the SELECT privilege on the JOB_HISTORY table to another user &-8220;HR&-8221;. Which statement would create a synonym EMP_JOBS so that &-8220;HR&-8221; can execute the following query successfully?(Assume the structure of tables as given)

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT * from EMP_JOBS; 
  1. Andy issues &-8211;
    CREATE SYNONYM EMP_JOBS for JOB_HISTORY
  2. HR issues &-8211;
    CREATE SYNONYM EMP_JOBS for andy.JOB_HISTORY
  3. HR issues &-8211;
    CREATE PUBLIC SYNONYM EMP_JOBS FOR andy.JOB_HISTORY
  4. None of the above

Answer: B. Only SYSDBA or a user with DBA privileges can create public synonyms.

38.Which keyword can assure that the DML operations performed on the view stay in the domain of the view?

  1. OR REPLACE
  2. CREATE
  3. WITH CHECK OPTION
  4. None of the above

Answer: C. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don&-8217;t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause.

Consider the following table structure and the given statement and answer the questions 39 and 40 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
CREATE OR REPLACE VIEW empvu100
AS 
SELECT * FROM employees 
WHERE department_id= 100
WITH CHECK OPTION CONSTRAINT empvu100_ck; 

39.What will the above statement do?

  1. It will allow the users to perform INSERT or UPDATE on all departments
  2. It will allow the user to perform INSERT or UPDATE any row which has department 100
  3. The user can UPDATE any row in the employees table
  4. The user can INSERT rows without any restriction in the employees table

Answer: B. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don&-8217;t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause. An ORA error will be thrown if an INSERT or UPDATE will be executed on any row with a department_id other than 100.

40.Suppose you fire an UPDATE statement as shown below:

UPDATE empvu100
Set department_id = 200
Where employee_id  = 121; 

What will be the outcome of this statement?

  1. No rows are updated
  2. An ORA error is thrown
  3. Both A and B
  4. None of the above

Answer: C. If the view with CHECK OPTION is updated and new record&-8217;s value violates the scope of the view, ORA exception &-8220;ORA-01402: view WITH CHECK OPTION where-clause violation&-8221; is raised.

41.What is true about the WITH CHECK CONSTRAINT?

  1. INSERTs or UPDATEs performed through the view cannot create rows that the view cannot select
  2. Only INSERTs performed through the view cannot create rows that the view cannot select
  3. Only UPDATEs performed through the view cannot create rows that the view cannot select
  4. None of the above

Answer: A.

42.How can you prevent DML operations on a View?

  1. By defining a WITH CHECK OPTION constraint
  2. By defining a WITH READ ONLY option
  3. Neither of A nor B
  4. None of the above

Answer: B. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it&-8217;s important that users can only query data, not make any changes to it.

Consider the table structure and the given statement and answer the questions 43, 44 and 45 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
CREATE OR REPLACE empvu100(employee_id , first_name, job)
AS 
SELECT employee_id , last_name, job
FROM employees
WHERE department_id = 100
WITH READ ONLY; 

43.What is true about the above statement?

  1. The view will not be created
  2. INSERT operation on this view an will throw an ORA error
  3. On UPDATING the rows for all the employees in department 100, an ORA error will be thrown
  4. None of the above

Answer: B, C. DML operations are not permitted on view which are created with READ ONLY option.

44.How many rows can be deleted from the view as shown above?

  1. All rows of the view
  2. All the rows of only the department 100
  3. No rows
  4. None of the above

Answer: C. DML operations are not permitted on view which are created with READ ONLY option.

45.Which of the following statements will drop the view created as above?

  1. DROP READ ONLY VIEW empvu100;
  2. DROP NOFORCE empvu100;
  3. DROP VIEW empvu100;
  4. None of the above

Answer: C. Read only view can be dropped using the DROP VIEW command.

46.What is true about dropping a View?

  1. The columns in the view from the base tables are also dropped
  2. The definition of the view is removed from the database
  3. Dropping of a view has no effect on the underlying base table
  4. None of the above

Answer: B, C.

47.Which of the following privileges should a user have to drop a view?

  1. CREATE ANY VIEW
  2. CREATE VIEW
  3. DROP ANY VIEW
  4. DROP VIEW

Answer: C.

48.What is true about sequences?

  1. It generates integers
  2. It is a shareable object
  3. Can be used to create a PRIMARY KEY value
  4. All of the above

Answer: D. A sequence speeds up the efficiency of accessing sequence values when cached in memory

49.What is true about a sequence?

  1. It is created when the Oracle Database is installed
  2. It is created by a user who has CREATE SEQUENCE system privilege
  3. It cannot be shared by more than two users
  4. It drops automatically when the database is disconnected.

Answer: B, C. CREATE SEQUENCE system privilege is required by a user to create a sequence in its own schema which cannot be shared by other users.

50.What among the following options is true about Sequences?

  1. The integers generated through a sequence for one table cannot be used by other tables
  2. A sequence can only be incremented
  3. A sequence becomes invalid if another sequence generates the same integers
  4. A sequence can be used by many tables and they can be incremented or decremented

Answer: D.

Consider the following statement and answer the questions 51 to 59 that follow:

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 9999
NOCACHE
NOCYCLE;

51.What will be the first value generated by this sequence?

  1. 1
  2. 100
  3. 101
  4. 9999

Answer: C. The START WITH clause establishes the starting value for the sequence. Oracle 11g begins each sequence at 1 unless another value is specified in the START WITH clause.

52.What can be the last value generated by this sequence?

  1. 0
  2. 100
  3. 101
  4. 9999

Answer: D. The MINVALUE and MAXVALUE clauses establish a minimum or maximum value for the sequence.

53.What will be the 2nd value generated by this sequence?

  1. 102
  2. 100
  3. 99
  4. 9999

Answer: A. The INCREMENT BY clause specifies the interval between two sequential values. If the sequence is incremented by a positive value, the values the sequence generates are in ascending order. However, if a negative value is specified, the values the sequence generates are in descending order. If the INCREMENT BY clause isn&-8217;t included when the sequence is created, the default setting is used, which increases the sequence by one for each integer generated.

54.What will be the next value after the maximum integer 9999 is reached by this sequence?

  1. 101
  2. No value
  3. It will throw an ORA error
  4. None of the above

Answer: B. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value.

55.How many values will Oracle pre allocate in memory based on the sequence given above?

  1. 20
  2. 0
  3. 100
  4. 9999

Answer: A.

56.You execute the below query:

SELECT dept_depid_seq.NEXTVAL from dual; 

Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200
  2. 101
  3. 9999
  4. 201

Answer: D. The NEXTVAL pseudocolumn will generate the next unique integer of the sequence.

57.You execute the below query:

SELECT dept_depid_seq.CURRVAL from dual; 

Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200
  2. 101
  3. 9999
  4. 201

Answer: A. The CURRVAL pseudocolumn will generate the current unique integer already generated by the sequence.

58.Suppose you need to change the start value of this sequence to 1000. Which of the following statements will help?

  1. ALTER dept_deptid_seq
    INCREMENT BY 100
    START WITH 1000
    MAXVALUE 9999
    NOCACHE
    NOCYCLE; 
  2. The sequence has to be dropped and re-created to start the sequence from 1000.
  3. ALTER SEQUENCE dept_deptid_seq
    START WITH 101
  4. ALTER SEQUENCE dept_deptid_seq
    INCREMENT BY 100
    START WITH 101
    CYCLE;

Answer: B. Starting number of a sequence cannot be modified. Oracle raises the exception &-8220;ORA-02283: cannot alter starting sequence number&-8221;.

59.Suppose that the above sequence is altered as below:

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 99
NOCACHE
NOCYCLE; 

What will be the outcome of this alteration?

  1. ORA error
  2. The maximum value for the altered sequence will now be 99
  3. Neither of A nor B
  4. None of the above

Answer: A. The MAXVALUE cannot be less than the START WITH value while altering a sequence.

60.When can we use the CYCLE option in Sequences?

  1. If we want to purge the old rows faster
  2. If we do not want to use the sequence to generate PRIMARY KEY values
  3. Both A and B
  4. None of the above

Answer: C. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value. If the CYCLE option is specified and Oracle 11g reaches the maximum value for an ascending sequence or the minimum value for a descending sequence, the CYCLE option initiates the cycle of numbers again.

61.What is true about NEXTVAL pseudo column?

  1. It re-generates the CURRVAL of a sequence
  2. It returns the next available sequence value
  3. It can return duplicate values
  4. It generates the same values for different users

Answer: B. The pseudocolumn NEXTVAL (NEXT VALUE) is used to actually generate the sequence value. In other words, it calls the sequence object and requests the value of the next number in the sequence. After a value is generated, it&-8217;s stored in the CURRVAL (CURRENT VALUE) pseudocolumn so that you can reference it again.

62.What is true about CURRVAL pseudo column?

  1. CURRVAL can be used before NEXTVAL with respect to a sequence
  2. CURRVAL gives the current value of a sequence
  3. CURRVAL can give duplicate values
  4. None of the above

Answer: B.

63.When can NEXTVAL and CURRVAL be used?

  1. SET clause of an INSERT statement
  2. VALUES clause of an UPDATE statement
  3. The SELECT list of a SELECT statement that is not part of a sub-query
  4. The SELECT list of an INSERT statement

Answer: C, D. The sequence can be used in SELECT query, PL/SQL cursor or in IAS (INSERT-AS-SELECT)direct operations.

64.When can NEXTVAL and CURRVAL not be used?

  1. The SELECT list of a view
  2. The SELECT statement with the DISTINCT keyword
  3. A sub-query in SELECT, DELETE or UPDATE statement
  4. All of the above

Answer: D.

Consider the given statement and answer the questions 65 and 66 that follow:

CREATE TABLE employees 
(employee_id  NUMBER(4) DEFAULT emp_empid_seq.CURRVAL,
 department_id NUMBER(4)); 

65.What will be the outcome of this statement? (Assume that emp_empid_seq is sequence used to generate employee ID values)

  1. Table will be created
  2. The department_id column will have the values from the sequence generated for the employee ID
  3. The department_id column will have a DEFAULT value
  4. ORA error

Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

66.What will be the outcome of this statement if the CURRVAL is replaced with NEXTVAL? (Assume that emp_empid_seq is generated to generate employee ID values)

  1. Table will be created
  2. The department_id column will have the values from the sequence generated for the employee ID
  3. The department_id column will have a DEFAULT value
  4. ORA error

Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

Examine the given exhibit giving the structures of the tables Departments and Location. Answer the questions 67 and 68 that follow:

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
SQL> desc locations
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 LOCATION_ID		 NOT NULL NUMBER(4)
 STREET_ADDRESS 		  VARCHAR2(40)
 POSTAL_CODE			  VARCHAR2(12)
 CITY			 NOT NULL VARCHAR2(30)
 STATE_PROVINCE 		  VARCHAR2(25)
 COUNTRY_ID			  CHAR(2)

67.You need to insert a new department named &-8220;HR&-8221; in the location ID 1000. Which of the following statements will give you the required results?

  1. INSERT INTO departments (dept_id, dept_name, location_id)
    VALUES (dept_deptid_seq.NEXTVAL, 'HR', 1000); 
  2. INSERT INTO departments (dept_id, dept_name, location_id)
    VALUES (dept_deptid_seq.NEXTVAL, "HR", 1000); 
  3. INSERT INTO departments (dept_id, dept_name, location_id)
    VALUES (dept_deptid_seq.CURRVAL, 'HR', 1000); 
  4. None of the above

Answer: A.The option C will cause a &-8216;Unique constraint violation&-8217; as it will try to insert current value of department id which aleady exists in the DEPARTMENTS table.

68.Suppose you execute the below query before inserting the values as shown in the option A in question 67. What will be the outcome of the query?

SELECT dept_deptid_seq.CURRVAL FROM DUAL; 
  1. ORA error
  2. It will give the current value of the sequence
  3. Neither of A nor B
  4. None of the above

Answer: B. When a user logs in to Oracle 11g, no value is initially stored in the CURRVAL pseudocolumn; the current value is NULL. After a NEXTVAL call has been issued to generate a sequence value, CURRVAL stores that value until the next value is generated. CURRVAL contains only the last value generated.

69.How can gaps occur in the values of a sequence?

  1. When a rollback occurs
  2. The system crashes
  3. A sequence is used in another table
  4. All of the above

Answer: D.

70.What is true about caching sequence values?

  1. Caching sequence values is not possible in Oracle
  2. The cache is populated when the maximum limit of the sequence is reached
  3. Caching starts the first time when the sequence is referred
  4. None of the above

Answer: C. If the NOCACHE option is specified when the sequence is created, each number is generated when the request is received. However, if an organization&-8217;s transactions require large amounts of sequential numbers throughout a session, the CACHE option can be used to have Oracle 11g generate a set of values ahead of time and store them in the server&-8217;s memory. Then, when a user requests a sequence value, the next available value is assigned-without Oracle 11g having to generate the number. On the other hand, if the CACHE option isn&-8217;t specified, Oracle 11g assumes a default option of CACHE 20 and stores 20 sequential values in memory automatically for users to access.

71.The following query for the sequence EMP_EMPID_SEQ is executed after a transaction which inserted five employee details.

Select emp_empID_seq.CURRVAL from dual; 

Suppose the employee transaction rolled back. What will be the result of the above query?

  1. The sequence value at the starting of employee transaction
  2. NULL
  3. The sequence value at the end of employee transaction
  4. None of the above

Answer: C. Sequence values are unaffected by commit or rollback. If a transaction which uses sequence generator is rolled back, the sequence values are wasted and cannot be recovered.

72.Which of the following privileges are required to modify a sequence?

  1. CREATE OR REPLACE privilege
  2. ALTER privilege for the sequence
  3. ALTER TABLE privilege
  4. UPDATE privilege

Answer: B. To alter a sequence, the sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

73.What happens when a sequence is altered?

  1. The existing integers already generated by the sequence are altered as well
  2. Only the future integers are affected
  3. The sequence stops caching the future integers
  4. None of the above

Answer: B. By using the ALTER SEQUENCE command, any changes are applied only to values generated after the modifications are made.

74.Suppose you need to drop a sequence. Which of the following commands will help?

  1. ALTER SEQUENCE sequence_name START WITH NULL;
  2. DROP sequence_name;
  3. DROP SEQUENCE sequence_name;
  4. None of the above

Answer: C. The DROP command is used to drop a sequence

75.Which of the following privileges will allow you to drop a sequence? (Choose the most appropriate answer)

  1. ALTER SEQUENCE
  2. ALTER TABLE
  3. DROP SEQUENCE
  4. DROP ANY SEQUENCE

Answer: D. To drop a sequence, either the sequence must be in your own schema or you must have the DROP ANY SEQUENCE system privilege.

76.What is true about Indexes?

  1. Indexes are only manually created
  2. Indexes are only automatically created
  3. Both A and B
  4. None of the above

Answer: D. Indexes can be created manually as well as automatically following certain actions like creating a primary key or unqiue constraint.

77.Which of the following is used by an index to locate the data quickly?

  1. ROWNUM
  2. ROWID
  3. Sequence
  4. None of the above

Answer: B. An Oracle 11g index is a database object that stores a map of column values and the ROWIDs of matching table rows. A ROWID is the physical address of a table row.

78.What happens when there is no index on a column of a table?

  1. The data is located quickly
  2. There is a full table scan
  3. The table cannot be created
  4. The table cannot be altered

Answer: B.

79.What among the following is true about an Index?

  1. Index reduces the disk I/O
  2. Index locates data quickly
  3. Indexes are logically and physically independent of the table that they index
  4. All of the above

Answer: D.

80.What will happen if an index is dropped?

  1. The column on which the index is created, is dropped too
  2. The table on which the index is created, is dropped too
  3. Indexes once created cannot be dropped
  4. As Indexes are logically and physically independent objects, they can be dropped without affecting other objects

Answer: D. Indexes are the objects which are physically stored in schema. Dropping an index doesn&-8217;t impacts other objects.

81.What happens when a table is dropped?

  1. The indexes still remain as they are logically and independent objects
  2. The indexes in the table are also dropped
  3. Neither of A nor B
  4. None of the above

Answer: B.

82.How are indexes created automatically?

  1. When we create a table
  2. When a table is altered
  3. When a PRIMARY KEY is defined on a column (or group of columns) of a table
  4. When a UNIQUE KEY constraint is defined in the table definition

Answer: C, D.

83.For which of the following objects, a synonym can be created?

  1. Tables and views only
  2. Table, view and sequence
  3. Stored procedure, function, or package
  4. Synonym

Answer: B, C, D. The schema object for which you are creating the synonym can be of the following types:Table or object table, View or object view, Sequence, Stored procedure, function, or package, Materialized view, Java class schema object, User-defined object type, Synonym

84. Which of the following can you use to reference a table owned by another user?

  1. INDEX
  2. TABLE
  3. SYNONYMS
  4. SEQUENCES

Answer: C. A synonym is an alternative name or alias for a database object.

85.What among of the following is an example of a Non-unique index?

  1. PRIMARY KEY
  2. UNIQUE KEY
  3. FOREIGN KEY
  4. None of the above

Answer: C.

86.Which of the following is the main and basic type of an Index?

  1. Bitmap
  2. B-tree
  3. Unique
  4. Non-unique

Answer: A, B. The B-tree (balanced-tree) index is the most common index used in Oracle. You can create this type of index with a basic CREATE INDEX statement. A bitmap index varies in structure and use from a B-tree index. This index is useful for improving queries on columns that have low selectivity (low cardinality, or a small number of distinct values).

87.You need to speed up a query by creating an index on the FIRST_NAME of the EMPLOYEES table. Which of the following statements can you use? (Assume the table structure as shown)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. CREATE INDEX emp_first_name_idx 
    ON employees (first_name); 
  2. CREATE INDEX emp_first_name_idx 
    ON employees first_name; 
  3. ALTER INDEX emp_first_name_idx 
    ON employees (first_name); 
  4. None of the above

Answer: A.

88.What does the UNIQUE keyword do while creating indexes?

  1. It specifies that the value of the column(s) upon which the index is created must be unique
  2. You cannot use the UNIQUE keyword when creating indexes
  3. It specifies that the index that is created can be used only by one table
  4. None of the above

Answer: A. A unique index is typically created automatically when a PRIMARY KEY or UNIQUE constraint is defined on a column. Unique indexes can also be explicitly created by including the UNIQUE keyword in the CREATE INDEX statement.

89.What will happen when you specify the keyword BITMAP while creating an Index?

  1. It creates the index with a bitmap for each distinct key.
  2. It does not create the index on each row separately
  3. Both A and B
  4. None of the above

Answer: C.

90.You have queries written which are expected to retrieve less than 2% to 4% of rows. Which of the following can be applied on the relevant tables to achieve the query performance of such query? (Choose the best answer)

  1. Indexes
  2. UNION set operator
  3. MINUS set operator
  4. None of the above

Answer: A. Indexes are the best way to achieve query performance. Heavy IO operations can be reduced and simplified using index scans.

91.In what scenarios can Indexes be useful?

  1. If the table(s) is very large
  2. If a column has less values
  3. If a column contains a large number of NULL values
  4. If a column has a wide range of values

Answer: C, D.

92.The table EMPLOYEES is updated frequently. When can Indexes be created on this table? (Choose the most appropriate answer)

  1. Indexes should not be created if a table is updated frequently
  2. Indexes should be created at the time when the table is created
  3. Neither of A nor B
  4. None of the above

Answer: A. Frequent or bulk DML operations on a table with an index add an overhead of maintaining the index segment, which might affect the DML operation performance.

93.Consider the following query and answer the following query. Assume that the EMPLOYEE_ID , DEPARTMENT_ID and FIRST_NAME columns of EMPLOYEES table are indexed. (Assume the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT first_name, last_name 
FROM employees 
WHERE comm IS NULL; 

Will the existing indexes help in this case if there are 1 million rows in the table EMPLOYEES?

  1. Yes
  2. No
  3. It might help
  4. None of the above

Answer: B. Indexes are not used when the query predicates do not contain the columns on which the index is created.

94.Which of the following will remove an Index?

  1. DELETE FROM index_name; 
  2. DROP INDEX index_name; 
  3. DROP INDEX;
  4. None of the above

Answer: B. You must have the DROP ANY INDEX privilege to drop an index.



Advertisements

SQL &-8211; Introduction

SQL &-8211; SQL Exams Syllabus

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL Certificate &-8211; Mock Exams

SQL Certificate &-8211; Useful Resources

SQL Certificate &-8211; Discussion

UPSC IAS Exams Notes

Developer&-8217;s Best Practices

Questions and Answers

Effective Resume Writing

HR Interview Questions

Computer Glossary

Who is Who

Manipulating Data Questions

Manipulating Data Questions &-8211; this Article or News was published on this date:2019-05-13 11:20:27 kindly share it with friends if you find it helpful

Manipulating Data Questions


Advertisements


1.What does ACID mean with respect to relational database?

  1. Accuracy, Consistency, Isolation, Database
  2. Accuracy, Concurrency, Isolation, Durability
  3. Atomicity, Consistency, Isolation, Durability
  4. Atomicity, Concurrency, Isolation, Durability

Answer: C. All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. Atomicity states that all tasks of a transaction are performed or none of them are. There are no partial transactions. Consistency implies the transaction takes the database from one consistent state to another consistent state. Isolation means the effect of a transaction is not visible to other transactions until the transaction is committed. Durability means that changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

2. What does the word DML stands for in Oracle SQL?

  1. Durability Management Language
  2. Database Management Language
  3. Database Manipulation Language
  4. None of the above

Answer: C. DML stands for Data Manipulation Language.

3. Which of the following are DML commands in Oracle Database?

  1. SELECT
  2. GROUP BY
  3. INTERSECT
  4. INSERT

Answer: A, D. On strict grounds, SELECT is a DML command as it is one of the mandatory clauses for manipulation of data present in tables.

4.Which of the following DML commands can be considered to be a hybrid of INSERT and UPDATE in a single statement?

  1. INTERSECT
  2. INSERT
  3. SELECT
  4. MERGE

Answer: D. MERGE can perform INSERT and UPDATE actions in a single statement in Oracle.

5. What all operations can MERGE statement perform in SQL?

  1. INSERT
  2. DELETE
  3. GROUP BY
  4. None of the above

Answer: A, B. In some conditions MERGE can perform the DELETE operation too, along with INSERT and UPDATE.

6.Which of following commands is a DDL (Data Definition Language) command but is often considered along with DML commands?

  1. DELETE
  2. INSERT
  3. TRUNCATE
  4. None of the above

Answer: C. TRUNCATE is a DDL command. It removes the records from the table without any condition. It is not the part of any ongoing transaction
and an uncommitted transaction in the session is committed after TRUNCATE is executed.

7.Which of the following commands manipulate data basically?

  1. MINUS
  2. UPDATE
  3. TRUNCATE
  4. All of the above

Answer: B, C. UPDATE is a DML statement to modify a column value in a table. TRUNCATE manipulates the data by removing them unconditionally
from a table.

8. Which of the following commands is used to populate table rows with data?

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

Answer: B. INSERT command is used to insert rows in a table.

9. What is true about the INSERT statement? (Choose the most appropriate answer)

  1. It can insert data in one row of one table at a time
  2. It can insert data in many rows of one table at a time
  3. It can insert data in many rows of many tables at a time
  4. All of the above

Answer: C. The INSERT statement is capable of inserting a row or set of rows in a single table at a time.

10.What is true about the insertion of rows in tables?

  1. The rows can be inserted randomly
  2. Any number of rows can be inserted in a table without any restrictions
  3. Generally the rows are inserted in a table based on certain rules known as constraints
  4. All of the above

Answer: C. Constraints are business rules imposed on the columns so as to ensure the behavior of the data coming in the column. These constraints
are validated for the data during the INSERT process.

11. What is true about the INSERT statement in Oracle SQL? (Choose the most appropriate answer)

  1. An INSERT statement can override any constraint put on the table
  2. An INSERT statement cannot be used on a table if a constraint is already placed on the table
  3. An INSERT statement can be used on a table only if a constraint is already placed on the table
  4. An INSERT statement can never insert a row that violates a constraint.

Answer: D. Oracle raises exception if any of the data contained in the insert statement violates the constraint.

Consider the following data set from the EMPLOYEES table along with its structure and answer the questions 12, 13 and 14:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER (6)
 FIRST_NAME			  VARCHAR2 (20)
 LAST_NAME		 NOT NULL VARCHAR2 (25)
 EMAIL			 NOT NULL VARCHAR2 (25)
 PHONE_NUMBER			  VARCHAR2 (20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2 (10)
 SALARY 			  NUMBER (8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
EMPLOYEE_ID FIRST_NAME   JOB_ID 
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

12. Examine the structure of the EMPLOYEES table. You issue the following command:

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (5100, 'BRUCE', 'CLERK');

Assuming that there is a duplicate value check constraint on the EMPLOYEE_ID column, what will be the outcome of the above statement?

  1. It will insert another row with 5100 BRUCE CLERK values
  2. It will insert another row with 51002 BRUCE CLERK values
  3. It will throw a &-8216;Constraint violated&-8217; ORA error
  4. None of the above

Answer: C. As the row with values &-8220;5100, BRUCE, CLERK&-8221; already exists in the table, the insert statement with same data set is not possible.

13.You issue the following command to the data set shown above:

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id) VALUES (51003,'BRUCE','CLERK');

What will be the output of this statement?

  1. It will insert a new row with 51003 BRUCE CLERK values
  2. It will throw an ORA error as there cannot be another BRUCE who is a CLERK
  3. It will throw an &-8216;Constraint violated&-8217; ORA error
  4. None of the above

Answer: A. As there is no constraint on the columns FIRST_NAME and job_id, the INSERT will work without any error

14. You issue the following command to the data set shown above:

INSERT INTO EMPLOYEES (employee_id  , first_name , job_id ) VALUES (51003,'BRUCE', NULL);

What will be the output of this statement?

  1. It will insert a new row with 51003 BRUCE CLERK values
  2. It will throw an ORA error as there cannot be another BRUCE who is a CLERK
  3. It will throw an &-8216;Constraint violated&-8217; ORA error
  4. It will insert a new row with 51003 BRUCE NULL values

Answer: D. As there is no NOT NULL constraint on the columns FIRST_NAME and JOB_ID , the NULL value will get inserted.

15. What among the following can be said regarding inserting of rows in tables?

  1. The user cannot interactively insert rows
  2. A query can be written with substitution variables for an interactive insertion of rows by the users
  3. When a user is prompted for inserting rows, the insert doesn&-8217;t work and it throws an ORA error
  4. None of the above

Answer: B. An INSERT statement can make use of substitution variable to prompt the user to key in values during the runtime. It provides an interactive way of inserting data into tables

16.Which of the following can be used to insert rows in tables?

  1. SELECT
  2. INSERT
  3. Sub-queries
  4. All of the above

Answer: D. INSERT statement can make use of explicit INSERT, INSERT-SELECT or a sub-query method to insert data into tables.

17. Which among the following is a common technique for inserting rows into a table? (Choose the most sensible and appropriate answer)

  1. Using SELECT clause
  2. Manually typing each value into the INSERT clause
  3. Using SET operators
  4. None of the above

Answer: A. Using the SELECT clause is the most common technique for inserting rows into tables. It reduces the effort of manually keying in values for each column.

18.Which of the following commands is used to change the rows that already exist in a table?

  1. INSERT
  2. UNION
  3. UPDATE
  4. SELECT

Answer: C. UPDATE is a DML statement which is used to modify the column values in a table.

19.What is true about the UPDATE command?

  1. It can update only one row at a time
  2. It can update only 100 rows at a time
  3. It can update unlimited rows at a time in bulk
  4. None of the above

Answer: C. An UPDATE can update multiple rows in one or more rows at a time based on the WHERE clause conditions.

20.Which of the following clauses decides how many rows are to be updated?

  1. SELECT
  2. WHERE
  3. FROM
  4. All of the above

Answer: B. UPDATE statement makes use of WHERE clause to capture the set of rows which needs to be updated.

21.What among the following is true about the UPDATE statement? (Choose the most appropriate answer)

  1. An UPDATE can update rows from only one table
  2. An UPDATE can update rows from multiple tables
  3. A single UPDATE command cannot affect rows in multiple tables
  4. None of the above

Answer: A, C. An UPDATE statement affects rows of only one table and not multiple tables.

Consider the following data set from the EMPLOYEES table and its structure. Answer questions 22 to 24 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
EMPLOYEE_ID FIRST_NAME   JOB_ID 
------------------- ------------------ --------
5100 	             BRUCE             CLERK
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

22. You need to change the JOB_ID for Bruce (Employee Id 7389) to &-8216;ACCOUNTANT&-8217;. Which of the following statements will you fire?

  1. UPDATE employees
    SET job_id  = 'ACCOUNTANT'
    WHERE employee_id   = 7389;
  2. INSERT INTO EMPLOYEES (employee_id  , first_name , job_id ) VALUES (5100,'BRUCE', 'ACCOUNTANT');
  3. UPDATE employees
    SET job_id  = 'ACCOUNTANT'
    WHERE job_id  = 'CLERK';
  4. UPDATE employees
    SET job_id  = 'ACCOUNTANT';

Answer: A. Option B fails because it modifies the job code of all clerks to ACCOUNTANT. Option C is wrong because it update job code to
ACCOUNTANT for all the employees in the table.

Answer the following questions 23 and 24 based on the below actions &-8211;

You issue the following query to the EMPLOYEES table with the data set as shown above.

UPDATE employees
Set job_id  = NULL
Where employee_id   = 51000;

The data set will be as shown below: (Assume that there is a duplicate value constraint on the EMPLOYEE_ID column)

EMPLOYEE_ID FIRST_NAME   JOB_ID 
------------------- ------------------ --------
5100 	             BRUCE             
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

23. Suppose you fire an UPDATE statement to update Bruce&-8217;s JOB_ID to &-8216;SALESMAN&-8217; (with respect to the data set shown above). What will be the outcome of the query?

  1. Bruce&-8217;s job code will still be NULL
  2. Bruce&-8217;s job code will be modified to &-8216;SALESMAN&-8217;
  3. ORA error
  4. No action

Answer: B. The UPDATE will add the new value to the NULL value changing the NULL to the new value

24. You issue an UPDATE statement to update the employee id 7389 to 7900. You query the employee by its id &-8216;7389&-8217; before committing the transaction. What will be the
outcome?

  1. Update will work successfully while select will show 7389.
  2. Update will work successfully while select will show no records.
  3. Constraint on EMPLOYEE_ID will not allow it to be updated
  4. It will update successfully but all the values for the EMPLOYEE_ID 7389 will become NULL.

Answer: B. A query in a session is consistent with the ongoing transactions. If the same query would have been executed in a different session, it would have shown the employee record with id 7389 because the active transaction in the first session is not yet committed.

25. What among the following is a typical use of an UPDATE statement? (Select the most appropriate answer)

  1. To retrieve a row and update one of more columns of that row
  2. To modify all the rows for some columns
  3. To modify all the rows for all the columns of a table
  4. None of the above

Answer: A. Although, the UPDATE statement can modify all column values in all rows, but typically it is used to select a row and update one or more columns.

26. Which of the following practices appropriately describe for selecting which row set to update using the UPDATE statement?

  1. If some rows are to be updated, PRIMARY KEY constraint can be used
  2. If all rows are to be updated, WHERE clause can be considered
  3. To update a set of rows use WHERE, to update all rows of a table, put a PRIMARY KEY constraint on the table
  4. None of the above

Answer: C.

27. Which of the following columns in a table are not usually updated?

  1. LONG type columns in the table
  2. LOB columns in the table
  3. A primary key column which also serves as foreign key reference in another table
  4. All of the above

Answer: C. As a common practice, the primary key columns which serve as foreign key reference in other tables, should not be updated. Though they can be updated by deferring the constraints which is usually not recommended.

Consider the following data set and structure of the EMPLOYEES table and answer the questions 28 and 29 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
EMPLOYEE_ID FIRST_NAME   JOB_ID 
------------------- ------------------ --------
5100 	             BRUCE             NULL
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

28. You issue an UPDATE statement as follows:

UPDATE employees 
SET job_id  = NULL;

What will be the outcome of the above statement?

  1. The first row of the data set will get updated to NULL
  2. The 3rd column of the first row will get updated to NULL
  3. The 3rd column of all the rows will get updated to NULL
  4. And ORA error will be thrown

Answer: C. An UPDATE statement without a WHERE clause will update all the rows of the table.

29. You issue an UPDATE statement as follows:

UPDATE employees 
SET employee_id   = NULL;
WHERE job_id  = 'CLERK';

What will be the outcome of the above statement? (Here the column EMPLOYEE_ID is marked as mandatory by putting a constraint)

  1. The first column of the data set will get updated to NULL
  2. The 3rd column of the first row will get updated to NULL
  3. The 3rd column of all the rows will get updated to NULL
  4. And ORA error will be thrown

Answer: D. The constraints on the column must be obeyed while updating its value. In the given UPDATE statement, error will be thrown because the
EMPLOYEE_ID column is a primary key in the EMPLOYEES table which means it cannot be NULL.

30. Which of the following commands can be used to remove existing records from a table?

  1. UPDATE
  2. INSERT
  3. MINUS
  4. DELETE

Answer: D. DELETE is used to remove the records from the table which can be optionally based upon a condition. Being a DML statement, it is the part of a transaction.

31. What among the following is true about the DELETE statement?

  1. The DELETE statement has to be accompanied by the WHERE clause
  2. It is not mandatory to write a WHERE clause with the DELETE statement
  3. DELETE can remove data from multiple tables at a time
  4. None of the above

Answer: B. The WHERE clause predicate is optional in DELETE statement. If the WHERE clause is omitted, all the rows of the table will be deleted.

32.What among the following happens when we issue a DELETE statement on a table? (Choose the most appropriate answer)

  1. A prompt pops up asking the user whether he/she is sure of deleting the rows requested
  2. The rows obeying the condition given in the DELETE statement are removed immediately
  3. The requested rows are removed immediately without any prompt.
  4. None of the above

Answer: C. As a part of the active or a new transaction, the rows in the table will be deleted.

33.Consider the following data set from the EMPLOYEES table and its structure:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
 EMPLOYEE_ID FIRST_NAME   JOB_ID 
------------------- ------------------ --------
5100 	             BRUCE             
5101 	             JESSICA           SALESMAN
5102 	             DEBBY             SALESMAN

You need to delete the data from the JOB_ID column in the row with employee_id 51001. Which of the following queries will be correct?

  1. UPDATE employees
    SET job_id  = NULL
    WHERE employee_id   = 51001;
  2. DELETE job_id  FROM employees
    WHERE employee_id   = 51001;
  3. DELETE FROM employees;
  4. None of the above

Answer: D. You cannot delete a particular column value for a particular row with the DELETE statement. The entire row gets deleted based on the conditions given. Unwanted values in a column can be updated to NULL. Option &-8216;A&-8217; is near but not correct in the context of the question.

34. What is the difference between the UPSERT and MERGE statements?

  1. There is no difference
  2. UPSERT is the latest term adopted for MERGE statement, which has turned obsolete
  3. UPSERT can perform delete operation which MERGE cannot
  4. MERGE does INSERT, UPDATE and DELETE, UPSERT does only UPDATE and INSERT

Answer: D. UPSERT is an obsolete statement and MERGE took over with new capabilities.

35. What is the difference between the MERGE command and the commands INSERT, UPDATE and DELETE?

  1. MERGE statement consumes more time than each operation (INSERT, UPDATE, DELETE) done separately
  2. MERGE is obsolete after Oracle 10g
  3. MERGE can perform all three operations on a table while INSERT, UPDATE and DELETE perform one operation at a time.
  4. None of the above.

Answer: C. The MERGE statement can embed all three operations on a table in a single statement while INSERT, UPDATE and DELETE perform
one operation at a time.

36. Which of the following objects can be the data source in a MERGE statement?

  1. A table only
  2. A sub-query only
  3. A table or a sub-query
  4. Both A or B

Answer: C. MERGE works well with a table or a subquery.

37. What among the following is a TRUNCATE statement equivalent to? (Choose the most suitable answer)

  1. To a DELETE statement
  2. To an UPDATE statement
  3. A DELETE statement without a WHERE clause
  4. None of the above

Answer: C. TRUNCATE deletes all the rows in one command.

38.Which of the following situations indicate that a DML operation has taken place?

  1. When new rows are added to a table
  2. When two queries are combined
  3. When a table is truncated
  4. None of the above

Answer: A. When existing rows in a table are inserted, modified or removed from a table, it is done through a DML statement.

39.Which of the following best defines a transaction?

  1. A transaction consists of DDL statements on the database schema
  2. A transaction consists of COMMIT or ROLLBACK in a database session
  3. A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session
  4. A transaction consists of collection of DML and DDL statements in different sessions of the database

Answer: C. A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL statement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or TCL (COMMIT or ROLLBACK) command. Note that DDL and DCL
commands hold auto commit feature.

40. What does a collection of DML statements that form a logical unit work known as?

  1. ACID property
  2. UNION
  3. UNION ALL
  4. Transaction

Answer: D.

41.What happens when a DML statement in an active transaction encounters an error on execution?

  1. The complete transactions is rolled back
  2. The DMLs in the transaction are mutually exclusive and hence can continue their execution
  3. The other DMLs in the transactions are interrupted and wait until the error is resolved
  4. None of the above

Answer: A. If any of the DML statement in an active transaction encounters error, the whole transaction ends up in a rollback.

42.What is true about the keyword VALUES in INSERT statements?

  1. VALUES can add multiple rows at a time during the INSERT
  2. VALUES can add only 100 rows at a time during the INSERT
  3. VALUES is mandatory to be used if we use the keyword INSERT
  4. VALUES add only one row at a time

Answer: D. The VALUES keyword is used only when the column values are explicitly specified in the INSERT statement.

Consider the following statement and the table structure. Answer the questions 43 to 45 that follow:

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
INSERT INTO departments (department_id , department_name , manager_id, location_id )
VALUES (100, 'Human Resources', 121, 1000); 

43. How many rows will be inserted by the above statement?

  1. 0
  2. 2
  3. 3
  4. 1

Answer: D. When the keyword VALUES is used, it inserts only one row at a time.

44. In which order the values will get inserted with respect to the above INSERT statement?

  1. Location_id , manager_id, department_name , department_id
  2. department_id , department_name , manager_id, location_id
  3. department_id , manager_id, department_name , location_id
  4. department_id , department_name , location_id , manager_id

Answer: B. If the columns are mentioned in the INSERT clause, the VALUES keyword should contain values in the same order

45. Suppose the above given statement is modified as below:

INSERT INTO departments VALUES (100, 'Human Resources', 121, 1000); 

What will be the outcome of this modification? Assume that the DEPARTMENTS table has four columns namely, department_id ,DEPARTMENT_NAME ,MANAGER_ID and LOCATION_ID .

  1. It will insert values into all the columns of the departments table assuming that column values are provided in the same sequence as the column in the table
  2. It will throw an ORA error because column names are not explicitly mentioned
  3. It will throw an ORA error because VALUES clause is wrongly used in the INSERT
  4. None of the above

Answer: A. Including the column names in the INSERT statement is optional provided the values must comply with the count and sequence of the
columns in the table.

46. What will be the outcome of the below INSERT statement? (Consider the table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT INTO EMPLOYEES (employee_id , hire_date) VALUES (210,"21-JUN-2013"); 
  1. It will insert only the employee_id and the hire date of the employee, leaving all other columns as blanks
  2. It will insert only the employee_id
  3. It will throw an ORA error
  4. None of the above

Answer: C. The date literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

47.What will be the outcome of the below INSERT statement? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT INTO EMPLOYEES (employee_id , first_name) VALUES (210,"Bryan"); 
  1. It will insert only the employee_id and the first name of Bryan, leaving all other columns as blanks
  2. It will insert only the first name
  3. It will throw an ORA error
  4. None of the above

Answer: C. The string literal formatting contains error. It should be enclosed within single quotation marks and not double quotation marks.

48. Suppose you need to insert the name O&-8217;Callaghan as the last name of the employees table. Which of the following queries will give you the required results? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,'O'callahan'); 
  2. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,'O"callahan'); 
  3. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,'O' 'Callahan'); 
  4. INSERT INTO EMPLOYEES (employee_id , last_name) VALUES (210,"O'callahan"); 

Answer: C.

49. What will be the outcome of the below INSERT statement? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT INTO EMPLOYEES (employee_id , first_name) VALUES ("210",'Bryan'); 
  1. It will throw a numeric value error
  2. It will insert only the employee_id and the first name of Bryan, leaving all other columns as NULL
  3. It will insert only the employee_id
  4. None of the above

Answer: A. Number values should not be enclosed within quotes.

50. What will be the outcome of the below INSERT statement? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT INTO departments VALUES (200,'Accounts', NULL, NULL); 
  1. It will throw an ORA error
  2. It will execute successfully but with wrong values might get inserted in the columns
  3. It will execute successfully
  4. None of the above

Answer: C. NULLs can be used in the VALUES clause to fill up the column values alternatively.

51. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the table structure
given)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
INSERT INTO departments VALUES (NULL, 'Accounts', NULL); 
  1. It will throw an ORA error
  2. It will execute successfully but with wrong results
  3. It will execute successfully but with correct results
  4. None of the above

Answer: A. NULL values cannot be inserted into non null columns.

52. What will be the outcome of the below INSERT statement? (Assume there is a NOT NULL constraint on the department_id column and consider the given table
structure)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
INSERT INTO departments VALUES (200, 34, NULL); 
  1. It will execute successfully but with wrong results
  2. It will throw an ORA error
  3. It will execute successfully but with correct results
  4. None of the above

Answer: B. Data type of the value mismatches with the data type of the column in the table.

53. Which of the following commands is used to save the changed data in a table permanently?

  1. ROLLBACK
  2. COMMIT
  3. INSERT
  4. UPDATE

Answer: B. The TCL command COMMIT is used to end the current active transaction in a session by making all the pending data changes permanent
in the tables.

54. Which of the following commands allows undoing the changed data?

  1. ROLLBACK
  2. COMMIT
  3. INSERT
  4. UPDATE

Answer: A. The TCL command ROLLBACK is used to end the current active transaction in a session by discarding all the pending data changes.

55. Which of the following commands allows enabling markers in an active transaction?

  1. ROLLBACK
  2. COMMIT
  3. SAVEPOINT
  4. None of the above

Answer: C. SAVEPOINT marks a point in a transaction which divides the transaction into smaller sections.

56. Which of the following commands prevents other users from making changes to a table?

  1. ROLLBACK
  2. COMMIT
  3. LOCK TABLE
  4. SAVEPOINT

Answer: C.

57. What is true about an INSERT statement which tries to insert values into a virtual column? (Choose the most appropriate answer)

  1. It cannot insert values in the Virtual column
  2. It can insert values
  3. It throws an ORA error
  4. All of the above

Answer: A. A Virtual column is a column which is always auto generated based on the derivation expression defined in the column specification. Its value cannot be explicitly inserted by the user.

58.Which of the following commands allows the user to insert multiple rows with a single statement?

  1. INSERT
  2. INSERT ALL
  3. UNION ALL
  4. None of the above

Answer: B. Bulk insert operations can be carried out using INSERT ALL.

59. Which of the following is the syntax for inserting rows through a sub-query?

  1. INSERT INTO tablename [{column_name,..}]
    subquery; 
  2. INSERT INTO tablename  VALUES [{column_name,..}]
    subquery; 
  3. Both A and B
  4. None of the above

Answer: A.

Consider the following exhibit of the EMPLOYEES table and answer the questions 60 to 63 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

60. Which of the following queries will execute successfully?

  1. UPDATE employees
    SET salary = salary + 1000
    WHERE to_char (hire_date, 'YYYY') > '2006'; 
  2. UPDATE employees
    SET salary = salary + 1000
    WHERE to_date (hire_date, 'YYYY') > '2006'; 
  3. UPDATE employees
    SET salary = salary + 1000
    WHERE hire_date > to_date (substr ('01-jan-200',8)); 
  4. UPDATE employees
    SET salary = salary + 1000
    WHERE hire_date in (to_date ('JUN 01 11', to_date ('JUL 01  11')); 

Answer: A.

61.Due to structural reorganization in the organization, you are asked to update department IDs for all the employees to NULL before the final decision is made public. Only those records should be updated which have the JOB_ID as NULL. Which of the following queries will work?

  1. UPDATE employees
    SET department_id  = NULL
    Where job_id  = NULL; 
  2. UPDATE employees
    SET department_id  = NULL
    Where job_id  = TO_NUMBER(NULL); 
  3. UPDATE employees
    SET department_id  = NULL
    Where job_id  IS NULL; 
  4. UPDATE employees
    SET department_id  = TO_NUMBER (' ', 9999)
    Where job_id  = TO_NUMBER(NULL); 

Answer: C. Use IS NULL operator to check column value for nullity.

62.You need to add a basic employee data into EMPLOYEES table. The basic data contains the last name as &-8216;Bond&-8217; and department ID as 300. Which of the following
statements will give the correct results?

  1. INSERT INTO employees (employee_id , last_name, department_id ) 
    (100,'Bond',
    (select department_id  from departments where department_id  = 300));  
  2. INSERT INTO employees (employee_id , last_name, department_id ) 
    VALUES (100,'Bond',
    (select department_id  from departments where department_id  = 300));
  3. INSERT INTO employees (employee_id , last_name, department_id ) 
    VALUES ('100','Bond',300);
  4. None of the above

Answer: B, C. Sub queries do work in INSERT statements provided they return a scalar value of data type matching or compatible to the column for which they are used.

63. You fire the following query:

DELETE FROM EMPLOYEES; 

Assuming that there are no active transactions on the EMPLOYEES table in any sessions, which of the following statements is true?

  1. It removes all the rows and structure of the table
  2. It removes all the rows which can be rolled back
  3. It removes all the rows permanently
  4. None of the above

Answer: B. Being a DML statement, the data changes due to DELETE operation are made permanent only after COMMIT is issued in the session.

64.Consider the structure of the COUNTRY table as shown:

SQL> desc countries
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 COUNTRY_ID		 NOT NULL CHAR(2)
 COUNTRY_NAME			  VARCHAR2(40)
 REGION_ID			  NUMBER

You issue the following statements in a session.

INSERT INTO COUNTRIES (1, 'Whales')
/
INSERT INTO COUNTRIES (2, 'England')
/
SAVEPOINT A;
UPDATE COUNTRIES
SET country_id= 100 where country_id= 1
/
SAVEPOINT B; 
DELETE FROM COUNTRIES where country_id= 2
/
COMMIT
/
DELETE FROM COUNTRIES where country_id= 100
/

What will happen when a ROLLBACK TO SAVEPOINT command is issued for the user session?

  1. The rollback generates an error
  2. Only DELETE statements are rolled back
  3. No SQL statement is rolled back
  4. None of the above

Answer: A, C. Since there are two savepoints &-8211; A and B, and the ROLLBACK command does specifies the actual savepoint mark, Oracle throws error.

65.If a user issues a DML command and exits the SQL Developer abruptly without a COMMIT or ROLLBACK, what will be the outcome? (Assume the session is not auto
commit)

  1. Automatic COMMIT
  2. Automatic ROLLBACK
  3. Might be a COMMIT or a ROLLBACK to end the transaction
  4. None of the above

Answer: B. When transaction is interrupted by a system failure, the entire transaction is automatically rolled back.

66. Which of the following commands / statements would end a transaction?

  1. COMMIT
  2. SELECT
  3. SAVEPOINT
  4. CREATE

Answer: A, D. Apart from TCL commands i.e. COMMIT or ROLLBACK, the DDL commands and DCL commands possess auto commit feature. The
active transaction will be committed if the DDL statement is executed in the same session.

67.When does a transaction complete?

  1. When a ROLLBACK is executed
  2. When a COMMIT is executed
  3. When TRUNCATE is executed
  4. All of the above

Answer: D. Transaction completes if a TCL, DCL or a DDL command is executed in the session.

68. Examine the given table structures and consider the following query:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
INSERT INTO EMPLOYEES (department_id ) VALUES
(select department_id  FROM departments); 

What will be the outcome of the above query?

  1. The columns in the EMPLOYEES table and the departments table do not match
  2. The WHERE clause is mandatory to be used in a sub-query
  3. The VALUES keyword cannot be used with the INSERT clause when sub-queries are used
  4. None of the above

Answer: C. Wrong usage of VALUES keyword. It must be used only when you have column data in hand, which has to be inserted in the table.

69.Examine the given table structure and consider the following query:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
UPDATE (select employee_id , job_id  from employees)
SET hire_date = '01-JAN-13'
WHERE employee_id  = (select employee_id  FROM job_history); 

Which of the following is true regarding the given query?

  1. It would not execute as we cannot use two tables in a single update statement
  2. It would not execute as UPDATE cannot use a sub-query
  3. It would execute with the restrictions on the column specified
  4. It would not execute as sub-query is used in the WHERE clause

Answer: C.

70.What happens when a transaction is committed?

  1. The changes made during the transaction are saved for a particular user session
  2. The changes made during the transaction are discarded
  3. If the transaction is a DDL, the commit doesn&-8217;t work
  4. None of the above

Answer: D. Committing a transaction saves the pending data changes permanently into the database.

71. Which of the following reasons will the best one on the usage of string?

  1. Using sub-queries
  2. Syntax errors
  3. Access permissions
  4. Constraint violations

Answer: C, B, D. References to non-existing objects / columns, Space issues might be other reasons.

72. What happens when an INSERT statement tries to insert records in an old table?

  1. All the columns will get NULL values
  2. A new table with the same name would get created automatically and the values would get inserted
  3. INSERT cannot work and it throws an ORA error
  4. None of the above

Answer: C.

73. A user named &-8216;Jonathan Adams&-8217; is able to SELECT columns from the EMPLOYEES table but he is unable to insert records into EMPLOYEES. What can be the reason?

  1. Jonathan is connected to a database which does not support INSERT statements
  2. The INSERT statement cannot be applied on the table EMPLOYEES
  3. Jonathan has access to SELECT but no access to INSERT INTO the table EMPLOYEES
  4. None of the above

Answer: C. Users can enjoy table access based on their responsibilities. One can have only read access on a table while other can enjoy read and write access.

74. Suppose 1 million rows are to be inserted into the AUDIT table. An INSERT transaction runs successfully for the first 1000 rows and an ORA error is thrown &-8216;Constraint violated&-8217;. What will happen to the values inserted in the first 1000 rows?

  1. They will remain as it is
  2. They all will get deleted
  3. They all will get rolled back
  4. None of the above

Answer: C. If any of the DML statement during the transaction encounters error(s), the complete transaction will be rolled back.

Examine the table structure and consider the following query and answer the questions 75, 76 and 77 that follow:

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
INSERT INTO departments values (15, NULL);

75. What will be the outcome of this statement?

  1. It will insert a row with department_id = 15 and all the other values as NULL
  2. It will execute successfully but insert 0 rows in the table
  3. It will throw an ORA error as the no. of columns and values do not match
  4. None of the above

Answer: C. The DEPARTMENTS table contains four columns but the INSERT statement supplies value for two columns only without mentioning the
columns too. Hence, the ORA error is thrown.

76. What is true about the above INSERT statement?

  1. If the columns are not mentioned in the INSERT statement, the values are inserted positionally in the columns
  2. It is mandatory to mention columns after the INSERT statement
  3. Both A and B
  4. None of the above

Answer: A. If the columns are not specified in the INSERT statement, Oracle sequentially and positionally maps each value to the column in the table.

77. With respect to the statement given above, what will happen if the table is altered to add a new column?

  1. The statement will still work
  2. The statement execution will throw an error as there will be a mismatch in the no. of columns and values
  3. There will be no change and the statement will execute as before
  4. None of the above

Answer: B. Since the columns were not specified earlier, the problem will still exist. Mismatch in the column-value mapping would throw an ORA error.

Examine the table structure given below and consider the following queries and answer the questions 78 and 79 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Query 1:

INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, 'ADAMS','21-DEC-12');

Query 2:

INSERT INTO employees (employee_id , last_name, hire_date)
VALUES (100, upper('ADAMS'),to_date('21-DEC-12','DD-MON-YY'));

78. Which of the above two queries is better?

  1. Both are better
  2. Only Query 1 is better
  3. Only Query 2 is better
  4. None of the queries is correct

Answer: C. Query-2 is better because it inserts date value as a date and not as a string. Though Oracle will perform implicit conversion of string literal specified as a date, but not recommended.

79. Which of the following queries is equivalent of the query 2 given above?

  1. INSERT INTO employees (employee_id , last_name, hire_date)
    VALUES (101-1, upper('ADAMS'),to_date('21-DEC-12','DD-MON-YY'));
    
  2. INSERT INTO employees (employee_id , last_name, hire_date)
    VALUES (99+1, upper('ADAMS'),to_date('22-DEC-12','DD-MON-YY') +1 );
    
  3. INSERT INTO employees (employee_id , last_name, hire_date)
    VALUES (100, upper('ADAMS'),to_date('21-DEC-12','DD-MON-YY') - 1);
    
  4. INSERT INTO employees (employee_id , last_name, hire_date)
    VALUES (100, upper('ADAMS'),to_date('28-DEC-12','DD-MON-YY')-7 );
    

Answer: A, C, D. Arithmetic operations /functions can be used to insert values as shown above.

80. You need to copy the data from one table to another table. Which of the following methods can be used?

  1. You can use the COPY command
  2. You can use the INSERT command
  3. You can use the UPDATE command
  4. None of the above

Answer: B. The direct path operations INSERT-AS-SELECT (IAS) is the most commonly used method to copy data from one table to another.

81.Which of the following statements will copy data from the JOB_HISTORY table to the JOB_HISTORY_ARCHIVE table? (Consider the table structure as given)

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
  1. INSERT INTO job_history values (select * from job_history);
  2. INSERT INTO JOB_HISTORY_ARCHIVE values (select * from job_history_archive);
  3. INSERT INTO JOB_HISTORY_ARCHIVE select * from job_history;
  4. None of the above

Answer: C. The option &-8216;C&-8217; correctly shows the usage of IAS (INSERT-AS-SELECT) method.

Examine the given table structure. Consider the following query and answer the questions 82 and 83 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT ALL
WHEN job_id   = 'SA_REP' then
INTO employees (employee_id , department_id , salary, hire_date)
VALUES (employee_id , 10, salary, hire_date)
WHEN job_id  > 'SA_REP' then
INTO employees (employee_id , department_id  , salary, hire_date)
VALUES (employee_id , 20, salary, hire_date)
SELECT employee_id , department_id , job_id, salary, commission_pct , hire_date
FROM employees
WHERE hire_date > sysdate - 30;

82. Interpret the output of the above INSERT statement.

  1. Thrown an error
  2. It will insert the records for all the employees who were hired a month before the sysdate.
  3. It will insert the records for all the employees who are Sales Representatives in department 10
  4. None of the above

Answer: B, C. INSERT ALL can make conditional inserts into the target tables.

83. Which employees&-8217; data will be inserted in the department 20?

  1. Sales Representatives
  2. Accountants
  3. Both A or B
  4. None of the above

Answer: B. As per the INSERT ALL statement, the details of employees whose job_id is not &-8216;Sales Representative&-8217;.

84. What will be the outcome of the below query? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
INSERT INTO employees (employee_id , salary) VALUES (&employee_id , &salary);
COMMIT;
  1. Syntax error as substitution variables cannot be used in DML statements
  2. The user will be prompted for entering the employee ID and the salary but substitution variables cannot insert data in the table
  3. The user will be prompted for entering the employee ID and the salary and record will be successfully created in the EMPLOYEES table
  4. None of the above

Answer: C. Substitution variables work well with the DML statements.

85. Evaluate the following SQL statements that are executed in a user session in the specified order:

CREATE SEQUENCE id_seq;
SELECT id_seq.nextval
FROM dual;
 
INSERT INTO employees (employee_id ,first_name,job_id )
VALUES (ord_seq.CURRVAL, 'Steyn','Trainee');
 
UPDATE employees
SET employee_id = id_seq.NEXTVAL
WHERE first_name = 'Steyn'
AND job_id ='Trainee';

What would be the outcome of the above statements?

  1. The CREATE SEQUENCE command would throw error because the minimum and maximum value for the sequence have not been specified
  2. All the statements would execute successfully and the employee_id column would contain the value 2 for the employee STEYN.
  3. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
  4. All the statements would execute successfully and the employee_id column would have the value 20 for the employee STEYN because the default CACHE value is 20.

Answer: B.

86. What is the restriction on the sub-query used in the UPDATE statement?

  1. The sub-query should be a multi row sub-query
  2. The sub-query should be a single row sub-query
  3. There&-8217;s no restriction
  4. The sub-query can be either a single row or a multi row sub-query

Answer: B. The sub-query should not return multiple rows when being used in an UPDATE statement

Examine the given table structure and consider the query given below and answer the questions 87 and 88 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id =7382);

87. What will be the outcome of the above query?

  1. It throws an ORA error on execution
  2. Salary of all the employees will be updated with the same salary as the employee 7382
  3. Salary of all the employees will be updated to NULL
  4. None of the above

Answer: B. Query results can be used to update the column values in a table.

88. Suppose if the employee 7382 doesn&-8217;t exist in the EMPLOYEES table. What will be the outcome of the query?

  1. It throws an ORA error on execution because query results cannot be updated to the columns
  2. Salary of all the employees will be updated to NULL
  3. ORA exception &-8216;NO_DATA_FOUND&-8217; will be raised because employee 7382 doesn&-8217;t exists
  4. None of the above

Answer: B. UPDATE statements do not raise any exception except for syntactical errors.

Examine the given table structure and consider the query given below and answer the questions 89 and 90 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
UPDATE employees
set salary = (select salary from employees where last_name = 'Adams');

89. What will be the outcome of the query?

  1. It executes successfully
  2. All the rows of the table have the same salary
  3. It might throw an ORA error &-8216;TOO_MANY_ROWS&-8217; upon execution
  4. None of the above

Answer: C. The sub-query might return more than one row causing an error.

90. What changes in the above query will make sure there are no errors caused?

  1. Use a single row function like MAX, MIN or AVG to reduce multi row results into a scalar result
  2. Adding a Primary key constraint on SALARY column
  3. No change required
  4. None of the above

Answer: A.

Examine the given table structure and consider the following query and answer the questions 91 and 92 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
UPDATE employees
SET salary = (select max (salary) from employees where last_name = 'Adams');

91. What will be the outcome of the query given above?

  1. It will update the salaries of all the employees equal to the salary of the employee named Adam
  2. It will update the salaries of all the employees equal to the average salary of all with last name as &-8216;Adam&-8217;
  3. It will update 0 rows
  4. It will update only one row

Answer: B. Arithmetic functions MAX or a MIN can be used with sub-queries to get scalar values and avoid errors.

92. Assume that the sub-query above is replaced with the following:

 SELECT distinct salary from employees where last_name = 'Adam'; 

What will be the outcome of the main query given above?

  1. It will execute successfully giving incorrect results
  2. It will execute successfully giving correct results
  3. It will throw an ORA error
  4. None of the above

Answer: C. it gives an error because as there are many with the last name as &-8216;Adam&-8217; there will many distinct salaries.

Examine the given table structure and consider the following query and answer the questions 93 and 94 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
UPDATE employees
SET salary = 50000;
WHERE job_id  in (select job_id  from job_history where department_id  = 10);

93. What will the above statement do? (Choose the most appropriate answer)

  1. It will update all the salaries for all the employees as 50000
  2. It will update all the salaries for all the employees who are in department 10
  3. It will update the salaries for all the employees who have one of the job IDs similar to those in department 10
  4. None of the above

Answer: C.

94. What will happen if the WHERE clause given above is replaced with the following?

 WHERE job_id = (select job_id from job_history where department_id  = 10);
  1. It will execute successfully with incorrect results
  2. It will execute successfully with correct results
  3. It will throw an ORA error
  4. None of the above

Answer: C. The equal sign will raise the error.

Examine the given table structure and consider the following statement. Answer the questions 95 to 97 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
DELETE FROM employees where last_name = 'A%';
COMMIT;

95. What will be the outcome of the query given above?

  1. Executes successfully but no rows are deleted
  2. All the employees whose last_name starts with &-8220;A&-8221; will be deleted
  3. ORA error because DELETE statement cannot have WHERE predicate
  4. All the rows from the employees table will get deleted

Answer: A. DELETE statement can have WHERE clause predicate. Based on conditions, the records will be removed from the table.

96. Consider the following statement:

DELETE FROM employees where employee_id  IS NULL and job_id = NULL;
COMMIT;

Assuming there is a NOT NULL constraint on the column employee_id , what will be the outcome of the above query?

  1. It will raise ORA error because of invalid WHERE predicates
  2. It will execute successfully and no rows will be deleted
  3. It will raise ORA error because multiple predicates cannot work in DELETE statements
  4. None of the above

Answer: B. Multiple predicates can be applied to the DML statements UPDATE and DELETE.

97. Consider the following query:

DELETE FROM employees where department_id  = &deptID;
COMMIT;

What will happen when the above statement is executed?

  1. It will raise error because DML statements cannot use substitution variables
  2. It will prompt for the department ID to be deleted from the user and delete the record with the given department ID
  3. It will prompt for the department ID but transaction cannot be committed
  4. None of the above

Answer: B. Substitution variables can be used with DML statements.

98. All parts of a transaction should complete or none of them. Which property of ACID rule complies with the given statement?

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability

Answer: A. ACID refers to the basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability. Atomicity implies that entire sequence of actions must be either completed or aborted. Consistency implies that the transaction takes the resources from one consistent state to another. Isolation implies that a transaction&-8217;s effect is not visible to other transactions until the transaction is committed. Durability implies that the changes made by the committed transaction are permanent and must survive system failure.

99. What does the principle of Durability in the ACID property state?

  1. It states that a database can lose completed transactions
  2. It states that a transaction cannot get completed
  3. It states that once a transaction completes, it must be impossible for the DB to lose it.
  4. None of the above

Answer: C.

100. An incomplete transaction should be invisible to all the other users. Which of the properties of the ACID state this?

  1. Isolation
  2. Consistency
  3. Atomicity
  4. Durability

Answer: A. &-8220;I&-8221; stands for Isolation.

101. What does the principle of consistency states?

  1. It states that the results of a query must be consistent with the state of the DB at the time the query started
  2. It states that an incomplete transaction should be invisible to all the other users
  3. It states that once a transaction completes, it must be impossible for the DB to lose it
  4. None of the above

Answer: A. the &-8220;C&-8221; in ACID property stands for Consistency

102. What among the following best describes a Transaction?

  1. INSERT to COMMIT/ROLLBACK
  2. UPDATE to COMMIT/ROLLBACK
  3. DELETE to COMMIT/ROLLBACK
  4. INSERT/UPDATE/DELETE to COMMIT/ROLLBACK

Answer: D.

103. A user named &-8220;Jonathan&-8221; inserts data in the table EMPLOYEES. When will the other users be able to see the new data?

  1. When Jonathan provides access permission to the users
  2. When Jonathan executes a ROLLBACK statement in the session
  3. When Jonathan executes a COMMIT statement in the same session
  4. When Jonathan opens a new session and issues a COMMIT

Answer: C. The active transaction must be committed in the same session.

104. What can be said about the nesting of transactions?

  1. Maximum 2 levels of nesting are possible
  2. Maximum 255 levels of nesting are possible
  3. Nesting of a transaction is impossible
  4. Only 1 level of nesting is possible

Answer: C.

105. Which of the following reasons will terminate a transaction?

  1. A DDL statement
  2. Exiting a client
  3. System crashes
  4. All of the above

Answer: D. DDL is auto commit and will end the ongoing active transaction.



Advertisements

SQL &-8211; Introduction

SQL &-8211; SQL Exams Syllabus

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL &-8211; The SQL SELECT Statement

SQL &-8211; Restricting and Sorting Data

SQL &-8211; Using Single-Row Functions

SQL &-8211; Conversion Functions

SQL &-8211; Conditional Expressions

SQL &-8211; Using the Group Functions

SQL &-8211; Get Data from Multiple Tables

SQL &-8211; Subqueries to Solve Queries

SQL &-8211; Using the Set Operators

SQL &-8211; Manipulating Data

SQL &-8211; Using DDL Statements

SQL &-8211; Creating Other Schema Objects

SQL Certificate &-8211; Mock Exams

SQL Certificate &-8211; Useful Resources

SQL Certificate &-8211; Discussion

UPSC IAS Exams Notes

Developer&-8217;s Best Practices

Questions and Answers

Effective Resume Writing

HR Interview Questions

Computer Glossary

Who is Who