SQL QUIZ with Answer Part 3.
Hello Everyone,
Today I will start SQL quiz part 3 , this is important for those guys who is preparing for OCA(Oracle certified Associate) certification. I suggest you to go through my previous Oracle SQL and PL/SQL blogs in Archive folder for your reference.
Note: This Type of SQL and PL/SQL questions asked in Oracle Certification exams.
Before part 3 please explore my below blogs SQL quiz Part1 and SQL quiz Part2.
https://dheeraj60.blogspot.com/2020/07/sql-quiz-practice-for-oca-with-answer.html
https://dheeraj60.blogspot.com/2020/07/sql-quiz-with-answer-part-2-dotcmdb.html
Let’s start the quiz with Questions and Answers:
1. You need to create a PL/SQL program to insert records into employee table.
Which block of code successfully uses the insert command?
A. DECLARE
v_hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp, ename, hiredate, deptno)
VALUES(empno_sequence.nextval, 'and name',v_hirerdate and deptno)
B. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,hiredate,deptno)
C. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,hiredate)
VALUES(empno_sequence.nextval, name, v_hirerdate)
END:
D. DECLARE
v-hiredate DATE:=SYSDATE:
BEGIN
INSERT INTO emp(empnp,ename,heridate,deptno)
VALUES(empno_sequence.nextval, 'and name',v_herdate and deptno)
Job=Clerk
END:
Answer: C
Explanation:
Because expression EMPNO_SEQUENCE.NEXTVAL will calculate next value for EMPNP column automatically after each insert.
2. Evaluate this PL/SQL block.
BEGIN
FOR i IN 1..10 LOOP
IF I=4 OR I=6 THEN null;
ELSE
INSERT INTO test(result)
VALUES (I) ;
END IF;
COMMIT;
END LOOP;
ROLL BACK;
END.
How many values will be inserted into the TEST table?
A. 0
B. 4
C. 6
D. 8
E. 10
Answer: D
Explanation:
Because loop will be executed 10 times, but 2 times IF-THEN Condition will not allow to insert 2 values into TEST table, so result is 8.
3. You issue below command:
CREATE public synonym EMP for dotc.employee;
Which task has been accomplished?
A. The object can now be accessed by all the users.
B. All users were given object privileges to the table.
C. The need to qualify the object name with its schema is eliminated only for
you.
D. The need to qualify the object name with its schema is eliminated for all users.
Answer: D
Explanation:
Because the public synonym will be created for table EMPLOYEE of dotc owner. After that other users will not need to use object owner prefix to access data inside this table.
4. You attempt to query to the database with this command:
SELECT dept_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data))
FROM employee WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60
GROUP BY by dept_no
ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date));
Why does this statement cause an error?
A. A select clause cannot contain a group function.
B. A where clause cannot be used to restrict groups.
C. An order by clause cannot contain a group function.
D. A group function cannot contain a single row function.
Answer: B
Explanation:
Because function AVG cannot be used in a WHERE clause of SELECT statement.
5. How do you send the output of your SQL* Plus session to a text operating system file called MYOUTPUT.LST?
A. SAVE MYOUTPUT.LST
B. SPOOL MYOUTPUT.LST
C. PRINT MYOUTPUT.LST
D. SEND MYOUTPUT.LST
Answer: B
Explanation:
Because command SPOOL is used in SQL * Plus to send output of the
Session to a text operation system file.
6. In which order does the Oracle Server evaluate clauses?
A. HAVING, WHERE, GROUP BY
B. WHERE, GROUP BY, HAVING
C. GROUP BY, HAVING, WHERE
D. WHERE, HAVING, GROUP BY
Answer: B
Explanation:
Because the Oracle server first evaluate WHERE clause to reduce number of rows that need to be processed, than GROUP BY clause and after that HAVING clause.
7. In the declarative section of a PL/SQL block, you created but did not initialize a
number variable. When the block executes what will be the initial value of the
variable?
A. 0.
B. Null.
C. It depends on the scale and precision of the variable.
D. The block will not execute because the variable was not initialized.
Answer: B
Explanation:
Because if a number variable is not initialized it has NULL value by default.
8. When selecting data which statement is valid about projection?
A. Projection allows due to choose rows.
B. Projection allows due to choose columns.
C. Projection allows due to joined tables together.
D. Projection allows due to add columns to a table.
Answer: B
Explanation: Because projection is used to choose columns for data selecting.
9. Structure of DEPT table is as follows:
Name Null Type
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Examine the declaration section.
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY INTEGER
dept_table dept_table_type;
You need to assign LOC file in record 15 the value of 'Atlanta'. Which PL/SQL
statement makes this assignment?
A. dept_table.loc.15 := 'Atlanta';
B. dept_table[15].loc := 'Atlanta';
C. dept_table(15).loc := 'Atlanta';
D. dept_table_type(15).loc := 'Atlanta';
Answer: C
Explanation:
Because this syntax is correct to assign LOC file in record 15.
10. Given the executable section of a PL/SQL block
FROM employee_record IN Salary_Cursor Loop
employee_id_table (employee_id):=
employee_record.last_name;
END Loop
Close Salary_Cursor;
END;
Why does this section cause an error?
A. The cursor needs to be opened.
B. Terminating conditions are missing.
C. No FETCH statements were issued.
D. The cursor does not need to be explicitly closed.
Answer: D
Explanation:
Because cursor does not need to be closed if it's cursor for loop. The cursor for loop opens, parses and executes the cursor automatically.
11. To remove all the data form employee table while leaving the table definition intact.
You want to be able to undo this operation. How would you accomplish this task?
A. DROP TABLE employee.
B. DELETE FROM employee.
C. TRUNCATE TABLE employee.
D. This task can't be accomplished.
Answer: B
Explanation:
Because using DELETE FROM statement you just delete all rows in the table while leaving the table definition intact. Also DELETE FROM statement is DML operation, so it can be rolled back if you want to undo this operation.
12. Which statement is true about nesting blocks?
A. Variable name must be unique between blocks.
B. A variable defined in the outer block is visible in the inner blocks.
C. A variable defined in the inner block is visible in the outer blocks.
D. A variable is in an inner block may have the same name as a variable in an
outer block only if the data types are different.
Answer: B
Explanation:
Answer B is correct because a variable defined in the outer block is visible and can be used in the inner block.
Please go through this Blog carefully and let me know if you have any doubts in questions. Also go through my previous blog SQL quiz part 1 and SQL quiz part2 for questions related to SQL and PL/SQL which commonly asked in OCA exam. For more understanding you can go through my topics of each section which is available in my Archive May and June folder. In next Blog I will come up with more questions which commonly asked in OCA exams/certification and also this type of questions is useful for you for preparing interview as well.
Thanks.
Comments
Post a Comment