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)
WHERE shipdate IS NULL;
b. SELECT order#, COUNT(shipdate)
WHERE shipdate IS NULL
GROUP BY order#;
c. SELECT COUNT(shipdate)
WHERE shipdate IS NULL;
d. SELECT COUNT(*)
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?
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.
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)
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.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more