[New!! 2026] (2과목) SQLD 완벽 요약강의 | 요약강의 | SQL 기본 및 활용 | 최단시간 최대효율👍 | 핵심 요약노트 | SQL개발자

Main ideas / lessons conveyed

1) DBMS and relational DB focus (SQLD exam context)

• To access data, you must go through a Database Management System (DBMS) rather than accessing a database directly.
• A DBMS provides the environment to manage and access databases.
• The most common DBMS type is a Relational DBMS (RDBMS).
• RDBMS models relationships between tables, typically via:
  • Primary Key (PK) ↔ Foreign Key (FK) relationships.
• For the SQLD exam:
  • The scope centers on DBMS concepts.
  • Oracle is emphasized (most syntax/content explained with Oracle).
  • Some SQL Server topics are included for comparison.

---

2) What data structures are called in SQL

• Table: holds multiple sets of data.
• Attribute / column / field: schema elements describing data.
• Tuple / row / record: actual data entries.
• Column header: describes what a column means.

---

3) SQL as a language + the 4 categories of SQL

• SQL is an international standard language for DBMS.
• SQL is grouped into 4 categories:
  • Definition Language (DDL)
  • Manipulation Language (DML)
  • Control Language
  • Transaction Control

---

4) SQL statement interpretation strategy (especially SELECT)

• A key exam skill is interpreting SQL statements, not only writing them.
• The lecture recommends step-by-step understanding using the execution order for SELECT.

SELECT execution order (computer interpretation order)

• Common write order (often):
  • SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
• Common execution / interpretation order (computer):
  • FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• Suggested mnemonic: “FWGHSO”.

Interpretation idea:

• FROM: choose the source table(s)
• WHERE: filter rows (tuple-level filtering)
• SELECT: choose output attributes/expressions (projection)
• Later clauses follow similar conceptual handling (grouping/sorting as applicable)

---

5) Core SELECT concepts covered

• SELECT * (selecting all columns)
  • If nothing special is specified, it implies “all columns” (also mentions ALL as an idea).
• Projection with specific columns
  • Example concept: output only name, age.
• DISTINCT
  • Removes duplicate output values.
• Aliases
  • Rename output columns (and table aliases in FROM).
  • Restrictions:
    • Reserved words cannot be used as aliases.
    • Special character rules may require escaping/delimiters (e.g., double quotes).
• String concatenation
  • Oracle: ||
  • SQL Server: uses + (contrasted)

---

6) Functions (character, numeric, date/time, conversion)

General function idea

• A function takes inputs, processes them, and returns outputs.
• Types by cardinality:
  • Single-row functions: 1 input → 1 output per row
  • Multi-row/aggregate functions: many inputs → 1 output overall

Testing functions in Oracle

• Use Oracle’s dummy table DUAL to test expressions without real table data.

Character functions (key examples)

• Substring: extract part of a string using start index and length.
• Trim / LTRIM / RTRIM
  • Remove spaces or specified characters from left/right.
• Note: other character functions exist, but not all are deeply covered.

Numeric functions (key examples)

• Rounding: digit precision is controlled; behavior differs for positive/negative digits.
• Truncation: discards digits beyond a specified position.
• Floor / Ceiling
  • FLOOR(3.58) → 3
  • CEIL(3.58) → 4

Date functions

• Oracle current date concept:
  • SYSDATE
• SQL Server equivalent concept noted:
  • GETDATE

Conversion functions (type casting)

• Numeric ↔ character conversion (e.g., TO_CHAR-like functions).
• Text ↔ date conversion (mentions a TODAY-style concept).
• Key lesson: strings that look like dates are still text until converted—this affects arithmetic/comparison.

Aggregate vs scalar function usage rule

• Aggregate functions / direct functions:
  • Use in SELECT with grouping contexts (or in HAVING after grouping).
  • Cannot be used for WHERE-style external row filtering.
• COUNT behavior
  • COUNT(*), COUNT(1), COUNT(0) are treated as the same idea: count rows.
  • COUNT(salary) differs if salary is NULL.

---

7) NULL-handling functions

• Main concepts:
  • NVL: replace NULL with a given value.
  • NVL2-like branching: different outputs depending on whether a column is NULL.
  • COALESCE-like / NULL ordering alternatives: “if null then … otherwise …” style.
• Core exam rule:
  • You cannot compare to NULL with normal operators.
  • Use proper NULL predicates:
    • IS NULL / IS NOT NULL

---

8) CASE and DECODE (branching)

• CASE:
  • Conditional branching; outputs different labels based on values.
  • Includes an ELSE fallback.
• DECODE:
  • Oracle-specific branching style for similar logic.

---

9) WHERE conditions and operators

Comparison operators and logical patterns

• Standard comparisons: =, != / <>, >=, etc.
• BETWEEN
  • Inclusive bounds: A <= x <= B.
• Date filtering
  • Uses “earlier than” logic plus BETWEEN examples.

LIKE and wildcards

• % = any length (including 0 characters)
• _ = exactly one character
• ESCAPE character
  • Needed when % or _ must be matched literally rather than treated as wildcards.

---

10) GROUP BY and HAVING (group filtering)

• GROUP BY groups rows to define aggregation granularity.
• HAVING filters groups after aggregation.
• Performance/resource note:
  • GROUP BY can be expensive; filtering earlier vs later can matter (speaker gives reasoning).

---

11) ORDER BY (sorting)

• Sorting happens after the SELECT result formation.
• Default ordering is ascending.
• Descending uses a keyword (intended: DESC).
• Multi-key sorting with tie-breaking.
• Sorting can reference:
  • aliases from SELECT
  • column positions (e.g., 2 DESC, 3 ASC)
• Ordering behavior:
  • numbers: natural ascending
  • strings: alphabetical
  • dates: chronological
• DBMS difference for NULL ordering:
  • Oracle vs SQL Server treat NULL as max/min differently (exam trap emphasis).

---

12) Joins (combining tables)

Why joins exist

• Normalization splits data into multiple tables; joins recombine related information.

Types of joins covered

• Equi-join
  • Join condition uses equality.
• JOIN ... ON
  • ON links columns even if column names differ.
• If join type isn’t specified:
  • default behaves like an inner join.
• Natural join
  • Automatically uses same-named columns as join keys; no explicit ON.
• Cross join
  • Cartesian product: all combinations.
• Outer joins
  • Return unmatched rows too:
    • Left outer join: keep all rows from left table; unmatched right becomes NULL
    • Right outer join: keep all rows from right table
    • Full outer join: keep unmatched from both sides (left + right behavior)

---

13) Subqueries and inline views (FROM subquery / “temporary table” concept)

Subquery types

• Single-row subquery
  • Returns one value; used where a scalar is expected.
• Multi-column subquery
  • Returns multiple columns.
• General lesson:
  • interpret inside-out, then apply the outside condition.

IN/ANY/EXISTS operators (briefly)

• Mentioned as additional conceptual operators beyond IN:
  • ANY, ALL, EXISTS

Inline view (subquery in FROM)

• Inline view behaves like a temporary table created from the subquery.
• It’s described as a view-like logical object:
  • convenience via naming
  • avoids exposing full raw data

---

14) Set operators

• UNION
  • Combine results; remove duplicates.
• UNION ALL
  • Combine results; keep duplicates.
• INTERSECT
  • Keep only the intersection.
• MINUS / EXCEPT-like
  • Difference: rows in the first but not the second.
• Ordering note:
  • internal alignment may exist; ordering isn’t guaranteed.

---

15) Advanced grouping: ROLLUP, CUBE, GROUPING SETS

• ROLLUP
  • Summarizes at multiple levels:
    • (col1, col2) → (col1) → grand total
  • With n columns: produces n + 1 levels.
• CUBE
  • Produces all grouping combinations.
  • With n columns: produces 2^n combinations.
• Grouping sets
  • Explicitly specifies which grouping combinations to produce.
  • Can include grand total via empty grouping () (using NULL concept).

---

16) Window functions (OVER clause)

• Window functions use the OVER keyword.
• They compute results per row without collapsing rows like GROUP BY.

Ranking functions

• ROW_NUMBER, RANK, DENSE_RANK
• Tie behavior includes rank/gap differences (as described in the lecture).

Aggregate-as-window

• Example idea: SUM(...) OVER() repeats the total on each row.
• PARTITION BY
  • Divides rows into partitions (e.g., by region) before computing.
• Window ORDER BY
  • Supports running/cumulative computations.
• ROWS vs RANGE
  • Affects frame aggregation boundaries.

Frame-related examples

• LAG / LEAD
  • LAG: previous row value
  • LEAD: next row value
  • Partition restart: missing neighbor → NULL
• LAST_VALUE / FIRST_VALUE
  • Default window frame may limit what counts as “last”.
  • Requires frame specification like RANGE BETWEEN ... to include full future rows.

Moving averages and cumulative sums

• Show how ROWS vs RANGE changes which rows are included.

---

17) Top-N queries (limiting results)

• DBs use different constructs:
  • Oracle: ROWNUM
  • SQL Server: TOP
  • also mentioned: LIMIT, FETCH, etc.
• Key rule:
  • Use ORDER BY plus an appropriate limiting strategy.
  • If limiting happens before sorting (or incorrectly using ALL/-equivalent ideas), results can be random.
• ROWNUM filtering logic
  • Use <= N rather than = N to avoid empty/incorrect top-N traps.

---

18) Hierarchical queries (Oracle-style)

• Uses parent-child relationships in a self-referencing structure.
• Key keywords:
  • START WITH
  • CONNECT BY (direction depends on how it’s written)
  • LEVEL (depth)
  • SYS_CONNECT_BY_PATH (path building concept)
• Direction control:
  • forward (parent → child) or reverse (child → parent).

---

19) Pivot / Unpivot

• PIVOT
  • Converts row values into columns (wide format).
• UNPIVOT
  • Converts columns into rows (long format).

---

20) Regular expressions (limited exam emphasis)

• RegEx is described as advanced pattern matching (similar “joker” concept to LIKE).
• Recommended study approach:
  • focus on frequently appearing patterns since exam questions are limited.

---

21) SQL management syntax: DML, transaction control, DDL, DCL

DML (Data Manipulation Language)

• INSERT
  • Adds new rows.
  • If inserting without providing all columns, unspecified columns become NULL.
• UPDATE
  • Modifies existing rows; condition is critical.
  • Warning: forgetting WHERE updates all rows.
• DELETE
  • Deletes rows matching a condition.
• MERGE
  • “Upsert” logic:
    • if match → update
    • if not match → insert

Transaction control concepts

• A transaction is the smallest logical unit of work.
• Supports:
  • COMMIT
  • ROLLBACK
  • Savepoints
  • Isolation levels (dirty read / non-repeatable / phantom concepts referenced)
• Savepoint behavior:
  • rolling back returns the database to that savepoint, undoing later changes.
• Auto-commit differences:
  • Oracle: auto-commit typically OFF by default (manual commit is needed)
  • SQL Server: auto-commit typically ON by default (changes reflect immediately)

DDL (Data Definition Language)

• Defines/changes schema:
  • CREATE TABLE
  • constraints:
    • NOT NULL, PRIMARY KEY, UNIQUE, CHECK
    • FOREIGN KEY referencing another table
• Alter operations:
  • ADD column, MODIFY column, DROP column, RENAME column
• Deleting a table:
  • DROP TABLE
  • CASCADE concept: if other tables reference it via FK, constraints are removed so deletion proceeds.
• Data removal without dropping:
  • TRUNCATE TABLE
    • removes all rows but keeps structure
    • cannot be rolled back (warning emphasized)
• DROP vs TRUNCATE vs DELETE
  • DELETE: removes rows with WHERE, transactional
  • TRUNCATE: bulk structural operation

DCL (Data Control Language)

• GRANT: give permissions
• REVOKE: revoke permissions
• Privilege inheritance ideas:
  • WITH GRANT OPTION
    • recipient can further grant permissions
    • revoking upstream can cascade revoke
  • WITH ADMIN OPTION
    • grants administrative/system-level capabilities
    • different inheritance/revocation behavior

---

Speakers / sources featured

• Primary speaker (not named in provided subtitles): instructor/lecturer presenting the course content.
• Sources mentioned (not as speakers):
  • Oracle
  • SQL Server
  • DUAL (Oracle example dummy table)
  • YouTube channel (speaker references their separate lecture on relational algebra/calculus under “Introduction to Databases”)