Oracle_test4

1. What does this expression produce? Type your answer in as a response.

SUBSTR(  TRANSLATE(  REPLACE('This is a string', ' ', '')    , 'ia', 'AI'), 5, 3)

The given expression starts by removing all spaces from the string 'This is a string' using the REPLACE function. The TRANSLATE function then replaces all occurrences of 'ia' with 'AI'. Finally, the SUBSTR function extracts a substring starting from the 5th character with a length of 3. Therefore, the expression produces the output 'AsI'.

Explanation

The given expression starts by removing all spaces from the string 'This is a string' using the REPLACE function. The TRANSLATE function then replaces all occurrences of 'ia' with 'AI'. Finally, the SUBSTR function extracts a substring starting from the 5th character with a length of 3. Therefore, the expression produces the output 'AsI'.

Submit

2. 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"?

No change is required to achieve the desired results.

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

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

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

The given SQL statement is calculating the annual compensation by multiplying the monthly salary by 12 and adding 100. To achieve the desired result of calculating the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12", the parentheses should be added around "sal+100" in the calculation. Therefore, the correct change that must be made to the above syntax is: SELECT ename, sal, 12* (sal+100) FROM emp.

Explanation

The given SQL statement is calculating the annual compensation by multiplying the monthly salary by 12 and adding 100. To achieve the desired result of calculating the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12", the parentheses should be added around "sal+100" in the calculation. Therefore, the correct change that must be made to the above syntax is: SELECT ename, sal, 12* (sal+100) FROM emp.

3. 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?

The results are not sorted.

The results are sorted numerically.

The results are sorted alphabetically.

The results are sorted numerically and then alphabetically.

The SQL statement will sort the results first by the "price" column in numerical order, and then by the "product_name" column in alphabetical order.

Explanation

The SQL statement will sort the results first by the "price" column in numerical order, and then by the "product_name" column in alphabetical order.

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

SELECT SUBSTR ('Hello',) FROM dual;

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

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

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

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

The correct answer is SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual; This query uses the TRIM function to remove the letter 'H' from the string 'Hello World', and then applies the LOWER function to convert the remaining string to lowercase. The result is 'ello world'.

Explanation

The correct answer is SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual; This query uses the TRIM function to remove the letter 'H' from the string 'Hello World', and then applies the LOWER function to convert the remaining string to lowercase. The result is 'ello world'.

5. 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?

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

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

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

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

The correct answer is "SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE
'%SA\_%'ESCAPE'\';"

This SQL statement uses the LIKE operator with the wildcard '%' to search for strings in the JOB_ID column that contain 'SA_' as a substring. The backslash '\' is used as an escape character to treat the underscore '_' as a literal character rather than a wildcard.

Explanation

The correct answer is "SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE
'%SA\_%'ESCAPE'\';"

This SQL statement uses the LIKE operator with the wildcard '%' to search for strings in the JOB_ID column that contain 'SA_' as a substring. The backslash '\' is used as an escape character to treat the underscore '_' as a literal character rather than a wildcard.

6. 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?

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

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

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

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

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

The correct answer is SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='smith'. This statement calculates the number of different departments that have employees with the last name Smith. It uses the COUNT function to count the number of distinct (unique) department IDs where the last name is Smith.

Explanation

The correct answer is SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='smith'. This statement calculates the number of different departments that have employees with the last name Smith. It uses the COUNT function to count the number of distinct (unique) department IDs where the last name is Smith.

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

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

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

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

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

The correct answer is "SELECT ename, salary*12 'Annual Salary' FROM employees;". This statement generates the alias "Annual Salary" for the calculated column SALARY*12 by using single quotes around the alias name.

Explanation

The correct answer is "SELECT ename, salary*12 'Annual Salary' FROM employees;". This statement generates the alias "Annual Salary" for the calculated column SALARY*12 by using single quotes around the alias name.

8. Which two statements are true regarding the ORDER BY clause?

The sort is in ascending by order by default.

The sort is in descending order by default.

The ORDER BY clause must precede the WHERE clause.

The ORDER BY clause is executed on the client side.

The ORDER BY clause comes last in the SELECT statement.

The first statement is true because if the ORDER BY clause does not specify the sort order as ascending or descending, it will default to ascending order. The fifth statement is also true because the ORDER BY clause is typically placed at the end of the SELECT statement to specify the sorting criteria for the result set.

Explanation

The first statement is true because if the ORDER BY clause does not specify the sort order as ascending or descending, it will default to ascending order. The fifth statement is also true because the ORDER BY clause is typically placed at the end of the SELECT statement to specify the sorting criteria for the result set.

Submit

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

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

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

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

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

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

The correct answer is SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; This statement uses the TO_CHAR function to convert the system date (SYSDATE) into a string in the format 'yyyy', which represents the year. The FROM dual clause is used to select from a dummy table in Oracle.

Explanation

The correct answer is SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; This statement uses the TO_CHAR function to convert the system date (SYSDATE) into a string in the format 'yyyy', which represents the year. The FROM dual clause is used to select from a dummy table in Oracle.

10. 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?

WHERE lower(country_address) = "france"

WHERE lower(country_address) = 'france'

WHERE lower(country_address) IS 'france'

WHERE lower(country_address) = '%france%'

WHERE lower(country_address) LIKE %france%

The correct answer is "WHERE lower(country_address) = 'france'". This WHERE clause is checking if the country_address column is equal to the string 'france' after converting it to lowercase. This will identify customers that are located in France.

Explanation

The correct answer is "WHERE lower(country_address) = 'france'". This WHERE clause is checking if the country_address column is equal to the string 'france' after converting it to lowercase. This will identify customers that are located in France.

11. 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?

SELECT dear customer, customer_name, FROM customer;

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

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

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

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

The correct answer is "SELECT 'Dear Customer' || customer_name || ',' FROM customer;". This statement concatenates the string 'Dear Customer' with the values from the customer_name column in the CUSTOMERS table, and adds a comma at the end. This will produce the desired output of "Dear Customer customer_name," for each row in the table.

Explanation

The correct answer is "SELECT 'Dear Customer' || customer_name || ',' FROM customer;". This statement concatenates the string 'Dear Customer' with the values from the customer_name column in the CUSTOMERS table, and adds a comma at the end. This will produce the desired output of "Dear Customer customer_name," for each row in the table.

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

INSTR returns the numeric position of a named character.

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

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

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

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

INSTR is a function in SQL that returns the numeric position of a named character within a string. TRIM is another function that trims the heading or trailing characters (or both) from a character string. Both of these statements correctly describe the functions available in SQL.

Explanation

INSTR is a function in SQL that returns the numeric position of a named character within a string. TRIM is another function that trims the heading or trailing characters (or both) from a character string. Both of these statements correctly describe the functions available in SQL.

Submit

13. 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?

17000.00

17000*****

****17000

**17000.00

An error statement

The SQL statement is attempting to use the LPAD function to left pad the salary value with asterisks (*) to a total length of 10 characters. However, the LPAD function expects the second argument to be a number, not a string. Therefore, the SQL statement will result in an error.

Explanation

The SQL statement is attempting to use the LPAD function to left pad the salary value with asterisks (*) to a total length of 10 characters. However, the LPAD function expects the second argument to be a number, not a string. Therefore, the SQL statement will result in an error.

14. What will the following expression return when the PRICE is NULL?

POWER((NVL(PRICE, 0) / NVL(PRICE, 1)) * 5, 2)

25

5

2

1

None of the above

The expression calculates the result of (PRICE / PRICE) * 5 and then raises it to the power of 2. Since PRICE is NULL, the NVL function replaces it with 0 in the numerator and 1 in the denominator. Therefore, the expression becomes (0 / 1) * 5, which equals 0. When 0 is raised to the power of 2, the result is still 0. Hence, the correct answer is 1.

Explanation

The expression calculates the result of (PRICE / PRICE) * 5 and then raises it to the power of 2. Since PRICE is NULL, the NVL function replaces it with 0 in the numerator and 1 in the denominator. Therefore, the expression becomes (0 / 1) * 5, which equals 0. When 0 is raised to the power of 2, the result is still 0. Hence, the correct answer is 1.

15. 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?

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

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

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

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

The correct answer is "SELECT last_name FROM EMP WHERE last name ='_A%';". This statement uses the LIKE operator with the wildcard "_" to match any single character, followed by "A%" to match any string starting with "A". Therefore, it will display the last names of employees who have the letter "A" as the second character in their names.

Explanation

The correct answer is "SELECT last_name FROM EMP WHERE last name ='_A%';". This statement uses the LIKE operator with the wildcard "_" to match any single character, followed by "A%" to match any string starting with "A". Therefore, it will display the last names of employees who have the letter "A" as the second character in their names.