Some questions about SQL

Question 1

1.

Your database administrator has granted you the CREATE ANY PROCEDURE privilege. Which operations can you

successfully perform in the database with this privilege? (Choose all that apply.)

	a.	You can create a trigger in any schema.
	b.	You can create a function in any schema.
	c.	You can alter a procedure in any schema.
	d.	You can drop a procedure in any schema.
	e.	You can create a procedure in any schema.
	f.	You can create a package in your own schema.

0.5 points

Question 2

1. Examine the data in the TEACHER table.

Assume the user enters the following SELECT statement to retrieve data from the TEACHER table:

SQL> SELECT * FROM teacher WHERE INSTR(subject_id, '&1') = 4 AND LOWER(subject_id) LIKE 'HST%';

When prompted for the WHERE clause value, you enter an underscore (_).

Which result will this statement provide?

		It will execute, but it will not retrieve any data.
		It will display information on all teachers whose SUBJECT_ID begins with 'HST_'.
		It will return a syntax error because the TO_CHAR function was not used in the WHERE clause.
		It will display information on all teachers whose SUBJECT_ID begins with 'HST_', regardless of the case in which the SUBJECT_ID is stored.

Question 4

1. In the E-R model, what symbol is used to represent an attribute that can have multiple values for an entity instance?

	A.	oval with smaller ovals attached to it
	B.	double oval
	C.	solid oval
	D.	dashed oval

0.5 points

Question 5

1.

You have created the update_emp package in your database and want to access the procedures and functions

present inside the package.

Which statement correctly describes the characteristics of a package?

	a.	A package specification cannot exist without a package body.
	b.	A package body cannot exist without a package specification.
	c.	A package body and package specification are stored together.
	d.	A package body and package specification must be declared together.

Question 9

1. The student table contains these columns:

SQL> DESCRIBE student

ID NUMBER(9)

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25) ENROLL_DATE DATE

You need to create a report to display a student's enrollment date and projected graduation date.

These are the desired results:

Prompt the user for a student ID.

Display the student's first name, last name, and date of enrollment.

Display the student's projected graduation date by adding four years to the enrollment date value.

Which statement produces all three of the desired results?

		SELECT CONCAT(INITCAP(first_name), INITCAP(last_name)), TO_CHAR(ADD_MONTHS(enroll_date, 48), 'DD MONTH YYYY') grad_date FROM student WHERE id = &id;
		SELECT CONCAT(INITCAP(first_name), INITCAP(last_name)), TO_CHAR(enroll_date, 'DD MONTH YYYY'), ADD_MONTHS(enroll_date, 48) grad_date FROM student WHERE id = &id;
		SELECT INITCAP(first_name)||INITCAP(last_name), TO_CHAR(enroll_date, 'DD MONTH YYYY'), ADD_YEARS(enroll_date, 4) grad_date FROM student WHERE id = &id;
		SELECT INITCAP(first_name)||INITCAP(last_name) student_name, TO_CHAR(enroll_date, 'DD MONTH YYYY') date_enrolled, TO_CHAR(ADD_MONTHS(enroll_date, 4), 'DD MONTH YYYY') grad_date FROM student WHERE id = &id;

0.5 points

Question 10

1. In authorization, a set of operations performed by an individuals or group as part of a job is called a

	A.	privilege
	B.	matrix
	C.	role
	D.	user

0.5 points

Question 11

1. There are ____ classical approaches to database design.

	a.	two
	b.	three
	c.	four
	d.	five

Question 18

1. Which set operator would you use to display the employee IDs of employees hired after January 10, 2007 in the EMP table and employee IDs of employees who have held more than one position in the EMP_HIST table, eliminating any duplicate IDs?

		UNION
		UNION ALL
		INTERSECT
		MINUS

0.5 points

Question 19

1.

Using dynamic SQL, you want to create a procedure that will accept an IN parameter containing a table name

and remove all the rows from the specified table. Which code fragment should you use?

	a.	CREATE OR REPLACE PROCEDURE row_delete (del_table VARCHAR2) IS BEGIN DELETE FROM del_table; END;
	b.	CREATE OR REPLACE PROCEDURE row_delete IS BEGIN DELETE row_delete; END;
	c.	CREATE OR REPLACE PROCEDURE row_delete (del_table VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || del_table; END;
	d.	CREATE OR REPLACE PROCEDURE row_delete(del_table VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'DELETE * FROM' || del_table; END;

Question 21

1. Which of the following algorithms uses public-key encryption?

	A.	AES
	B.	3DES
	C.	RSA
	D.	DES