Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

96 cards

()

Section 1

Preview this deck

SUBSTR

Front

1 / 96

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (96)

Section 1

(50 cards)

SUBSTR

Front

Substring SUBSTR(arg,starting character, how many to display)

Back

ORDER BY

Front

Sorts the retrieved rows in ascending (ASC) or descending (DESC) order - default order is ascending Can use with aliases

Back

SELECT

Front

SELECT *|{column_name1,column_name2,...} FROM table; Selects the specified columns from the specified table

Back

Character functions

Front

Character functions get character data as input and return character or numeric data as output WHERE search is case sensitive

Back

LOWER

Front

Character function that converts all the input characters to lowercase LOWER(arg)

Back

UPPER

Front

Character function that converts all the input characters to upper case UPPER(arg)

Back

DESCRIBE or DESC

Front

Provides a description of the specified table - returns column names and datatypes

Back

Types of single-row functions

Front

Numeric Character General Conversion Data

Back

Entity

Front

The smallest unit containing a meaningful set of data is called an "entity"

Back

Data

Front

Information we store to use later

Back

INITCAP

Front

Converts first character of each word to upper case and lowercase the rest INITCAP(arg)

Back

NULLS FIRST / NULLS LAST

Front

Used after an order by statement, orders the null statements

Back

CLOB

Front

Maximum size is (3 GB * DB_BLOCK_SIZE)

Back

Rules of Precedence

Front

1. Arithmetic Operators 2. Concatenation Operators 3. Comparison conditions 4. IS (not) NULL, Like, (not) IN 5. (not) BETWEEN 6. Not equal to 7. NOT 8. AND 9. OR

Back

BETWEEN...AND

Front

Used with a WHERE function, limits to a range between one number/date and another.

Back

BFILE

Front

Binary data stored in an external file

Back

LONG

Front

Variable-length character data (up to 2 GB)

Back

DBMS

Front

Database Management System, a collection of programs that enable you to enter, organize, and select data in a database

Back

IN

Front

Used with a where function to restrict the conditions to match. Essentially creates a vector that the column entry for a returning record must match

Back

Database

Front

A place we store an organized collection of data Store, access, manipulate, retrieve.

Back

LTRIM

Front

gets rid of spaces to the left of characters

Back

One to many

Front

A 'higher level entity' can have one to many things associated with itg

Back

OR

Front

Returns true if either component condition is true

Back

IS NULL

Front

Same as = NULL Used for searching for NULL values

Back

LENGTH

Front

Returns the length of an arg (including spaces LENGTH(arg)

Back

Concatenate

Front

Two parallel bars || between the two things being concatenated Good to use an alias here

Back

Column

Front

The union of all cells vertically in a table. Can only contain one type of data.

Back

Is not equal to

Front

<>

Back

Schema

Front

The collection of objects for each user in the Oracle Database

Back

NOT

Front

Returns true if the condition is false

Back

AND

Front

Returns true if both component conditions are true

Back

DATE

Front

Date and time values

Back

VARCHAR2(size)

Front

Variable length character data

Back

Functions

Front

Functions are created for easy reuse of the code that manipulates data and returns some value Single row function gets one row and returns one value for that row

Back

LIKE

Front

Like allows to search in string values Use % to determine before/after for 0 or more characters _ is used with only one character

Back

CONCAT

Front

CONCAT (arg,arg) Concatenates two or more arguments

Back

CHAR(size)

Front

Fixed length character data

Back

SELECT DISTINCT

Front

Selects rows with distinct column (only 1)

Back

WHERE

Front

Limits a select function by a logical expression *must refer to column name

Back

NUMBER(p,s)

Front

Variable-length numeric data

Back

Column alias

Front

Select column_name AS column_alias FROM table Double quotation marks if you have spaces or extra symbols

Back

ROWID

Front

A base 64 number system representing the unique address of a row in its table

Back

TRIM

Front

gets rid of spaces around characters

Back

Four Types of DBMS

Front

Hierarchical Network Relational Object-Relational

Back

Primary Keys

Front

The ID's of each record that are unique

Back

SQL

Front

Standard Query Language - used to interact with the database

Back

INSTR

Front

ISTR(arg,target) tells how many character into a string the target character is

Back

Record

Front

The union of all cells horizontally in a table. Can contain multiple types of data.

Back

RTRIM

Front

gets rid of spaces to the right of characters

Back

RAW and LONG RAW

Front

Raw binary data

Back

Section 2

(46 cards)

For Dates YYYY

Front

Full year in numbers

Back

SYSDATE

Front

Returns the system's date

Back

For Dates SS

Front

Two digit seconds

Back

For Dates DY

Front

Three letter abbreviation of the day

Back

For Dates HH (HH12-HH24)

Front

display in 12 or 24 hour format

Back

For NUmbers L

Front

Display local currency symbol

Back

RPAD

Front

RPAD(arg,desired characters, added character) Add in a character a certain number of times to the right of the existing characters to get the cell up to a total number of characters

Back

REPLACE

Front

REPLACE(arg,what,with) Replaces what you're searching for with something

Back

For Numbers 0

Front

Represent a 0

Back

For Dates MI

Front

Two digit minute

Back

For Numbers $

Front

Display a dollar sign

Back

For Numbers ,

Front

Display a comma

Back

TO_DATE

Front

TO_DATE(char [,'format_model'])

Back

For Dates MM

Front

Two digits of the month

Back

NULLIF

Front

NULLIF(E1, E2) Compares E1 and E2 - returns NULL if the two are equal, returns E1 if they're not equal

Back

For Dates MON

Front

Three letter abbreviation of the month

Back

For Dates MONTH

Front

Full name of the month

Back

ROUND date

Front

ROUND(date, time period) Rounds to the beginning of the nearest time period

Back

TO_CHAR

Front

TO_CHAR(date | number [,'format']

Back

CEIL

Front

CEIL(number) Returns the smallest integer number greater or equal to a specified number

Back

For Dates RR-YY

Front

Last two digits of the year

Back

COALESCE

Front

COALESCE(E1, E2, ... , EX) If E1 is null, look to E2. Will continue until it reaches EX

Back

For Numbers .

Front

Display a decimal point

Back

LPAD

Front

LPAD(arg,desired characters, added character) Add in a character a certain number of times to the left of the existing characters to get the cell up to a total number of characters

Back

For Dates DD

Front

Two digit number of the day of the month

Back

MONTHS_BETWEEN

Front

MONTHS_BETWEEN(date1, date2) Number of months between two dates

Back

For Dates YEAR

Front

Spelling of the year in english

Back

For Dates SP

Front

Spelling of number

Back

CURRENT_TIMESTAMP

Front

Returns current date and time from user's session

Back

NVL

Front

NVL(Expression1, Expression2) Expression one is the column or value that could be null. Expression two is the column or value that will show if expression 1 is null

Back

TO_NUMBER

Front

TO_NUMBER(char [,'format_model'])

Back

SESSIONTIMEZONE

Front

Returns time zone of the user's session

Back

For Numbers 9

Front

Represent a number

Back

LAST_DAY

Front

LAST_DAY(date) returns the last day of that month

Back

ROUND

Front

ROUND(number, decimal places) Rounds a number to the given number of decimal places

Back

For Dates SPTH or THSP

Front

Spelling of ordinal number

Back

For Dates TH

Front

Ordinal Number

Back

NVL2

Front

NVL2(E1, E2, E3) If E1 is not null, it returns E2, if it is null, it returns E3

Back

TRUNC

Front

TRUNC(number, decimal place) Like round, but it doesn't round

Back

CURRENT_DATE

Front

Returns current date from the user's session

Back

NEXT_DAY

Front

NEXT_DAY(date, 'DAY') Returns the next day that's that day of the week

Back

MOD

Front

MOD(#1, #2) - Returns remaineder of dividing #1 by #2

Back

Nesting functions

Front

You can nest functions within other runctions. Result of the inner function will be the input of the outer function

Back

TRUNC date

Front

TRUNC(date, time period) Rounds to the beginning of the current month or year

Back

FLOOR

Front

FLOOR(number) Returns the largest integer number equal or less than a specified number

Back

ADD_MONTHS

Front

ADD_MONTHS(date,months to add) Add specific number of months to specified date

Back