SQL QUIZ with Answer Part 1.
Hello Everyone,
Today I will start SQL quiz part 1 , 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 blogs from archive folder May and June for your reference.
Dheeraj60.blogspot.com
Let’s start with Quiz:
1. The employee table contains these columns:
Last_name Varchar (25)
First_name Varchar (25)
Salary INT
You need to display the names of employees on more than an average salary of all employees. Evaluate the SQL statement.
SELECT, LAST_NAME, FIRST_NAME from employee where salary< avg(salary);
Which change should you make to achieve the desired results?
A. Change the function in the Where clause.
B. Move the function to the select clause and add a group clause.
C. Use a sub query in the where clause to compare the average salary value.
D. Move the function to the select clause and add a group by clause and a having
clause.
Answer: C
Explanation:
Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause.
2. How would you add a foreign key constraint on the dept_no column in the EMP
table. Referring to the ID column in the DEPT table?
A. Use the ALTER TABLE command with the ADD clause in the DEPT table.
B. Use the ALTER TABLE command with the ADD clause on the EMP table.
C. Use the ALTER TABLE command with the MODIFY clause on the DEPT
table.
D. Use the ALTER TABLE command with the MODIFY clause on the EMP table.
E. This task cannot be accomplished.
Answer: B
Explanation:
Answer B is correct because constraint will be created for EMP table using ALTER
TABLE command.
3. You need to update employee salaries if the salary of an employee is less than 1000. The salary needs to be incremented by 10%. Use SQL*Plus substitution variable to accept the employee number. Which PL/SQL block successfully updates the salaries?
A. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
INTO Sal := Sal*1.1
WHERE empno = and p_empno;
END IF;
END;
B. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
SAL := SAL * 1.1;
END IF;
END;
C. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Sal := Sal*1.1
WHERE empno = and p_empno;
END IF;
END;
D. Declare
V_sal emp.sal % TYPE;
Begin
SELECT Sal
INTO V_sal
FROM emp
WHERE empno = and P_empno;
IF (V_Sal<1000) THEN
UPDATE emp
Set Sal := Sal*1.1
WHERE empno = and p_empno;
END IF;
END;
Answer: D
Explanation:
Answer D is correct because it's uses cursor and IF-THEN structure correctly to increase salary for all employees with current salary less than 1000.
4. Which statement about SQL is true?
A. Null values are displayed last in the ascending sequences.
B. Data values are displayed in descending order by default.
C. You cannot specify a column alias in an ORDER BY clause.
D. You cannot sort query results by a column that is not included in the SELECT
List.
E. The results are sorted by the first column in the SELECT list, if the ORDER BY
clause is not provided.
Answer: A
Explanation:
Answer A is correct because of null values are displayed last in the ascending sequences.
5. Written a PL/SQL loop, you need to test if the current FETCH was successful. Which
SQL cursor attribute would you use to accomplish this task?
A. SQL % ISOPEN
B. SQL % ROWCOUNT
C. SQL % FOUND
D. This task cannot be accomplished with a SQL cursor attribute.
E. A SQL cursor attribute cannot be used within a PL/SQL loop.
Answer: C
Explanation:
Answer C is correct because of SQL%FOUND attribute returns TRUE if current FETCH have been successful.
6. The structure of the DEPT table is as follows:
NAME NULL TYPE
Deptno Not Null NUMBER(2)
Dname VARCHAR2(14)
Loc VARCHAR2(13)
Examine the code
Declare
Type dept_record_type is record
(dno NUMBER, name VARCHAR2(20));
dept_rec dept_record;
Begin
Select deptno, dname
INTO dept_rec
FROM dept
WHERE deptno=10;
END
Which statement displays the name of selected department?
A. DBMS_OUTPUT.PUT_LINE (name);
B. DBMS_OUTPUT.PUT_LINE (dname);
C. DBMS_OUTPUT.PUT_LINE (dept_rec.name);
D. DBMS_OUTPUT.PUT_LINE (dept_rec.dname);
E. DBMS_OUTPUT.PUT_LINE (dept_rec (name));
Answer: C
Explanation:
Answer C is correct because it shows field NAME for record DEPT_REC of
DEPT_RECORD type.
7. In SQL you issued this command:
Delete from dept where dept_id=900
You received an integrated constraint error because the child record was found.
What could you do to make the statement execute?
A. Delete the child record first.
B. You cannot make the command execute.
C. Add a fourth keyword to the command.
D. Add the constraints cascade option to the command.
Answer: A
Explanation:
Answer A is correct because you need first delete the child record in dependable table for avoid foreign constraint violation.
.
8. Evaluate this IF statement.
IF v_value>100 THEN
v_new-value:=2*v-value;
ELSIF v-value>200 THEN
v-new-value:=3*v-value;
ELSIF v-value>300 THEN
v-new-value:=4*v-value;
ELSE
v-new-value:=5*v-value;
END IF
What would be assigned to v_new_value if v_value=250?
A. 250
B. 500
C. 750
D. 1000
Answer: B
Explanation:
Answer B is correct because first IF condition v_value > 100 will be TRUE if v_value have been assigned with new value equal 250. Result of 2*v_value is 500.
9. You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?
A. A cursor for loop.
B. A multiple selection cursor.
C. A cursor for each active set.
D. A cursor that uses parameters.
Answer: D
Explanation:
Answer D is correct because a cursor with parameters can be used several times in a block, selecting active set each time that it was opened depending on parameters' values.
10 . Which statement is true when writing a cursor for loop?
A. You must explicitly fetch the rows within a cursor for loop.
B. You must explicitly open the cursor prior to the cursor for loop.
C. You must explicitly close the cursor prior to the end of program.
D. You do not explicitly open, fetch or close a cursor within a cursor for loop.
E. You must explicitly declare the record variable that holds the row returned from the cursor.
Answer: D
Explanation:
Answer B is correct because when using a cursor for loop you don't need explicitly open, fetch or close a cursor (by definition of a cursor for loop).
Please go this blog carefully and see all the 10 questions as I provided the Answers also let me know if you have any doubts. This type of questions mostly asked in OCA exams and also good practice for you to understand this. For reference you can see my blogs related to SQL and PL/SQL. In next blog I will come up with another set of questions with Answers related to OCA paper.
Thanks.
Comments
Post a Comment