Oracle Database: SQL Fundamentals I
Question No: 171 – (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
listing of customers who do not have a credit limit and were born before 1980
finding the number of customers, in each city, whose marital status is #39;married#39;
finding the average credit limit of male customers residing in #39;Tokyo#39; or #39;Sydney#39;
listing of those customers whose credit limit is the same as the credit limit of customers residing in the city #39;Tokyo#39;
finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: D,E Explanation:
Describe the Types of Problems That the Subqueries Can Solve
There are many situations where you will need the result of one query as the input for another.
Use of a Subquery Result Set for Comparison Purposes
Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:
select avg(salary) from employees;
select last_name from employees where salary lt; result_of_previous_query ; Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary lt; (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison with the rows retrieved by the parent query.
The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in (select distinct(department_id) from employees);
Question No: 172 – (Topic 2)
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
Both commands execute successfully.
The first CREATE TABLE command generates an error because the NULL constraint is not valid.
The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
Answer: B Explanation: Defining Constraints
The slide gives the syntax for defining constraints when creating a table. You can create constraints at either the column level or table level. Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of the table definition and must refer to the column or columns on which the constraint pertains in a set of parentheses. It is mainly the syntax that differentiates the two; otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
Question No: 173 – (Topic 2)
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command: DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?
Only the synonym would be dropped.
The synonym would be dropped and the corresponding table would become invalid.
The synonym would be dropped and the packages referring to the synonym would be dropped.
The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
Answer: A Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.
Question No: 174 – (Topic 2)
Which three statements are true regarding views? (Choose three.)
Views can be created only from tables.
Views can be created from tables or other views.
Only simple views can use indexes existing on the underlying tables.
Both simple and complex views can use indexes existing on the underlying tables.
Complex views can be created only on multiple tables that exist in the same schema.
Complex views can be created on multiple tables that exist in the same or different
Answer: B,D,F Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)
Question No: 175 – (Topic 2)
Which two statements are true about WHERE and HAVING clauses? (Choose two)
A WHERE clause can be used to restrict both rows and groups.
A WHERE clause can be used to restrict rows only.
A HAVING clause can be used to restrict both rows and groups.
A HAVING clause can be used to restrict groups only.
A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
A HAVING clause CANNOT be used in sub queries.
Answer: B,D Explanation:
B: WHERE clause cannot be use to restrict groups
WHERE clause cannot be use when there is group functions. D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on
the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause
rows are grouped
the group function is applied to the group
the group that match the criteria in the Having clause are displayed.
Incorrect Answers :
Where clause cannot be use to restrict groups
C. A HAVING clause can only e used to restrict GROUPS.
WHERE clause cannot be use when there is group function, instead HAVING is to be use.
There is no constraint to use HAVING clause in a sub queries.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20
Question No: 176 – (Topic 2)
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
The HAVING clause can be used with aggregate functions in subqueries.
The WHERE clause can be used to exclude rows after dividing them into groups.
The WHERE clause can be used to exclude rows before dividing them into groups.
The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Question No: 177 – (Topic 2)
You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25)
What happens when you execute this DELETE statement? DELETE employees;
You get an error because of a primary key violation.
The data and structure of the EMPLOYEES table are deleted.
The data in the EMPLOYEES table is deleted but not the structure.
You get an error because the statement is not syntactically correct.
Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement.
DELETE [FROM] table
[WHEREcondition]; Incorrect answer:
AStatement will not cause error
BDelete statement will not delete the table structure DStatement will not cause error
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19
Question No: 178 – (Topic 2)
Which statement is true regarding transactions? (Choose all that apply.)
A transaction can consist only of a set of DML and DDL statements.
A part or an entire transaction can be undone by using ROLLBACK command.
A transaction consists of a set of DML or DCL statements.
A part or an entire transaction can be made permanent with a COMMIT.
A transaction can consist of only a set of queries or DML or DDL statements.
Question No: 179 – (Topic 2)
EMPLOYEES and DEPARTMENTS data: EMPLOYEES
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=
WHERE department_name = #39;Administration#39;)), Salary = (SELECT salary
WHERE emp_name = #39;Smith#39;) WHERE job_id = #39;IT_ADMIN#39;;
What happens when the statement is executed?
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.
The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
The statement fails because there is more than one row matching the employee name Smith.
The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
The statement fails because there is no #39;Administration#39; department in the DEPARTMENTS table.
Answer: D Explanation:
#39;=#39; 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 :
The Update statement will fail no update was done.
The update statement will fail no update was done.
The update statement will fail no update was done.
The update statement will fail but not due to job_it=#39;IT_ADMIN#39;
The update statement will fail but not due to department_id=#39;Administration#39;
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12
Question No: 180 – (Topic 2)
Evaluate this SQL statement:
SELECT ename, sal, 12*sal 100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as quot;monthly salary plus a monthly bonus of $100, multiplied by 12quot;?
No change is required to achieve the desired results.
SELECT ename, sal, 12*(sal 100) FROM emp;
SELECT ename, sal, (12*sal) 100 FROM emp;
SELECT ename, sal 100,*12 FROM emp;
Answer: B Explanation:
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal 100) from EMP;
AMultiplication and division has priority over addition and subtraction in Operator precedence.
CGive wrong results DWrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11
100% Free Download!
–Download Free Demo:1z0-051 Demo PDF
100% Pass Guaranteed!
–Download 2018 Latesttests 1z0-051 Full Exam PDF and VCE
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|