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;
25 Cards in this Set
- Front
- Back
- 3rd side (hint)
|
What is the structure of a select |
SELECT <COLS> FROM <TABLES> WHERE <CONDITIONS> - FILTER GROUP BY <COLS> HAVING <CONDITIONS> - FILTER GROUPS ORDER BY <COLS> |
6 parameters |
|
|
In 3NF How do we do a many to many table structure? |
Using three tables 1st p and object 2nd pk plus fk and fk 3rd pk and object |
Link |
|
|
Schemas are like what? |
Folders |
Think security |
|
|
Variable structure is like what |
DECLARE @<NAME> <DATATYPE> SET @<NAME> = <VALUE> |
Java with @s |
|
|
What does a HAVING condition look like |
HAVING COUNT ([OrderID] > 10) |
Think after grouping performed what can we act on? |
|
|
Why no SELECT *? |
Bad performance. Get only what you need. Also as someone reading it for the first time you have to run the query just to find out what will come back |
Think efficiency |
|
|
What should you do at the end of query |
Use ; |
Think C# good practice |
|
|
What are the output options? How do we choose |
Text Grid File. You get them using the buttons. |
What would you like? |
|
|
When should you always an alias? |
Aggregated cols Calculated cols Cols involved in a join |
Think conventions |
|
|
Why don't column aliases work in the WHERE? |
Because the WHERE line doesn't have visibility of the SELECT line. FROM is evaluated first then WHERE then SELECT. |
Think order of operations |
|
|
BETWEEN |
Used in where to test column value if there are in a range WHERE <COL> BETWEEN <VAL1> AND <VAL2> |
Think range |
|
|
IN |
WHERE <COL> IN (<SET>) or 1 column subquery
If the value of test_expression is equal to any value returned by subquery or is equal to any expressionfrom the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE |
Think set |
|
|
LIKE options |
% zero or more - one char [] range [AFR] finds A,F or R. [A - R] finds A to R. [^] Any char not in bracket. |
Your ****** just memorise |
|
|
How to evaluate null? |
WHERE <COL> IS NULL |
|
|
|
Join order |
FromTable.seam = To Table.seam |
Basics |
|
|
Outer join produces what |
Return cols on left and match cols on right, if there is no seam match, use null fillers |
Inner plus |
|
|
Full outer join produces |
All rows on both sides and if they don't match on seam replace with nulls |
All plus |
|
|
Left join produces |
CHECK ANSWER: All rows on left join with matches on right, no match then use null filler |
I half of outer join |
|
|
Right join produces |
Same as left join but rhs. All rows on right joined with matches.. non matched rows appear either null filler |
|
|
|
Where can you do a nested query? |
Select line: , (SELECT bla) AS bla From line: FROM (bla) AS bla Join line: JOIN (bla) AS b ON b.seam from Table.seam Where line: WHERE colName IN (bla) |
Just mem |
|
|
Define: IN |
If the value of test_expression is equal to any value returned by subquery or is equal to any expressionfrom the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE |
|
|
|
Define: EXISTS |
Returns true if sub query returns any values. SELECT DepartmentID, Name FROM HumanResources.Department WHERE EXISTS (SELECT bla bla) ORDER BY Name ASC ; |
|
|
|
Define: ALL |
Result ValueReturns TRUE when the comparison specified is TRUE for all pairs (scalar_expression, when x is a value in the single-column set; otherwise returns FALSE. IF @NumberOfDays >= ALL (query) true false |
|
|
|
Define: SOME/ANY |
SOME or ANY returns TRUE when the comparison specified is TRUE for any pair (scalar_expression, x) where x is a value in the single-column set; otherwise, returns FALSE. |
|
|
|
What is a union and what are the rules. |
Will tack two queries of the same amount of columns and matching data types together. LHS of query will dictate column names. Only one value for ORDER BY. |
|
