M6 SQL

Question 2

Which join returns all rows from the left table and matching rows from the right?

Answer 2

LEFT OUTER JOIN

Question 3

Which join returns all rows from the right table and matching rows from the left?

Answer 3

RIGHT OUTER JOIN

Question 4

Fill in the blank: A non-equality join uses comparison operators like <, >, BETWEEN instead of _____.

Answer 4

=

Question 5

Which set operator combines results from two SELECTs and removes duplicates?

Answer 5

UNION

Question 6

Which set operator combines all rows from two SELECTs including duplicates?

Answer 6

UNION ALL

Question 7

Which set operator returns only the rows that appear in both SELECTs?

Answer 7

INTERSECT

Question 8

Which clause is used in a traditional join method?

Answer 8

WHERE clause

Question 9

How do you avoid Cartesian joins?

Answer 9

Include a proper join condition using ON or WHERE

Question 10

What function extracts a portion of a string based on position?

Answer 10

SUBSTR

Question 11

Which function returns the number of characters in a string?

Answer 11

LENGTH

Question 12

Function that pads the right side of a string with a character?

Answer 12

RPAD

Question 13

Function that removes a character from the left side of a string?

Answer 13

LTRIM

Question 14

Function that removes a character from the right side of a string?

Answer 14

RTRIM

Question 15

What function replaces single characters in a string with others?

Answer 15

TRANSLATE

Question 16

What function joins two strings together?

Answer 16

CONCAT

Question 17

What function converts all letters in a string to lowercase?

Answer 17

LOWER

Question 18

What function capitalizes the first letter of each word in a string?

Answer 18

INITCAP

Question 19

What function rounds a number to a specified decimal place?

Answer 19

ROUND

Question 20

Function that truncates a number after a decimal place?

Answer 20

TRUNC

Question 21

Which function returns the remainder of a division operation?

Answer 21

MOD

Question 22

Function that returns the absolute value of a number?

Answer 22

ABS

Question 23

What function raises one number to the power of another?

Answer 23

POWER

Question 24

Function that adds a number of months to a date?

Answer 24

ADD_MONTHS

Question 25

Function that returns the next occurrence of a specified weekday?

Answer 25

NEXT_DAY

Question 26

Function that returns the last day of the month for a date?

Answer 26

LAST_DAY

Question 27

What does CURRENT_DATE return?

Answer 27

The current date and time from the session's time zone

Question 28

What does SYSDATE return?

Answer 28

The current date and time from the operating system

Question 29

What does ROUND(date, 'MONTH') do?

Answer 29

Rounds the date to the nearest month

Question 30

What does TRUNC(date, 'YEAR') do?

Answer 30

Truncates the date to the first day of the year

Question 31

What does NVL(x, y) return if x is NULL?

Answer 31

y

Question 32

What does TO_CHAR(date, 'YYYY-MM-DD') return?

Answer 32

A formatted string representing the date

Question 33

What does TO_NUMBER('123') return?

Answer 33

The numeric value 123

Question 34

What function compares a value to multiple values and returns a match?

Answer 34

DECODE

Question 35

What expression provides complex IF-THEN-ELSE logic in SQL?

Answer 35

CASE expression

Question 36

What function checks if a string matches a regular expression pattern?

Answer 36

REGEXP_LIKE

Question 37

What function extracts part of a string matching a pattern?

Answer 37

REGEXP_SUBSTR

Question 38

What regular expression pattern matches exactly three digits?

Answer 38

[0-9]{3}

Question 39

What happens when functions are nested?

Answer 39

The inner function is evaluated first

Question 40

Which function returns the average of a numeric column?

Answer 40

AVG

Question 41

What function returns the maximum value in a column?

Answer 41

MAX

Question 42

What function returns the minimum value in a column?

Answer 42

MIN

Question 43

What group function determines how spread out data is?

Answer 43

VARIANCE

Question 44

What keyword is used to include only unique values in an aggregate?

Answer 44

DISTINCT

Question 45

What clause performs multiple GROUP BYs in one query?

Answer 45

GROUPING SETS

Question 46

What clause creates hierarchical totals from GROUP BY columns?

Answer 46

ROLLUP

Question 47

Can group functions be nested?

Answer 47

Yes, up to two levels deep

Question 48

What is the correct order of SQL clauses: WHERE, HAVING, GROUP BY?

Answer 48

WHERE -> GROUP BY -> HAVING

Question 49

Explain the primary difference between an inner join and an outer join.

Answer 49

An inner join returns rows only when there is a match in both tables involved in the join, effectively showing only intersecting data. In contrast, an outer join returns all rows from one specified table (the preserved table) and only the matching rows from the other table, including NULLs for non-matching rows from the second table.

Question 50

When would you use a self-join, and what is a crucial syntax requirement for performing one?

Answer 50

A self-join is used when you need to establish a relationship between different rows within the same table, such as finding employees who referred other employees in a single employee table. A crucial syntax requirement is the use of table aliases, which makes Oracle 12c treat the single table as if it were two separate tables during the query execution.

Question 51

Describe the purpose of the NVL function and provide a scenario where it would be essential.

Answer 51

The NVL function substitutes a specified value for a NULL value in a column. It is essential when performing arithmetic operations on columns that might contain NULLs, because any calculation involving a NULL value will result in NULL, leading to inaccurate results without NVL.

Question 52

What is the DUAL table used for in Oracle SQL, and why is it beneficial?

Answer 52

The DUAL table is a special Oracle table with a single column and a single row, primarily used to test functions and expressions. It is beneficial because it allows you to execute SQL queries that do not require referencing actual user data tables, making it convenient for quick function testing or retrieving system values like SYSDATE.

Question 53

Differentiate between the ROUND and TRUNC functions when applied to numeric data.

Answer 53

The ROUND function rounds a numeric value to a specified precision, adjusting the number up or down based on standard rounding rules. The TRUNC function, on the other hand, truncates a numeric value to a specified position by simply removing any digits after that position without rounding.

Question 54

Explain the purpose of the INSTR function and how it differs from SUBSTR.

Answer 54

The INSTR function searches a string for a specified substring and returns the starting numerical position where the substring is found. SUBSTR extracts a portion of a string (a substring) based on a specified starting position and an optional length. INSTR locates a substring, while SUBSTR extracts a substring.

Question 55

What is a Cartesian join, and how can it accidentally be created in a SQL query?

Answer 55

A Cartesian join, also known as a cross join, is created when the joining condition between two tables is accidentally omitted in the WHERE clause or explicitly stated using the CROSS JOIN keywords. It results in every possible row combination from the first table multiplied by every row from the second table (m * n rows).

Question 56

When should the HAVING clause be used instead of the WHERE clause in a SELECT statement?

Answer 56

The HAVING clause should be used to restrict groups based on conditions involving aggregate functions, acting after the GROUP BY clause has organized the data. The WHERE clause, however, is used to filter individual rows before any grouping or aggregation takes place.

Question 57

Describe the primary function of the TO_CHAR function and give an example of its use for a date.

Answer 57

The TO_CHAR function converts dates and numbers into a formatted character string, allowing for custom display formats. For example, SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual; would display the current date in a format like "December 17, 2009."

Question 58

What is the maximum depth for nesting group functions, and how does the evaluation order work when functions are nested?

Answer 58

Group functions can be nested to a maximum depth of two. When functions are nested, the inner function is always evaluated first, and its computed result is then passed as the input argument to the outer function for its execution.