SQL Quiz-2 By "The Cloud9"

1. 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

The correct answer is "SHOW ERRORS". This command can be used in SQL *Plus to see the actual error message when creating a function. It will display the compilation errors that occurred during the creation of the function, allowing the user to identify and fix any issues.

Explanation

The correct answer is "SHOW ERRORS". This command can be used in SQL *Plus to see the actual error message when creating a function. It will display the compilation errors that occurred during the creation of the function, allowing the user to identify and fix any issues.

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 ASSELECT employee_id, emp_name,department_idFROM employeesWHERE mgr_id IN (102, 120);

CREATE VIEW emp_Vu ASSELECT employee_id, emp_name, job_iddepartment_idFROM employeesWHERE mgr_id IN (102, 120);

CREATE VIEW emp_Vu ASSELECT department_id, SUM(sal) TOTALSALFROM employeesWHERE mgr_id IN (102, 120)GROUP BY department_id;

CREATE VIEW emp_Vu ASSELECT employee_id, emp_name, job_id,DISTINCT department_idFROM employees;

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.

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.

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

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.

Explanation

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.

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.

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.

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.

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_nameFROM employeesWhere employee_id=180)WHERE employee_id =180;

UPDATE new_employees SET name = (SELECTlast_name||first_nameFROM employees)WHERE employee_id =180;

UPDATE new_employees SET name = (SELECT last_name||first_name FROM employeesWHERE employee_id=180)WHERE employee_id =(SELECT employee_idFROM new employees);

UPDATE new_employees SET name = (SELECT last name||first_nameFROM employeesWHERE employee_id=(SELECT employee_idFROM new_employees))WHERE employee_id=180;

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.

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.

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.

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.

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.

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

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;

Explanation

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;

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 employeesWHERE MIN(salary) < 5000 AND MAX(salary) > 15000;

SELECT dept_id, MIN(salary), MAX(salary)FROM employeesWHERE MIN(salary) < 5000 AND MAX(salary) > 15000GROUP BY dept_id;

SELECT dept_id, MIN(salary), MAX(salary)FROM employeesHAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

SELECT dept_id, MIN(salary), MAX(salary)FROM employeesGROUP BY dept_idHAVING MIN(salary) < 5000 AND MAX(salary) < 15000;

SELECT dept_id, MIN(salary), MAX(salary)FROM employeesGROUP BY dept_id, salaryHAVING MIN(salary) < 5000 AND MAX(salary) > 15000;

The correct answer is the fourth option: SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary)

Explanation

The correct answer is the fourth option: SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary)

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_gradesWHERE semester_end > ’01-JAN-2000’ and semester end < 31-DEC-2000’;

SELECT COUNT(gpa)FROM student gradesWHERE semester_end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

SELECT AVG(gpa)FROM student_grades

SELECT MEDIAN(gpa)FROM student_gradesWHERE semester end > ’01-JAN-2000’ and semester end < ’31-DEC-2000’;

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.

Explanation

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.

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 commandEXECUTE :X := CALCTAX;

You need to create a SQL *Plus environment variable X and issue the commandEXECUTE :X := CALCTAX(1000);

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.

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.

11. Which two statements about packages are true? (Choose two)

Packages can be nested.

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.

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.

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.

Submit

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’);

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.

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.

Submit

13. Which two programming constructs can be grouped within a package? (Choose two)

Cursor

Constant

Trigger

Sequence

View

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.

Explanation

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.

Submit

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’);

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.

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.

Submit

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.

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.

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.

Submit

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 theUSER_TRIGGERS data dictionary view to see the compilation errors.

If errors occur during the compilation of a trigger you can use the SHOW ERRORS commandwithin iSQL *Plus to see the compilation errors.

If errors occur during the compilation of a trigger you can go into SQL *Plus and query theUSER_ERRORS data dictionary view to see compilation errors.

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.

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.

Submit