joi, 29 ianuarie 2009

Raspunsuri Final Term Exam Semester 1

http://fisierulmeu.ro/24326VHROQZJ/RASPUNSURI-ORACLE-doc.html
http://fisierulmeu.ro/24JEEIEW2PR6/RASPUNSURI-ORACLE-2-doc.html
http://fisierulmeu.ro/24QNT2ZET93E/RASPUNSURI-ORACLE-5-doc.html

RASPUNSURI ORACLE MID EXAM Design

RASPUNSURI ORACLE FINAL EXAM Design

Oracle Academy Database Design - Student
Final Term Exam Semester 1

Section 12

1. The explanation below is a column integrity constraint:
A column must contain only values consistent with the defined data format of the column. True or False?
Mark for Review
(1) Points

True (*)

False


Correct


2. Identify all of the incorrect statements that complete this sentence: A primary key is: (Choose three) Mark for Review
(1) Points

(Choose all correct answers)

A single column that uniquely identifies each column in a table (*)

One or more columns in a table that uniquely identifies each row in that table

A set of columns in one table that uniquely identifies each row in another table (*)

Only one column that must be null (*)


Correct


3. The explanation below is a User Defined integrity rule and must therefore be manually coded, the Database cannot enforce this rule automatically:
A primary key must be unique, and no part of the primary key can be null. True or False?
Mark for Review
(1) Points

True

False (*)


Correct


4. The text below is an example of what constraint type:
The value in the manager_id column of the EMPLOYEES table must match a value in the employee_id column in the EMPLOYEES table.
Mark for Review
(1) Points

Entity integrity

User-defined integrity

Column integrity

Referential integrity (*)


Incorrect. Refer to Section 12


5. In a physical data model, an attribute becomes a _____________. Mark for Review
(1) Points

Table

Foreign Key

Constraint

Column (*)


Correct


6. The transformation from an ER diagram to a physical design involves changing terminology. Primary Unique Identifiers in the ER diagram become __________ and relationships become ____________. Mark for Review
(1) Points

Foreign keys, Primary keys

Primary keys, Foreign keys (*)

Foreign keys, mandatory business rules

Unique Keys, Primary keys


Correct


7. In a physical data model, a relationship is represented as a combination of: (Choose Two) Mark for Review
(1) Points

(Choose all correct answers)

Column

Primary Key or Unique Key (*)

Check Constraint or Unique Key

Foreign Key (*)


Correct


8. It is possible to implement non-transferability via a simple Foreign Key Relationship. True or False? Mark for Review
(1) Points

True

False (*)


Correct


9. Many to many relationships are implemented via a structure called a: ________________ Mark for Review
(1) Points

Supertype

Intersection Table (*)

Intersection Entity

Subtype


Incorrect. Refer to Section 12


10. When mapping supertypes, relationships at the supertype level transform as usual. Relationships at subtype level are implemented as foreign keys, but the foreign key columns all become mandatory. True or False? Mark for Review
(1) Points

True

False (*)


Correct


Section 12

11. Which of the following are reasons why you should consider using a Subtype Implementation? Mark for Review
(1) Points

The resulting table will reside in a single database and be used by just ONE user.

When the common access paths for the supertypes are different.

Business functionality and business rules, access paths and frequency of access are all very different between subtypes. (*)

Most of the relationships are at the supertype level


Correct




Section 13

12. The _______ clause can be added to a SELECT statement to return a subset of the data. Mark for Review
(1) Points

ANYWHERE

WHICH

WHERE (*)

EVERY


Correct.


13. The DESCRIBE command returns all rows from a table. True or False? Mark for Review
(1) Points

True

False (*)


Incorrect. Refer to Section 13




Section 16

14. Which SQL keyword specifies that an alias will be substituted for a column name in the output of a SQL query? Mark for Review
(1) Points

AS (*)

OR

AND

SUBSTITUTE


Correct.


15. You query the database with this SQL statement:
SELECT *
FROM transaction
WHERE product_id = 4569;

Which SQL SELECT statement capabilities are achieved when this statement is executed?
Mark for Review
(1) Points

Selection only (*)

Projection only

Selection and projection only

Projection, selection and joining


Incorrect. See Section 16


16. Evaluate this SELECT statement:
SELECT (salary * raise_percent) raise
FROM employees;

If the RAISE_PERCENT column only contains null values, what will the statement return?
Mark for Review
(1) Points

Only zeroes

Only null values (*)

A null value or a zero depending on the value of the SALARY column

A null value or a numeric value depending on the value of the SALARY column


Correct.


17. What would you use in the SELECT clause to return all the columns in the table? Mark for Review
(1) Points

An asterisk (*) (*)

A minus sign (-)

A plus sign (+)

The ALL keyword


Correct.


18. When you use the SELECT clause to list one or two columns only from a table and no WHERE clause, which SQL capability is used? Mark for Review
(1) Points

Joining only

Selection only

Projection only (*)

Projection and Selection


Correct.


19. In a SELECT clause, what is the result of 2 + 3 * 2? Mark for Review
(1) Points

6

8 (*)

10

13


Correct.


20. You query the database with this SQL statement:
SELECT * FROM students;

Why would you use this statement?
Mark for Review
(1) Points

To insert data

To view data (*)

To display the table structure

To delete data


Correct.

Section 16

21. Any Non-UID must be dependant on the entire UID. True or False? Mark for Review
(1) Points

True (*)

False


Correct


22. When is an entity in 2nd Normal Form? Mark for Review
(1) Points

When all non-UID attributes are dependent upon the entire UID. (*)

When no attritibutes are mutually independant and fully independent on the primary key.

When no attritibutes are mutually independent and all are fully dependent on the primary key.

None of the Above.


Incorrect. Refer to Section 6




Section 17

23. What does the DISTINCT keyword do when it is used in a SELECT clause? Mark for Review
(1) Points

Hides NULL values

Eliminates all unique values and compares values

Eliminates duplicate rows in the result (*)

Eliminates only unique rows in the result


Correct. See Section 17


24. Which symbol represents the not equal to condition? Mark for Review
(1) Points

#

'+'

!= (*)

~


Correct.


25. Which statement best describes how column headings are displayed by default in Oracle Application Express: Mark for Review
(1) Points

Column headings are displayed left-justified and in lowercase.

Column headings are displayed left-justified and in uppercase.

Column headings are displayed centered and in uppercase. (*)

Column headings are displayed centered and in mixed case.


Correct. See Section 17


26. Which operator is used to combine columns of character strings to other columns? Mark for Review
(1) Points

*

/

+

|| (*)


Correct. See Section 17


27. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
(1) Points

IN

LIKE

BETWEEN...AND... (*)

IS NULL


Correct.


28. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10? Mark for Review
(1) Points

WHERE (*)

FROM

SELECT

IS


Correct.


29. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id IN(10, 20, 30)
AND salary > 20000;

Which values would cause the logical condition to return TRUE?
Mark for Review
(1) Points

DEPARTMENT_ID = 10 and SALARY = 20000

DEPARTMENT_ID = 20 and SALARY = 20000

DEPARTMENT_ID = null and SALARY = 20001

DEPARTMENT_ID = 10 and SALARY = 20001 (*)


Correct. See Section 17


30. What will the result of the following SELECT statement be:
SELECT last_name, salary, salary + 300
FROM employees;
How will the heading for the SALARY column appear in the display by default in Oracle Application Express?
Mark for Review
(1) Points

Display the last name, salary and the results of adding 300 to each salary for all the employees (*)

Modify the salary column by adding 300 and displaying the last name, salary and the new salary.

Modify the salary column by adding 300 and only display the last name and the new salary.

Display the last name, salary and the results of adding 300 to the salary of the first employee row


Correct. See Section 17


Section 17

31. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER (4)
MANAGER_ID NUMBER (9)
POSITION_ID NUMBER (4)

Which SELECT statement should you use if you want to display unique combinations of the TEAM_ID and MANAGER_ID columns?
Mark for Review
(1) Points

SELECT * FROM players;

SELECT team_id, manager_id FROM players;

SELECT DISTINCT team_id, manager_id FROM players; (*)

SELECT team_id, DISTINCT manager_id FROM players;

SELECT team_id, manager_id DISTINCT FROM players;


Correct.


32. You need write a SELECT statement that should only return rows that contain 34, 46, or 48 for the DEPARTMENT_ID column. Which operator should you use in the WHERE clause to compare the DEPARTMENT_ID column to this specific list of values? Mark for Review
(1) Points

=

!=

IN (*)

BETWEEN..AND..


Correct.


33. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review
(1) Points

SELECT manager_id, department_id DISTINCT FROM employees;

SELECT manager_id, department_id FROM employees; (*)

SELECT DISTINCT manager_id, department_id FROM employees;

SELECT manager_id, DISTINCT department_id FROM employees;


Incorrect. See Section 17.


34. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9) PrimaryKey
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER(5) NOT NULL
MANAGER_ID NUMBER(9) NOT NULL

Evaluate these two SELECT statements:
1. SELECT DISTINCT employee_id, department_id, manager_id FROM employees;
2. SELECT employee_id, department_id, manager_id FROM employees;

Which of the following statements is true?
Mark for Review
(1) Points

The two statements will display the same data. (*)

The first statement will display a particular DEPARTMENT_ID only once.

The first statement will NOT display values from all of the rows in the EMPLOYEES table

The second statement could display a unique combination of the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID values more than once.


Correct. See Section 17


35. The STUDENT table contains these columns:
STUDENT_ID NUMBER(10) Primary Key
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
MAIN_SUBJECT_ID NUMBER(3)
ADVISOR_ID NUMBER(5)

Evaluate this statement:
SELECT DISTINCT advisor_id, main_subject_id
FROM student;

Which statement is true?
Mark for Review
(1) Points

Each ADVISOR_ID can be displayed only once.

Each MAIN_SUBJECT_ID can be displayed more than once per ADVISOR_ID. (*)

Each combination of ADVISOR_ID and MAIN_SUBJECT_ID can be displayed more than once.

Each MAIN_SUBJECT_ID can be displayed only once per query.


Incorrect. See Section 17


36. The EMPLOYEES table includes these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL

You want to produce a report that provides the first names, last names and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task?
Mark for Review
(1) Points

SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-MAR-00' AND '30-AUG-00';
(*)


SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '30-AUG-00' AND '01-MAR-00';


SELECT last_name, first_name, hire_date
FROM employees
GROUP BY hire_date >= '01-MAR-00' and hire_date <= '30- AUG-00';


SELECT last_name, first_name, hire_date
FROM employees
AND hire_date >= '01-MAR-00' and hire_date <= '30-AUG- 00';



Correct.




Section 18

37. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
FROM employees e, employees m
ORDER BY e.last_name, e.first_name
WHERE e.employee_id = m.manager_id;

This statement fails when executed. Which change will correct the problem?
Mark for Review
(1) Points

Reorder the clauses in the query. (*)

Remove the table aliases in the WHERE clause.

Remove the table aliases in the ORDER BY clause.

Include a HAVING clause.


Correct.


38. The PLAYERS table contains these columns:
PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)
You want to display all players' names with position 6900 or greater. You want the players names to be displayed alphabetically by last name and then by first name. Which statement should you use to achieve the required results?
Mark for Review
(1) Points

SELECT last_name, first_name
FROM players
WHERE position_id >= 6900
ORDER BY last_name, first_name;
(*)


SELECT last_name, first_name
FROM players
WHERE position_id > 6900
ORDER BY last_name, first_name;


SELECT last_name, first_name
FROM players
WHERE position_id <= 6900
ORDER BY last_name, first_name;


SELECT last_name, first_name
FROM players
WHERE position_id >= 6900
ORDER BY last_name DESC, first_name;



Correct.


39. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE salary > 30000
AND department_id = 10
OR email IS NOT NULL;
Which statement is true?
Mark for Review
(1) Points

The OR condition will be evaluated before the AND condition.

The AND condition will be evaluated before the OR condition. (*)

The OR and AND conditions have the same precedence and will be evaluated from left to right

The OR and AND conditions have the same precedence and will be evaluated from right to left


Correct.


40. Evaluate this SELECT statement:
SELECT *
FROM employees
WHERE department_id = 34
OR department_id = 45
OR department_id = 67;

Which operator is the equivalent of the OR conditions used in this SELECT statement?
Mark for Review
(1) Points

IN (*)

AND

LIKE

BETWEEN ... AND ...


Incorrect! See Section 18.

Section 18

41. You need to create a report to display all employees that were hired on or after January 1, 1996. The data should display in this format:
Employee Start Date and Salary
14837 - Smith 10-MAY-92 / 5000


Which SELECT statement could you use?
Mark for Review
(1) Points

SELECT employee_id || - || last_name "Employee",
hire_date || / || salary "Start Date and Salary"
FROM employees
WHERE hire_date <= '01-JAN-96';


SELECT employee_id ||' '|| last_name "Employee",
hire_date ||' '|| salary "Start Date and Salary"
FROM employees
WHERE hire_date <= '01-JAN-96';


SELECT employee_id ||'"- "|| last_name "Employee",
hire_date ||" / "|| salary "Start Date and Salary"
FROM employees
WHERE hire_date <= '01-JAN-96';


SELECT employee_id ||' - '|| last_name 'Employee',
hire_date ||' / '|| salary 'Start Date and Salary'
FROM employees
WHERE hire_date <= '01-JAN-96';


SELECT employee_id ||' - '|| last_name "Employee",
hire_date ||' / '|| salary "Start Date and Salary"
FROM employees
WHERE hire_date <= '01-JAN-96';
(*)



Incorrect! See Section 18.


42. You attempt to query the database with this SQL statement:
SELECT product_id "Product Number", category_id "Category", price "Price"
FROM products
WHERE "Category" = 5570
ORDER BY "Product Number";

This statement fails when executed. Which clause contains a syntax error?
Mark for Review
(1) Points

SELECT product_id "Product Number", category_id "Category", price "price"

ORDER BY "Product Number";

FROM products

WHERE "Category" = 5570 (*)


Incorrect! See Section 18.


43. Evaluate this SELECT statement:
SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;

Which clause contains an error?
Mark for Review
(1) Points

SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)

FROM employees

WHERE salary IS NOT NULL

ORDER BY last_name, 3;


Correct.


44. Evaluate this SELECT statement:
SELECT last_name, first_name, department_id, manager_id
FROM employees;

You need to sort data by manager id values and then alphabetically by employee last name and first name values. Which ORDER BY clause could you use?
Mark for Review
(1) Points

ORDER BY department_id, last_name

ORDER BY manager_id, last_name, first_name (*)

ORDER BY last_name, first_name, manager_id

ORDER BY manager_id, first_name, last_name


Correct.


45. Which statement about the logical operators is true? Mark for Review
(1) Points

The order of operator precedence is AND, OR, and NOT.

The order of operator precedence is AND, NOT, and OR.

The order of operator precedence is NOT, OR, and AND.

The order of operator precedence is NOT, AND, and OR. (*)


Correct.


46. Which comparison condition means "Less Than or Equal To?" Mark for Review
(1) Points

"=)"

"+<"

">="

"<=" (*)


Correct.


47. From left to right, what is the correct order of Precedence? Mark for Review
(1) Points

Arithmetic, Concatenation, Comparison, OR (*)

NOT, AND, OR, Arithmetic

Arithmetic, NOT, Logical, Comparison

Arithmetic, NOT, Concatenation, Logical


Correct.


48. Which statement about the ORDER BY clause is true? Mark for Review
(1) Points

You can use a column alias in the ORDER BY clause. (*)

The default sort order of the ORDER BY clause is descending.

The ORDER BY clause can only contain columns that are included in the SELECT list.

The ORDER BY clause should immediately precede the FROM clause in a SELECT statement


Correct.


49. Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column? Mark for Review
(1) Points

ORDER BY (*)

WHERE

FROM

HAVING


Correct.


50. You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause? Mark for Review
(1) Points

DESC (*)

ASC

SORT

CHANGE


Correct.