D426 Operators

What is the function of the unary - operator in SQL?

It subtracts one numeric value from another.

It returns the absolute value of a number.

It is used for string concatenation.

It reverses the sign of one numeric value.

It reverses the sign of one numeric value. i.e -5

Subtracting one numeric value from another is binary, not unary.

Which wildcard in LIKE matches exactly one character?

A. %

B. *

C. _

D. #

C. _ In SQL LIKE, underscore (_) matches exactly one character; % matches any number of characters.

LIKE BINARY ‘%E%’;

What does the BINARY do?

BINARY makes the pattern matching case-sensitive. An example matching word would be ‘English’ (because of the capital E).

Which SQL statement removes specific rows from a table?

ERASE

DELETE

REMOVE

DROP

DELETE

❌ DROP is for tables/databases

❌ REMOVE is not a SQL command

❌ ERASE is not a SQL command

Which numeric data type is approximate and uses floating point?

A. DECIMAL

B. INT

C. FLOAT

D. DATE

C. FLOAT. FLOAT is an approximate floating-point numeric type; DECIMAL is exact fixed-point, INT is integer, DATE is temporal.

To store the number of employees in a small company (which will not exceed 30,000), which integer data type offers the best storage efficiency?

TINYINT

SMALLINT

INT

BIGINT

SMALLINT

TINYINT

-128 to 127

SMALLINT

-32,768 to 32,767

Which function returns the current date and time in many SQL implementations?

A. MOMENT()

B. DATE_ADD()

C. CURRENT_TIMESTAMP (or NOW())

D. TODAY()

C. CURRENT_TIMESTAMP (or NOW())

NOW() (and CURRENT_TIMESTAMP) return current date/time in many SQL dialects; DATE_ADD is for arithmetic, TODAY() is not standard SQL.

To store a 5-digit US ZIP code like '90210', which data type provides the most efficient and appropriate storage? 

INT 

VARCHAR(5)

CHAR(5)

DECIMAL(5,0)

CHAR(5)

❌ INT will remove leading zeros (like for zipcodes like 07356)

❌ DECIMAL will remove leading zeros

❌ VARCHAR(5) will allow any length of input from 1-5, when we want exactly 5.

If you need to store a variable-length name up to 100 characters, which type is most appropriate?

A. CHAR(100)

B. VARCHAR(100)

C. TEXT(100)

D. BLOB(100)

B. VARCHAR(100)

VARCHAR is variable-length up to the max and is more storage-efficient for varying-length names than fixed CHAR(100); TEXT is overkill.

ROUND(n,d) returns ___

Returns n rounded to d decimal places

REPLACE(s, from, to) Returns the string s with all occurrences of from replaced with to.

SELECT REPLACE('This and that', 'and', 'or');

Returns ___

Returns ‘This or that’

SUBSTRING(s, pos, len) Returns the substring from s that starts at position pos and has length len.

SELECT SUBSTRING('Boomerang', 1, 4);

Returns ___

‘Boom’. String positions in SQL start at 1. So B is position 1.

CURDATE(); returns the current date, CURTIME(); returns the current time, and ___ returns the current date AND time

NOW();

Which data type would you choose to store an exact monetary value with two decimal places?

A. FLOAT

B. DECIMAL(10,2)

C. VARCHAR(10)

D. DOUBLE

B. DECIMAL(10,2). DECIMAL gives exact fixed-point precision needed for money; FLOAT and DOUBLE are approximate, and VARCHAR is textual.

Which data type is fixed-length and pads unused characters?

A. VARCHAR(N)

B. TEXT

C. CHAR(N)

D. BLOB

C. CHAR(N). CHAR is fixed-length and pads unused characters; VARCHAR is variable length, TEXT is for larger variable-length data, and BLOB is binary.

Which data type is best for storing large binary objects like images?

A. TEXT

B. BLOB

C. CHAR

D. DATE

B. BLOB. BLOB stores binary large objects, such as images; TEXT is for large text, CHAR/VARCHAR for short strings, DATE for dates.

What does the DISTINCT clause do in a SELECT?

A. Removes NULL values from results

B. Returns only unique values for the selected expressions

C. Orders the result set uniquely

D. Limits rows returned to 100

B. Returns only unique values for the selected expressions. DISTINCT removes duplicate result rows for the selected expressions; it does not remove NULLs specifically, nor order or limit rows.

LOG(n) returns ___

Returns the natural logarithm of n

What is square root?

A value that, when multiplied by itself, gives you the original number. The square root of 9 is 3, because 3×3=9.

ABS(n) returns ___

The absolute value of n

What is absolute value?

Absolute value is the distance a number is from zero on the number line. It is always positive.

According to standard operator precedence, which of the following operators is evaluated before AND but after comparison operators like = or >?

<

+ (binary)

NOT

*

NOT

Common SQL operator precedence order is:

1. *, / (multiplication, division)

2. +, - (addition, subtraction)

3. Comparison operators (=, >, <, >=, <=, <>)

4. NOT

5. AND

6. OR

What is the function of the unary - operator in SQL?

It subtracts one numeric value from another.

It returns the absolute value of a number.

It is used for string concatenation.

It reverses the sign of one numeric value.

It reverses the sign of one numeric value. i.e -5

Subtracting one numeric value from another is binary, not unary.

Which of the following comparison operators is used to check for inequality in SQL?

~=

!==

<>

=!

<>

❌ =! is invalid syntax.

!= would be correct, but is not listed here. <> is the same thing. It just means greater than or less than the value next to it, which is the same as “not this value”.

The ___ operator is either unary or binary arithmetic operator

-

The - operator usually has two numeric operands. Ex: 5.4 - 3.0. Occasionally, the - operator changes the sign of a single numeric operand. Ex: -DegreesCentigrade.

The ___ operator is a binary arithmetic operator

%

The % operator returns the remainder of one operand divided by another. The remainder is converted to an integer. The % operator is pronounced 'modulo'.

The ___ operator is a binary comparison operator

!=

!= compares two operands of the same data type, and returns TRUE if the operands are different values. The <> operator is equivalent to !=, and most databases support both operators.

The ___ operator is a unary logical operator

NOT

NOT takes a single (unary) logical operand. NOT TRUE returns FALSE. NOT FALSE returns TRUE.

The ___ is a binary logical operator

AND

AND takes two (binary) logical operands and returns one logical value. AND returns TRUE when both operands are TRUE. AND returns FALSE when either operand is FALSE.

Which SQL operator is used to match a value against a list of possible values?

A. BETWEEN

B. LIKE

C. IN

D. EXISTS

C. IN.

IN checks whether a value matches any value in a list, while BETWEEN checks a range, LIKE checks patterns, and EXISTS tests for rows returned by a subquery.

Which operator returns TRUE only when both operands are TRUE?

A. OR

B. NOT

C. AND

D. BETWEEN

C. AND.

AND returns TRUE only when both operands are TRUE; OR returns TRUE if either is TRUE and NOT negates.

Which of the following is a comparison operator?

A. CONCAT

B. !=

C. ROUND

D. GROUP BY

B. !=

!= (or <>) is a comparison operator for inequality; CONCAT and ROUND are functions, GROUP BY is a clause.

Which operator is used to negate a logical expression?

A. NOT

B. !=

C. <>

D. IS NOT

A. NOT

NOT negates a boolean expression; != and <> are inequality operators while IS NOT is used in specific contexts (e.g., IS NOT NULL).

Which SQL logical operator returns FALSE only when both operands are FALSE?

A. AND

B. OR

C. XOR

D. NOT

B. OR.

OR returns FALSE only when both operands are FALSE; AND returns TRUE only when both are TRUE, NOT negates.

A relational operator that allows for the combination of information from two or more tables is known as the ____ operator.

A. SELECT
B. PROJECT
C. JOIN
D. DIFFERENCE

C. The Join clause facilitates the connection of two table through identification of a common attribute.

The LIKE operator is used for ____.

A. BETWEEN
B. IS NULL
C. pattern matching
D. IN

C. The LIKE operator combined with wildcard characters can evaluate string values.