Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
85 Cards in this Set
- Front
- Back
|
Case |
-Compares an expression and based on the syntax determines what action to perform -statement you are checking |
|
|
UTC TIME |
Coordinated Universal Time
-Primary time standard by which the world regulates clocks and time |
|
|
UPPER |
Uppercase
|
|
|
LOWER |
Lowercase |
|
|
Substring |
Partial String |
|
|
LEN |
Length of the field |
|
|
CHARINDEX |
Location (index) of a character |
|
|
PATINDEX |
Location (index) of a pattern |
|
|
LEFT |
Left number of characters |
|
|
RIGHT |
Right number of characters |
|
|
REVERSE |
Reverse the string |
|
|
REPLACE |
Replace a character or set of characters with a different character or set of characters |
|
|
ISNUMERIC |
Returns 1/0 (bit, Y/N, T/F) if the value is numeric |
|
|
ISDATE |
Returns 1/0 (bit, Y/N, T/F) if the value is a date |
|
|
LTRIM |
Removes spaces to the LEFT |
|
|
RTRIM |
Remove spaces to the RIGHT |
|
|
STUFF |
Inserts a string into another string |
|
|
CONCAT |
Concatenates multiple strings |
|
|
ABS |
Absolute Value |
|
|
ROUND |
Round to specific length |
|
|
EXP |
Exponential |
|
|
LOG |
Natural Logarithm |
|
|
LOG10 |
Base-10 logarithm |
|
|
LOG10 |
Base-10 logarithm |
|
|
SIN/COS/TAN ACOS/ASIN/ATAN |
Sine, Cosine, Tangent |
|
|
SQUARE |
Square value |
|
|
CEILING |
Always round up |
|
|
FLOOR |
Always round down |
|
|
PI |
PI |
|
|
% |
Modular (remainder) |
|
|
Update and Delete |
-Similar to SELECT -you can JOIN tables, you can use WHERE clauses, you can use subqueries, etc -changes are PERMANENT -Perform the change on TEMP (#) first to double check the results |
|
|
ALTER |
-Allows to change a current table |
|
|
GETDATE () |
Get current date and time |
|
|
DATENAME |
Returns a character string that represents the specified datepart of the specified date |
|
|
DATEPART |
Returns integer that represents the specified datepart of the specified date |
|
|
DAY |
Returns an integer that represents the day of the specified date |
|
|
MONTH |
Returns an integer that represents the month part of a specified date |
|
|
YEAR |
Returns an integer that represents the year part of a specified date |
|
|
DATEDIFF |
Returns the number of date or time datepart boundaries that are crossed between two specified dates |
|
|
DATEADD |
Returns a new datetime value by adding an interval to the specified datepart of the specified date |
|
|
ISDATE |
Determines whether a datetime or smalldatetime input expression is a valid date or time value |
|
|
GROUP BY |
Aggregates data based on one or more colums -Very useful with functions like MAX, MIN, AVG, and count |
|
|
Database Design |
Process of producing a detailed data model of a database |
|
|
Anomaly |
Something that deviates from what is standard, normal, or expected -In database world, means losing data information when certain actions may take place |
|
|
Dimensional Modeling |
The set of techniques used in data warehouse |
|
|
Steps for Dimensional Modeling |
1. Choose the business process-describe the business process (usually done in plain text or use basic Business Process Modeling Notation (BPMN) 2. Declare the grain (exact description of what the dimensional model should be focusing on) 3. Define the dimensions (for example date dimension could contain date such as year, month, weekday) 4. Make keys for fact table-identify numeric facts that will populate each fact table row |
|
|
Denormalization |
normalization (snowflake) removes redundant data by separating it out into two tables |
|
|
Modular Programming |
Software design technique that emphasizes separating the functionality of a program into independent, interchangeable modules -Modules should only execute one piece/step of a process |
|
|
Object Oriented Programming (OOP) |
Programming technique that represents the usage of objects that have data fields and procedures known as methods |
|
|
Variables |
Provide dynamic values to be used in queries |
|
|
Dynamic SQL |
Creating very flexible and customizable queries |
|
|
Stored Procedures |
Modular task, can have zero to many parameters |
|
|
Views |
Custom view of the data (helps having to write a lot of SELCT*FROM...JOIN queries) |
|
|
Functions |
Performs calculations on the data (MAX, MIN, LEN, SUBSTRING) -Table valued, scalar, and aggregate functions |
|
|
SQL Jobs |
SQL server has the ability to schedule multiple tasks in a sequence and execute the task on a scheduled time |
|
|
Isolation |
A property that defines how/when the changes made by one operation become visible to concurrent operations -determines the order to apply the transactions |
|
|
ACID |
-A set of properties that guarantee the database transactions are processed reliably -Atomicity: requires each transaction is all or nothing -Consistency:ensures that any transaction is valid -Isolation: ensures that concurrent transaction results are executed serially -Durability: once a transaction is committed, it will remain committed
|
|
|
Committed vs Uncommitted |
Committed -Makes sure the data is committed (transaction is finished) Uncommitted -You can read data even if a transaction is currently happening |
|
|
Lock |
When multiple users try to access a database concurrently |
|
|
Dirty Read |
Read uncommitted data. You can read data that is being locked by using syntax to read uncommitted data |
|
|
Read Uncommitted |
You can allow yourself to perform dirty reads using one of the follow 2 methods: 1. Set transaction isolation level read uncommitted 2 . WITH (no lock) |
|
|
Over |
Determines the partitioning and ordering of the data |
|
|
Partition By |
Divides the query result set into partitions |
|
|
Order By |
Defines the logical order of the rows within each partition |
|
|
Pivot |
Rotates data from rows to columns |
|
|
Unpivot |
rotates the data from columns to rows |
|
|
View |
Creates a virtual table whose contents (columns and rows) are defined by a query |
|
|
ANSI_NULLS |
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returnszero rows even if there are null values in column_name.A SELECT statement that uses WHERE column_name <> NULL returnszero rows even if there are nonnull values in column_name. WhenSET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparisonoperators do not follow the ISO standard. A SELECT statement that uses WHEREcolumn_name = NULL returnsthe rows that have null values in column_name.A SELECT statement that uses WHERE column_name <> NULL returnsthe rows that have nonnull values in the column. Also, a SELECTstatement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL. |
|
|
QUOTED_IDENTIFIER |
Allows the ability to use double quotes ("") as an identifier |
|
|
Stored Procedure |
A subroutine available to applications that access a relational database system -Reffered to as proc, sproc, storedproc, sp/SP |
|
|
BEGIN...END |
Encloses a series of TSQL statements so that a group of statements can be executed |
|
|
GO |
Signals the end of a batch of SQL statements - Ireference GO as a COMMIT action (which is a different command when discussingSQL) |
|
|
IF ELSE |
Imposesconditions on the execution of a Transact-SQL statement. The Transact-SQLstatement that follows an IF keyword and its condition is executed if thecondition is satisfied: the Boolean expression returns TRUE. The optional ELSEkeyword introduces another Transact-SQL statement that is executed when the IFcondition is not satisfied: the Boolean expression returns FALSE.
|
|
|
WHILE |
Sets a condition for the repeated execution of an SQL statement or statement block
|
|
|
BREAK |
Causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed
|
|
|
CONTINUE |
Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.
|
|
|
CURSOR |
-A cursor basically ‘scrolls’ through a table performing an operation
-Cursors are resource intensive pieces of code, which is why they are not favorable to create -They can go forward and backwards while scrolling through a table -Cursors usually have 4 basic pieces *Declare a cursor that defines a result set * Open the cursor to establish the result set *Fetch the data into local variables as needed from the cursor, one row at a time *Close the cursor when complete |
|
|
Common Table Expression (CTE) |
A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. |
|
|
Dynamic SQL |
-Provides maximum flexibility -Can be used to create an entire SQL statement that is variable |
|
|
INNER JOIN |
Set of records that match both tables |
|
|
LEFT OUTER JOIN |
All records from the "Left" table, while displaying those records that match to second table, if there is no match then records will contain NULLS |
|
|
LEFT OUTER JOIN is NULL |
All records from only the "Left" table |
|
|
RIGHT OUTER |
Just like the LEFT OUTER JOINs above, but the output is focused around the “Right” table.
|
|
|
FULL OUTER JOIN |
Excludes records that match both tables |
|
|
CROSS JOIN |
-Everything to Everything (so 4 x4=16 output). -This produces a large amount of output, you normally won't use this command |
