Database Relational Model & SQL Advanced Flashcards -L4

Flashcards for database lecture review.

Data Definition Language

Defines the tables in a database.

Data Manipulation Language

Queries/updates the tables in a database.

1:M Relationship Foreign Key Placement

Place the PK of the '1' side (parent entity) as a Foreign Key in the 'M' side (child entity).

1:1 Non-Recursive Relationship (Optional Side)

Place FK on the optional side of the relationship.

Fan Trap

One entity in two 1:M relationships to other entities, leading to a link between the other two entities.

SQL OR Operator

The entire condition is true if either one of the conditions is true.

SQL AND Operator

The entire condition is true only if both conditions are true.

SQL NOT Operator

Opposite of a condition - true becomes false; false becomes true.

SQL IS NULL

Check whether an attribute value is null.

SQL IS NOT NULL

Check whether an attribute value is NOT null.

SQL BETWEEN

Define range limits.

SQL LIKE

Check for similar character strings.

SQL IN

Check whether an attribute value matches a value contained within a subset of listed values.

SQL Date Function: SYSDATE

Today’s current date, as per your computer system.

SQL Date Function: TO_DATE

Convert a character string to DATE type by a certain format.

SQL Date Function: MONTHS_BETWEEN

Months difference between two dates.

SQL Date Function: ADD_MONTHS

Add a set number of months to a date field.

SQL Date Function: NEXT_DAY

The next given weekday after the given date.

SQL Numeric Function: ROUND

Round numeric fields to the stated precision.

SQL Numeric Function: TRUNC

Delete numeric points to the stated precision.

SQL Numeric Function: MOD

Remainder of a division operator.

SQL Conversion Function: CASE

Different operations depending on conditions.

SQL Conversion Function: TO_NUMBER

Convert a string to a number field (useful for arithmetic calculations).

SQL Conversion Function: TO_CHAR

Use to format values (e.g., convert dates and numbers).

SQL String Function: LOWER

Returns a string with all characters converted to lowercase.

SQL String Function: UPPER

Returns a string with all characters converted to uppercase.

SQL String Function: INITCAP

Converts the first letter of every word to uppercase, and every other letter to lowercase.

SQL String Function: SUBSTR

Extract a portion of the string.

SQL String Function: INSTR

Return the numeric position of a substring within the string.

SQL String Function: LENGTH

Number of characters in the character string.

SQL String Function: LPAD

Left-pad a string with another string, to a certain length.

SQL String Function: RPAD

Right-pad a string with another string, to a certain length.

SQL String Function: TRIM

Removes leading & trailing spaces from both ends.

SQL String Function: LTRIM

Remove leading spaces from left side only or Remove all occurrences of selected characters from the left-hand side of the string.

SQL String Function: RTRIM

Same as LTRIM, but from the right-hand side.

SQL String Function: REPLACE

Substitute a string with another string.

SQL String Function: SOUNDEX

Use to find values based on their “sound”.

SQL Function: NVL

Substitute a value for any NULLS; valid values stay as they are.

SQL Function: NVL2

Substitute a value for any NULLS, and a value for any fields that are not null.