MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

$ 30

MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

True/False (2 points each)
Indicate whether the sentence or statement is true or false.
1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
2. A table name can consist of numbers, letters, and blank spaces.
3. A constraint can only be created as part of the CREATE TABLE command.
4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
6. By default, the lowest value that can be generated by a sequence is 0.
7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
10. By default, the column headings displayed in a report are in upper-case characters.

Multiple Choice (3 points each)
Identify the letter of the choice that best completes the statement or answers the question.
11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
a. one-to-many c. one-to-all
b. many-to-many d. one-to-one

Contents of the BOOKS table
12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
a. SELECT title, cost+.20 “New Retail Price” FROM books;
b. SELECT title, cost*.20 “New Retail Price” FROM books;
c. SELECT title, cost*1.20 “New Retail Price” FROM books;
d. none of the above

Structure of the CUSTOMERS table
13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
a. ALTER TABLE customers
MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
b. ALTER TABLE customers
MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
c. ALTER TABLE customers
MODIFY (city (+8), lastname (+4));
d. ALTER TABLE customers
MODIFY (city (20), lastname (14));

14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
a. The constraint exists between two tables, called the parent table and the child table.
b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.

15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
a. ALTER USER rthomas PASSWORD EXPIRE ;
b. ALTER USER rthomas CHANGE PASSWORD;
c. ALTER USER rthomas UPDATE PASSWORD;
d. ALTER USER rthomas EXPIRE PASSWORD;

16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
a. Asc c. ascending
b. A d. either a or c

17. Which of the following is an accurate statement?
a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user’s session.
c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
d. none of the above

18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
a. NVL c. IFNVL
b. IFNL d. NVL2

Contents of the ORDERS table
19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
a. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
b. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL
GROUP BY order#;
c. SELECT COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
d. SELECT COUNT(*)
FROM orders
WHERE shipdate IS NULL;

20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
a. Z
b. 9
c. ,
d. .

Completion (4 points each)
Complete each sentence or statement.
21. A(n) ____________________ is a group of interrelated files.
22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
25. The ____________________ function is used to round numeric fields to a stated position.

SQL
26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
EMPLOYEE (employee name, street, city)
WORKS (employee name, company_name, salary)
A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation

27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
DEPTLOCATION (DNumber, DLocation)
PROJECT (PName, PNumber, PLocation, DNum)
WORKSON (ESSN, PNO, Hours)
DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)

Integrity Constraints:
Primary key = Foreign Key
EMPLOYEE.SSN = DEPENDENT.ESSN
EMPLOYEE.SSN = WORKSON.ESSN
EMPLOYEE.SSN = DEPARTMENT.MGRSSN
EMPLOYEE.SSN = EMPLOYEE.SuperSSN
DEPARTMENT.DNumber = EMPLOYEE.DNO
DEPARTMENT.DNumber = DEPTLOCATION.DNumber
DEPARTMENT.DNumber = PROJECT.DNum
PROJECT.PNumber = WORKSON.PNO

28. (18 points) Write SQL syntax to resolve the following queries.
– Find the names of all employees who are directly supervised by the employee named “John Doe”
– List the name of employees whose salary is greater than the average salary of his or her corresponding department
– For each department, retrieve the department name and the average salary of all employees working in that department.

82 in stock

SKU: MSCD610ORACLEEXAM Categories: ,

Description

MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

True/False (2 points each)
Indicate whether the sentence or statement is true or false.
1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
2. A table name can consist of numbers, letters, and blank spaces.
3. A constraint can only be created as part of the CREATE TABLE command.
4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
6. By default, the lowest value that can be generated by a sequence is 0.
7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
10. By default, the column headings displayed in a report are in upper-case characters.

Multiple Choice (3 points each)
Identify the letter of the choice that best completes the statement or answers the question.
11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
a. one-to-many c. one-to-all
b. many-to-many d. one-to-one

Contents of the BOOKS table
12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
a. SELECT title, cost+.20 “New Retail Price” FROM books;
b. SELECT title, cost*.20 “New Retail Price” FROM books;
c. SELECT title, cost*1.20 “New Retail Price” FROM books;
d. none of the above

Structure of the CUSTOMERS table
13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
a. ALTER TABLE customers
MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
b. ALTER TABLE customers
MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
c. ALTER TABLE customers
MODIFY (city (+8), lastname (+4));
d. ALTER TABLE customers
MODIFY (city (20), lastname (14));

14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
a. The constraint exists between two tables, called the parent table and the child table.
b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.

15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
a. ALTER USER rthomas PASSWORD EXPIRE ;
b. ALTER USER rthomas CHANGE PASSWORD;
c. ALTER USER rthomas UPDATE PASSWORD;
d. ALTER USER rthomas EXPIRE PASSWORD;

16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
a. Asc c. ascending
b. A d. either a or c

17. Which of the following is an accurate statement?
a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user’s session.
c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
d. none of the above

18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
a. NVL c. IFNVL
b. IFNL d. NVL2

Contents of the ORDERS table
19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
a. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
b. SELECT order#, COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL
GROUP BY order#;
c. SELECT COUNT(shipdate)
FROM orders
WHERE shipdate IS NULL;
d. SELECT COUNT(*)
FROM orders
WHERE shipdate IS NULL;

20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
a. Z
b. 9
c. ,
d. .

Completion (4 points each)
Complete each sentence or statement.
21. A(n) ____________________ is a group of interrelated files.
22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
25. The ____________________ function is used to round numeric fields to a stated position.

SQL
26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
EMPLOYEE (employee name, street, city)
WORKS (employee name, company_name, salary)
A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation

27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
DEPTLOCATION (DNumber, DLocation)
PROJECT (PName, PNumber, PLocation, DNum)
WORKSON (ESSN, PNO, Hours)
DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)

Integrity Constraints:
Primary key = Foreign Key
EMPLOYEE.SSN = DEPENDENT.ESSN
EMPLOYEE.SSN = WORKSON.ESSN
EMPLOYEE.SSN = DEPARTMENT.MGRSSN
EMPLOYEE.SSN = EMPLOYEE.SuperSSN
DEPARTMENT.DNumber = EMPLOYEE.DNO
DEPARTMENT.DNumber = DEPTLOCATION.DNumber
DEPARTMENT.DNumber = PROJECT.DNum
PROJECT.PNumber = WORKSON.PNO

28. (18 points) Write SQL syntax to resolve the following queries.
– Find the names of all employees who are directly supervised by the employee named “John Doe”
– List the name of employees whose salary is greater than the average salary of his or her corresponding department
– For each department, retrieve the department name and the average salary of all employees working in that department.

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.