SQL QUIZ with Answer Part 4.
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.
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
Let’s start the quiz with Questions and Answers:
1. Which statement is true a drop table command is executed on a table?
A. Only a DBA can execute the drop table command.
B. Any appending transactions on the table are rolled back.
C. The structure of the table remains in the database and the indexes are
deleted.
D. The drop table command can be executed on a table on which there are
pending transactions.
E. The table structure and its deleted data can't be rolled back and restored
once the drop table command is executed.
Answer: E
Explanation:
Because after the DROP TABLE command you cannot roll back
Deleted data. It's possible only using recovery procedures.
2. Examine the structure of STUDENTS table;
Name Null Type
STU_ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHER2(50)
GRADUATION DATE
What statement adds a new column after NAME Column to hold phone numbers?
A. ALTER TABLE student
ADD COLUMN3(phone varchar2(9))
B. ALTER TABLE student
ADD COLUMN3(phone varchar2(9)) AS COLUMN3;
C. ALTER TABLE student
ADD COLUMN3(phone varchar2(9)) POSITION 3;
D. You can't specify position when new column is added.
Answer: D
Explanation:
Because it's impossible to specify position where new column will be
added after table creation.
3. Examine the code:
DECLARE.
CURSOR emp_cursor IS
SELECT ename,deptno
FROM emp;
emp_rec emp_cursor %ROWTYPE
BEGIN
OPEN emp_cursor
LOOP
FETCH emp_cursor
INTO emp_rec
EXIT WHEN emp_cursor NOT FOUND;
INSERT INTO temp_emp(name'dno)
VALUES(emp_rec.ename,emp_rec deptno);
END LOOP;
CLOSE emp_cursor;
END;
Using a cursor FOR loop,which PL/SQL block equivalent to the above code?
A. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
END;
B. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
OPEN emp-cursor;
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp_rec.deptno);
END LOOP
END;
C. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
BEGIN
FOR emp-rec IN emp-cursor LOOP
OPEN emp-cursor;
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
CLOSE emp-cursor;
END;
D. DECLARE
CURSOR emp-cursor 1S
SELECT ename,dept no
FROM emp;
emp-rec emp-cursor%ROWTYPE;
BEGIN
FETCH emp-cursor
INTO emp-rec;
FOR emp-recIN emp-cursor LOOP
INSERT INTO temp-emp(name,dno)
VALUES (emp-rec.ename,
emp-re.deptno);
END LOOP
END;
Answer: A
Explanation:
Because for cursor for loop you don't need explicitly open cursor, fetch data and close cursor, it works automatically by definition of a cursor for loop.
4. Under which situation it is necessary to use an explicit cursor?
A. When any DML or select statement is used in a PL/SQL block?
B. When a delete statement in a PL/SQL block deletes more than one row.
C. When a select statement in a PL/SQL block is more than one row.
D. When an update statement in a PL/SQL block has to modify more than one
row.
Answer: C
Explanation:
Because only an explicit cursor will be able to handle situation when statement returns more than one row. In all other situations every SQL statement executes in an implicit cursor, including UPDATE, INSERT, DELETE DML statements and SELECT statement.
5. Which data dictionary view contains the definition of a view?
A. MY_VIEWS.
B. USER_VIEWS.
C. SYSTEM_VIEWS.
D. USER_TAB_VIEWS.
Answer: B
Explanation:
Because the USER_VIEWS contains the definition of a view.
6 . Which statement about using a sub query in the from clause is true?
A. You can't use a sub query in the from clause.
B. You eliminate the need to create a new view or table by placing a sub query
in the from clause.
C. You eliminate the need to grant select privileges on the table used in the
from clause sub query.
D. You define a data source for future select statement when using a sub query
in the from clause.
Answer: B
Explanation:
Answer B is correct because you don't need to create a new view or table, join them with a table or view in main query and extract result from join construction.
7. The employee table has ten columns. Since you often query the table with condition
based on four or more columns, you created an index on all the columns in the table.
Which result will occur?
A. Updates on the table will be slower.
B. The speed of inserts will be increased.
C. All queries on the table will be faster.
D. The size of the employee table will be increased.
Answer: A
Explanation:
Because if you use index built on ALL columns each DML operation(INSERT, UPDATE, DELETE) will be performed slower than without index. Index will be rebuilt after each DML operation. If table is large it can cause performance issues with this table.
8. Evaluate this PL/SQL block:
BEGIN
FROM i IN 1. . 5 LOOP
IF i=1 THEN NULL;
ELSIF i=3 THEN COMMIT;
ELSIF 1=5 THEN ROLLBACK;
ELSE INSERT INTO test (results);
VALUES(i);
END IF;
END LOOP;
COMMIT;
END;
How many values will be permanently inserted into the TEST table?
A. 0.
B. 1
C. 2
D. 3
E. 5
F. 6
Answer: B
Explanation:
Answer B is correct because only one value will be inserted into the TEST table. First iteration in loop will do nothing because of NULL operator. Second one will insert value into table. Third will commit change. Forth will insert second value, but fifth iteration will rollback last transaction. So it will be just one value inserted into the table.
9. Which script would you use to query the data dictionary to view only the names of the
primary key constraints using a substitution parameter for the table name?
A. ACCEPT TABLE PROMPT('table to view primary key constraint:')
SELECT constraint_name
FROM user_constraint
WHERE table_name=upper('&table') AND constraint_type= 'P';
B. ACCEPT TABLE PROMPT('table to view primary key constraint:')
SELECT constraint_name
FROM user_constraint
WHERE table_name=upper('&table') AND constraint_type=
'PRIMARY';
C. ACCEPT TABLE PROMPT('table to view primary key constraint:')
SELECT constraint_name,constraint_type
FROM user_constraint
WHERE table_name=upper('&table');
D. ACCEPT TABLE PROMPT('table to view primary key constraint:')
SELECT constraint_name
FROM user_cons_columns
WHERE table_name=upper('&table') AND constraint_type= 'P';
Answer: A
Explanation:
Because this syntax is correct to display only the names of the primary key constraints, usage of condition constraint_type = 'P' in the WHERE clause is correct. Information can be found in the USER_CONSTRAINT table.
10. Which statement would you use to add a primary key constraint to the dotc table using the id_number column immediately enabling the constraint?
A. This task can't be accomplished.
B. ALTER TABLE dotc
ADD CONSTRAINT dotc_id_pk PRIMARY KEY(id_number);
C. ALTER TABLE dotc
ADD (id_number CONATRAINT dotc_id_pk PRIMARY KEY);
D. ALTER TABLE dotc
MODIFY(id_number CONSTRAINT dotc_id_pk PRIMARY KEY);
Answer: B
Explanation:
Because this statement will add a primary key constraint to the patient table and immediately enable it.
11. Given the cursor statement:
DECLARE
CURSOR query_cursor(v_salary)IS
SELECT last_name,salary,dept_no
FROM employee
WHERE SALARY>v_salary;
Why does this statement cause an error?
A. The parameter mode is not defined.
B. A where clause is not allowed in a cursor statement.
C. The into clause is missing from the select statement.
D. A scalar data type was not specified for the parameter.
Answer: D
Explanation:
Because a scalar data type have to be specified in v_salary definition for cursor query_cursor.
12. Which statement describes the use of a group function?
A. A group function produces a group of results from one row.
B. A group function produces one result from each row in the table.
C. A group function produces one result from many rows per group.
D. A group function produces many results from many rows per group.
Answer: C
Explanation:
Because a group function produces one result from all rows, which have been included in one row.
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 and SQL quiz part 3 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