Get Full Version of the Exam
Consider these three statements:
create synonym s1 for employees; create public synonym s1 for departments; select * from s1; Which of the following statements is correct?
The second statement will fail because an object S1 already exists.
The third statement will show the contents of EMPLOYEES.
The third statement will show the contents of DEPARTMENTS.
The third statement will show the contents of the table S1, if such a table exists in the current schema.
Correct Answer: B
The order of priority is to search the schema namespace before the public namespace, so it will be the private synonym (to EMPLOYEES) that will be found.
Which two statements are true regarding subqueries?
A subquery can retrieve zero or more rows.
Only two subqueries can be placed at one level.
A subquery can be used only in SQL query statements.
A subquery can appear on either side of a comparison operator.
There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Correct Answer: AD
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abel#39;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.
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 gt;, =, or IN
Comparison conditions fall into two classes: single-row operators (gt;, =, gt;=, lt;, lt;gt;, lt;=) 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 quot;onlyquot; 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.
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
You issue the following query:
Which statement is true regarding the outcome of this query?
It produces an error because the NATURAL join can be used only with two tables
It produces an error because a column used in the NATURAL join cannot have a qualifier
It produces an error because all columns used in the NATURAL join should have a qualifier
It executes successfully
Correct Answer: B
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column
JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not.
An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?
Number of days since the promo started based on the current Chicago data and time
Number of days since the promo started based on the current Singapore data and time.
An error because the WHERE condition specified is invalid
An error because the ROUND function specified is invalid
Correct Answer: A
Which two are true about aggregate functions? (Choose two.)
You can use aggregate functions in any clause of a SELECT statement.
You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
You can use aggregate functions on a table, only by grouping the whole table as one single group.
You cannot group the rows of a table by more than one column while using aggregate functions.
Correct Answer: AD
Evaluate this SQL statement: SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d USING (department_id)
WHERE d.department_id NOT IN (10, 40) ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?
remove the ORDER BY clause
remove the table alias prefix from the WHERE clause
remove the table alias from the SELECT clause
prefix the column in the USING clause with the table alias
prefix the column in the ORDER BY clause with the table alias
replace the conditionquot;d.department_id NOT IN (10, 40)quot;in the WHERE clause withquot;d.department_id lt;gt; 10 AND d.department_id lt;gt; 40quot;
Correct Answer: CE
Prefix the column in the ORDER BY Clause would cause the statement to succeed, assuming that the statement failed because the dept_name existed in employee amp; department tables.
Not C: Removing the alias from the columns in the SELECT clause would cause the Statement to fail if the columns existing in both tables.
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Which statement is true regarding the above command?
The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match
The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
Correct Answer: B
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
CREATE TABLE table [(column, column…)] AS subquery;
Match the number of specified columns to the number of subquery columns. Define columns with column names and default values.
The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.
The column definition can contain only the column name and default value. If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY column will not pass the NOT NULL feature to the new column. Any other constraint rules are not passed to the new table. However, you can add constraints in the column definition.
Which statements are correct regarding indexes? (Choose all that apply.)
For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
When a table is dropped, the corresponding indexes are automatically dropped
Correct Answer: ABD
The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
GRANT select ON deptTO ALL_USERS;
GRANT select ON deptTO ALL;
GRANT QUERY ON deptTO ALL_USERS
GRANT select ON deptTO PUBLIC;
Correct Answer: D
view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
A.table to view all constraints definition and names B.show all object name belong to user
C.does not display column associated E.no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
Which two statements are true regarding working with dates? (Choose two.)
The default internal storage of dates is in the numeric format.
The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.
The default internal storage of dates is in the character format.
The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.
Correct Answer: AB Explanation: Working with Dates
The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.
The default display and input format for any date is DD-MON-RR.
RR Date Format
The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.
Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want.
This data is stored internally as follows:
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43