Scribd
Upload

Section 4 SQL Quiz Answers

315 views6 pages
The SQL statement SELECT last_name, LPAD(salary, 15, '$')SALARY FROM employees; will display each employee's last name and format their salary value to be left-padded to 15 characters with a…

Uploaded by

Angga Wijaya
  • Section 4

Section 4

(Answer all questions in this section)


1. What will the following SQL statemtent display?
SELECT last_name, LPAD(salary, 15, '$')SALARY
FROM employees;

Mark for Review

(1) Points
The last name of employees that have a salary that includes a $ in the
value, size of 15 and the column labeled SALARY.
The query will result in an error: "ORA-00923: FROM keyword not found
where expected."
The last name and the format of the salary limited to 15 digits to the left of
the decimal and the column labeled SALARY.
The last name and salary for all employees with the format of the salary 15
characters long, left-padded with the $ and the column labeled SALARY. (*)

Correct

2. Which character manipulation function always returns a numerical value?


Mark for Review

(1) Points
SUBSTR
LENGTH (*)
LPAD
TRIM
Correct

3. Which SQL function is used to return the position where a specific


character string begins within a larger character string?
Mark for Review

(1) Points
INSTR (*)
LENGTH
SUBSTR
CONCAT
Correct

4. The PRICE table contains this data:


PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95. Which script did you use?

Mark for Review

(1) Points
SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;
SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604; (*)
Incorrect. Refer to Section 4 Lesson 1.

5. Evaluate this SELECT statement:


SELECT LENGTH(email)
FROM employee;

What will this SELECT statement display?

Mark for Review

(1) Points
The longest e-mail address in the EMPLOYEE table
The maximum number of characters allowed in the EMAIL column
The email address of each employee in the EMPLOYEE table
The number of characters for each value in the EMAIL column in the
employees table (*)
Correct
6. Which of the following SQL statements will correctly display the last name
and the number of weeks employed for all employees in department 90?
Mark for Review

(1) Points
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90; (*)
SELECT last name, (SYSDATE-hire_date)/7 DISPLAY WEEKS
FROM employees
WHERE department id = 90;
SELECT last_name, (SYSDATE-hire_date)AS WEEK
FROM employees
WHERE department_id = 90;
SELECT last_name, # of WEEKS
FROM employees
WHERE department_id = 90;
Correct

7. You need to subtract three months from the current date. Which function
should you use?
Mark for Review

(1) Points
TO_DATE
MONTHS_BETWEEN
ROUND
ADD_MONTHS (*)
Correct

8. The EMPLOYEES table contains these columns:


LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
HIRE_DATE DATE
EVAL_MONTHS NUMBER(3)

Evaluate this SELECT statement:

SELECT hire_date + eval_months


FROM employees;
The values returned by this SELECT statement will be of which data type?

Mark for Review

(1) Points
NUMBER
INTEGER
DATETIME
DATE (*)
Correct

9. Which of the following Date Functions will add calendar months to a date?
Mark for Review

(1) Points
MONTHS + Date
NEXT_MONTH
Months + Calendar (Month)
ADD_MONTHS (*)
Correct

10. Round and Trunc cannot be used on Date datatypes. True or False?


Mark for Review

(1) Points
True
False (*)
Correct
11. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
Mark for Review

(1) Points
46
45.9
50 (*)
None of the above
Correct
12. Which comparison operator retrieves a list of values?
Mark for Review

(1) Points
LIKE
IS NULL
BETWEEN IN
IN (*)
Correct

13. Which two functions can be used to manipulate number or date column


values, but NOT character column values? (Choose two.)
Mark for Review

(1) Points
CONCAT
RPAD
TRUNC(*)

ROUND(*)

INSTR
Correct

14. You issue this SQL statement:


SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?

Mark for Review

(1) Points
1282.25
1282
1300 (*)
1200
Correct

15. Which number function may be used to determine if a value is odd or


even?
Mark for Review
(1) Points
ROUND
TRUNC
MOD (*)
BINARY
Correct

Common questions

Powered by AI

Evaluating date and time functions is vital in automating transactional record keeping, reporting, and historical data analysis. By leveraging functions such as ADD_MONTHS for future planning, or ROUND/TRUNC for ensuring calculation precision, database queries are optimized for both performance and accuracy. This enhances consistent data presentation and supports complex analyses, crucial for strategic decision-making .

The ROUND function is used to round numerical values to a specified number of decimal places or whole numbers. When a negative precision is specified, as in 'SELECT ROUND(45.923, -1) FROM DUAL;', it rounds to the nearest ten, resulting in 50. This function is crucial for preparing numerical data for reports where such rounding aligns with business rules or display requirements .

While TRUNC truncates a number to a specified number of decimal places, MOD is used to return the remainder of a division operation. In evaluating numeric data characteristics, these functions can complement each other by enabling calculations such as separating integer parts from decimal or determining even/odd nature of integers, which can be essential for data categorization or mathematical computations .

The TRIM function in SQL is specifically used to remove unwanted leading and trailing characters (usually spaces) from strings, enhancing data quality before analysis. Unlike RPAD or LPAD, which pad string lengths, TRIM focuses on cleaning and ensuring data integrity by eliminating extraneous characters that may disrupt pattern matching or comparisons .

Subtracting time durations in SQL involves accurately managing the transition across calendar boundaries (months, years). The function ADD_MONTHS is used with a negative parameter to subtract months from the current date, thus facilitating the calculation of past dates, deadlines or historical data snapshot boundaries without manual calculation errors .

The IN operator in SQL is significant for filtering a record set by specifying multiple possible values for a column. This operator simplifies the query process where multiple OR conditions would be cumbersome, thereby enhancing query clarity and efficiency. It is especially useful in situations where filters have to be applied across a discrete set of known values .

The LENGTH function in SQL returns the number of characters in a string. When applied to email addresses in a database, as in 'SELECT LENGTH(email) FROM employee;', it provides the character count for each email entry in the EMPLOYEE table. This function helps in understanding the data distribution or for performing operations based on email length, such as truncating overly long emails or ensuring they meet specific length criteria .

Improper use of the SUBSTR function can lead to incorrect data slicing, which affects data interpretation and processing. For instance, querying with 'SELECT SUBSTR(product_id, 3, 2) FROM price;' correctly retrieves substring values, but errors in indices can lead to incorrect values that mislead analysis or application logic, particularly in contexts involving critical identifiers or codes .

The ADD_MONTHS function in SQL is used to add a specified number of calendar months to a date. By doing so, it adjusts the date accurately handling day and month boundaries. This function is particularly useful for computing future deadlines or billing cycles by shifting the date forward by the desired number of months .

The LPAD function in SQL left-pads a string with a specified character to a specified length. In the query 'SELECT last_name, LPAD(salary, 15, '$') SALARY FROM employees;', the LPAD function pads the 'salary' column values with the '$' character to make each salary string 15 characters long. This is used to format the salary display where each salary value appears as a left-padded string of uniform length, enhancing readability and comparability .

Section 4 (Answer all questions in this section) 1. What will the following SQL statemtent display? SELECT last_name, LPAD(sa
Correct 4. The PRICE table contains this data: PRODUCT_ID MANUFACTURER_ID 86950 59604 You query the database and return the v
6. Which of the following SQL statements will correctly display the last name and the number of weeks employed for all employ
The values returned by this SELECT statement will be of which data type? Mark for Review (1) Points NUMBER INTEGER DATETIME D
12. Which comparison operator retrieves a list of values? Mark for Review (1) Points LIKE IS NULL BETWEEN IN IN (*) Correct 1
(1) Points ROUND TRUNC MOD (*) BINARY Correct

You might also like