SQL QUIZ with Answer Part 5.
Hello Everyone,
Today I will start SQL Quiz part 4, 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 as well as good for you to check your SQL and PL/SQL skills. also Good for Candidate who is preparing for interview.
Before part 3 please explore my below blogs SQL Quiz Part1 SQL Quiz Part2 , SQL Quiz Part3 and SQL Quiz Part4 .
https://dheeraj60.blogspot.com/2020/07/sql-quiz-with-answer-part-3-dotcmdb.html
https://dheeraj60.blogspot.com/2020/07/sql-quiz-with-answer-part-2-dotcmdb.html
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-4-dotcmdb.html
Let’s start the quiz with Questions and Answers:
1. The employee table contains three columns:
BONUS NUMBER(7,2)
DEPT_ID NUMBER(9)
There are three departments and each department has at least one employee bonus
values at least one employee. Bonus values are greater than 500;not all employee
receive a bonus.
Evaluate this PL/SQL block:
DECLARE
v_bonus employee.bonus%TYPE:=300;
BEGIN
UPDATE employee
SET bonus=bonus+v_bonus
WHERE dept_id IN (10,20,30);
COMMIT;
END;
What will be the result?
A. All the employees will be given a 300 bonus.
B. A subset of 300 employees will be given a 300 bonus.
C. All employees will be given a 300 increase in bonus.
D. A subset of employees will be given a 300 increase in bonus.
Answer: D
Explanation:
Because only employees from departments 10, 20, 30 will receive a 300 increase in bonus.
2. You have been granted update privileges on the name column of the employee table. Which data dictionary view would you query to display the column . The privileges were granted on the schema that owns the employee table?
A. ALL_TABLES.
B. ALL_SOURCES.
C. ALL_OBJECTS.
D. TABLE_PRIVILEGES.
E. ALL_COL_PRIVS_RECD.
F. This information can't be retrieved from a single data dictionary view.
Answer: E
Explanation:
Because ALL_COL_PRIVS_RECD contains information about column for which you have been granted update privileges.
3. Which alter command would you use to reinstate a disabled primary constraint?
A. ALTER TABLE
ENABLE PRIMARY KEY(ID)
B. ALTER TABLE CARS
ENABLE CONSTRAINT cars_id_pk.
C. ALTER TABLE CARS
ENABLE PRIMARY KEY(id)CASCADE;
D. ALTER TABLE CARS
ADD CONSTRAINT cards_id_pk PRIMARY KEY(id);
Answer: B
Explanation:
Because this statement will enable the PRIMARY KEY.
4. You need to perform a major update on the employee table. You have decided to disable the primary key constraint on the empid column and the check constraint on the job column. What happens when you try to enable the constraint after the update is completed?
A. You need to recreate the constraints once they are disabled.
B. Any existing rows that do not conform with the constraints are automatically deleted.
C. Only the future values are verified to confirm with the constraints having
the existing values unchecked.
D. The indexes on both the columns with the primary key constraint and the
check constraints are automatically recreated.
E. All the existing columns values are verified to confirm with the constraints
and an error message is narrated if any existing values is not conformed.
Answer: E
Explanation:
Answers E is correct because it will be error if any existing values (old and updated) will violate the PRIMARY KEY or the CHECK constraints.
5. Examine the declaration section:
DECLARE
CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2)
IS
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO=p_deptno
AND JOB=p_job;
BEGIN
. . .
Which statement opens the cursor successfully?
A. OPEN emp_coursor.
B. OPEN emp_cursor('clerk',10);
C. OPEN emp_cursor(10, 'analyst');
D. OPEN emp_cursor (p_deptno,p_job);
Answer: C
Explanation:
Because the statement uses correct data types (NUMBER and VARCHAR2) for the parameters of cursor.
6. You want to display the average salary for the departments 20 and 50 but only if those departments have an average salary of at least 2000. Which statement will produce the required results?
A. SELECT deptno, AVG(sal)
FROM emp
WHERE depno IN(20,50)
GROUP BY deptno
HAVING AVG (sal)>=2000;
B. SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG (sal)>=2000;
Deptno IN (20,50);
C. SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20,50)
AND AVG (sal)>=2000
GROUP BY deptno;
D. SELECT deptno, AVG(sal)
FROM emp
WHERE deptno IN (20,50)
GROUP BY AVG(sal)
HAVING AVG(sal)>=2000
Answer: A
Explanation:
Answers A is correct because this statement will display correct results using the WHERE, the GROUP BY, the HAVING clauses and function AVG.
7. You want to retrieve the employee details from the emp table and process them in
PL/SQL block. Which type of variable do you create in the PL/SQL block to retrieve
all the rows and columns using a single select statement from the emp table?
A. PL/SQL record.
B. %ROWTYPE variable.
C. PL/SQL table of scalars.
D. PL/SQL table of records.
Answer: D
Explanation:
Because to retrieve ALL the rows and columns you need to use PL/SQL table of records.
8. Which statement shows the view definition of the view emp_view that is created based on the emp table?
A. DESCRIBE emp
B. DESCRIBE view emp_view
C. SELECT TEXT
FROM user_views
WHERE view_name= 'EMP_VIEW';
D. SELECT view_text
FROM my_views
WHERE view_name= 'EMP_VIEW';
E. SELECT view_text
FROM table emp
WHERE view_name= 'EMP_VIEW';
Answer: C
Explanation:
Answer C is correct because this statement uses USER_VIEWS with correct condition in the WHERE clause.
9. You attempt to query the data base with this command:
SELECT name,salary
FROM employee
WHERE salary=
(SELECT salary
FROM employee
WHERE last_name= 'Ashish' OR dept_no=20)
Why could this statement cause an error?
A. Sub queries are not allowed in the where clause.
B. Logical apparatus are not allowed in where clause.
C. A multiple row sub query used with a single row comparison operator.
D. A single row query is used with a multiple row comparison operator.
Answer: C
Explanation:
Because sub query can return many rows, not only one, so usage of a single row comparison operator will cause an error.
10 . Evaluate these two SQL Commands
1 SELECT distinct object_type
FROM user_object;
2 SELECT object_type
FROM all_objects;
How will the results differ?
A. Statement1 will display the distinct object types in the database.
Statement2 will display all the object types in the database.
B. Statement1 will display the distinct types owned by the user.
Statement2 will display all the object types in the database.
C. Statement1 will display the distinct object type owned by the user.
Statement2 will display the object types the user can access.
D. Statement1 will display the distinct object types that user can access.
Statement2 will display all the object types that the user owns.
Answer: C
Explanation:
Answer C is correct because DISTINCT keyword is used to eliminate duplicates in results, statement1 will show the distinct object type for objects owned by user (viewUSER_OBJECTS), statement2 will show all object types (with duplicates) for all objects which can be accessed by user (view ALL_OBJECTS).
11. Examine the Below code :
SELECT employee.ename
FROM employee
WHERE employee.empno NOT IN
(SELECT manager.mgr
FROM emp manager);
What is not in operator equivalent to the above query?
A. !=
B. ALL.
C. !=ALL.
D. NOT LIKE.
Answer: C
Explanation:
Because operator !=ALL does not exist
12. Examine the structure of the department and employee table.
There points a line from id PK to dept_id
DEPARTMENT
id PK Name
EMPLOYEE
id PK Last_ name First_name Dept_id
Evaluate this SQL statement:
CREATE INDEX emp_dept_id_idx
ON employee(dept_id);
Which result will this statement provide?
A. Store and index in the employee table.
B. Increase the chance of full table scans.
C. May reduce the amount of disk I/O for select statement.
D. May reduce the amount of disk I/O for insert statement.
E. Override the unique index created when the PK relation was defined.
Answer: C
Explanation:
Because by creating index on DEPT_ID column you will reduce the I/O reads for select statement. Oracle will use index without full scan of DEPARTMENT table.
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 , SQL quiz part 2 ,SQL quiz part 3 and SQL quiz part 4 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 other interesting topics for candidate who wants to excel his/her carrier in IT industry.
Thanks.
Comments
Post a Comment