Learn SQL with a small inventory and sales database
Learning MySQL is easier when the data belongs to a recognizable problem. In this course exercise, imagine a stationery shop in Ambala wants to know which products need attention and how much each generated in completed sales. One spreadsheet may work for a few entries. As more orders arrive, repeating a product's name, price and stock in every order row makes mistakes harder to spot. A practice database lets you give each fact a place, link related records and use SQL to answer questions without editing the original rows.
You can work through the lab in phpMyAdmin or a MySQL client. Keep the database separate from any live project and use fictitious products and orders. The first goal is to design tables that represent the difference between a product and a purchase. The next goal is to read those tables using filters, joins and totals. The final goal is to show how a PHP page can retrieve a report using a validated parameter. You can practise all the database exercises before learning PHP. Keep your expected output beside the query so you can tell when a plausible-looking result is wrong.
Translate the stock and sales problem into tables
Begin with a list of facts people would use. The shop needs a product name, a stock quantity, an order date, an order status, the products included in each order and the prices charged. Ask which facts can change independently. A product can appear on many orders and an order can contain several products. The price charged on an order line may differ from the price of the same product today. That amount belongs to the order line. Storing only the current product price would make a report compute past orders using today's prices.
Create three tables for the exercise. Products has a stable product ID and its current stock quantity. Shop orders has an order ID, an order date and a status. Order items records which products belong to an order, the quantity on that line and the unit price actually charged. The lab treats stock quantity as a current snapshot. Sales reports use order items. The sample does not automatically reduce stock when a row is inserted. This separation keeps the first exercises clear and lets you discuss how a real stock update might later need a transaction.
Decide what each database key protects
A primary key gives each row an identifier. Products can have similar names, so using an ID avoids treating two labels as the same item. A foreign key links each order item to an existing product and order. In this simplified lab, each product can appear only once per order, so the pair of order ID and product ID serves as the item key. If a real system allows another line for the same product with a different discount, that system may need a separate line ID. For now the pair makes the assumption visible and prevents an accidental duplicate order line.
Draw the tables and write how many rows one relationship can connect. One order can have many items. An item must refer to exactly one order and one product. One product can appear on many order items, or none. When you are asked why a product with no orders should still appear in a catalogue report, refer back to this last relationship. You should be able to explain the model before opening a database editor.
Create the sample schema in an isolated practice database
Create a disposable practice database through your local development setup and select it before running the statements. If the current database contains other work, start a new one and check its name again. Create Products and Shop orders first because the item table depends on them. Treat the price as a fixed-point decimal, so order amounts can represent rupees and paise without using a floating-point quantity for money. Mark fields required by the lab as not nullable. Keep technical column names consistent so it remains clear whether a number means stock quantity, ordered quantity or price.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(80) NOT NULL,
stock_qty INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
CREATE TABLE shop_orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(8, 2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES shop_orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;
The practice table allows a zero quantity because an unsigned integer type includes zero. A useful extension is to define and enforce a rule that sold quantities must be positive. Document that rule in the application validation and review how your database version supports enforcing it in the table. For the exercises below, all quantities are positive and unit prices are nonnegative. Similarly, the sample only uses the values completed and cancelled for order status. Discuss whether a real application needs a constrained set of status values or a related lookup table.
Load four products and three example orders
INSERT INTO products (product_id, product_name, stock_qty) VALUES
(1, 'Notebook', 50),
(2, 'USB cable', 12),
(3, 'Pencil case', 0),
(4, 'Craft paper', 80);
INSERT INTO shop_orders (order_id, order_date, status) VALUES
(1001, '2026-06-01', 'completed'),
(1002, '2026-06-02', 'completed'),
(1003, '2026-06-02', 'cancelled');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1001, 1, 3, 35.00),
(1001, 2, 1, 160.00),
(1002, 1, 2, 40.00),
(1002, 4, 4, 15.00),
(1003, 2, 3, 150.00);
Now open the three tables and check their row counts. You should see four products, three orders and five order items. Compare the two notebook lines to see why each recorded price lives on its own line. Look at the pencil case and confirm a product can exist without an item row in any order. Finally, note that the cancelled order still appears in the data. A report must decide whether to include it. Deleting it just to make one total work would lose information needed for a different report.
Select the fields the question actually needs
Start with a simple reporting task. The shop wants to identify items with no more than twelve units in stock. Select the product ID, product name and stock quantity from Products. Add a condition on the stock field and sort by quantity ascending, then by product ID. With the sample rows, expect Pencil case at zero before USB cable at twelve. Notebook and Craft paper should not appear. Test the boundary by changing a sample stock quantity to twelve on a disposable copy. It should be included because the condition uses an inclusive comparison.
SELECT product_id, product_name, stock_qty
FROM products
WHERE stock_qty <= 12
ORDER BY stock_qty, product_id;
For the next exercise, select completed orders in a date range. State whether the end boundary is included. A useful convention for reporting is to include the start and exclude the following day or month from the end. For the example, choose 2026-06-01 as the inclusive start and 2026-07-01 as the exclusive end. The result should contain orders 1001 and 1002. Check that adding the status filter excludes 1003 even though it falls inside the date range. Keep the selected columns relevant to what the person reading the result needs.
Compare exact matching with a text search
Search for the exact product name Notebook first and check that one product is returned. Then search for names beginning with Craft and compare how wildcard matching behaves. Keep a note about how your database's configured collation treats differences in case and accent. Do not assume behaviour from one test installation applies to every database. If a product ID is already known, query the ID directly. If the requirement is to search by typed product text, decide whether to accept wildcards as search syntax or treat them as literal characters.
An empty result is still a successful query. Ask the stock question for quantities less than zero in the sample. You should see no products. Explain that a blank set differs from an error message about a missing table or invalid SQL. When you build a web page for this result later, show a meaningful message for no matches. A reporting tool can indicate a successful check without presenting an empty area or claiming an error occurred.
Join the tables with a result you can check by hand
For a report of completed order lines, join the orders table to order items on the order ID, then join each item to Products on its product ID. Show the order ID, product text, quantity, unit price and a calculated line amount. Sort by the two IDs so you can compare the SQL result with the sample. The four completed order lines amount to 105, 160, 80 and 60 rupees. Order 1003 contributes a fifth item to the database but its 450 rupees belong to a cancelled order, so it should not appear in the report.
SELECT o.order_id, p.product_name, i.quantity, i.unit_price,
i.quantity * i.unit_price AS line_amount
FROM shop_orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.status = 'completed'
ORDER BY o.order_id, p.product_id;
If you accidentally join an order ID to a product ID, the syntax may still be valid, but the expected relationship is wrong. If you omit a join condition, combinations that should not be matched can multiply the result. Diagnose the line count immediately after adding each join. Start with the order IDs, then add item IDs and their products. A useful troubleshooting question is what one row represents at that stage. After joining an order to its items, one row represents a line, not a whole order. Counting those lines gives a different answer from counting orders.
Keep products with no matching order items
A catalogue report may need to include every product, including those with zero completed sales. An inner join returns only rows meeting its join conditions. A left join can keep each row from Products even if no matching sales row exists. Be careful when applying a filter to the right-side table in WHERE. If it rejects the rows with missing right-side values, items with no sales can disappear again. One clear approach is to calculate completed totals separately, where the status filter naturally belongs, then left join those totals to the product catalogue.
Group sales into a report and explain the grain
When a manager wants one amount per product, decide what one row should mean before writing the SQL. Grouping by product ID makes each group represent one product. Grouping by the display name alone could merge two different products with the same label. In the report below, the inner query groups only the amounts that belong to completed orders. The outer query retrieves every product and supplies zero for one with no matching total. With the sample, Notebook should have 185, USB cable 160, Craft paper 60 and Pencil case zero.
SELECT p.product_id, p.product_name,
COALESCE(s.completed_sales, 0.00) AS completed_sales
FROM products AS p
LEFT JOIN (
SELECT i.product_id, SUM(i.quantity * i.unit_price) AS completed_sales
FROM order_items AS i
JOIN shop_orders AS o ON o.order_id = i.order_id
WHERE o.status = 'completed'
GROUP BY i.product_id
) AS s ON s.product_id = p.product_id
ORDER BY completed_sales DESC, p.product_id;
Add up those four values by hand to confirm the overall total is 405. The order-level view gives 265 for order 1001 and 140 for order 1002, which sums to the same number. This reconciliation is more informative than scanning a long result. If the totals disagree, compare the individual line amounts and confirm both reports apply the same status and date rules. Check each intermediate step without adding another filter just to force the final number to match.
Distinguish WHERE from HAVING
In an order-level version of the report, use WHERE to remove cancelled orders first. Group the remaining lines by order ID. Then use HAVING to keep groups whose summed line amount is at least 200 rupees. The example should return order 1001 at 265 and exclude order 1002 at 140. If you place a 200 threshold on the individual line amount before grouping, the filter answers a different question. Neither line in order 1001 reaches 200 on its own. Test both queries and explain why the correct report must check the total after aggregation.
Add a second exercise that counts orders and items separately. Order 1001 contributes two item rows but one order. Completed orders total two and their item rows total four. Use the count of distinct order IDs on the joined rows when the question concerns orders. Use the item count when it concerns lines. The same joined tables let you answer both questions once you identify the unit being counted.
Check constraints and rehearse a safe change
Try inserting a sample item for a product ID that does not exist in the isolated lab. With foreign keys enforced, MySQL should reject the relationship. Read the error to learn which constraint failed. Next, start with an existing order and product combination and try inserting it again. The primary key prevents the duplicate pair. Use these results to describe the difference between identifying a row and linking it to another table. Keep the catalogue data intact so you can rerun the read exercises.
Before updating a stock quantity, run a SELECT with the exact same condition you plan to use for UPDATE. Check the matching IDs and current values. Then rehearse within a transaction on the practice data, inspect the new value, roll back and confirm that the original value is restored. With the example tables using InnoDB, the update below should temporarily change the USB cable count to 14 and then return it to 12 after rollback. Run the statements in the same database session and review the result after each step.
START TRANSACTION;
SELECT product_id, stock_qty FROM products WHERE product_id = 2;
UPDATE products SET stock_qty = 14 WHERE product_id = 2;
SELECT product_id, stock_qty FROM products WHERE product_id = 2;
ROLLBACK;
SELECT product_id, stock_qty FROM products WHERE product_id = 2;
This example changes one identified product. It does not claim that every MySQL operation can be undone with a rollback. Some statements such as schema changes can cause commits, and table engine and connection behaviour matter. Use a disposable database and keep a copy of the sample data. If you accidentally update more than the expected row in this exercise, roll back before trying again and compare the condition you used with the original SELECT.
Connect a report to PHP using values as parameters
For a browser view, choose a simple read-only feature. A person enters a maximum stock quantity, and the page lists products whose stock is within that limit. Validate the incoming value as an integer in a sensible range. Pass the validated value to a prepared PDO statement. Keep the database connection settings outside public source and use a database account with the access the report needs. Because the feature only reads the product list, it does not require database administrator privileges or unrelated write permissions.
$query = $pdo->prepare(
'SELECT product_id, product_name, stock_qty
FROM products
WHERE stock_qty <= :maximum
ORDER BY stock_qty, product_id'
);
$query->bindValue('maximum', $maximum, PDO::PARAM_INT);
$query->execute();
$rows = $query->fetchAll(PDO::FETCH_ASSOC);
This fragment assumes the PDO connection is already configured and maximum is a validated integer. Test it with limits of zero, twelve and fifty. Zero gives only the Pencil case in the example, while twelve gives two products and fifty gives three. Try a nonnumeric value and verify your application catches it before running the query. If the user can choose the sort column, select from known options in your application. Prepared statement parameters represent values, so a sort column cannot be passed through the same value placeholder.
When printing names in HTML, escape the output as appropriate for its context. If reading fails, show the reader an understandable error and keep detailed diagnostics in a protected server log. If the query worked and returned no rows, say which filter produced no matches. You can test this distinction using a second lab data set with every product above the selected limit. Keep the report read-only while working on it so that testing a filter cannot change the inventory.
Work through three report changes without losing the original checks
Compare June with a month having no sales
Wrap the completed-sales calculation in a date range, adding the conditions on the order date beside the condition for status. Select the start date and the first date after the reporting period so the boundaries stay clear. Using the sample, June should still produce 405 rupees. July should return zero for each product when you keep every row from the catalogue. Verify especially that the first day of July is excluded from June and included in July if you add a new July test order later. This preserves the meaning of a missing report as a successful query with no matching sales rather than a broken report.
Pick one practice order and add it to a copy of the July data with one line containing two craft papers at 15 rupees each. Check that July now shows 30 rupees for Craft paper and zero for the other products. June should retain its original amounts. If both months now show the new entry, examine which part of the query owns the date filter. The date is a property of the order, although the amount comes from an item. A clear test lets you explain why both tables participate in the filter and total.
Report cancelled sales separately from completed sales
The shop might ask how much value was in cancelled orders while checking why orders were cancelled. Build a separate summary using the recorded cancelled status. Keep that reported amount separate from realised sales. On the sample, the one cancelled order has a line worth 450 rupees. A single grand total of 855 would include both statuses and would not answer the completed-sales question. If you present both totals on one page, label what each represents and identify their date range.
Try extending this output from two fixed summaries to a result grouped by status. Count orders and add line amounts without mixing the two units. The expected completed row shows two orders and 405 rupees, and the cancelled row shows one order and 450 rupees. The counts are only meaningful if each order is counted once. Examine the grouped query after the join so you can point to the expression preventing a line count from being reported as an order count. Add a short description of whether orders with no items should appear in each count. That decision tells you what join behaviour the report needs.
Read a query plan before adding indexes to a bigger lab
The four-row catalogue is useful for comparing expected answers but too small for meaningful speed comparisons. If you want to study performance, generate a disposable copy with many synthetic order rows and keep the small original for correctness tests. Use EXPLAIN on a query filtering orders by status and date. Examine how MySQL plans to reach rows and whether the query can use a relevant index. A plan can legitimately choose a scan for a tiny table or a filter matching most rows. Use the result to ask which rows are expected to match before adding an index.
For a practice comparison, use a date range that includes only a small share of the generated orders. Record the query, row count and query plan before trying an index in the lab. Test the same query after adding the index. Keep the expected results constant and avoid judging performance from a single timing. Indexes require storage and additional maintenance on writes, so explain which repeated report makes the change worth considering. In an interview, point to the original correctness checks as evidence that the change did not alter the answer.
A focused follow-up is to compare a report selecting one known product ID with a report sorting all products by computed totals. The primary key may already support the first goal. The second query needs to read or compute the amounts it sorts. This comparison helps you recognise when an index fits a lookup and when the work is in grouping. Write one paragraph comparing these two tasks in your own words before inspecting the actual plan.
Verify the report and document a portfolio milestone
Create a one-page project note with your model, schema setup and fake sample data. Provide a result for each question a shop owner could ask. Mark which results should include cancelled orders and which relate only to completed sales. Save the stock filter, detail report, product summary and order summary in clearly named examples. A reviewer should be able to rebuild the sample and see the same outputs. If the result contains a zero for a product without sales, describe why that is important to the inventory decision.
Demonstrate the report by starting with the raw tables, then asking a business question. Show the simple filter before showing the joined result. Explain how you noticed or prevented a duplicated total. Next, run the product report and reconcile its sum with the total by order. If you include the PHP view, show which value reaches the prepared statement and how a user sees an empty result. Leave database credentials and real customer details out of the demo.
Practise MySQL interview questions
Why use a table for order items?
An order may contain more than one product, and products appear across many orders. The item table stores the connection and details such as quantity and price. Being able to store both notebook prices answers why the amount charged belongs to the line. Use the project to explain why a product with no order can exist independently and why totalling current catalogue prices would be incorrect.
When would you use LEFT JOIN?
Use it when the left-side records still matter without a match. In the product totals report, that includes Pencil case with no completed sales. Show why a right-side filter in WHERE can remove unmatched results and where your query applies the completed status instead. Make the desired rows clear before describing SQL syntax.
How do you diagnose a suspicious count?
Check what one row means after each join. Compare order IDs and line IDs to find whether the join multiplied rows. Verify several rows against the sample and check whether you need a distinct count or a different grouping. If an interviewer asks which result proves your condition worked, give the expected count of two orders and four lines, and show the query that counts each.
Questions about taking MySQL training in Ambala
Do I need to know PHP or spreadsheets first?
You can design tables and run SQL without PHP. Being able to read rows and columns will help with the first tests. PHP becomes useful when the data needs to be shown through a website. If you already use spreadsheets, compare a spreadsheet calculation with one grouped SQL result. Look at which cells or rows represent details and where the calculated result should appear.
What should I ask when comparing courses?
Ask whether your goal is learning database design on its own, building a PHP website feature, or doing both. Describe a small report you want to build. Confirm the next batch schedule, fee, local software setup and time for guided practice. If you are visiting the New Milap Nagar centre from Ambala Cantt or another nearby area, discuss class timings and visit arrangements before enrolling.