SQL Quiz-2 By "The Cloud9"
-
When creating a function in SQL *Plus, you receive this message: “Warning: Function created with compilation errors.” Which command can you issue to see the actual error message?
-
SHOW FUNCTION_ERROR
-
SHOW USER_ERRORS
-
SHOW ERRORS
-
SHOW ALL_ERRORS
-
SQL Quiz-2 by 'The Cloud9' assesses advanced SQL skills through practical questions on table structures, data manipulation, and procedural SQL. It's designed for learners to enhance their database management capabilities.
Quiz Preview
- 2.
Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?
-
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
-
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
-
CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;
-
CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;
Correct Answer
A. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);Explanation
The correct answer is "CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120)." This statement creates a view called emp_Vu that allows the user to insert rows. It selects the employee_id, emp_name, job_id, and department_id columns from the employees table and filters the results to only include rows where the mgr_id is either 102 or 120. This allows the user to insert rows into the view that meet these criteria.Rate this question:
-
- 3.
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
-
Immediately after the SELECT clause
-
Before the WHERE clause
-
Before the FROM clause
-
After the ORDER BY clause
-
After the WHERE clause
Correct Answer
A. After the WHERE clauseExplanation
The GROUP BY clause is placed after the WHERE clause in a SELECT statement. This is because the WHERE clause is used to filter the rows before grouping them, and the GROUP BY clause is used to group the rows based on specified columns. By placing the GROUP BY clause after the WHERE clause, the query ensures that the filtering is done before the grouping operation is performed.Rate this question:
-
- 4.
You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?
-
FOR EACH ROW trigger on the EMP table.
-
Statement-level trigger on the EMP table.
-
FOR EACH ROW trigger on the AUDIT_TABLE table.
-
Statement-level trigger on the AUDIT_TABLE table.
Correct Answer
A. FOR EACH ROW trigger on the EMP table.Explanation
A FOR EACH ROW trigger on the EMP table is the correct answer because it specifies that the trigger should be executed for each row that is changed in the EMP table. This means that whenever a row is inserted, updated, or deleted in the EMP table, the trigger will be triggered and the information will be placed into the AUDIT_TABLE. This ensures that every change made to the EMP table is audited in real-time.Rate this question:
-
- 5.
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?
-
UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;
-
UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees) WHERE employee_id =180;
-
UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);
-
UPDATE new_employees SET name = (SELECT last name|| first_name FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id =180;
Correct Answer
A. UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id =180;Explanation
The correct answer is the first option: UPDATE new_employees SET name = (Select last_name||first_name FROM employees Where employee_id =180) WHERE employee_id =180. This statement updates the name column in the new_employees table with the concatenation of the last_name and first_name columns from the employees table, where the employee_id is 180. It then specifies that this update should only be applied to the row with an employee_id of 180 in the new_employees table.Rate this question:
-
- 6.
In which case would you use a FULL OUTER JOIN?
-
Both tables have NULL values.
-
You want all unmatched data from one table.
-
You want all matched data from both tables.
-
You want all unmatched data from both tables.
-
You want all matched and unmatched data from only one table.
Correct Answer
A. You want all matched data from both tables.Explanation
A FULL OUTER JOIN is used when you want to retrieve all the matched data from both tables. This means that you want to include all the rows from both tables, even if they do not have a match in the other table. In this case, you are not concerned with the unmatched data or the NULL values, but rather want to combine all the matched data from both tables.Rate this question:
-
- 7.
You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?
-
SYS privileges
-
Your privileges
-
Public privileges
-
User A’s privileges
Correct Answer
A. Your privilegesExplanation
When you create a procedure, it will be executed under the privileges of the creator, unless the
procedure has the following statement AUTHID CURRENT_USER. If you specify AUTHID
CURRENT_USER, the privileges of the current user are checked at run time, and external
references are resolved in the schema of the current user. Like this example
SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table varchar2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE;
6 END;Rate this question:
-
- 8.
Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?
-
SELECT dept_id, MIN(salary(, MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000;
-
SELECT dept_id, MIN(salary), MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000 GROUP BY dept_id;
-
SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
-
SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX(salary) < 15000;
-
SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id, salary HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Correct Answer
A. SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX(salary) < 15000;Explanation
The correct answer is the fourth option: SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary) < 5000 AND MAX(salary) < 15000. This statement correctly selects the department ID, minimum salary, and maximum salary from the employees table. The GROUP BY clause groups the results by department ID, and the HAVING clause filters the results to only include departments where the minimum salary is less than 5000 and the maximum salary is less than 15000.Rate this question:
-
- 9.
The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?
-
SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;
-
SELECT COUNT(gpa) FROM student grades WHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
-
SELECT AVG(gpa) FROM student_grades
-
SELECT MEDIAN(gpa) FROM student_grades WHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;
Correct Answer
A. SELECT AVG(gpa) FROM student_gradesExplanation
The correct answer is "SELECT AVG(gpa) FROM student_grades". This statement calculates the average grade point average (GPA) for all students in the STUDENT_GRADES table. It does not specify any conditions for the semester_end column, so it will include all semesters in the calculation.Rate this question:
-
- 10.
Given a function CALCTAX: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?
-
You need to execute the command CALCTAX(1000);.
-
You need to execute the command EXECUTE FUNCTION calctax;.
-
You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.
-
You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;
-
You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
Correct Answer
A. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);Explanation
When you call a function from SQL*PLUS you need to assign the returned value a bind variable,
and you need the EXECUTE command to execute the function.Rate this question:
-
- 11.
Which two statements about packages are true? (Choose two)
-
Packages can be nested.
-
You can pass parameters to packages.
-
You can pass parameters to packages.
-
The contents of packages can be shared by many applications.
-
You can achieve information hiding by making package constructs private.
Correct Answer(s)
A. The contents of packages can be shared by many applications.
A. You can achieve information hiding by making package constructs private.Explanation
Explanation
Actually theses are some of the advantages of the package, sharing the package among
applications and hide the logic of the procedures and function that are inside the package by
declaring them in the package header and write the code of these procedures and functions
inside the package body.
Incorrect Answers:
A: Packages can not be nested
B: Parameters can't be passed to a package; parameters can be passed to procedures and
functions only.
C: By the first time you call a procedure, function, or reference a global variable within the
package, the whole package will be loaded into the memory and stay there, so when ever you
need to reference any of the package's constructs again you will find it in the memory.Rate this question:
-
- 12.
Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT ‘placeholder’, p_location VARCHAR2 DEFAULT ‘Boston’) IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure? (Choose three)
-
Add_dept;
-
Add_dept(‘Accounting’);
-
Add_dept(, ‘New York’);
-
Add_dept(p_location=>’New York’);
Correct Answer(s)
A. Add_dept;
A. Add_dept(‘Accounting’);
A. Add_dept(p_location=>’New York’);Explanation
The first valid call to the add_dept procedure is add_dept;, which uses the default values for both parameters. The second valid call is add_dept(‘Accounting’);, which provides a value for the p_dept_name parameter and uses the default value for the p_location parameter. The third valid call is add_dept(p_location=>’New York’);, which uses the default value for the p_dept_name parameter and provides a value for the p_location parameter.Rate this question:
-
- 13.
Which two programming constructs can be grouped within a package? (Choose two)
-
Cursor
-
Constant
-
Trigger
-
Sequence
-
View
Correct Answer(s)
A. Cursor
A. ConstantExplanation
Incorrect Answers
C: Triggers are objects that we create are created on the tables.
D: Sequences can't be grouped inside the packages, but we can reference then inside the
package.
E: Views are created and they are database objects, and they can't be grouped inside the
packages.Rate this question:
-
- 14.
Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT ‘AUSTIN’, V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus?
-
EXECUTE INSERT_TEAM;
-
EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);
-
EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);
-
EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY := ‘AUSTIN’);
-
EXECUTE INSERT_TEAM (3, ‘LONGHORNS’);
Correct Answer(s)
A. EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);
A. EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);Explanation
The two statements that will successfully invoke this procedure in SQL *Plus are:
1. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
2. EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
These statements are correct because they provide the required values for the parameters V_ID, V_CITY, and V_NAME in the correct order. The first statement uses named notation to specify the values for V_NAME and V_CITY, while the second statement uses positional notation. Both statements will successfully insert a new record into the TEAM table with the specified values.Rate this question:
-
- 15.
Which four triggering events can cause a trigger to fire?
-
A specific error or any errors occurs.
-
A database is shut down or started up.
-
A specific user or any user logs on or off.
-
A user executes a CREATE or an ALTER table statement.
-
A user executes a SELECT statement with an ORDER BY clause.
-
A user executes a JOIN statement that uses four or more tables.
Correct Answer(s)
A. A specific error or any errors occurs.
A. A database is shut down or started up.
A. A specific user or any user logs on or off.
A. A user executes a CREATE or an ALTER table statement.Explanation
The trigger can be fired when a specific error or any errors occur, when a database is shut down or started up, when a specific user or any user logs on or off, and when a user executes a CREATE or an ALTER table statement. These triggering events can activate the trigger and initiate the associated actions or procedures defined within it.Rate this question:
-
- 16.
Which statements are true? (Choose all that apply)
-
If errors occur during the compilation of a trigger, the trigger is still created.
-
If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.
-
If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors.
-
If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.
Correct Answer(s)
A. If errors occur during the compilation of a trigger, the trigger is still created.
A. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors.
A. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.Explanation
The given answer is correct. If errors occur during the compilation of a trigger, the trigger is still created. Additionally, you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors, and you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.Rate this question:
-
Quiz Review Timeline (Updated): Mar 20, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 20, 2023Quiz Edited by
ProProfs Editorial Team -
Oct 27, 2011Quiz Created by
Gaurav.4086
Back to top