SQL 1 Flashcards
datepart
component of date i.e
date, month, week, year, hour minute
dateadd function
needs to have
1. datepart
2. number
3, date or datetime in this order
If a function does not take an argument
It does not need to be in ()
Concatenation Operator
”+” in SQL server only
// (actually straight lines not slashes, jsut cant find keyboard character for straight parallel lines) - Oracle only
Cast function
changes data from one data type to another
Ex. For concat funcitons, data types need to be the same. Would use cast to make both the same to then add together.
Convert function
similar to cast function, specific to SQL server in changing data types
Datediff function
calculates the difference between two dates (Date diff)
datefromparts
returns the date (integer)
Concat function
” select concat (prov_name, ‘,’, Clinician_title) “Provider’
Combines text of two different fields to one. Better than just using + operator bc if there is a null value, + won’t return anything. Need to use concat function
Coalesce function
returns the first non null input
Len function
returns the # of characters in any string
Can also use Length()
determining the cardinality of the relationship
When using a JOIN, you need to identify how each relationship is defined in order to guide you to using the relationship in a query
Ex one to many, many to one exactly
Inner Join
only includes rows that represent a match between two tables
Good for relationships that don’t include cardinality of “zero, or” on either side
Good to use if you need only valid matches (non-nulls)
Outer Join
Returns all rows in the left table + remaining matching rows in both tables (non null results in right table)
Good to use when there is a “zero or”
cross join
Usually done on accident; rarely used
can happen if the word Join is omitted from the from clause or a “,” is accidentally input
Leads to being a cartesian product
- usually results in massive results, slows performance
- meaningless results
