Inserting and Selecting Data

Purpose: display the structure (schema) of an existing table so the developer knows how to write valid queries and DML statements.
- Syntax (case-insensitive):
- $DESCRIBE\ tablename;$ or $DESC\ tablename;$
- Columns returned in result grid:
- Field – column name.
- Type – data type (e.g., $varchar(40)$ , $decimal(10,2)$ , $date$ …).
- Null – indicates whether the column may contain $NULL$ (YES/NO).
- Key – shows if the column is part of a key (PRI, UNI, MUL).
- Default – default value automatically inserted when no explicit value is given.
- Extra – additional metadata (e.g., auto_increment).
Practical workflow in MySQL Workbench (illustrated in screenshot):
- Selecting a schema → Right-click table → Table Inspector → Columns OR run $DESC agent;$ in SQL editor.
- Output shows e.g.
- $agent_code$ $varchar(6) NOT\ NULL PRIMARY\ KEY$
- $commission$ $decimal(10,2) NULL$
- Message pane confirms "6 row(s) returned" and execution time.
Significance:
- Essential before INSERTs or UPDATEs to avoid type & constraint violations.
- Helps detect “NOT NULL” columns that must always receive a value.

Core DML commands:
- $SELECT$ – read data (does not modify); always immediately visible to every user.
- $INSERT$ , $UPDATE$ , $DELETE$ – modify data; changes stay private to current session until committed.
Atomicity & Isolation (ACID):
- Other sessions can’t see uncommitted changes; guarantees consistency or total rollback.

Complete‐value form (column list omitted because all columns filled, in table-defined order):
- $INSERT INTO patient VALUES (232,'Mohammed','Al Walkra','4431-1132');$
- Additional examples supplied for patient IDs $334$ and $400$ .
Multi-row insert (MySQL-specific; Oracle requires separate statements or $INSERT ALL$ ):
- $INSERT INTO patient VALUES\ (401,'Next','Subject','1234-4321'),\ (402,'Final','Demo','4444-3333');$
- Benefits: fewer round-trips, faster bulk loading.

Provide explicit column list whenever you omit one or more attributes:
- $INSERT INTO patient\ (patient_id, name)\ VALUES (500,'Ali');$
Rules:
- You may reorder columns in the list to match your data sequence.
- Columns declared $NOT\ NULL$ (without a default) must receive a value, otherwise DB rejects statement.
- Columns that are nullable receive $NULL$ automatically unless a DEFAULT is defined.

Default behaviour in a fresh MySQL install: autocommit = ON.
- Each DML statement implicitly commits.
Disabling autocommit for group operations:
- $START\ TRANSACTION;$ (alias $BEGIN;$ ) — begins an explicit transaction; Workbench UI also has a toggle button.
- Perform multiple $INSERT/UPDATE/DELETE$ .
- $COMMIT;$ — persist all changes so other users can see them.
- $ROLLBACK;$ — revert to pre-transaction state.
- Network loss / client crash behaves like an automatic rollback, safeguarding consistency.
All statements in the same transaction succeed or fail together, across multiple tables (atomicity guarantee).

Projection vs Selection:
- Projection = choose columns. Example: $SELECT description, price FROM medicine;$
- Selection = choose rows (via $WHERE$ ). Example: SELECT * FROM medicine WHERE price>150;
$SELECT *$ returns every column (useful for ad-hoc debugging, discouraged in production for performance).
Sample output (first query above) illustrates descriptive text + price for each medicine record.

Numeric comparison operators: =,
Character string comparison obeys collation (case-insensitive by default in MySQL unless BINARY or explicit collations used).

Date literal format (MySQL default) $'YYYY-MM-DD'$ or full timestamp $'YYYY-MM-DD HH:MI:SS'$ .
Range selection:
- SELECT * FROM appointment WHERE start_time>= '2022-09-08' AND start_time < '2022-09-09'; (grabs all on Sept 8).
Current date/time:
- $NOW();$ returns current timestamp.
- Example filter: SELECT * FROM appointment WHERE start_time< NOW(); (past appointments).
Adding intervals:
- $SELECT DATE_ADD(NOW(), INTERVAL 5 DAY);$ → date five days ahead; useful for reminders & expirations.

Combine multiple predicates:
- $AND$ (both true) – SELECT * FROM student WHERE name='Test' AND gpa<3;
- $OR$ (either true) – SELECT * FROM student WHERE name='Test' OR gpa<3;
- $NOT$ (negates) – SELECT * FROM student WHERE NOT (gpa>=3);
Operator precedence: $NOT$ > $AND$ > $OR$ (parentheses recommended for clarity).

$BETWEEN$ inclusive shortcut:
- $column BETWEEN 4 AND 10$ ≡ column>=4\ AND\ column<=10.
$IS\ NULL$ / $IS\ NOT\ NULL$ — only correct way to test tri-valued logic; $column=NULL$ fails (unknown).
$IN (….)$ list membership; more readable than multiple $OR$ s.
- Example: $SELECT * FROM student WHERE id IN (1234,1235,2222);$

Wildcard % matches 0+ characters.
- $'m%'$ → starts with m.
- $'%m'$ → ends with m.
- $'%m%'$ → contains m anywhere.
MySQL also supports underscore _ for single-character wildcard.
Performance hint: leading wildcard (e.g., $'%m'$ ) prevents index usage.

DESCRIBE + column metadata informs ORMs/schema-driven applications for automatic validation.
Transactions critical in banking, inventory, healthcare (e.g., updating appointment & billing tables atomically).
Missing-value strategy must align with data governance; excessive NULLs can hamper analytics.
Pattern searches (LIKE) useful for end-user search boxes but often replaced by full-text indexes or external search engines for scalability.
Ethical dimension: When dealing with patient table examples, ensure compliance with HIPAA/GDPR; practice on anonymized data.

Interactive SQL sandboxes (free):
- W3Schools SQL editor: https://www.w3schools.com/sql/
- W3Schools SQL quiz for quick recall.
- GeeksforGeeks SQL tutorial + explanation-heavy articles.
- PracticeTestGeeks: certification-style question bank.
Recommended videos (from slides):
- Inserting data: https://www.youtube.com/watch?v=Cxilfg-M158
- Transaction control: https://www.youtube.com/watch?v=GOQVlrQohtM
- WHERE & logical operators: https://www.youtube.com/watch?v=kUDznltqKbl
- Pattern matching: https://www.youtube.com/watch?v=T11d2ScMtk8