Oracle_test4
-
What does this expression produce? Type your answer in as a response. SUBSTR( TRANSLATE( REPLACE('This is a string', ' ', '') , 'ia', 'AI'), 5, 3)
Oracle_test4 is designed to assess skills in SQL, focusing on functions, query syntax, and data manipulation. The quiz evaluates understanding of SELECT statements, ORDER BY clause, and string manipulation in SQL, essential for database management proficiency.
Quiz Preview
- 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;
Correct Answer
A. 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.Rate this question:
-
- 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.
Correct Answer
A. The results are sorted numerically and then alphabetically.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.Rate this question:
-
- 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;
Correct Answer
A. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual;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'.Rate this question:
-
- 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_';
Correct Answer
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%'ESCAPE'\';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.Rate this question:
-
- 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';
Correct Answer
A. SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='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.Rate this question:
-
- 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
Correct Answer
A. SELECT ename, salary*12 "Annual Salary" FROM employees;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.Rate this question:
-
- 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.
Correct Answer(s)
A. The sort is in ascending by order by default.
A. The ORDER BY clause comes last in the SELECT statement.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.Rate this question:
-
- 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;
Correct Answer
A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;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.Rate this question:
-
- 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%
Correct Answer
A. WHERE lower(country_address) = '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.Rate this question:
-
- 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;
Correct Answer
A. SELECT 'Dear Customer' || customer_name || ',' FROM customer;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.Rate this question:
-
- 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.
Correct Answer(s)
A. INSTR returns the numeric position of a named character.
A. TRIM trims the heading of trailing characters (or both) from a character string.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.Rate this question:
-
- 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
Correct Answer
A. An error statementExplanation
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.Rate this question:
-
- 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
Correct Answer
A. 1Explanation
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.Rate this question:
-
- 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%'
Correct Answer
A. SELECT last_name FROM EMP WHERE last name ='_A%';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.Rate this question:
-
Quiz Review Timeline (Updated): Mar 22, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
Jan 04, 2010Quiz Created by
Sudha_test
Back to top