Tag: databases

Questions Related to databases

You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task?

technology databases

  1. UPDATE, DELETE, and INSERT TRiggers

  2. Just UPDATE and INSERT triggers

  3. INSTEAD OF TRiggers

  4. Triggers cannot be used in this circumstance.

Show answer
Correct Option: C

You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation?
technology databases

  1. A stored procedure and a trigger

  2. A batch and a trigger

  3. An UPDATE TRigger and an INSERT trigger

  4. One trigger by itself

Show answer
Correct Option: D

Which statement is valid when removing procedures?
technology databases

  1. Use a drop procedure statement to drop a standalone procedure

  2. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package specification

  3. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package body

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.

Show answer
Correct Option: A

Which table should you query to determine when your procedure was last compiled?

technology databases

  1. USER_PROCEDURES

  2. USER_PROCS

  3. USER_OBJECTS

  4. USER_PLSQL_UNITS

Show answer
Correct Option: C

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID, LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

technology databases

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);

Show answer
Correct Option: B

AI Explanation

To successfully invoke the UPD_BAT_STAT procedure and pass a parameter, you need to use the correct syntax. Let's go through each option to determine which statement should be added to the procedure:

Option A) EXECUTE UPD_BAT_STAT(V_ID) - This option is incorrect because EXECUTE is not a valid keyword to invoke a procedure in PL/SQL.

Option B) UPD_BAT_STAT(V_ID) - This option is correct because it uses the correct syntax to invoke the UPD_BAT_STAT procedure and pass the V_ID parameter.

Option C) RUN UPD_BAT_STAT(V_ID) - This option is incorrect because RUN is not a valid keyword to invoke a procedure in PL/SQL.

Option D) START UPD_BAT_STAT(V_ID) - This option is incorrect because START is not a valid keyword to invoke a procedure in PL/SQL.

The correct answer is Option B) UPD_BAT_STAT(V_ID). This option is correct because it uses the correct syntax to invoke the UPD_BAT_STAT procedure and pass the V_ID parameter.

Which type of argument passes a value from a procedure to the calling environment?
technology databases

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

  4. IN

Show answer
Correct Option: C

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?
technology databases

  1. EXECUTE INSERT_TEAM;

  2. EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);

  3. EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);

  4. EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY :=‘AUSTIN’);

Show answer
Correct Option: B,C

Which two describe a stored procedure?

technology databases

  1. A stored procedure is typically written in SQL.

  2. A stored procedure is a named PL/SQL block that can accept parameters

  3. A stored procedure is a type of PL/SQL subprogram that performs an action

  4. A stored procedure has three parts: the specification, the body, and the exception handler part

  5. The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment.

Show answer
Correct Option: B,C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A stored procedure is typically written in SQL - This option is incorrect. While stored procedures can be written in SQL, they can also be written in other programming languages such as PL/SQL.

Option B) A stored procedure is a named PL/SQL block that can accept parameters - This option is correct. A stored procedure is a named PL/SQL block that can accept parameters. It allows you to encapsulate and execute a set of SQL statements and PL/SQL code.

Option C) A stored procedure is a type of PL/SQL subprogram that performs an action - This option is correct. A stored procedure is a type of PL/SQL subprogram that performs a specific action or set of actions.

Option D) A stored procedure has three parts: the specification, the body, and the exception handler part - This option is incorrect. While it is true that a stored procedure can have three parts (specification, body, and exception handler), it is not a mandatory requirement. A stored procedure can have only a body or a body with an exception handler.

Option E) The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment - This option is incorrect. While the executable section of a stored procedure can contain statements that assign values, control execution, and return values, it is not the only section of a stored procedure. A stored procedure can also have a declaration section where variables are declared and initialized.

The correct answer is B, C. These options correctly describe a stored procedure as a named PL/SQL block that can accept parameters and performs a specific action or set of actions.

What is true about stored procedures?

technology databases

  1. A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal

Show answer
Correct Option: C

Examine this code: CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id NUMBER(4); BEGIN INSERT INTO departments VALUES (5, ‘Education’, 150, p_location_id); SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999; END insert_dept; / CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations (location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You now invoke the insert_location procedure using the following command: EXECUTE insert_location (19, ‘San Francisco’) What is the result in this EXECUTE command?

technology databases

  1. The locations, departments, and employees tables are empty

  2. The departments table has one row. The locations and the employees tables are empty

  3. The location table has one row. The departments and the employees tables are empty.

  4. The locations table and the departments table both have one row. The employees table is empty.

Show answer
Correct Option: A

AI Explanation

To answer this question, let's analyze the code step by step:

  1. The code defines a procedure named insert_dept that takes a parameter p_location_id. Inside the procedure, a v_dept_id variable is declared. The procedure then inserts a row into the departments table with the provided location id, but with fixed values for department_id, department_name, and manager_id.

  2. Next, the procedure attempts to select the department_id into the v_dept_id variable from the employees table where the employee_id is 99999. However, since the employees table is empty (as stated in the question), this select statement will not retrieve any rows.

  3. The insert_dept procedure does not return any value or perform any other actions.

  4. The code also defines another procedure named insert_location that takes two parameters p_location_id and p_city. Inside the procedure, it inserts a row into the locations table with the provided location id and city. It then calls the insert_dept procedure, passing the p_location_id parameter.

  5. Finally, the code attempts to execute the insert_location procedure with the arguments 19 and 'San Francisco'.

Based on the code analysis, the correct answer is:

A. The locations, departments, and employees tables are empty.

Explanation:

  • The code does not insert any rows into the tables. Therefore, all the tables remain empty after executing the insert_location procedure.