Online Access Free 1Z1-051 Practice Test

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

• A.The WHERE clause can be used to apply additional conditions in SELECT statementcontaining the ON or the USING clause
• B.Both USING and ON clause can be used for equijoins and nonequijoins
• C.The ON clause can be used to join tables on columns that have different names but compatible data types
• D.A maximum of one pair of columns can be joined between two tables using the ON clause

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A.SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
• B.SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• C.SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D.SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;

Which four are types of functions available in SQL? (Choose 4)

• A.calendar
• B.translation
• C.conversion
• D.date
• E.string
• F.character
• G.integer
• H.numeric

Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

• A.Finding the number of characters in an expression
• B.Substituting a character string in a text expression with a specified string
• C.Displaying a date in a nondefault format
• D.Combining more than two columns or expressions into a single column in the output

The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states "Dear Customer customer_name, ".
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.
Which statement produces this output?

• A.SELECT 'Dear Customer ' || customer_name ',' FROM customers;
• B.SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
• C.SELECT "Dear Customer", customer_name || ',' FROM customers;
• D.SELECT dear customer, customer_name, FROM customers;
• E.SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
• F.SELECT "Dear Customer " || customer_name || "," FROM customers;