Easy. Choose the imported database. Target: DATABASE() returns portfolio_retail_lab.
USE portfolio_retail_lab;
SELECT DATABASE();
Medium. Inspect the customers table's column definitions. Target: customer_id, customer_name, city, email. customer_id is the primary key.
DESCRIBE customers;
Hard. Read the product list without changing it. Target: 6 rows; id 1 Notebook 100.00 and id 6 Planner 300.00.
SELECT * FROM products ORDER BY product_id;
Professional. Confirm that the import is complete before practising. Target: 6 customers, 6 products, 8 orders, 14 items. COUNT is previewed here only as an import check and taught later.
SELECT COUNT(*) AS customers FROM customers;
SELECT COUNT(*) AS products FROM products;
SELECT COUNT(*) AS orders FROM orders;
SELECT COUNT(*) AS items FROM order_items;
Easy. List every customer with all columns. Target: 6 rows; Charu (id 3) has email NULL
SELECT * FROM customers ORDER BY customer_id;
Easy. List every order with all columns. Target: 8 rows; first (101, 1, 2026-01-05, completed), last (108, 4, 2026-03-11, completed)
SELECT * FROM orders ORDER BY order_id;
Easy. List every order item. Target: 14 rows; first (101, 1, 2, 100.00), last (108, 4, 2, 250.00)
SELECT * FROM order_items ORDER BY order_id, product_id;
Easy. Inspect the orders table structure. Target: 4 columns: order_id INT PRI, customer_id INT MUL, order_date DATE, status VARCHAR(20)
DESCRIBE orders;
Medium. Inspect the order_items structure and name its key columns. Target: order_id and product_id are both PRI
DESCRIBE order_items;
Medium. Show the full CREATE statement for products. Target: Shows PRIMARY KEY and the CHECK (price >= 0)
SHOW CREATE TABLE products;
Medium. List the indexes on orders. Target: PRIMARY on order_id plus an index on customer_id (from the foreign key)
SHOW INDEX FROM orders;
Medium. Show only the customer_name and city columns of customers. Target: 6 rows: (Asha, Pune); (Bilal, Mumbai); (Charu, Pune); (Dev, Delhi); (Esha, Mumbai); (Farah, Jaipur)
SELECT customer_name, city FROM customers ORDER BY customer_id;
Medium. Show the first 3 products only. Target: 3 rows: Notebook 100.00, Pen 20.00, Desk lamp 800.00
SELECT * FROM products ORDER BY product_id LIMIT 3;
Hard. Count the columns of every table using information_schema. Target: customers 4, order_items 4, orders 4, products 4
SELECT table_name, COUNT(*) AS columns_count FROM information_schema.columns WHERE table_schema = 'portfolio_retail_lab' GROUP BY table_name ORDER BY table_name;
Hard. List all foreign keys in the schema. Target: 3 rows: order_items.order_id→orders, order_items.product_id→products, orders.customer_id→customers
SELECT table_name, column_name, referenced_table_name FROM information_schema.key_column_usage WHERE table_schema='portfolio_retail_lab' AND referenced_table_name IS NOT NULL ORDER BY table_name, column_name;
Hard. Read the products table from another database without USE, by using the full name. Target: 6 rows: Notebook, Pen, Desk lamp, Mug, Keyboard, Planner
SELECT product_name FROM portfolio_retail_lab.products ORDER BY product_id;
Hard. Show order_items rows for order 101 only. Target: 2 rows: (101, 1, 2, 100.00); (101, 2, 5, 20.00)
SELECT * FROM order_items WHERE order_id = 101;
Professional. Write an import-check script that shows each table name and row count in one result. Target: 4 rows: customers 6, products 6, orders 8, order_items 14
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers UNION ALL SELECT 'products', COUNT(*) FROM products UNION ALL SELECT 'orders', COUNT(*) FROM orders UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;
Professional. Write a data dictionary note: one line per table saying what one row means (its grain). Target: 4 lines, grain stated for each table
/* customers: one row per customer */ /* products: one row per product */ /* orders: one row per order */ /* order_items: one row per product within an order */