Lab · SQL and Databases
Lab: tables for a kirana shop
The brief
Sharma General Store in Indore wants to stop writing sales in a notebook. Design its first three tables (products, customers and sales) with the right types, keys and rules, add the stock, and change a table when the owner asks for GST.
Why it matters
Every billing app, from a kirana shop to a supermarket chain, starts with tables like these. Getting the keys and rules right on day one stops bad data (a negative price, two customers with one phone number) before it ever gets in.
Skills you practise
- CREATE TABLE
- data types
- PRIMARY KEY and FOREIGN KEY
- NOT NULL, UNIQUE, CHECK and DEFAULT
- INSERT
- ALTER TABLE
What you will have at the end
shop.sql: a script that builds the shop's products, customers and sales tables with keys and rules, loads the stock and adds GST.
How it is marked (rubric, 100 points)
| Correct tablesEvery check passes: the right columns, types, keys and default values. | 40 |
|---|---|
| Rules that protect the dataNOT NULL, UNIQUE, CHECK and foreign keys are on the columns that need them, and you can say what each one stops. | 30 |
| Readable scriptOne column per line, lined up, with a short comment above each block. | 15 |
| ExplainedYou can explain why phone is TEXT, why customer_id may be NULL and why the new column needs a DEFAULT. | 15 |
0/5 steps done
Step 1 of 5
The products table
shop.sql creates a products table with id, name, category, price and stock, and the rules below.
Create products with five columns. id is an INTEGER PRIMARY KEY, so every product has its own number. name and category are TEXT and NOT NULL. price is an INTEGER (whole rupees), NOT NULL, with CHECK (price > 0) so a price of zero or less is rejected. stock is an INTEGER, NOT NULL, with DEFAULT 0, so a new product starts with no stock when none is given.
The check tries to add a product priced at 0 and one without a stock value. Your rules should reject the first and give the second a stock of 0.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.