Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : Examine the structure of the sales table:
Evaluate the given create table statement:
Which two statements are true about the creation of the SALES1 table?
1. The SALES1 table is created with no rows but only a structure.
2. The SALES1 table would have primary key and unique constraints on the specified columns.
3. Access Mostly Uused Products by 50000+ Subscribers
4. The SALES1 table would have not null and unique constraints on the specified columns.
5. The SALES1 table would not be created because column-specified names in the select and
create table clauses do not match
Correct Answer : Get Lastest Questions and Answer :
Explanation:
Question : Which two statements are true regarding subqueries?
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.
1. A,C
2. B,D
3. Access Mostly Uused Products by 50000+ Subscribers
4. C,D
5. A,E
Correct Answer : Get Lastest Questions and Answer :
Explanation: Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel's salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main
query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.
Subquery Syntax : A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax:
operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=) and
multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement.
The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries. Subqueries can be nested to an unlimited depth in a FROM clause but to "only" 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
Question : Examine the structure of the products table:
You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?
1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D
Correct Answer : Get Lastest Questions and Answer :
Exp:
Related Questions
Question : Which create table statement is valid?
1. A
2. B
3. C
4. D
Question : Which statement is true regarding the UNION operator?
1. By default, the output is not sorted.
2. Null values are not ignored during duplicate checking.
3. Names of all columns must be identical across all select statements.
4. The number of columns selected in all select statements need not be the same.
Question :
1. A,C
2. B,C
3. A,B
4. B,D
4. C,D
Question : View the Exhibit and examine the structures of the employees and departments tables.
You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London
(location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their
department.
You issue the given (left side) command:
What is the outcome?
1. It executes successfully and gives the correct result.
2. It executes successfully but does not give the correct result.
3. It generates an error because a subquery cannot have a join condition in an update statement.
4. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
Question : You need to produce a report where each customer's credit limit has been incremented by $.
In the output, the customer's last name should have the heading Name and the incremented credit
limit should be labeled New credit Limit. The column headings should have only the first letter of
each word in uppercase.
Which statement would accomplish this requirement?
1. A
2. B
3. C
4. D
Question : 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 given query:
Which statement is true regarding the above query?
1. It executes and produces the required result.
2. It produces an error because an expression cannot be used in the order by clause.
3. It produces an error because the DESC option cannot be used with an expression in the order by clause.
4. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.