data theory

What does CRUD stand for?

Create, read, update, delete

What are the join types

Inner join, left/right outer join, full outer, cross

What does inner join return

rows only returned if they exist in both tables

left outer join results

all rows returned from first table, matching rows from second table, missing values are null

right outer join results

all rows from second table, matching rows, missing are null

full outer join results

all rows from both tables, missing values are null

cross join results

eacb row from left table is joined with each row from right table

what is the order of clauses in a SELECT statement?

select, from, where, group by, having, order by

what is the best date format?

ymd

how do you write a DATEDIFF statement

DATEDIFF(datepart, startdate, enddate)

How do you write a DATEADD statement

DATEADD(datepart, num, date)

What are the basic predicates?

=, <, >, <=, >=, <>, etc.

BETWEEN predicate

shortened version of >= AND <=

EXISTS predicate

tests for existence of certain rows using a subquery

IN predicate

compares a value with a set of values

LIKE predicate

searches for strings with a certain pattern, % matches any num of characters, _ matches a single character

NULL predicate

tests for null values

What is an ERD

a form of semantic modeling, description of data in a system

What is an entity?

something or someone that contains data/info

What are the 3 kinds of relationships?

1:1, 1:M, M:N