Q1. - (Topic 1)
You created an ORDERS table with the following description: Exhibit:
You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?
A. You cannot add a primary key constraint if data exists in the column
B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
C. The primary key constraint can be created only a the time of table creation
D. You cannot have two constraints on one column
Answer: B
Q2. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q3. - (Topic 1)
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
Answer: C
Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further
restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for
which the specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1.
Rows are grouped.
2.
The group function is applied to the group.
3.
The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you
place the GROUP BY clause first because it is more logical. Groups are formed and group
functions are calculated before the HAVING clause is applied to the groups in the SELECT
list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.
Q4. - (Topic 1)
Which statement correctly describes SQL and /SQL*Plus?
A. Both SQL and /SQL*plus allow manipulation of values in the database.
B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
Answer: A
Q5. - (Topic 2)
View the Exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date ) FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
Answer: B,D
Explanation:
Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees.
Q6. - (Topic 1)
What is true about sequences?
A. The start value of the sequence is always 1.
B. A sequence always increments by 1.
C. The minimum value of an ascending sequence defaults to 1.
D. The maximum value of descending sequence defaults to 1.
Answer: C
Q7. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:
Evaluate the following query: Exhibit:
The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
D. The alias NAME should not be enclosed within double quotation marks
Answer: A
Q8. - (Topic 2)
EMPLOYEES and DEPARTMENTS data: EMPLOYEES
DEPARTMENTS
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN'
What happens when the statement is executed?
A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
D. The statement fails because there is more than one row matching the employee name Smith.
E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.
Answer: D
Explanation:
'=' is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A. The Update statement will fail no update was done.
B. The update statement will fail no update was done.
C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12
Q9. - (Topic 2)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');
Answer: C,E,F
Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11
Q10. - (Topic 2)
Which two statements are true regarding indexes? (Choose two.)
A. They can be created on tables and clusters.
B. They can be created on tables and simple views.
C. You can create only one index by using the same columns.
D. You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.
Answer: A,D