Introduction
MEND & MOSS repairs household items. Two suppliers both use the SKU BOLT-1, but they sell different bolts. The shop keeps a local shelf note alongside each supplier's title, price and revision. A revision is a positive edition number: larger means newer.
In this Guided Lab, you will stage a six-line supplier CSV without changing stock, then create reusable SQL views that separate usable records from invalid ones. A view is a saved query, not a second copy of its rows. You need basic terminal navigation only; the SQL and SQLite commands are introduced before use. All shop records are synthetic.
Use the Terminal Tab throughout. SQLite and the starting database are prepared. There is no website to build. Keep the original stock and both files in deliveries/ unchanged.
Read the Stock and Stage a Delivery
In this step, you will connect a normal stock record to its supplier, then import the complete current delivery into a separate staging table. Begin in Terminal. cd changes directory; this absolute path also recovers from an ordinary wrong-directory error:
cd /home/labex/project/supplier-desk
ls lists files. You should see stock.db and deliveries:
ls
The database is a real file. sqlite3 stock.db opens it in SQLite's interactive shell. Stay in the sqlite> prompt for the SQL and dot commands below; do not type them at the Linux shell prompt:
sqlite3 stock.db
A table stores rows in named columns. SELECT chooses columns to read, FROM names a table and ORDER BY gives a predictable display order. SQL statements end with ;. The two dot commands control display: .headers on shows column names, and .mode column aligns values. Dot commands have no semicolon:
.headers on
.mode column
SELECT supplier,sku,title,price_cents,revision,shelf_note
FROM stock ORDER BY supplier,sku;
There are three stock rows. North's N / BOLT-1 is Steel bolt, 120 cents, revision 2, in Bin A. West's W / BOLT-1 is Brass bolt, 180 cents, revision 1, in Drawer 4. One cent is the smallest currency unit in this fictional shop; 120 means 1.20. Integer cents avoid decimal rounding in this exercise. N / WASH-2 must also remain available.
The pair supplier,sku identifies a stock record. The SKU alone cannot distinguish the two bolts. SELECT * asks for every column:
SELECT * FROM suppliers ORDER BY code;
Expect N / North Parts and W / West Workshop. A supplier code in a delivery must occur in this table.
SQLite checks foreign-key relationships separately for each connection. Enable them in this shell. This setting does not change stock data:
PRAGMA foreign_keys=ON;
Leave SQLite with .quit; you return to the Linux shell:
.quit
cat prints a text file. Read the actual CSV:
cat deliveries/current.csv
CSV means comma-separated values. The first line names five columns. The file contains prices and revisions as text, including 12kits, an unknown supplier Z, and a row whose SKU is empty. These are deliberate input problems, not instructions to guess a correction. Source files remain unchanged. The provided CSV has exactly five fields per data row; standard CSV quoting is supported.
Reopen the database:
sqlite3 stock.db
The prepared raw_delivery table has five TEXT columns in that order. TEXT preserves exactly what arrived, including invalid numbers; importing directly into stock would mix inspection with business updates. .import --csv selects CSV parsing, and --skip 1 skips its heading:
.import --csv --skip 1 deliveries/current.csv raw_delivery
rowid is SQLite's row identifier for this staging table. It is useful as a delivery-line number, not as a product identity. Show every imported row:
.headers on
.mode column
SELECT rowid AS delivery_line,* FROM raw_delivery ORDER BY rowid;
Expect lines 1–6 in file order. Lines 1–3 are the two bolts and new repair tape. Line 4 has 12kits, line 5 supplier Z, and line 6 an empty SKU. AS gives the displayed rowid the clearer name delivery_line.
An import appends rows. If you accidentally imported twice, clear only this staging table with DELETE FROM raw_delivery;, then repeat the import once. This is the ordinary recovery for this unit; never delete stock or reset the whole VM:
DELETE FROM raw_delivery;
.import --csv --skip 1 deliveries/current.csv raw_delivery
Finish at the Linux shell:
.quit
The staged delivery is your first outcome. Stock still has its original three rows and shelf notes.
Separate Usable Rows from Invalid Values
In this step, you will save an inspection script and use its actual views to review the delivery. Begin in Terminal, in /home/labex/project/supplier-desk.
The shop accepts a known supplier, a nonempty SKU and a title containing something besides spaces. Prices must contain 1–6 ASCII digits and represent a positive number. Revisions must contain 1–3 ASCII digits and represent a positive number. Leading zeros are allowed. These small bounds are the input contract, not a claim to validate arbitrary numeric formats.
CREATE VIEW name AS SELECT ... saves a query under a name. DROP VIEW IF EXISTS removes only that saved query if it is present; it does not remove the source table. WHERE keeps rows whose condition is true. BETWEEN 1 AND 6 includes both endpoints; NOT BETWEEN detects values outside them.
A CASE expression selects the first matching reason. WHEN gives a condition and THEN its result; no match gives NULL, meaning no error here. IS NULL tests that absence. OR combines alternative reasons to reject a value. NOT IN (SELECT code FROM suppliers) checks supplier membership. trim removes surrounding spaces, and length counts characters.
For the numeric checks, GLOB '*[^0-9]*' detects any character outside ASCII digits: * matches any sequence and [^0-9] matches one nondigit. The separate length check rejects empty strings. CAST(... AS INTEGER) converts text to an integer, but is used only after its digit/length checks. Casting alone is unsafe: text such as 12kits must not be accepted as 12.
You will create four views. delivery_check retains original values and a reason. typed_delivery converts only valid rows. delivery_errors lists rejected line numbers and reasons. delivery_ready exposes values suitable for a later update. The script drops only these named views so it can be run again; it never deletes stock or source rows.
The next Linux command saves that SQL in inspect.sql. > replaces the destination file. <<'SQL' begins a literal here-document: paste the whole block, including the final SQL alone on a line. Do not run this cat command inside SQLite.
cat > inspect.sql <<'SQL'
DROP VIEW IF EXISTS delivery_ready;
DROP VIEW IF EXISTS delivery_errors;
DROP VIEW IF EXISTS typed_delivery;
DROP VIEW IF EXISTS delivery_check;
CREATE VIEW delivery_check AS
SELECT rowid AS delivery_line, *,
CASE
WHEN supplier NOT IN (SELECT code FROM suppliers) OR supplier IS NULL
THEN 'unknown supplier'
WHEN sku IS NULL OR sku = '' THEN 'missing SKU'
WHEN title IS NULL OR trim(title) = '' THEN 'missing title'
WHEN price_cents IS NULL OR length(price_cents) NOT BETWEEN 1 AND 6
OR price_cents GLOB '*[^0-9]*' OR CAST(price_cents AS INTEGER) < 1
THEN 'invalid price'
WHEN revision IS NULL OR length(revision) NOT BETWEEN 1 AND 3
OR revision GLOB '*[^0-9]*' OR CAST(revision AS INTEGER) < 1
THEN 'invalid revision'
END AS reason
FROM raw_delivery;
CREATE VIEW typed_delivery AS
SELECT delivery_line, supplier, sku, title,
CAST(price_cents AS INTEGER) AS price_cents,
CAST(revision AS INTEGER) AS revision
FROM delivery_check WHERE reason IS NULL;
CREATE VIEW delivery_errors AS
SELECT delivery_line, reason FROM delivery_check WHERE reason IS NOT NULL;
CREATE VIEW delivery_ready AS
SELECT supplier,sku,title,price_cents,revision FROM typed_delivery;
SQL
If the terminal keeps waiting, the ending marker is missing: enter SQL alone. If you mistyped a line, repeat the complete block to replace this script. You do not need an editor or a reset.
Run a script using Linux input redirection <. -bail tells SQLite to stop on its first error instead of continuing with later statements. No output is expected on success:
sqlite3 -bail stock.db < inspect.sql
For an ordinary filename or syntax error, use ls, return with the absolute cd from step 1, correct inspect.sql using the complete block, and rerun it. Stock has not been changed.
SQLite also accepts one quoted SQL statement after the database path. -header -column selects a readable display for this one command. The quotes keep spaces and semicolons in one shell argument:
sqlite3 -header -column stock.db 'SELECT * FROM delivery_errors ORDER BY delivery_line;'
Expect:
delivery_line reason
------------- ----------------
4 invalid price
5 unknown supplier
6 missing SKU
The reason belongs to a delivery line, so a rejected item can be traced back to the source. No source value has been repaired or erased.
sqlite3 -header -column stock.db 'SELECT * FROM delivery_ready ORDER BY supplier,sku;'
Expect three acceptable rows: North's bolt at 135 cents/revision 3, repair tape at 450/revision 1, and West's bolt at 190/revision 2. The two bolt identities remain separate. typeof reports SQLite's actual value type:
sqlite3 -header -column stock.db 'SELECT sku,typeof(price_cents),typeof(revision) FROM delivery_ready ORDER BY supplier,sku;'
Both type columns should say integer. Finally, read the real stock:
sqlite3 -header -column stock.db 'SELECT * FROM stock ORDER BY supplier,sku;'
It still has its original values and shelf notes. You have prepared a reusable receiving inspection; you have not applied the supplier's changes. The next independent Guided Lab starts with a new database and a larger staged delivery.
Summary
You used real SQLite rows to connect supplier identities, delivery values and local stock. You interpreted the actual accepted and rejected records, preserved the original sources, and saved reusable SQL for the next delivery.
