Oracle_test4

Which SELECT statement will the result 'ello world' from the string 'Hello World'?

• A.

  SELECT SUBSTR ('Hello',) FROM dual;

• B.

  SELECT INITCAP (TRIM ('Hello World',1,1) FROM dual;

• C.

  SELECT LOWER (SUBSTR ('Hello World',1,1) FROM dual;

• D.

  SELECT LOWER (SUBSTR ('Hello World',2,1) FROM dual;

• E.

  SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual;

Which four statements correctly describe functions that are available in SQL?

• A.

  INSTR returns the numeric position of a named character.

• B.

  NVL2 returns the first non-null expression in the expression list.

• C.

  TRUNCATE rounds the column, expression, or value to n decimal places

• D.

  DECODE translates an expression after comparing it to each search value.

• E.

  TRIM trims the heading of trailing characters (or both) from a character string.

 Which two statements are true regarding the ORDER BY clause?

• A.

  The sort is in ascending by order by default.

• B.

  The sort is in descending order by default.

• C.

  The ORDER BY clause must precede the WHERE clause.

• D.

  The ORDER BY clause is executed on the client side.

• E.

  The ORDER BY clause comes last in the SELECT statement.

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12? 

• A.

  SELECT ename, salary*12 'Annual Salary' FROM employees;

• B.

  SELECT ename, salary*12 "Annual Salary" FROM employees;

• C.

  SELECT ename, salary*12 AS Annual Salary FROM employees;

• D.

  SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees

You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?

• A.

  SELECT last_name FROM EMP WHERE last_ name LIKE '_A%';

• B.

  SELECT last_name FROM EMP WHERE last name ='*A%'

• C.

  SELECT last_name FROM EMP WHERE last name ='_A%';

• D.

  SELECT last_name FROM EMP WHERE last name LIKE '*A%'

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

• A.

  SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

• B.

  SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;

• C.

  SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

• D.

  SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;

• E.

  SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?

• A.

  SELECT COUNT (*) FROM employees WHERE last _name='smith';

• B.

  SELECT COUNT (dept_id) FROM employees WHERE last _name='smith';

• C.

  SELECT DISTINCT (COUNT (dept_id) FROM employees WHERE last _name='smith';

• D.

  SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='smith';

• E.

  SELECT UNIQE (dept_id) FROM employees WHERE last _name='smith';

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 "monthly salary plus a monthly bonus of $100, multiplied by 12"?

• A.

  No change is required to achieve the desired results.

• B.

  SELECT ename, sal, 12* (sal+100) FROM emp;

• C.

  SELECT ename, sal, (12* sal)+100 FROM emp;

• D.

  SELECT ename, sal +100,*12 FROM emp;

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

• A.

  SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%'ESCAPE'\';

• B.

  SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';

• C.

  SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'ESCAPE'\';

• D.

  SELECT employee_id, last_name, job_id FROM employees WHERE job_id '%SA_';

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 customer;

• B.

  SELECT "Dear Customer", customer_name, ||',' FROM customer;

• C.

  SELECT 'Dear Customer' || customer_name ',' FROM customer;

• D.

  SELECT 'Dear Customer' || customer_name || ',' FROM customer;

• E.

  SELECT "Dear Customer" || customer_name || "," FROM customer;

The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) POSTAL_CODE VARCHAR2(12) CUSTOMER_pHONE VARCHAR2(20) A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?

• A.

  WHERE lower(country_address) = "france"

• B.

  WHERE lower(country_address) = 'france'

• C.

  WHERE lower(country_address) IS 'france'

• D.

  WHERE lower(country_address) = '%france%'

• E.

  WHERE lower(country_address) LIKE %france%

The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8,2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product _ name; What is true about the SQL statement?

• A.

  The results are not sorted.

• B.

  The results are sorted numerically.

• C.

  The results are sorted alphabetically.

• D.

  The results are sorted numerically and then alphabetically.

Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP WHERE EMP _ ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

• A.

  17000.00

• B.

  17000*****

• C.

  ****17000

• D.

  **17000.00

• E.

  An error statement

What will the following expression return when the PRICE is NULL? POWER((NVL(PRICE, 0) / NVL(PRICE, 1)) * 5, 2)  

• A.

  25

• B.

  5

• C.

  2

• D.

  1

• E.

  None of the above

What does this expression produce? Type your answer in as a response. SUBSTR(  TRANSLATE(  REPLACE('This is a string', ' ', '')    , 'ia', 'AI'), 5, 3)