Examine the description of the EMPLOYEES table:
Which statement will execute successfully, returning distinct employees with non-null first names?
A. SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;
B. SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;
C. SELECT Distinct * FROM employees WHERE first_ name < > NULL;
D. SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;
Examine the data in the EMP table:
You execute this query:
SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"
FROM emp
WHERE sal >= 12000
GROUP BY "Department "
ORDER BY AverageSalary;
Why does an error occur?
A. An alias name must not be used in an ORDER BY clause.
B. An alias name must not contain space characters.
C. An alias name must not be used in a GROUP BY clause.
D. An alias name must always be specified in quotes.
Which two statements are true about Entity Relationships?
A. A Relationship can be mandatory for both entities
B. A one-to-one relationship is always a self-referencing relationship
C. A many-to-many relationship can be implemented only by using foreign keys
D. A table name can be specified just once when selecting data from a table having a selfreferencing relationship
E. A one-to-many relatonship in one direction is a one-to-one relationship in the other direction
Which three are true about system and object privileges
A. WITH GRANT OPTION can be used when granting an object privilege to both users and roles
B. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
C. Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading effect.
D. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect
E. Adding a primary key constraint to an existing table in another schema requires a system privilege
F. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs object privilege
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
Evaluate the following MERGE statement:
MERGE_INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?
A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 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?
A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
C. listing of customers who do not have a credit limit and were born before 1980
D. finding the number of customers, in each city, whose marital status is 'married'.
E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
Which two statements are true regarding the USING and ON clauses in table joins?
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
Which set of commands will prompt only once for the name of the table to use in the query?
A. PROMPT Enter table name andx SELECT employee_id FROM andx WHERE last_name = ‘King
Examine the description of the CUSTONERS table:
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
A. LEFT OUTER JOIN with self join
B. PULL OUTER JOIN with self join
C. subquery
D. RIGHT OUTER JOIN with self join
E. self Join
Examine this query:
SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
What is returned upon execution?
A. 0 rows
B. an error
C. 8 rows
D. 6 rows
E. 1 row
F. 3 rows