03_sql_crash_course.md

03_sql_crash_course.md (4037B)
      1 # SQL Crash Course — Rogers Data Engineer Focus
      2 
      3 ## Core Syntax
      4 
      5 ```sql
      6 -- SELECT basics
      7 SELECT FirstName, LastName, Email
      8 FROM Students
      9 WHERE Active = 1
     10 ORDER BY LastName ASC;
     11 
     12 -- Filtering
     13 WHERE Department = 'Staff'
     14 WHERE HireDate > '2023-01-01'
     15 WHERE LastName LIKE 'Rob%'        -- starts with
     16 WHERE Status IN ('Active', 'OnLeave')
     17 WHERE Email IS NOT NULL
     18 ```
     19 
     20 ---
     21 
     22 ## High-Value Skills for This Job
     23 
     24 ### 1. JOINs — syncing data across systems means joining tables constantly
     25 
     26 ```sql
     27 -- INNER JOIN: only matching rows
     28 SELECT s.StudentID, s.LastName, e.Email
     29 FROM Students s
     30 INNER JOIN Enrollments e ON s.StudentID = e.StudentID;
     31 
     32 -- LEFT JOIN: all students, even those without enrollment
     33 SELECT s.StudentID, s.LastName, e.CourseID
     34 FROM Students s
     35 LEFT JOIN Enrollments e ON s.StudentID = e.StudentID;
     36 
     37 -- Practical: find students in one system but missing from another
     38 SELECT s.StudentID, s.LastName
     39 FROM SIS_Students s
     40 LEFT JOIN ActiveDirectory_Users ad ON s.StudentID = ad.EmployeeID
     41 WHERE ad.EmployeeID IS NULL;   -- these kids need AD accounts created
     42 ```
     43 
     44 ### 2. Aggregations — reporting and auditing
     45 
     46 ```sql
     47 SELECT Department, COUNT(*) AS HeadCount
     48 FROM Employees
     49 WHERE Active = 1
     50 GROUP BY Department
     51 HAVING COUNT(*) > 5
     52 ORDER BY HeadCount DESC;
     53 ```
     54 
     55 ### 3. INSERT / UPDATE / DELETE — onboarding automation writes data
     56 
     57 ```sql
     58 -- Insert new record
     59 INSERT INTO Users (Username, Email, Department, CreatedDate)
     60 VALUES ('jdoe', 'jdoe@district.org', 'Teachers', GETDATE());
     61 
     62 -- Update existing record
     63 UPDATE Employees
     64 SET Department = 'Administration', UpdatedDate = GETDATE()
     65 WHERE EmployeeID = 1042;
     66 
     67 -- Safe delete pattern — always confirm your WHERE clause first
     68 DELETE FROM TempSync
     69 WHERE ProcessedDate < DATEADD(day, -30, GETDATE());
     70 ```
     71 
     72 ### 4. CTEs — readable, maintainable queries (shows experience)
     73 
     74 ```sql
     75 -- Common Table Expression: name a subquery, then use it
     76 WITH ActiveStaff AS (
     77     SELECT EmployeeID, LastName, Email
     78     FROM Employees
     79     WHERE Active = 1 AND Type = 'Staff'
     80 )
     81 SELECT a.LastName, a.Email, d.DepartmentName
     82 FROM ActiveStaff a
     83 JOIN Departments d ON a.DepartmentID = d.DepartmentID;
     84 ```
     85 
     86 ### 5. Finding data problems — their core concern
     87 
     88 ```sql
     89 -- Duplicates
     90 SELECT Email, COUNT(*) AS Count
     91 FROM Students
     92 GROUP BY Email
     93 HAVING COUNT(*) > 1;
     94 
     95 -- Orphaned records (data in one table with no parent)
     96 SELECT e.*
     97 FROM Enrollments e
     98 LEFT JOIN Students s ON e.StudentID = s.StudentID
     99 WHERE s.StudentID IS NULL;
    100 
    101 -- Null audit
    102 SELECT COUNT(*) AS MissingEmail
    103 FROM Employees
    104 WHERE Email IS NULL AND Active = 1;
    105 ```
    106 
    107 ---
    108 
    109 ## Data Sync Pattern — What This Job Does Daily
    110 
    111 ```sql
    112 -- "Upsert" logic: update if exists, insert if not (SQL Server MERGE)
    113 MERGE INTO AD_Users AS target
    114 USING SIS_Export AS source
    115     ON target.StudentID = source.StudentID
    116 WHEN MATCHED THEN
    117     UPDATE SET target.Email = source.Email,
    118                target.LastName = source.LastName
    119 WHEN NOT MATCHED THEN
    120     INSERT (StudentID, Email, LastName)
    121     VALUES (source.StudentID, source.Email, source.LastName);
    122 ```
    123 
    124 This is the concept behind "synchronize student data across software systems." If they ask how you'd approach cross-system sync, describe this pattern even if you don't recite the syntax.
    125 
    126 ---
    127 
    128 ## Practical Things to Know
    129 
    130 | Concept | Why It Matters Here |
    131 |---|---|
    132 | `GETDATE()` / `GETUTCDATE()` | Timestamps on sync logs and audit trails |
    133 | `ISNULL(col, 'default')` | Clean up nulls before exporting to other systems |
    134 | `CAST` / `CONVERT` | Data types won't always match between systems |
    135 | Indexes | Know they exist and affect query speed — you don't need to build them yet |
    136 | Transactions (`BEGIN / COMMIT / ROLLBACK`) | Safe bulk updates — if something fails, nothing gets half-written |
    137 
    138 ---
    139 
    140 ## If They Ask "How's Your SQL?"
    141 
    142 > "I can write queries, joins, aggregations, and data validation checks comfortably. I've used it for data verification in my QA work. I'm less experienced with DBA tasks like index tuning or schema design, but I can hold my own on the query and automation side."
	resume
	Log \| Files \| Refs \| README
resume
