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;
61 Cards in this Set
- Front
- Back
|
Return Unique Rows |
SELECT DISTINCT col_name |
|
|
Check for null values |
col_name IS NOT NULL |
|
|
if/else blocks |
SELECT CASE WHEN (boolean statement) THEN output ELSE END |
|
|
Aggregation Functions |
AVG(), MAX(), MIN() any regular column in the SELECT statement not in an aggregate fxn, must be in the GROUP BY clause |
|
|
HAVING command |
If you need to use an aggregation function in a WHERE statement. HAVING must come after GROUP BY |
|
|
Combine rows into one set |
Must have same num of cols and matching data type. Can use a dummy var like NULL if missing a col. UNION ALL: faster, doesn't remove duplicates UNION: removes duplicate rows |
|
|
Limit returned results |
SELECT TOP x SELECT TOP x PERCENT |
|
|
Create new table |
USE db_name; CREATE TABLE tbl_name( PRIMARY KEY(col_name) col_name data_type, ) |
|
|
Deleting Tables |
DROP TABLE tbl_name |
|
|
Data Types |
varchar(num), varchar(max), int, char(num), float(total length, numDecimals) |
|
|
Insert Rows |
INSERT INTO tbl_name (col4, col1, ..) VALUES (val4, val1,...) INSERT INTO tbl_name select_statement |
|
|
Overwrite/Update Rows |
UPDATE tbl_name SET col1 = v1, col2 = v2 WHERE condition If WHERE is omitted, all rows will be updated |
|
|
Append Columns |
ALTER TABLE tbl_name ADD col_name data_type, col_name data_type |
|
|
Delete/Truncate Rows |
DELETE FROM tbl_name WHERE condition TRUNCATE TABLE tbl_name (faster!) |
|
|
Auto Increment Row |
ID NOT NULL IDENTITY(start, step_size) |
|
|
Column Constraints |
col_name data_type NOT NULL UNIQUE |
|
|
Table Constraints |
CONSTRAINT constraint_name UNIQUE(col_name1, col_name2) |
|
|
Foreign Key |
FOREIGN KEY(col_name) REFERENCES tbl_name |
|
|
Data Validation |
id INT CHECK(condition) price INT CHECK(price >= 0) |
|
|
String Matching |
col_name LIKE '[chars]%' [ ] = chars to match % = wildcard |
|
|
Substrings |
SUBSTRING(col_name, start index, length) LEFT(col_name, length) RIGHT(col_name, length) SQL indices start at 1! |
|
|
String Length |
LEN(col_name) |
|
|
Round |
ROUND(num, number of decimals) only rounds to that decimal, doesn't truncate! |
|
|
Data Conversions |
CAST(val as DECIMAL(10, 4)) CONVERT(int, val) |
|
|
Currency |
CAST(val as money) --no commas, 2 decimals CAST(val as money(1)) --commas, 2 decimals |
|
|
Date Subtraction |
DATEDIFF(part, startDate, endDate) |
|
|
Date Addition |
DATEADD(part, amount, date) |
|
|
Current Time |
SYSDATETIME: date, time, ms SYSDATETIMEOFFSET: date, time, ms, timezone SYSUTCDATETIME: date, UTC time, ms CURRENT_TIMESTAMP: date, time, seconds GETDATE: date, time, seconds GETUTCDATE: date, UTC time, seconds |
|
|
Int to Date |
CAST(CAST(2012 as CHAR(4)) as date) |
|
|
Date to String |
DATENAME(part, date_val) |
|
|
Date to Int |
DATEPART(part, date_val) YEAR() MONTH() DAY() |
|
|
Date Parts |
yy, yyyy, mm, m, dd, d |
|
|
Left Outer Join vs Right |
Left Outer Join grabs all rows in first table even if no match on right |
|
|
Outer vs Inner Join |
Inner Joins only returns overlap between tables |
|
|
CTE |
WITH temp_name(col1, col2) AS (SELECT statement) |
|
|
Create Stored Procedure |
CREATE PROCEDURE schema.spName @paramName dataType = defaultVal AS SELECT FROM WHERE col = @paramName |
|
|
Execute Stored Procedure |
EXEC spName @param1 = arg, @param2 = arg |
|
|
1NF |
A single column must not contain a group of data. Break up the duplicate data |
|
|
2NF |
Can't have two rows with same PK Row should only depend on one PK |
|
|
3NF |
No column should depend on other columns that isn't the PK |
|
|
One to Many |
One table with PK Second table with FK to first table's PK |
|
|
Many to Many |
Need a 3rd join table that has FKs to 2 other tables' PKs |
|
|
One-to-One |
PK is a FK to another table's PK |
|
|
Create Index |
CREATE INDEX index_name ON table_name(col1 DESC, col2 ASC) INDEX can be UNIQUE and /or CLUSTERED (kept in order) |
|
|
Create View |
CREATE VIEW view_name(col_name) AS (select_statement) |
|
|
Normalization |
Good for CRUD heavy DBs to keep data integrity. Saves space, but more performance intensive for queries due to many joins |
|
|
Denormalization |
Good for query heavy DBs. Redundant data wastes space, but fast for queries |
|
|
Star Schema |
One central fact table with FKs to denormalized dim tables. Wastes some space, but queries are simpler and faster. |
|
|
Snowflake Schema |
One central fact table with FKs to normalized dim tables. Saves space, but queries much more complex and performance intensive. |
|
|
Rollup |
Create subtotal group for each specified column SELECT Country, Region, SUM(Sales) FROM Sales GROUP BY ROLLUP (Country, Region); |
|
|
Cube |
Creates subtotal group for every combination of specified columns SELECT Country, Region, SUM(Sales) FROM Sales GROUP BY CUBE (Country, Region); |
|
|
For / While Loop |
declare @cnt int = 20 while @cnt > 0 begin select @cnt set @cnt = @cnt - 1; end |
|
|
Create Schema |
CREATE SCHEMA s_name AUTHORIZATION user1 CREATE TABLE tbl_name (col1, col2) GRANT SELECT ON SCHEMA::s_name TO user2 DENY SELECT ON SCHEMA::s_name TO user3; |
|
|
Schema Actions / Permissions |
GRANT, REVOKE, DENY CONTROL, ALTER, SELECT, DELETE, INSERT, UPDATE, REFERENCES |
|
|
Time Parts |
hh, mi, ss |
|
|
UTC to Local |
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) AS ColumnInLocalTime |
|
|
Bulk Insert |
BULK INSERT schema.tbl_name FROM 'file_path' WITH ( FORMAT = 'CSV' (just an example) ERRORFILE = 'file_name' CHECK CONSTRAINTS, FIELDTERMINATOR ='|', ROWS_PER_BATCH = 5000 ROWTERMINATOR ='|\n' ); |
|
|
Default Column Value |
col_name data_type DEFAULT default_value |
|
|
DDL |
Data description language: CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements |
|
|
DML |
Data manipulation language: queries. SELECT, ORDER BY, UPDATE, INSERT, etc |
|
|
Triggers |
CREATE TRIGGER trigger_name ON table_name AFTER INSERT, UPDATE, DELETE AS Action, like sp |
