Questions about sql

Question 7

1. To prevent errors, database users are never allowed to access the database simultaneously.

True

False

0.5 points

Question 8

1. What is the right way to code the data type of a row in a cursor named invoices_cursor that is extracted from a table named invoices?

	a.	invoices_cursor%ROWTYPE
	b.	invoices_ROWTYPE
	c.	invoices_cursor_ROWTYPE
	d.	invoices%ROWTYPE

0.5 points

Question 9

1. Which of the following is NOT an aggregate function?

		SUM
		MAX
		LENGTH
		COUNT
		AVG

1. Examine the structure of the EMPLOYEE table.

Which CREATE TABLE statement should you use to create the EMPLOYEE table?

		CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25) NOT NULL, emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id));
		CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25) NOT NULL, emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id));
		CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25) NOT NULL, emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id), CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id));
		CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25) NOT NULL, emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id), CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id), CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id));

0.5 points

Question 18

1. Click the Exhibit(s) button to examine the structures of the EMPLOYEE, PROJECT, and TASK tables.

You want to create a report of all employees, including employee name and project name, who are assigned to project tasks. You want to include all projects even if they currently have no tasks defined, and you want to include all tasks, even those not assigned to an employee.

Which joins should you use?

		a self join on the EMPLOYEE table and a left outer join between the TASK and PROJECT tables
		a natural join between the TASK and EMPLOYEE tables and a natural join between the TASK and PROJECT tables
		a full outer join between the TASK and EMPLOYEE tables and a natural join between the TASK and PROJECT tables
		a natural join between the TASK and EMPLOYEE tables and a left outer join between the TASK and PROJECT tables
		a full outer join between the TASK and EMPLOYEE tables and a full outer join between the TASK and PROJECT tables
		a left outer join between the TASK and EMPLOYEE tables and a right outer join between the TASK and PROJECT tables

Question 20

1.

In dynamic SQL that performs DML operations, in which phase is the number of processed rows returned?

	a.	bind
	b.	fetch
	c.	parse
	d.	execute

Question 23

1. To allow an authorized person to pass privileges on to others, the privileges must be given

	A.	with RESTRICT
	B.	to a role
	C.	WITH GRANT OPTION
	D.	with CASCADE

Question 25

1. Deadlock can be spotted by looking for which of the following in the wait-for graph?

	A.	a new node
	B.	a cycle
	C.	an edge
	D.	a path of length 2

Question 29

1. The level of the three-level database architecture that contains user views is the

	A.	internal
	B.	logical
	C.	external
	D.	physical

Question 32

1. Which statement should you use to eliminate the need for all users to qualify Marilyn's INVENTORY table with her schema when querying the table?

		CREATE SYNONYM inventory FOR inventory;
		CREATE PUBLIC inventory SYNONYM FOR marilyn.inventory;
		CREATE PUBLIC SYNONYM inventory FOR marilyn;
		CREATE PUBLIC SYNONYM inventory FOR marilyn.inventory;

Question 39

1. All of the following are set operations that can be performed on suitable relational tables EXCEPT

	A.	SUM
	B.	INTERSECTION
	C.	DIFFERENCE
	D.	UNION