Untitled Quiz
29 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Which function would you use to add '0' characters to the left of a job_id to ensure it is always 10 characters long?

  • `TRIM(job_id, 10, '0')`
  • `REPLACE(job_id, 10, '0')`
  • `LPAD(job_id, 10, '0')` (correct)
  • `RPAD(job_id, 10, '0')`

What is the purpose of the REPLACE function?

  • To change the case of a string (uppercase/lowercase).
  • To remove a specific character from a string.
  • To substitute a specified substring with another substring within a string. (correct)
  • To find a substring within a string.

Which function removes characters from the beginning, end, or both ends of a string?

  • `REPLACE`
  • `LPAD`
  • `SUBSTR`
  • `TRIM` (correct)

What will ROUND(15.75, -1) return?

<p>20 (D)</p> Signup and view all the answers

What will TRUNCATE(15.75, -1) return?

<p>10 (A)</p> Signup and view all the answers

Which function returns the remainder of a division operation?

<p><code>MOD</code> (D)</p> Signup and view all the answers

Which of the following is NOT a characteristic of single-row functions in SQL?

<p>They can manipulate groups of rows to give one result per group. (D)</p> Signup and view all the answers

In which SQL clauses can single-row functions be used?

<p>SELECT, WHERE, and ORDER BY clauses (D)</p> Signup and view all the answers

Which of the following best describes the purpose of number functions in SQL?

<p>To accept numeric input and return numeric values. (A)</p> Signup and view all the answers

What is the result of FLOOR(45.99)?

<p>45.0 (B), 45 (C)</p> Signup and view all the answers

Which function can be used to calculate the square root of a number?

<p><code>SQRT()</code> (D)</p> Signup and view all the answers

What is a key difference between single-row and multiple-row functions?

<p>Multiple-row functions operate on groups of rows. (D)</p> Signup and view all the answers

What is the result of POWER(2, 3)?

<p>8 (D)</p> Signup and view all the answers

Which type of SQL function is best suited for extracting the month from a date column?

<p>Date function (C)</p> Signup and view all the answers

If a single-row function accepts a number as an argument, what type of value will it return?

<p>It depends on the specific function (B)</p> Signup and view all the answers

Which of the followings is true about nesting functions?

<p>Single-row functions can be nested. (B)</p> Signup and view all the answers

You need to display employee names in uppercase. Which type of SQL function would you use?

<p>Character function (A)</p> Signup and view all the answers

Which of the following scenarios would necessitate the use of a conversion function?

<p>Concatenating a number with a string for display. (B)</p> Signup and view all the answers

A query returns 15 rows. If a single-row function is applied in the SELECT statement, how many results will be returned?

<p>15 (D)</p> Signup and view all the answers

Which function is used to convert a string to lowercase?

<p><code>LOWER</code> (D)</p> Signup and view all the answers

Which function extracts a substring from a given string?

<p><code>SUBSTR</code> (C)</p> Signup and view all the answers

What will be the output of SELECT LENGTH('Data Science');?

<p>12 (B)</p> Signup and view all the answers

What does the INSTR function do?

<p>It finds the starting position of a substring within a string. (D)</p> Signup and view all the answers

Given the job_id is 'AD_VP', what will SELECT SUBSTR(job_id, 4); return?

<p><code>VP</code> (B)</p> Signup and view all the answers

In a database where last names are stored with consistent capitalization (e.g., 'Higgins'), what is the purpose of using LOWER(last_name) = 'higgins' in a SQL query?

<p>To ensure the query is case-insensitive, matching 'Higgins', 'higgins', etc. (A)</p> Signup and view all the answers

What will be the result of the following SQL statement: SELECT CONCAT('Hello', ' ', 'World');?

<p><code>Hello World</code> (B)</p> Signup and view all the answers

You need to extract the last three characters from a column named product_code. Which SQL statement should you use?

<p><code>SELECT SUBSTR(product_code, -3) FROM products;</code> (A)</p> Signup and view all the answers

Which of the following statements correctly retrieves employees whose job_id contains 'REP', regardless of case, and displays their last name in uppercase?

<p><code>SELECT UPPER(last_name) FROM employees WHERE LOWER(job_id) LIKE '%rep%';</code> (D)</p> Signup and view all the answers

Given an employee's first_name is 'Steven' and last_name is 'King', what would be the output of the following SQL statement? SELECT INSTR(last_name, 'g') FROM employees WHERE first_name = 'Steven';

<p>4 (C)</p> Signup and view all the answers

Flashcards

LPAD Function

Left-pads an expression to a specified length with a given string.

RPAD Function

Right-pads an expression to a specified length with a given string.

TRIM Function

Removes characters from the beginning, end, or both ends of a string.

REPLACE Function

Replaces all occurrences of a substring within a string with another substring.

Signup and view all the flashcards

ROUND Function

Rounds a number to a specified number of decimal places.

Signup and view all the flashcards

TRUNCATE Function

Truncates a number to a specified number of decimal places (cuts off).

Signup and view all the flashcards

MOD Function

Returns the remainder of one number divided by another.

Signup and view all the flashcards

CEIL Function

Rounds a number up to the nearest whole integer.

Signup and view all the flashcards

FLOOR Function

Rounds a number down to the nearest whole integer.

Signup and view all the flashcards

ABS function

Returns the absolute value of a number (positive equivalent).

Signup and view all the flashcards

SQL Functions

Built-in tools in SQL that perform calculations, modify data, manipulate output, format dates/numbers, and convert data types.

Signup and view all the flashcards

Single-Row Functions

Operate on individual rows and return one result for each row.

Signup and view all the flashcards

Multiple-Row Functions

Operate on groups of rows and return one result per group.

Signup and view all the flashcards

Function Argument

User-supplied constant, variable value, column name, or expression used by a function.

Signup and view all the flashcards

Single-Row Function Features

Operates on each row, returns one result per row, modifies the data type (possibly), used in SELECT, WHERE, and ORDER BY clauses, and can be nested.

Signup and view all the flashcards

Purpose of Single-Row Functions

Used to manipulate data items, accept arguments, and return one value.

Signup and view all the flashcards

Character Functions

Accept character input and can return both character and number values.

Signup and view all the flashcards

Number Functions

Accept numeric input and return numeric values.

Signup and view all the flashcards

Date Functions

Operate on values of the DATE data type.

Signup and view all the flashcards

Conversion Functions

Convert a value from one data type to another.

Signup and view all the flashcards

LOWER function

Converts a string to lower-case.

Signup and view all the flashcards

UPPER function

Converts a string to upper-case.

Signup and view all the flashcards

CONCAT Function

Combines two or more strings into one.

Signup and view all the flashcards

SUBSTR Function

Extracts a substring of a specified length from a string, starting at a given position.

Signup and view all the flashcards

LENGTH Function

Shows the number of characters in a string.

Signup and view all the flashcards

INSTR Function

Returns the numeric position of a substring within a string.

Signup and view all the flashcards

Case-Manipulation Functions

Functions that manipulate the case of character strings (UPPER, LOWER).

Signup and view all the flashcards

SUBSTR(string, m, n)

Returns characters from a string, starting at position 'm', 'n' characters long.

Signup and view all the flashcards

SUBSTR with negative 'm'

If 'm' is negative, the count starts from the end of the character value.

Signup and view all the flashcards

Study Notes

  • This lecture is about using single row functions in SQL

SQL Functions

  • Functions are a powerful feature in SQL
  • Functions can be used to:
    • Perform calculations on data
    • Modify individual data items
    • Manipulate output for groups of rows
    • Format dates and numbers for display
    • Convert column data types
  • Take an Input, perform a Function, and return an Output

Types of SQL Functions

  • There are 2 types of functions
    • Single-row functions
    • Multiple-row functions
  • Single-Row functions operate on single rows only
  • Single-row functions will return 1 result each row
  • There are different types of single row functions:
    • Character
    • Number
    • Date
    • Conversion
    • General
  • Multiple-Row Functions
    • Functions that can manipulate groups of rows
    • Functions return 1 result per group of rows
    • This is also known as group functions

Single-Row Functions

  • These functions are used to manipulate data items
  • Functions accept arguments and return one value
  • Arguments for the function can be:
    • User-supplied constant
    • Variable value
    • Column name
    • Expression
  • Features include:
    • Acting on each row that is returned in the query
    • Returning one result per row
    • May modify the data type
    • Can be used in SELECT, WHERE, and ORDER BY clauses
    • Can be nested
  • General format: function_name [(arg1, arg2,...)]
  • Single-row functions can be:
    • Character functions: Accept character input and can return both character and number values
    • Number functions: Accept numeric input and return numeric values
    • Date functions: Operate on values of the DATE data type
    • Conversion functions: Convert a value from one data type to another
    • General functions

Character Functions

  • Can accept character data as input
  • Can return both character and numeric values.
  • Can be divided into:
    • Case-Manipulation Functions
    • Character-Manipulation Functions
  • Case-Manipulation Functions:
    • Used to convert the case for character strings
    • LOWER converts a string to lower-case
    • UPPER converts a string to upper-case

Character-Manipulation Functions

  • CONCAT concatenates strings together
  • SUBSTR[ING] returns specified characters from character value starting at character position m, for n characters:
    • If m is negative, the count starts from the end of the character value
    • If n is omitted, all characters to the end of the string are returned
  • LENGTH: LENGTH (column | expression)
    • Shows the length of a string as a numeric value
  • INSTR: INSTR (column | expression, 'string')
    • Finds the numeric position of a named character
  • LPAD: LPAD(column | expression, n, 'string')
    • Returns an expression left-padded to the length of n characters with a character expression
  • RPAD: RPAD(column | expression, n, 'string')
    • Returns an expression right-padded to the length of n characters with a character expression
  • TRIM: TRIM([leading | trailing | both] trim_character FROM trim_source)
    • Enables to trim leading or trailing characters (or both) from a character string
  • REPLACE: REPLACE(text, search_string, replacement_string)
    • Searches a text expression for a character string
    • If found, it replaces it with a specified replacement string

Number Functions

  • ROUND: ROUND(column | expression, n)
    • Rounds a value to a specified decimal
    • If n is omitted, no decimal places -If n is negative, numbers to the left of decimal point are rounded
  • TRUNCATE: TRUNCATE(column | expression, n)
    • Truncates the column, expression, or value based on a decimal places
  • MOD: MOD(m, n)
    • Returns the remainder of m divided by n
  • CEIL: CEIL(column | expression)
    • Rounds up to the nearest integer
  • FLOOR: FLOOR(column | expression)
    • Rounds down to the nearest integer
  • ABS: ABS(column | expression)
    • Return the Absolute value
  • POWER: POWER(m, n)
    • m raised to the power of n
  • SQRT: SQRT(column | expression)
    • Returns the Square Root value

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

More Like This

SQL Functions and Clauses Quiz
4 questions

SQL Functions and Clauses Quiz

BelievableWalnutTree avatar
BelievableWalnutTree
SQL Functions: Introduction to SUM, AVG, MAX, MIN
49 questions

SQL Functions: Introduction to SUM, AVG, MAX, MIN

WellEstablishedWisdom avatar
WellEstablishedWisdom
PySpark SQL Functions: lit() and typedLit()
16 questions

PySpark SQL Functions: lit() and typedLit()

EnrapturedElf avatar
EnrapturedElf
SQL Basics and Functions Quiz
33 questions

SQL Basics and Functions Quiz

DauntlessParabola avatar
DauntlessParabola
Use Quizgecko on...
Browser
Open
Browser
Browser