Search This Blog

Wednesday, October 27, 2010

Final exam sem 1 (Oracle)

1. 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 (*)

Correct
2. Foreign keys must be null. True or False? Mark for Review
(1) Points

True


False (*)

Incorrect. Refer to Section 11 Lesson 1.
3. If a primary key is a set of columns then one column must be null. True or False? Mark for Review
(1) Points

True


False (*)

Correct
4. A table must have a primary key. True or False? Mark for Review
(1) Points

True


False (*)

Incorrect. Refer to Section 11 Lesson 1.
5. 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
6. The transformation from an ER diagram to a physical design involves changing terminology. Secondary Unique Identifiers become Mark for Review
(1) Points

Columns


Tables


Unique Constraints (*)


Primary Key Constraints

Incorrect. Refer to Section 11 Lesson 2.
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. 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
9. The Oracle Database can implement a many to many relationship. You simply create two foreign keys between the two tables. True or False? Mark for Review
(1) Points

True


False (*)

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

Supertype


Intersection Table (*)


Intersection Entity


Subtype

Correct


Section 11 11. 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 12. What command can be used to create a new row in a table in the database? Mark for Review
(1) Points

CREATE


NEW


ADD


INSERT (*)

Incorrect. Refer to Section 12 Lesson 1.
13. What command will return data from the database to you? Mark for Review
(1) Points

FETCH


GET


SELECT (*)


RETURN

Incorrect. Refer to Section 12 Lesson 1.
14. The DESCRIBE command returns all rows from a table. True or False? Mark for Review
(1) Points

True


False (*)

Correct.
15. The SQL statement ALTER TABLE EMPLOYEES DELETE COLUMN SALARY is a valid statement. True or False? Mark for Review
(1) Points

True


False (*)

Correct.
16. Once you have created a table, it is not possible to alter the definition of it. If you need to add a new column you must delete the table definition and create a new, correct table. True or False? Mark for Review
(1) Points

True


False (*)

Correct.
Section 13 17. Systems are always just rolled out as soon as the programming phase is finished. No further work is required once the development is finished. True or False? Mark for Review
(1) Points

True


False (*)

Incorrect. Refer to Section 13 Lesson 1.
18. In the analysis phase the tables are created and populated with test data. True or False? Mark for Review
(1) Points

True


False (*)

Correct.
Section 15 19. Which statement best describes how arithmetic expressions are handled? Mark for Review
(1) Points

Addition operations are handled before any other operations.


Multiplication and subtraction operations are handled before any other operations.


Multiplication and addition operations are handled before subtraction and division operations.


Division and multiplication operations are handled before subtraction and addition operations. (*)

Correct.
20. When listing columns in the SELECT list, what should you use to separate the columns? Mark for Review
(1) Points

Commas (*)


Semicolons


Dashes


Underscores

Correct.

Section 15 21. In which clause of a SELECT statement would you specify the name of the table or tables being queried? Mark for Review
(1) Points

The FROM clause (*)


The SELECT clause


The WHERE clause


Any of the above options, you can list tables wherever you want to in a SELECT statement.

Correct.
22. 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.
23. 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.
24. In a SQL statement, which clause specifies one or more columns to be returned by the query? Mark for Review
(1) Points

SELECT (*)


FROM


WHERE


Any of the above options, you can list columns wherever you want to in a SELECT statement.

Incorrect. See Section 15 Lesson 1.
Section 16 25. The Concatenation Operator does which of the following? Mark for Review
(1) Points

Links rows of data together inside the database.


Links two or more columns or literals to form a single output column (*)


Is represented by the asterisk (*) symbol


Separates columns.

Correct.
26. 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.
27. Where in a SQL statement can you not use arithmetic operators? Mark for Review
(1) Points

SELECT


FROM (*)


WHERE


NONE

Incorrect. See Section 16 Lesson 1.
28. You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use? Mark for Review
(1) Points

"= NULL"


NULL!


ISNULL


IS NULL (*)

Incorrect. See Section 16 Lesson 1.
29. You need to display employees whose salary is in the range of 10000 through 25000 for employees in department 50 . What does the WHERE clause look like? Mark for Review
(1) Points

WHERE department_id < 50 AND salary BETWEEN 10000 AND 25000 WHERE department_id > 50
AND salary BETWEEN 10000 AND 25000


WHERE department_id = 50
AND salary BETWEEN 25001 AND 10001


WHERE department_id = 50
AND salary BETWEEN 25000 AND 10000
(*)

Correct.
30. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause? Mark for Review
(1) Points

ONLY


UNIQUEONE


DISTINCT (*)


DISTINCTROW

Incorrect. See Section 16 Lesson 1.



Section 16 31. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"? Mark for Review
(1) Points

SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;


SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)


SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;


SELECT last_name||","|| job_id "Employee and Title" FROM employees;

Correct.
32. If the EMPLOYEES table has the following columns, and you want to write a SELECT statement to return the employee last name and department number for employee number 176, which of the following SQL statements should you use?
Name Type Length
EMPLOYEE_ID NUMBER 22
FIRST_NAME VARCHAR2 20
LAST_NAME VARCHAR2 25
EMAIL VARCHAR2 25
PHONE_NUMBER VARCHAR2 20
SALARY NUMBER 22
COMMISSION_PCT NUMBER 22
MANAGER_ID NUMBER 22
DEPARTMENT_ID NUMBER 22
Mark for Review
(1) Points

SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
(*)


SELECT last_name, department_id
FROM employees
WHERE employee_id equals 176;


SELECT first_name, employee_id
FROM employees
WHERE employee_id = 176;


SELECT last_name, employee_id
FROM employees
WHERE employee_id equals 176;

Correct.
33. You want to retrieve a list of customers whose last names begin with the letters Fr . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result? Mark for Review
(1) Points

AND


IN


BETWEEN


LIKE (*)

Correct.
34. You need to display all the employees whose last name starts with the letters Sm . Which WHERE clause should you use? Mark for Review
(1) Points

WHERE last_name LIKE 'Sm%' (*)


WHERE last_name LIKE '%Sm'


WHERE last_name LIKE '_Sm'


WHERE last_name LIKE 'Sm_'

Correct.
35. 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.

Correct
36. Would it be a good idea to model age as an attribute of STUDENT? Mark for Review
(1) Points

Yes


Maybe it could stop us having to calculate someone's age every time we need it


Sometimes


No - it breaks the Normalization rules (*)

Correct
Section 17 37. Which of the following best describes the meaning of the LIKE operator? Mark for Review
(1) Points

Display rows based on a range of values.


To test for values in a list.


Match a character pattern. (*)


To find Null values.

Incorrect. See Section 17 Lesson 1.
38. Which statement about the default sort order is true? Mark for Review
(1) Points

The lowest numeric values are displayed last.


The earliest date values are displayed first. (*)


Null values are displayed first.


Character values are displayed in reverse alphabetical order.

Correct.
39. 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.
40. 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.




Section 17 41. The ORDER BY clause always comes last. True or False? Mark for Review
(1) Points

True (*)


False

Incorrect. See Section 17 Lesson 1.
42. Which of the following are TRUE regarding the logical AND operator? Mark for Review
(1) Points

TRUE AND TRUE return FALSE


TRUE AND FALSE return TRUE


FALSE AND TRUE return NULL


TRUE AND FALSE return FALSE (*)

Correct.
43. 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.
44. Evaluate this SQL statement:
SELECT product_id, product_name, price
FROM products
ORDER BY product_name, price;
What occurs when the statement is executed?
Mark for Review
(1) Points

The results are sorted numerically only.


The results are sorted alphabetically only.


The results are sorted numerically and then alphabetically.


The results are sorted alphabetically and then numerically. (*)

Correct.
45. You query the database with this SQL statement:
SELECT price
FROM products
WHERE price IN(1, 25, 50, 250)
AND (price BETWEEN 25 AND 40 OR price > 50);
Which two values could the statement return? (Choose two.)
Mark for Review
(1) Points (Choose all correct answers)

1


50


25 (*)


10


250 (*)

Correct.
46. 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.
47. Evaluate this SELECT statement:
SELECT first_name, last_name, email
FROM employees
ORDER BY last_name;
Which statement is true?
Mark for Review
(1) Points

The rows will not be sorted.


The rows will be sorted alphabetically by the LAST_NAME values. (*)


The rows will be sorted in reverse alphabetical order by the LAST_NAME values.


The rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME values

Correct.
48. 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.
49. 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.
50. 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 must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?
Mark for Review
(1) Points

SELECT last_name, first_name, team_id, salary
FROM players
WHERE (salary > 25000 OR salary < 100000) AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id, salary; SELECT last_name, first_name, team_id, salary FROM players WHERE salary BETWEEN 25000 AND 100000 AND team_id BETWEEN 1200 AND 1500 ORDER BY team_id, salary DESC; (*) SELECT last_name, first_name, team_id, salary FROM players WHERE salary > 24999.99 AND salary < 100000
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id ASC, salary DESC;


SELECT last_name, first_name, team_id, salary
FROM players
WHERE salary BETWEEN 24999.99 AND 100000.01
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id DESC, salary DESC;

Correct.

No comments:

Post a Comment