Sql

Front 1

PRIMARY KEY

Back 1

Key that uniquely defines data in a table. Some times system-generated, synthetic or surrogate key.

Front 2

COMPOSITE PRIMARY KEY

Back 2

Primary key that uses more than one column, also called concatenated primary key.

Front 3

FOREIGN KEY

Back 3

Key that links tables together where one table it is the Primary Key and another it is not.

Front 4

NORMALIZATION

Back 4

to eliminate redundancy in tables, therefore avoiding any future data manipulation problems

Front 5

1st Normal Form

Back 5

All repeating groups must be removed and placed in a new table.

Front 6

2nd Normal Form

Back 6

All nonkey columns must depend on the entire primary key.

Front 7

3rd Normal Form

Back 7

Every non key column must be a fact about the primary key

Front 8

One to Many Relationship(1:M)

Back 8

Most common type.

Front 9

One to One Relationship(1:1)

Back 9

Exist but not typical

Front 10

Many to Many Relationship(M:M)

Back 10

Exists.. example is a book can have more than one author and an author can write more than one book.

Front 11

WHERE

Back 11

The WHERE clause, also called the predicate, provides the power to narrow down the scope of data retrieved. In fact, most SQL statements you write will probably contain a WHERE clause.

Front 12

SQL Comparison Operators

Back 12

=....................EQUAL

!=,<>..............NOT EQUAL

>,>=....................Greater than, Great than or Equal

<,<=....................LESS than, LESS than or Equal

Between/AND...Inclusive of two values

LIKE....................Pattern matching with wildcard charaters % and_

IN (...)..................List of Values

IS NULL...............Test for null values

Front 13

FUNCTIONS

Back 13

They can transform data in a way that is different from the way it is stored in the database.

function_name(input_parameter)

Front 14

SYNTAX SYMBOLS

Back 14

[]...Square brackets enclose syntax options

{}...Braces enclose items of which only one is required

|....A vertical bar denotes options

... Three dots indicate that the preceding expression can be repeated

Delimiters>>Delimiters other than brackets, braces, bars, or the three dots must be entered exactly as shown in the syntax. Commas, ().

CAPS... Words in all capital letters indicate the Oracle keywords that identify the individual elements of the SQL command or function.

UNDERLINE... Default values are underlined.

Front 15

LOWER FUNCTION

Back 15

The LOWER function transforms data into lowercase.

Select state, LOWER(state),Lower('State')

FROM zipcode;

Front 16

UPPER and INITCAP Functions

Back 16

The UPPER function does the opposite of the LOWER function.

The INITCAP function capitalizes the first letter of every word and lower cases the rest of the word.

Front 17

LPAD and RPAD

Back 17

Places spaces or characters before or after.

LPAD(char1, n [, char2])

RPAD(char1, n [, char2])

SELECT RPAD(city, 20, '*') City Name

Front 18

LTRIM, RTRIM, TRIM

Back 18

Opposite of LPAD AND RPAD, removes padding, characters, symbols from left, right, or both sides.

LPAD(char1, n [, char2])

RPAD(char1, n [, char2])

TRIM([LEADING|TRAILING|BOTH] char1 FROM char2)

Front 19

SUBSTR Function

Back 19

Transforms a string, returning a substring or subset of a string.

SUBSTR(char1, starting_position [, substring_length])

If the third parameter is not used, the default is to display the remainder of the string. If starting_position is negative start count from the end of the string.

Front 20

INSTR Function

Back 20

Looks for the occurance of a string inside another string, returning the starting position of the search string with the target string. It returns a number.

INSTR(char1, char2 [,starting_position [, occurrence]])

INSTR can take two optional input parameters. The third parameter allows you to specify the start position for the search. The fourth parameter specifies which occurrence of the string to look for. When these optional parameters are not used, the default value is 1.

Front 21

LENGTH Function

Back 21

determines the length of a string

Front 22

USE of Function WHERE clause

Back 22

The use of functions is not restricted to the SELECT list; they are also used in other SQL clauses. In a WHERE clause, a function restricts the output to rows that only evaluate to the result of the function.

Front 23

REPLACE

Back 23

Replaces one string with another string

REPLACE(char, if, then)

Front 24

TRANSLATE

Back 24

Unlike REPLACE, which replaces an entire string, the TRANSLATE function provides a one-for-one character substitution.

TRANSLATE(char, if, then)Rischert, Alice (2009-08-12).

Front 25

SOUNDEX

Back 25

The SOUNDEX function allows you to compare differently spelled words that phonetically sound alike.

Front 26

ABS

Back 26

Computes the absolute value of a number, measuring magnitude.

Front 27

SIGN Function

Back 27

The SIGN function tells you the sign of a value, returning a number 1 for a positive number, –1 for a negative number, or 0 for zero.

SIGN(value)

Front 28

ROUND and TRUNC Functions

Back 28

two useful functions that round and truncate (or cut off) values, respectively, based on a given number of digits of precision.

ROUND(value [, precision])

TRUNC(value [, precision])

Front 29

FLOOR and CEIL Functions

Back 29

The CEIL function returns the smallest integer greater than or equal to a value; the FLOOR function returns the largest integer equal to or less than a value. These functions perform much like the ROUND and TRUNC functions, without the optional precision parameter.

FLOOR(value)

CEIL(value)

Front 30

MOD Function

Back 30

MOD is a function that returns the modulus, or the remainder of a value divided by another value.

MOD(value, divisor)

Front 31

REMAINDER Function

Back 31

The REMAINDER function calculates the remainder, according to the IEEE specification.

REMAINDER(value, divisor)