Course roadmap

# Module 1  SQL basics — DML: INSERT / SELECT / UPDATE / DELETE
# Module 2  DDL — CREATE / ALTER / DROP / TRUNCATE tables
# Module 3  Built-in functions, sub-queries, multiple tables, joins
# Module 4  Python for databases — Jupyter, DB-API, SQL Magic, EDA
# Module 5  Hands-on project on real-world data
# Module 6  Advanced — Views, Stored Procedures, ACID, Inner & Outer JOINs

Tips & tricks

• Treat the roadmap as a checklist — after each module, tick what you can do from memory.
• Don't skip the integrated labs: theory sticks only when you run the queries yourself.
• SQL is the universal key; Python is the connector — different jobs, one workflow.

Key definitions

• SQL — the language used for relational databases to query (get data out of) a database.
• Data — a collection of facts: words, numbers, or even pictures.
• Database — a repository of data; supports adding, modifying, and querying.
• DBMS / RDBMS — the set of software tools controlling access, organization, and storage. Examples: MySQL, Oracle, DB2.

The five basic commands

CREATE TABLE ...    -- 1. create a table
INSERT INTO ...     -- 2. populate the table (Create)
SELECT ... FROM ... -- 3. read data               (Read)
UPDATE ... SET ...  -- 4. change data             (Update)
DELETE FROM ...     -- 5. remove rows             (Delete)

Tips & tricks

• The five commands are identical across every RDBMS — product-specific syntax comes later (if ever).
• In practice database ≈ DBMS ≈ database server ≈ data server; these terms are used interchangeably.

Tips & tricks

• Interview favorite: DELETE is DML; TRUNCATE is DDL.
• TRUNCATE vs DELETE without WHERE: both empty a table, but truncate is quicker, more efficient, and resets storage.

Concept → relational result

Common data types

Tips & tricks

• CHAR = truly fixed length (ISBN with dashes, 2-letter country codes); VARCHAR = anything human-entered (titles, names, emails).
• Sketch the ERD first, SQL second — the joins define themselves from PK→FK links.
• The relational model's superpower = logical + physical data independence and storage independence.

Best use cases

• Designing a new database from requirements — model first, build second.
• Explaining why a table's structure is the way it is.

Syntax & code

-- Lookup table with a tiny fixed-length code
CREATE TABLE provinces (
    id   CHAR(2)    PRIMARY KEY NOT NULL,   -- 'AB', 'BC', ...
    name VARCHAR(24)                        -- 'ALBERTA', 'BRITISH COLUMBIA', ...
);

-- Library example with constraints
CREATE TABLE author (
    author_id  CHAR(2)      PRIMARY KEY NOT NULL,
    lastname   VARCHAR(15)  NOT NULL,
    firstname  VARCHAR(15)  NOT NULL,
    email      VARCHAR(40),
    city       VARCHAR(15),
    country    CHAR(2)
);

Tips & tricks

• PRIMARY KEY prevents duplicates; NOT NULL makes the field required (an author must have a name).
• Designate the primary key at creation time — retrofitting uniqueness into a populated table is painful.
• The last column has no trailing comma — the classic beginner syntax error.

Syntax & code

ALTER TABLE author ADD COLUMN telephone_number BIGINT;
ALTER TABLE author MODIFY telephone_number CHAR(20);   -- widen to allow "(" "+" "-"
ALTER TABLE author DROP COLUMN telephone_number;

DROP TABLE author;                 -- table + data gone

TRUNCATE TABLE author IMMEDIATE;   -- DB2: all rows, not undoable
-- MySQL / PostgreSQL: TRUNCATE TABLE author;

Tips & tricks

• Store phone numbers as strings — ( + - break numeric columns.
• ALTERing a populated column's datatype fails when existing data is incompatible — check first.
• Mnemonic: TRUNCATE = fast empty; DROP = demolish. Neither takes a WHERE.

Syntax & code

INSERT INTO author (author_id, lastname, firstname, email, city, country)
VALUES ('A1', 'Chong', 'Raul', 'RFC@ca.ibm.com', 'Toronto', 'CA');

-- Multiple rows in one statement
INSERT INTO author (author_id, lastname, firstname, email, city, country)
VALUES
    ('A1', 'Chong', 'Raul', 'RFC@ca.ibm.com', 'Toronto', 'CA'),
    ('A2', 'Ahuja', 'Rav',  'RA@ca.ibm.com',  'Markham', 'CA');

Tips & tricks

• Golden rule: number of values = number of listed columns — that's what gives every column a value.
• Always name the columns instead of relying on positional order — your code survives schema changes.
• Text values need quotes ('A1'); numbers don't. Batch small loads into multi-row INSERTs.

Syntax & code

SELECT * FROM book;                    -- 4 rows, all columns
SELECT book_id, title FROM book;       -- output column order = SELECT order
SELECT book_id, title
FROM   book
WHERE  book_id = 'B1';                 -- only rows where the predicate is TRUE

Comparison operators

Tips & tricks

• Avoid SELECT * in production code: name columns for less traffic, self-documentation, and schema-change safety.
• A WHERE on a CHAR key ('B1') compares strings — mind the quotes.
• The "unknown" arm comes from NULL comparisons: NULL means we don't know, not false.

Syntax & code

SELECT COUNT(*)                 FROM tablename;                       -- all rows
SELECT COUNT(COUNTRY)           FROM MEDALS WHERE COUNTRY = 'CANADA'; -- matching rows
SELECT DISTINCT COUNTRY         FROM MEDALS WHERE MEDALTYPE = 'GOLD'; -- uniques
SELECT *                        FROM MEDALS WHERE YEAR = 2018 LIMIT 5; -- a peek

Tips & tricks

• Explore with LIMIT, not SELECT * — a few rows answer "what kind of data is this?" almost instantly, even on millions-of-row tables.
• COUNT(DISTINCT COUNTRY) answers "how many different X?" in one query.
• Dialect spelling: LIMIT (MySQL/SQLite/PG), TOP 10 (SQL Server), FETCH FIRST 10 ROWS ONLY (DB2/Oracle 12c+).

Syntax & code

SELECT * FROM author;  -- 1. preview current state

-- 2. change FIRSTNAME and LASTNAME of the author with AUTHOR_ID = 'A2'
UPDATE author
   SET lastname = 'Katta', firstname = 'Lakshmi'
 WHERE author_id = 'A2';

-- 3. remove the rows for two authors
DELETE FROM author WHERE author_id IN ('A2', 'A3');

Best use cases

• Data correction / cleansing — mis-entered names, bulk code updates.
• GDPR-style removal of specific records, always with a previewed condition.

Syntax & code

-- String pattern: % is the wildcard (0+ of any character)
SELECT firstname FROM author WHERE firstname LIKE 'R%';   -- starts with R
SELECT firstname FROM author WHERE firstname LIKE '%on%'; -- contains "on"

-- Range (inclusive) — same as pages >= 290 AND pages <= 300
SELECT title FROM book WHERE pages BETWEEN 290 AND 300;

-- Set of values — avoids a very long repeated predicate
SELECT * FROM author WHERE country IN ('CA', 'IN', 'CN');

Tips & tricks

• Pattern position is the whole trick: 'R%' starts-with, '%R' ends-with, '%R%' contains.
• LIKE is case-sensitive in some DBs — pair with UPPER()/LOWER() for robust matching.
• BETWEEN is inclusive; with dates/timestamps the end boundary can silently bite — prefer half-open ranges.

Syntax & code

SELECT title FROM book ORDER BY title;        -- alphabetical (ASC default)
SELECT title FROM book ORDER BY title DESC;   -- descending
SELECT title, pages FROM book ORDER BY 2;     -- sort by 2nd column in the list

Tips & tricks

• Prefer ORDER BY columnname over positional ORDER BY 2 in anything saved — reordering the SELECT list silently retargets positional sorts.
• Multi-column: ORDER BY country ASC, lastname DESC — later columns break ties.
• ORDER BY runs after SELECT, so it can reference aliases; WHERE runs before and cannot.

Best use cases

• Human-readable reporting; "top-N" queries: … ORDER BY score DESC LIMIT 5.

Syntax & code — progression on the author table

SELECT country FROM author ORDER BY 1;               -- 20 rows, duplicates
SELECT DISTINCT country FROM author;                 -- 6 unique countries

SELECT country, COUNT(*) AS "Count"                  -- per-country counts
FROM author GROUP BY country;

SELECT country, COUNT(*) AS "Count"                  -- keep only big groups
FROM author
GROUP BY country
HAVING COUNT(*) > 4;                                 -- → China 6, India 6

Tips & tricks

• Mnemonic: WHERE filters rows; HAVING filters groups. Conditions with aggregates belong in HAVING.
• Logical order: WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY.
• COUNT(*) counts rows; COUNT(column) counts non-NULL values — matters on real data.
• Use AS to name derived columns — the default heading for aggregates is meaningless junk ("2").

Aggregate functions

SELECT SUM(COST) FROM PETRESCUE;                     -- total of a column
SELECT SUM(COST) AS SUM_OF_COST FROM PETRESCUE;      -- name the output column
SELECT MAX(QUANTITY) FROM PETRESCUE;                 -- highest value
SELECT MIN(ID) FROM PETRESCUE WHERE ANIMAL = 'Dog';  -- on a subset
SELECT AVG(COST/QUANTITY) FROM PETRESCUE WHERE ANIMAL = 'Dog';  -- cost per dog

Scalar & string functions

SELECT ROUND(COST) FROM PETRESCUE;                   -- round each value
SELECT LENGTH(ANIMAL) FROM PETRESCUE;                -- length per value
SELECT UPPERCASE(ANIMAL) FROM PETRESCUE;             -- UPPER() in most dialects
SELECT * FROM PETRESCUE WHERE LOWERCASE(ANIMAL) = 'cat';  -- case-safe WHERE
SELECT DISTINCT(UPPERCASE(ANIMAL)) FROM PETRESCUE;   -- nested functions

Tips & tricks

• Push computation to the database for large data; pull raw rows only when analysis can't be expressed in SQL.
• UPPER/LOWER in WHERE = cheap insurance against inconsistent data entry.
• Always alias aggregate columns. Function-in-where on huge tables can prevent index use.

Syntax & code

SELECT DAY(RESCUE_DATE) FROM PETRESCUE WHERE ANIMAL = 'Cat'; -- day portion
SELECT COUNT(*) FROM PETRESCUE WHERE MONTH(RESCUE_DATE) = 5; -- May sales
SELECT DATE_ADD(RESCUE_DATE, INTERVAL 3 DAY) FROM PETRESCUE; -- +3 days
SELECT CURRENT DATE - RESCUE_DATE FROM PETRESCUE;            -- elapsed since rescue
                                                             -- in yrs, months, days

Tips & tricks

• Dialect spellings differ — DB2 CURRENT DATE, MySQL CURDATE(), SQLite DATE(rescue_date,'+3 day'), standard CURRENT_DATE.
• MONTH(date)=5 scans every row; for big tables prefer a range on the column so indexes work.
• BETWEEN on timestamps can miss late-Dec rows — use half-open ranges (>= start AND < next start).

Best use cases

• Seasonality / day-of-week analysis; SLA math ("process within 3 days"); ageing queries.

Three placements

-- ① In the WHERE clause — the classic workaround:
--    ❌ WHERE salary > AVG(salary)  → "invalid use of aggregate function"
SELECT employee_id, firstname, lastname, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

-- ② In the column list — compare each row's salary with the average
--    (naive version fails for lack of GROUP BY)
SELECT employee_id, salary,
       (SELECT AVG(salary) FROM employees) AS AVG_SALARY
FROM   employees;

-- ③ In the FROM clause — a derived table (excludes sensitive fields)
SELECT * FROM
   (SELECT employee_id, firstname, lastname, department_id
    FROM   employees) AS EMPLOYEEF;

Tips & tricks

• Rule of thumb: aggregate value needed for a comparison → sub-query.
• Always alias derived tables (AS EMPLOYEEF — "employee for all", non-sensitive).
• Derived tables show their real power in multi-table work and joins.

Syntax & code — EMPLOYEES × DEPARTMENTS

-- Sub-query: employees whose department exists / is in location L0002
SELECT * FROM employees
WHERE  DEP_ID IN (SELECT DEP_ID_DEP FROM departments);
SELECT * FROM employees
WHERE  DEP_ID IN (SELECT DEP_ID_DEP FROM departments WHERE LOC_ID = 'L0002');

-- Reversed direction: departments of employees earning > $70,000
SELECT DEP_ID_DEP, DEP_NAME FROM departments
WHERE  DEP_ID_DEP IN (SELECT DEP_ID FROM employees WHERE salary > 70000);

-- Implicit (full) join — every row × every row; MORE rows than either table
SELECT * FROM employees, departments;

-- Implicit join filtered to matching department IDs, with aliases
SELECT E.EMP_ID, D.DEP_NAME
FROM   employees E, departments D
WHERE  E.DEP_ID = D.DEP_ID_DEP;

Tips & tricks

• Fully qualify columns (employees.DEP_ID) — different tables can share column names; aliases make it short.
• When the condition is exactly a.col = b.col, prefer explicit INNER JOIN … ON, so the condition is never accidentally forgotten.
• Forgot the WHERE on a two-table FROM = accidental Cartesian explosion.

How tables link

Join families

Tips & tricks

• Sketch the ERD before writing joins — the key to join on is already in the diagram.
• Three-table joins = simply add tables to the chain (BORROWER↔LOAN on borrower ID, LOAN↔COPY on copy ID).
• Join key names don't have to match (DEP_ID vs DEP_ID_DEP) — the PK→FK relationship does.

Syntax & code — BORROWER ⟕ LOAN

-- Who is borrowing books, and the loan date
SELECT B.BORROWER_ID, B.LASTNAME, B.COUNTRY, L.BORROWER_ID, L.LOAN_DATE
FROM   BORROWER B                       -- left table (left of the JOIN clause)
INNER JOIN LOAN L                       -- right table
   ON B.BORROWER_ID = L.BORROWER_ID;    -- the join predicate

Tips & tricks

• INNER is the default — JOIN alone means the same; write it explicitly for readability.
• Declare aliases once (FROM BORROWER B) and prefix every column with them, in SELECT and ON alike.
• Fan-out is expected: if the right table has several matches per left key, the left row repeats once per match — not a bug.
• Borrowers with no loans don't appear here; they return with OUTER JOINs (next lecture).

Syntax & code

-- LEFT: all borrowers; Peters, Li, Wong show NULL loan data (never borrowed)
SELECT B.BORROWER_ID, B.LASTNAME, B.COUNTRY, L.BORROWER_ID, L.LOAN_DATE
FROM   BORROWER B
LEFT OUTER JOIN LOAN L  ON B.BORROWER_ID = L.BORROWER_ID;

-- RIGHT: all loans; one row has NULL borrower columns
--        → "a book on loan to an unknown person" = a data problem to investigate
SELECT L.BORROWER_ID, L.LOAN_DATE, B.LASTNAME, B.COUNTRY
FROM   BORROWER B
RIGHT OUTER JOIN LOAN L ON L.BORROWER_ID = B.BORROWER_ID;

-- FULL: all rows from both tables (very large result set possible)
SELECT B.BORROWER_ID, B.LASTNAME, L.LOAN_DATE
FROM   BORROWER B
FULL OUTER JOIN LOAN L ON B.BORROWER_ID = L.BORROWER_ID;

Tips & tricks

• Direction is about position in the statement, not importance — swap sides with LEFT before reaching for RIGHT.
• Aggregate columns from the NULL side read NULL, not zero — wrap in COALESCE(L.LOAN_DATE, 'never') for reporting.
• MySQL & SQLite have no FULL OUTER JOIN — emulate with LEFT JOIN UNION RIGHT JOIN.
• Treat unmatched-row NULLs as signal — RIGHT-join NULLs found a data-quality issue in the lecture.

The SQL API flow

• 1. API call(s) connect the program to the DBMS.
• 2. Build the SQL statement as a text string in a buffer; an API call passes it to the DBMS.
• 3. API calls check status / handle errors; others retrieve query results.
• 4. An API call disconnects from the database.

Proprietary APIs by database

Tips & tricks

• Internalize: applications never parse SQL themselves — they build text and hand it over; parsing, execution, errors, results all live in the DBMS.
• Notebooks (Jupyter) = live code + equations + visualizations + narrative, shareable and rich-output — the course's home base.

The canonical 5-step program

# 1. import the database module
import ibm_db           # (or sqlite3, psycopg2, mysql.connector, ...)

# 2. open the connection: db name, username, password
conn = ibm_db.connect(dbname, username, password)

# 3. create a cursor on the connection
cursor = conn.cursor()

# 4. run queries through the cursor, then fetch results
cursor.execute('SELECT * FROM employees')
results = cursor.fetchall()

# 5. free resources — ALWAYS
conn.close()

Connection methods

Tips & tricks

• Memorize the rhythm: connect → cursor → execute → fetch → close — identical for every database Python meets.
• Use with sqlite3.connect(...) as conn: / try…finally so close() runs even on exceptions.
• Cursors from the same connection are not isolated; from different connections it depends on transaction implementation.
• commit()/rollback() on the connection are the ACID story (lecture 29) surfacing in Python.

Set-up & first query

! pip install ipython-sql        # 1. install the dependency
%load_ext sql                    # 2. load the SQL extension
%sql sqlite://HR.db              # 3. connect (SQLite example)

%%sql                            # 4. cell magic runs whole-cell SQL
SELECT * FROM employee

Popular magics

Tips & tricks

• Stack multiple line magics in one cell — each on its own line.
• %timeit times one statement; %%timeit times the entire cell — the canonical line/cell pair.
• Use %%sql cell magic for anything longer than a line: the whole cell is SQL, so line-continuation backslashes become unnecessary (next lecture).
• List all available magics with %lsmagic.

Syntax & code

-- Backticks for column names with spaces/special chars (MySQL)
-- ⚠ single or double quotes DO NOT WORK for column names
SELECT `Breed`, COUNT(*) AS "Count" FROM dogs GROUP BY `Breed`;

-- Restrict result server-side (don't SELECT * then df.head() — slow)
SELECT * FROM census_data LIMIT 3;

# pandas reads a query straight into a DataFrame
query_statement = "SELECT `Name of Dog`, `Breed` FROM dogs LIMIT 5"
df = pd.read_sql(query_statement, conn)

# %sql line magic needs backslashes to continue lines
%sql SELECT `Breed`, COUNT(*) AS "Count" \
     FROM dogs \
     GROUP BY `Breed`
# ...but %%sql cell magic makes backslashes redundant

Quote decision tree

• Column names that are reserved / have spaces → backticks.
• SQL string values → single quotes (escape any inside with \).
• Python string holding SQL → wrap in single quotes, use double quotes for quoted column names inside.

Loading CSVs (phpMyAdmin)

• Browse the file → Format: CSV → check "first line contains the table column names" → Go.
• MySQL auto-detects headers and generates the CREATE/INSERT statements; default table name = the file's name, lowercased.

Catalogs per system

SELECT name FROM sqlite_master WHERE type = 'table';  -- list SQLite tables
PRAGMA table_info(table_name);                        -- attributes/columns
SHOW TABLES;                                          -- MySQL: list tables
DESCRIBE table_name;                                  -- MySQL: table details

Tips & tricks

• The recon routine for any unfamiliar DB: list tables → describe → SELECT * LIMIT 5.
• PRAGMA table_info returns name, type, notnull, pk per column — schema info without any GUI.
• sqlite_master.sql even holds the original CREATE statements — lost-schema recovery.

The pipeline

import pandas as pd, sqlite3, seaborn as sns

data = pd.read_csv('mcdonalds_menu_nutrition.csv')   # 1. CSV → DataFrame
conn = sqlite3.connect('mcdonalds.db')               # 2. serverless DB
data.to_sql('MCDONALDS_NUTRITION', conn)             # 3. load as table

df = pd.read_sql('SELECT * FROM MCDONALDS_NUTRITION', conn)  # 4. back out
df.head(); df.describe()                             #    260 items, 9 categories

# 5. find the max-sodium item: visualize first, confirm numerically
sns.swarmplot(x='Category', y='Sodium', data=df)     # spots ~3,600 mg
idx  = df['Sodium'].idxmax()                         # index at max → 82
item = df.at[idx, 'Item']                            # → Chicken McNuggets (40 piece)

sns.jointplot(x='Protein', y='Total Fat', data=df)   # scatter + histograms,
                                                     # Pearson r = 0.81
sns.boxplot(df['Sugars'])                            # middle 50% + outliers

Tips & tricks

• CSV → SQLite → pandas → seaborn: the fastest way to make any local dataset SQL-queryable with zero server setup.
• Visualize first (the swarmplot flags the outlier), then confirm numerically (idxmax + .at).
• Correlation lives between −1 and +1; 0.81 = strongly positive.
• .at takes (index, column) — people instinctively flip the order and get errors.
• Box plot: the box is the middle 50% of data; lines/points show skewness and outliers (sugar outliers ≈ 128 g).

Syntax & code

-- Omit sensitive columns (salary, birthdate)
CREATE VIEW EMPINFO
   (EMP_ID, NAME, ADDRESS, JOB_ID, MANAGER_ID, DEPT_ID)
   AS SELECT ID, NAME, ADDRESS, JOB_ID, MGR_ID, DEPT_ID
   FROM EMPLOYEES;

SELECT * FROM EMPINFO;    -- query like a table

-- Filtered view: only one manager's rows
CREATE VIEW MGR_EMPINFO AS
   SELECT NAME, ADDRESS, JOB_ID FROM EMPLOYEES
   WHERE MANAGER_ID = 30002;

DROP VIEW EMPINFO;        -- remove completely

When to use a view

• Omit sensitive data (tax info, birth dates, salaries).
• Combine tables in meaningful ways; show only what one process needs.
• Grant access to the view without granting access to the underlying tables.

Tips & tricks

• Reads/Writes through a view touch the base tables — data lives there, the view is just the definition.
• The defining SELECT may use WHERE / GROUP BY / HAVING and other views — but no ORDER BY, no host variables; view names up to 128 characters.
• Views being dynamic = they track schema/data changes automatically; no refresh job.

Why

Syntax & code — UPDATE_SAL(emp, rating)

DELIMITER $$                       -- stop ';' ending the definition early

CREATE PROCEDURE UPDATE_SAL(IN EMP_NUM INT, IN THE_RATING INT)
  LANGUAGE SQL
  BEGIN
      IF (THE_RATING = 1) THEN
          UPDATE EMPLOYEES SET SALARY = SALARY * 1.10   -- 10% raise
           WHERE EMPLOYEE_ID = EMP_NUM;
      ELSE
          UPDATE EMPLOYEES SET SALARY = SALARY * 1.05   -- 5% raise
           WHERE EMPLOYEE_ID = EMP_NUM;
      END IF;
  END
$$

DELIMITER ;                        -- restore the normal delimiter

CALL UPDATE_SAL('E5001', 1);       -- invoke from SQL or an application

Tips & tricks

• Delimiter discipline: set $$ before, restore ; after — forgetting the second line breaks everything you type next.
• Think of procedures as the database's API: thin, stable client code even when tables change.
• ⚠ SQL is not a fully-fledged programming language — don't move all your business logic into procedures.

The bank-purchase example — Rose buys $200 boots

BEGIN;                                 -- implicit on Db2 on Cloud

UPDATE accounts  SET balance = balance - 200  WHERE who = 'ROSE';
UPDATE accounts  SET balance = balance + 200  WHERE who = 'SHOE_SHOP';
UPDATE inventory SET stock   = stock   - 1    WHERE item = 'Boots';

COMMIT;    -- all three succeeded → persist everything
-- OR
ROLLBACK;  -- any failed (insufficient funds) → undo all, back to before

ACID

Tips & tricks

• In application code, tie COMMIT to the success path and ROLLBACK to the exception path — that's the whole pattern; this is what conn.commit()/conn.rollback() from lecture 22 are.
• Don't hold transactions open while waiting on user input or slow logic — Isolated blocks everyone meanwhile.
• SQL in Java/C/R/Python goes through database-specific APIs (JDBC, ibm_db…), often with EXEC SQL-style commands.

Top 10 habits from the whole course

• Write the WHERE first on every UPDATE/DELETE; preview it as a SELECT.
• LIMIT while exploring — never SELECT * a huge table just to peek.
• WHERE filters rows, HAVING filters groups.
• Prefer explicit JOIN … ON over comma-joins; alias tables and qualify columns.
• Store phone numbers as strings; pick CHAR only for truly fixed lengths.
• Name derived columns with AS — aggregates default to meaningless headings.
• Compare against an aggregate? That's a sub-query.
• NULLs unmatched after outer joins are signal, not noise — investigate them.
• Sensitive column? Serve a view, grant on the view, never the table.
• Multi-step money/inventory changes belong in ACID transactions.