QAQA Jobs India
Go Premium
Lesson 1 of 10 · Hands-on Lab · 60 min · Free preview

Databases for Testers: MySQL 8, DBeaver & the Practice Shop DB

Understand how relational databases back every application you test, install MySQL 8 with DBeaver (or use DB Fiddle/SQLite), and load the e-commerce practice schema used throughout the course.

Why this matters on the job

Every screen you test is a window onto a database. When a tester at a product company in Bengaluru or a service company in Pune says "the order was placed", the developer's first question is: what does the orders table say? A button can show "Payment successful" while the payments row says FAILED. A refund can appear on the UI but never reach the ledger. Testers who can open a SQL client, find the record and prove the mismatch raise defects that get fixed the same day.

That is why almost every QA interview in India — manual, automation or SDET — has a SQL round. Typical asks: "write a query for the second highest salary", "find duplicate records", "how would you verify data after a migration?". In this course you build one realistic e-commerce database and use it in every lesson, so each query you learn is something you would actually run on a project.

Concepts

A relational database stores data in tables (rows and columns). Tables are linked through keys:

TermMeaningExample in our shop DB
Primary key (PK)Uniquely identifies a row; never NULLorders.order_id
Foreign key (FK)Column that must match a PK in another tableorders.customer_id → customers.customer_id
ConstraintRule the DB enforcesUNIQUE(email), CHECK (price > 0)
SchemaThe set of tables, columns and rulesshopdb

SQL commands fall into five families. Testers mostly use DQL and DML, but must recognise all of them:

FamilyCommandsTypical QA use
DQLSELECTVerify data, find test data
DMLINSERT, UPDATE, DELETECreate/clean test data, negative tests
DDLCREATE, ALTER, DROP, TRUNCATESchema checks, practice setups
TCLSTART TRANSACTION, COMMIT, ROLLBACK, SAVEPOINTSafe experiments, rollback testing
DCLGRANT, REVOKEAccess/permission testing

Our practice schema models an online store: customers place orders; each order has one or more order_items pointing to products; each order has zero or more payments. A small employees table is added for classic interview questions. The data deliberately contains nine data defects (wrong totals, missing refunds, duplicates…). Do not fix them — you will hunt them down in later lessons, exactly like on a real project.

Hands-on Lab: Install MySQL 8 + DBeaver and load the practice shop DB

  1. Install MySQL 8. Pick one option:
    • Docker (recommended, same on Windows/Mac/Linux): 
      docker run --name qa-mysql -e MYSQL_ROOT_PASSWORD=Root@123 -p 3306:3306 -d mysql:8.4
docker ps            # STATUS should show 'Up'
docker exec -it qa-mysql mysql -uroot -p   # type Root@123, you get the mysql> prompt
    • Native: download MySQL Community Server 8.4 LTS from dev.mysql.com (Windows: MySQL Installer), or on macOS brew install mysql@8.4.
    • No install possible (office laptop)? Open DB Fiddle, choose MySQL v8, paste the schema + data script in the left pane and your queries in the right pane.
  2. Install DBeaver Community from dbeaver.io. Create a connection: Database → New Database Connection → MySQL → Host localhost, Port 3306, User root, Password Root@123. On the Driver properties tab set allowPublicKeyRetrieval=true and useSSL=false (local practice only). Click Test Connection → "Connected".
  3. Create the schema. Save the two blocks below as shopdb_setup.sql, open it in DBeaver's SQL editor and run it as a script (Alt+X), or from a terminal: docker exec -i qa-mysql mysql -uroot -pRoot@123 < shopdb_setup.sql. 
    -- QA Jobs India practice database: shopdb (MySQL 8.x)
DROP DATABASE IF EXISTS shopdb;
CREATE DATABASE shopdb;
USE shopdb;

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name  VARCHAR(50)  NOT NULL,
  last_name   VARCHAR(50),
  email       VARCHAR(100) NOT NULL UNIQUE,
  phone       VARCHAR(15),
  city        VARCHAR(50),
  state       VARCHAR(50),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  referred_by INT NULL,
  CONSTRAINT fk_cust_referrer FOREIGN KEY (referred_by) REFERENCES customers(customer_id)
);

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  category   VARCHAR(50)  NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  stock_qty  INT NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT chk_price CHECK (price > 0),
  CONSTRAINT chk_stock CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
  order_id     INT AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT NOT NULL,
  order_date   DATETIME NOT NULL,
  status       ENUM('PLACED','SHIPPED','DELIVERED','CANCELLED','RETURNED') NOT NULL DEFAULT 'PLACED',
  total_amount DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id      INT NOT NULL,
  product_id    INT NOT NULL,
  quantity      INT NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_qty CHECK (quantity > 0),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders(order_id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE payments (
  payment_id   INT AUTO_INCREMENT PRIMARY KEY,
  order_id     INT NOT NULL,
  payment_date DATETIME NOT NULL,
  method       ENUM('UPI','CARD','NETBANKING','COD','WALLET') NOT NULL,
  amount       DECIMAL(10,2) NOT NULL,
  status       ENUM('SUCCESS','FAILED','PENDING','REFUNDED') NOT NULL,
  CONSTRAINT fk_pay_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- extra table used only for interview-style queries (lesson 5)
CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  emp_name   VARCHAR(60) NOT NULL,
  department VARCHAR(30) NOT NULL,
  salary     DECIMAL(10,2) NOT NULL,
  manager_id INT NULL,
  hire_date  DATE NOT NULL
);
  4. Load the test data (append to the same file): 
    INSERT INTO customers (customer_id, first_name, last_name, email, phone, city, state, created_at, referred_by) VALUES
(1,'Aarav','Sharma','aarav.sharma@example.com','9876500001','Pune','Maharashtra','2025-01-05 10:15:00',NULL),
(2,'Priya','Iyer','priya.iyer@example.com','9876500002','Chennai','Tamil Nadu','2025-01-12 09:30:00',1),
(3,'Rohan','Kulkarni','rohan.kulkarni@example.com','9876500003','Nashik','Maharashtra','2025-02-01 18:05:00',1),
(4,'Sneha','Reddy','sneha.reddy@example.com','9876500004','Hyderabad','Telangana','2025-02-14 11:45:00',NULL),
(5,'Vikram','Singh','vikram.singh@example.com','9876500005','Delhi','Delhi','2025-03-03 14:20:00',4),
(6,'Ananya','Gupta','ananya.gupta@example.com',NULL,NULL,NULL,'2025-03-20 16:10:00',NULL),
(7,'Karan','Mehta','karan.mehta@example.com','9876500007','Mumbai','Maharashtra','2025-04-02 12:00:00',2),
(8,'Karan','Mehta','karan.m@example.com','9876500007','Mumbai','Maharashtra','2025-04-02 12:07:00',NULL),
(9,'Fatima','Khan','fatima.khan@example.com','9876500009','Bengaluru','Karnataka','2025-05-10 08:40:00',NULL),
(10,'Arjun','Nair','arjun.nair@example.com','+91 9876500010','Kochi','Kerala','2025-06-01 19:25:00',9);

INSERT INTO products (product_id, name, category, price, stock_qty, is_active) VALUES
(1,'Wireless Mouse','Electronics',799.00,120,1),
(2,'Mechanical Keyboard','Electronics',3499.00,40,1),
(3,'USB-C Hub','Electronics',1999.00,0,1),
(4,'Cotton T-Shirt','Fashion',499.00,300,1),
(5,'Running Shoes','Fashion',2999.00,60,1),
(6,'Steel Water Bottle','Home',349.00,200,1),
(7,'Non-stick Pan','Home',1299.00,35,1),
(8,'Software Testing Book','Books',650.00,80,1),
(9,'Desk Lamp','Home',899.00,25,0),
(10,'Bluetooth Speaker','Electronics',2499.00,15,1);

INSERT INTO orders (order_id, customer_id, order_date, status, total_amount) VALUES
(101,1,'2025-07-01 10:00:00','DELIVERED',4298.00),
(102,2,'2025-07-03 13:20:00','DELIVERED',1347.00),
(103,3,'2025-07-05 09:10:00','SHIPPED',2999.00),
(104,1,'2025-07-10 20:45:00','DELIVERED',1348.00),
(105,4,'2025-07-12 11:00:00','DELIVERED',3398.00),
(106,5,'2025-07-15 15:30:00','CANCELLED',1299.00),
(107,7,'2025-07-31 18:45:00','PLACED',1497.00),
(108,5,'2025-08-01 10:05:00','DELIVERED',3499.00),
(109,8,'2025-08-05 17:50:00','CANCELLED',2999.00),
(110,10,'2025-08-10 12:30:00','PLACED',899.00),
(111,2,'2025-08-15 08:15:00','DELIVERED',2649.00),
(112,4,'2025-09-01 21:00:00','SHIPPED',2897.00),
(113,6,'2025-09-02 10:40:00','DELIVERED',349.00);

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price) VALUES
(1,101,1,1,799.00),(2,101,2,1,3499.00),
(3,102,4,2,499.00),(4,102,6,1,349.00),
(5,103,5,1,2999.00),
(6,104,8,1,650.00),(7,104,6,2,349.00),
(8,105,10,1,2499.00),(9,105,1,1,799.00),
(10,106,7,1,1299.00),
(11,107,4,3,499.00),
(12,108,2,1,3499.00),
(13,109,5,1,2999.00),
(14,111,3,1,1999.00),(15,111,8,1,650.00),
(16,112,1,2,799.00),(17,112,7,1,1299.00),
(18,113,6,1,349.00);

INSERT INTO payments (payment_id, order_id, payment_date, method, amount, status) VALUES
(1,101,'2025-07-01 10:02:00','UPI',4298.00,'SUCCESS'),
(2,102,'2025-07-03 13:21:00','CARD',1347.00,'SUCCESS'),
(3,103,'2025-07-05 09:12:00','NETBANKING',2999.00,'SUCCESS'),
(4,104,'2025-07-10 20:46:00','UPI',1300.00,'SUCCESS'),
(5,105,'2025-07-12 11:01:00','CARD',3398.00,'SUCCESS'),
(6,106,'2025-07-15 15:31:00','UPI',1299.00,'SUCCESS'),
(7,106,'2025-07-16 10:00:00','UPI',1299.00,'REFUNDED'),
(8,107,'2025-07-31 18:45:00','COD',1497.00,'PENDING'),
(9,108,'2025-08-01 10:06:00','CARD',3499.00,'FAILED'),
(10,109,'2025-08-05 17:51:00','UPI',2999.00,'SUCCESS'),
(11,111,'2025-08-15 08:16:00','WALLET',2649.00,'SUCCESS'),
(12,112,'2025-09-01 21:02:00','CARD',2897.00,'SUCCESS'),
(13,113,'2025-09-02 10:38:00','WALLET',349.00,'SUCCESS');

INSERT INTO employees (emp_id, emp_name, department, salary, manager_id, hire_date) VALUES
(1,'Meera Joshi','QA',150000.00,NULL,'2018-04-01'),
(2,'Rahul Verma','QA',90000.00,1,'2020-06-15'),
(3,'Divya Menon','QA',90000.00,1,'2021-01-10'),
(4,'Sanjay Rao','QA',70000.00,2,'2022-03-01'),
(5,'Neha Kapoor','Dev',120000.00,6,'2019-05-20'),
(6,'Amit Desai','Dev',160000.00,NULL,'2017-08-20'),
(7,'Pooja Shah','Dev',120000.00,6,'2019-11-11'),
(8,'Imran Shaikh','Dev',95000.00,5,'2023-02-01'),
(9,'Kavya Pillai','Support',55000.00,NULL,'2022-07-01'),
(10,'Nikhil Bhat','Support',58000.00,9,'2024-01-15');
  5. Verify the load — always reconcile row counts after loading data: 
    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
UNION ALL SELECT 'payments',    COUNT(*) FROM payments
UNION ALL SELECT 'employees',   COUNT(*) FROM employees;

    Expected: customers 10, products 10, orders 13, order_items 18, payments 13, employees 10.

  6. Explore the structure like a tester reading a new project's DB: 
    SHOW TABLES;
DESCRIBE orders;
SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'shopdb' AND referenced_table_name IS NOT NULL;

    Expected: 6 tables; DESCRIBE shows status as an enum with default PLACED; the last query returns 5 foreign keys.

  7. Create a least-privilege test user (real projects rarely give testers root): 
    CREATE USER 'qa_user'@'%' IDENTIFIED BY 'Qa@12345';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'qa_user'@'%';
SHOW GRANTS FOR 'qa_user'@'%';

    Reconnect in DBeaver as qa_user and confirm DROP TABLE payments; fails with ERROR 1142 … command denied. That failed DROP is itself a passing security test.

SQLite alternative: on SQLite Online or the sqlite3 CLI the same script works after three edits: delete the DROP DATABASE, CREATE DATABASE and USE lines, replace every ENUM(...) with TEXT, and change INT AUTO_INCREMENT PRIMARY KEY to INTEGER PRIMARY KEY. Run PRAGMA foreign_keys = ON; first. Lessons 2–5 then work unchanged except for MySQL-only functions such as DATE_FORMAT and REGEXP (use strftime and GLOB in SQLite).

Common mistakes

  • “Public Key Retrieval is not allowed” in DBeaver — MySQL 8.4 uses caching_sha2_password; set allowPublicKeyRetrieval=true in driver properties.
  • Running only the selected statement (Ctrl+Enter) instead of the whole script (Alt+X), then wondering why tables are missing.
  • Forgetting USE shopdb; — you get ERROR 1046: No database selected.
  • Port 3306 already used by another local MySQL/XAMPP — map Docker to another port: -p 3307:3306.
  • Practising on a shared QA or (worse) production database. Always use your own local copy for experiments.

Real-world assignment

Your lead says: "We start testing the checkout module on Monday. Get DB access ready and document the order tables." Deliver a one-page note containing: (1) connection details (host, port, schema, user — never the password), (2) a text ER description — every table with PK, FKs and important constraints, (3) row counts per table as of today, and (4) three questions you would ask the developer, e.g. "Is total_amount stored or calculated?", "Can an order exist without items?", "Which status transitions are allowed?". Those questions are where real DB defects start.

Key takeaways

  • Testers use SQL to verify what the UI/API claims, to find test data, and to prove defects with evidence.
  • Know PK, FK, UNIQUE, NOT NULL, CHECK and DEFAULT — most data defects violate one of these rules (or a rule nobody enforced).
  • MySQL 8.4 LTS in Docker + DBeaver is a free, interview-ready setup; DB Fiddle or SQLite work when you cannot install anything.
  • Always reconcile row counts after loading data — the first and simplest data-validation check.
  • Use a least-privilege test user; never experiment on shared or production databases.

🧪 Practical checklist

Do each task yourself and tick it off. All tasks are required to complete this lesson.

🎤 Interview questions

Why should a tester know SQL?

To verify that what the UI or API shows is actually stored correctly, to prepare and clean test data, and to provide hard evidence (record IDs, values) in defect reports.

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row in its own table and cannot be NULL; a foreign key is a column whose value must exist as a primary key in another (or the same) table, enforcing referential integrity.

What is the difference between DDL and DML?

DDL (CREATE, ALTER, DROP, TRUNCATE) defines or changes structure; DML (INSERT, UPDATE, DELETE) changes the data inside tables. In MySQL, DDL causes an implicit commit.

What access do testers usually get on a QA database?

Normally a dedicated user with SELECT and limited DML on the test schema — never DDL on shared environments and never write access to production.

📝 Quiz, progress tracking & certificate

You're reading a free preview. Premium members tick off labs, take the quiz, unlock all 10 lessons and earn a verifiable certificate.

💎 Unlock with Premium Premium login
✓ You're subscribed! Job alerts arrive daily at 9 AM.
Scroll to Top