Chapter 3: SQL Editor & Sample Database Setup
Hands-on practice is the fastest and most durable way to master SQL. In this chapter, you will master how to execute, inspect, and debug queries using our safe in-browser SQL playground, explore the complete schema of the "AICodeLab Shop" sample database, and learn how to configure a local database on your own computer.
Zero Setup
In-Browser Sandbox
Powered by WebAssembly SQLite (sql.js). Write and execute queries instantly with 100% data safety.
AICodeLab Shop
Realistic Schema
3 relational tables (Customers, Products, Orders) with 22 seed records priced in Indian Rupees (₹).
Relational Keys
PK & FK Linkages
Primary Keys guarantee row uniqueness, while Foreign Keys link customer orders to product inventory.
Local Setup
Multiple Dialects
Step-by-step guides and ready-to-run DDL scripts for SQLite, PostgreSQL, MySQL, and SQL Server.
3.1 Two Ways to Practice SQL
To become proficient in SQL, regular hands-on execution is essential. As a learner or practicing professional, there are two primary pathways to write and execute queries: using an in-browser playground or installing a database server on your local machine.
Option A: In-Browser Editor (Recommended)
Runs instantly in your web browser with zero installation or configuration. Powered by WebAssembly SQLite (sql.js), every query executes securely inside your device's memory with zero latency. You can never break anything, and a single click restores all original data in 5 milliseconds.
Option B: Local Database Installation
Installs a standalone database engine (such as SQLite, PostgreSQL, MySQL, or SQL Server) directly on your operating system along with a graphical management tool (such as DB Browser, pgAdmin, or DBeaver). Records persist permanently on your computer storage disk.
| Feature Comparison | In-Browser Sandbox (This Site) | Local Database Installation |
|---|---|---|
| Installation Required | None (0 seconds) | Download installer (5 to 15 minutes) |
| Underlying Engine | SQLite compiled to WebAssembly (WASM) | SQLite, PostgreSQL, MySQL, or SQL Server |
| Data Persistence | In-memory for browser session; resets on reload | Saved permanently on local storage disk |
| Risk of Data Loss | Zero risk; instant "Reset DB" button | User manages backups and disk files |
| Internet Connection | Needed once to load the tutorial page | 100% offline once installed |
| Ideal Audience | Students, interview prep, quick learners | App developers, system DBAs, data engineers |
Under the Hood: SQLite in WebAssembly
The live editor on AICodeLab is powered by SQLite 3 compiled into WebAssembly using the open-source library sql.js. When you click Run SQL, the query is executed by the WebAssembly virtual machine directly inside your browser. No queries or private data are ever sent over the network to external servers.
3.2 How to Use the In-Browser Editor
The live SQL playground is designed to give you instant feedback with no barriers. It contains four core functional zones:
Clickable preset buttons that automatically populate the SQL input box with common query patterns (such as selecting all customers, filtering products by price, or listing database tables).
An interactive code textarea equipped with monospace font styling. You can freely type, paste, or modify any SQL statement here.
The Run SQL button executes your SQL code immediately. You can also press Ctrl + Enter (Cmd + Enter on Mac). The Reset DB button restores all sample data in 5 milliseconds.
Displays the returned rows in a clean tabular grid with column names, total row count, and execution speed. If a query contains typos, a descriptive red error banner tells you exactly what went wrong.
3.3 Meet the Sample Database "AICodeLab Shop"
Throughout all 82 chapters of this tutorial, we will use a realistic e-commerce sample database called "AICodeLab Shop". It models an electronics and gadget store with inventory pricing tracked in Indian Rupees (₹).
The database contains three relational tables: Customers (who buys), Products (what is in stock), and Orders (which customer bought which product on what date).
Table 1: Customers (8 Rows)
Stores customer contact profiles. CustomerID is the Primary Key.
| CustomerID [PK] | CustomerName | City | Country |
|---|---|---|---|
1 | Aarav Sharma | Mumbai | India |
2 | Priya Nair | Kochi | India |
3 | Liam Carter | Toronto | Canada |
4 | Sofia Rossi | Milan | Italy |
5 | Rohan Mehta | Pune | India |
6 | Emma Weber | Berlin | Germany |
7 | Noah Smith | Austin | USA |
8 | Mei Tanaka | Osaka | Japan |
Table 2: Products (6 Rows)
Stores catalog products and current inventory prices in Indian Rupees (₹). ProductID is the Primary Key.
| ProductID [PK] | ProductName | Category | Price (₹) |
|---|---|---|---|
1 | Wireless Mouse | Accessories | ₹799 |
2 | Mechanical Keyboard | Accessories | ₹3,499 |
3 | USB-C Hub | Accessories | ₹1,999 |
4 | 27-inch Monitor | Displays | ₹15,999 |
5 | Laptop Stand | Accessories | ₹1,299 |
6 | Webcam HD | Cameras | ₹2,799 |
Table 3: Orders (8 Rows)
Stores purchase transactions. OrderID is the Primary Key. CustomerID and ProductID are Foreign Keys.
| OrderID [PK] | CustomerID [FK] | ProductID [FK] | Quantity | OrderDate |
|---|---|---|---|---|
101 | 1 (Aarav) | 2 (Keyboard) | 1 | 2026-01-05 |
102 | 2 (Priya) | 1 (Mouse) | 2 | 2026-01-09 |
103 | 3 (Liam) | 4 (Monitor) | 1 | 2026-01-12 |
104 | 5 (Rohan) | 3 (Hub) | 1 | 2026-02-02 |
105 | 1 (Aarav) | 5 (Stand) | 3 | 2026-02-14 |
106 | 6 (Emma) | 6 (Webcam) | 1 | 2026-03-01 |
107 | 4 (Sofia) | 2 (Keyboard) | 1 | 2026-03-18 |
108 | 2 (Priya) | 4 (Monitor) | 1 | 2026-04-03 |
Data Dictionary: Columns, Types & Meanings
| Table | Column Name | Data Type | Key Type | Description & Meaning |
|---|---|---|---|---|
| Customers | CustomerID | INTEGER | Primary Key | Unique numerical identifier assigned to each registered customer. |
| Customers | CustomerName | TEXT / VARCHAR | Standard | Full legal name of the customer. |
| Customers | City | TEXT / VARCHAR | Standard | City of residence for shipping calculations. |
| Customers | Country | TEXT / VARCHAR | Standard | Nation of residence (e.g. India, Germany, Canada). |
| Products | ProductID | INTEGER | Primary Key | Unique SKU number for each catalog item. |
| Products | ProductName | TEXT / VARCHAR | Standard | Commercial title of the gadget or hardware accessory. |
| Products | Category | TEXT / VARCHAR | Standard | Product grouping (Accessories, Displays, Cameras). |
| Products | Price | INTEGER / NUMERIC | Standard | Retail unit price denominated in Indian Rupees (₹). |
| Orders | OrderID | INTEGER | Primary Key | Unique sequential invoice receipt number. |
| Orders | CustomerID | INTEGER | Foreign Key | References Customers(CustomerID) to indicate who purchased the item. |
| Orders | ProductID | INTEGER | Foreign Key | References Products(ProductID) to indicate which item was ordered. |
| Orders | Quantity | INTEGER | Standard | Count of units bought in this order. |
| Orders | OrderDate | TEXT / DATE | Standard | Calendar date of the purchase in ISO-8601 format (YYYY-MM-DD). |
What is a Primary Key?
A Primary Key (PK) is a column (or combination of columns) that guarantees each row in a table is 100% unique.
Two rows can never have the same Primary Key value, and a Primary Key can never be NULL (empty). Think of it like your government Aadhaar number, passport ID, or university roll number.
What is a Foreign Key?
A Foreign Key (FK) is a column in one table that links to the Primary Key of another table. In the Orders table, CustomerID tells the database which customer bought the item. This avoids repeating the customer name and address on every single order row, eliminating redundant duplication!
3.4 Step-by-Step: Setting Up Your Practice Database
Whether working locally or connecting to an enterprise cloud warehouse, setting up a database environment always follows five sequential steps. Use the controls below to step through the setup lifecycle:
3.5 Install a Database on Your Computer
If you want to practice outside this browser tutorial, you can install any of the four major relational database systems. Select a database tab below to view official download links and step-by-step installation instructions:
Best For: Complete Beginners & Lightweight Applications
SQLite requires zero installation of background services or server daemons. The entire database is stored in one clean file on your desktop.
Official Downloads:
- Official SQLite Project: sqlite.org
- Official Recommended GUI Client: DB Browser for SQLite (sqlitebrowser.org)
Numbered Setup Steps:
- Visit the official DB Browser for SQLite website and download the standard installer matching your operating system (Windows, macOS, or Linux).
- Run the downloaded installer and follow the standard setup prompts (leave default settings checked).
- Launch DB Browser for SQLite from your desktop or start menu.
- Click the "New Database" button in the top left corner, name your database file
aicodelab_shop.db, and save it in a folder of your choice. - Click on the "Execute SQL" tab at the top of the interface.
- Copy the SQLite setup script from Section 3.6 below, paste it into the editor pane, and click the blue Play ▶ icon to create all tables and insert all 22 sample records!
Note on Microsoft Access:
Microsoft Access is a desktop relational database tool bundled with Microsoft Office 365 (using .accdb files). While convenient for quick internal office forms and desktop reports, Access uses a non-standard SQL dialect (Jet/ACE SQL) and is not designed for modern web applications, distributed servers, or high-volume data science pipelines. For your career in data analytics and software engineering, we recommend focusing on SQLite, PostgreSQL, or MySQL.
3.6 Sample Data Scripts (By Dialect)
Here are the complete SQL scripts to recreate the Customers, Products, and Orders tables and load all 22 sample records. Expand your target dialect below and click Copy Script to run it in your local database tool:
1. SQLite Setup Script (Tested in Live Browser Editor) Default
-- SQLite Sample Database Script: AICodeLab Shop DROP TABLE IF EXISTS Orders; DROP TABLE IF EXISTS Products; DROP TABLE IF EXISTS Customers; CREATE TABLE Customers ( CustomerID INTEGER PRIMARY KEY, CustomerName TEXT NOT NULL, City TEXT NOT NULL, Country TEXT NOT NULL ); INSERT INTO Customers (CustomerID, CustomerName, City, Country) VALUES (1, 'Aarav Sharma', 'Mumbai', 'India'), (2, 'Priya Nair', 'Kochi', 'India'), (3, 'Liam Carter', 'Toronto', 'Canada'), (4, 'Sofia Rossi', 'Milan', 'Italy'), (5, 'Rohan Mehta', 'Pune', 'India'), (6, 'Emma Weber', 'Berlin', 'Germany'), (7, 'Noah Smith', 'Austin', 'USA'), (8, 'Mei Tanaka', 'Osaka', 'Japan'); CREATE TABLE Products ( ProductID INTEGER PRIMARY KEY, ProductName TEXT NOT NULL, Category TEXT NOT NULL, Price INTEGER NOT NULL ); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (1, 'Wireless Mouse', 'Accessories', 799), (2, 'Mechanical Keyboard', 'Accessories', 3499), (3, 'USB-C Hub', 'Accessories', 1999), (4, '27-inch Monitor', 'Displays', 15999), (5, 'Laptop Stand', 'Accessories', 1299), (6, 'Webcam HD', 'Cameras', 2799); CREATE TABLE Orders ( OrderID INTEGER PRIMARY KEY, CustomerID INTEGER, ProductID INTEGER, Quantity INTEGER NOT NULL, OrderDate TEXT NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID), FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ); INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (101, 1, 2, 1, '2026-01-05'), (102, 2, 1, 2, '2026-01-09'), (103, 3, 4, 1, '2026-01-12'), (104, 5, 3, 1, '2026-02-02'), (105, 1, 5, 3, '2026-02-14'), (106, 6, 6, 1, '2026-03-01'), (107, 4, 2, 1, '2026-03-18'), (108, 2, 4, 1, '2026-04-03');
2. PostgreSQL Setup Script Postgres
VARCHAR, NUMERIC, and DATE).
-- PostgreSQL Sample Database Script: AICodeLab Shop DROP TABLE IF EXISTS Orders CASCADE; DROP TABLE IF EXISTS Products CASCADE; DROP TABLE IF EXISTS Customers CASCADE; CREATE TABLE Customers ( CustomerID SERIAL PRIMARY KEY, CustomerName VARCHAR(100) NOT NULL, City VARCHAR(100) NOT NULL, Country VARCHAR(100) NOT NULL ); INSERT INTO Customers (CustomerID, CustomerName, City, Country) VALUES (1, 'Aarav Sharma', 'Mumbai', 'India'), (2, 'Priya Nair', 'Kochi', 'India'), (3, 'Liam Carter', 'Toronto', 'Canada'), (4, 'Sofia Rossi', 'Milan', 'Italy'), (5, 'Rohan Mehta', 'Pune', 'India'), (6, 'Emma Weber', 'Berlin', 'Germany'), (7, 'Noah Smith', 'Austin', 'USA'), (8, 'Mei Tanaka', 'Osaka', 'Japan'); CREATE TABLE Products ( ProductID SERIAL PRIMARY KEY, ProductName VARCHAR(150) NOT NULL, Category VARCHAR(100) NOT NULL, Price NUMERIC(10, 2) NOT NULL ); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (1, 'Wireless Mouse', 'Accessories', 799), (2, 'Mechanical Keyboard', 'Accessories', 3499), (3, 'USB-C Hub', 'Accessories', 1999), (4, '27-inch Monitor', 'Displays', 15999), (5, 'Laptop Stand', 'Accessories', 1299), (6, 'Webcam HD', 'Cameras', 2799); CREATE TABLE Orders ( OrderID SERIAL PRIMARY KEY, CustomerID INT REFERENCES Customers(CustomerID) ON DELETE CASCADE, ProductID INT REFERENCES Products(ProductID) ON DELETE CASCADE, Quantity INT NOT NULL, OrderDate DATE NOT NULL ); INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (101, 1, 2, 1, '2026-01-05'), (102, 2, 1, 2, '2026-01-09'), (103, 3, 4, 1, '2026-01-12'), (104, 5, 3, 1, '2026-02-02'), (105, 1, 5, 3, '2026-02-14'), (106, 6, 6, 1, '2026-03-01'), (107, 4, 2, 1, '2026-03-18'), (108, 2, 4, 1, '2026-04-03');
3. MySQL Setup Script MySQL
AUTO_INCREMENT and ENGINE=InnoDB).
-- MySQL Sample Database Script: AICodeLab Shop DROP TABLE IF EXISTS Orders; DROP TABLE IF EXISTS Products; DROP TABLE IF EXISTS Customers; CREATE TABLE Customers ( CustomerID INT AUTO_INCREMENT PRIMARY KEY, CustomerName VARCHAR(100) NOT NULL, City VARCHAR(100) NOT NULL, Country VARCHAR(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO Customers (CustomerID, CustomerName, City, Country) VALUES (1, 'Aarav Sharma', 'Mumbai', 'India'), (2, 'Priya Nair', 'Kochi', 'India'), (3, 'Liam Carter', 'Toronto', 'Canada'), (4, 'Sofia Rossi', 'Milan', 'Italy'), (5, 'Rohan Mehta', 'Pune', 'India'), (6, 'Emma Weber', 'Berlin', 'Germany'), (7, 'Noah Smith', 'Austin', 'USA'), (8, 'Mei Tanaka', 'Osaka', 'Japan'); CREATE TABLE Products ( ProductID INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(150) NOT NULL, Category VARCHAR(100) NOT NULL, Price DECIMAL(10, 2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (1, 'Wireless Mouse', 'Accessories', 799), (2, 'Mechanical Keyboard', 'Accessories', 3499), (3, 'USB-C Hub', 'Accessories', 1999), (4, '27-inch Monitor', 'Displays', 15999), (5, 'Laptop Stand', 'Accessories', 1299), (6, 'Webcam HD', 'Cameras', 2799); CREATE TABLE Orders ( OrderID INT AUTO_INCREMENT PRIMARY KEY, CustomerID INT, ProductID INT, Quantity INT NOT NULL, OrderDate DATE NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE, FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (101, 1, 2, 1, '2026-01-05'), (102, 2, 1, 2, '2026-01-09'), (103, 3, 4, 1, '2026-01-12'), (104, 5, 3, 1, '2026-02-02'), (105, 1, 5, 3, '2026-02-14'), (106, 6, 6, 1, '2026-03-01'), (107, 4, 2, 1, '2026-03-18'), (108, 2, 4, 1, '2026-04-03');
4. Microsoft SQL Server (T-SQL) Setup Script T-SQL
IDENTITY(1,1) and NVARCHAR).
-- SQL Server (T-SQL) Sample Database Script: AICodeLab Shop
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers (
CustomerID INT NOT NULL PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
City NVARCHAR(100) NOT NULL,
Country NVARCHAR(100) NOT NULL
);
INSERT INTO dbo.Customers (CustomerID, CustomerName, City, Country) VALUES
(1, N'Aarav Sharma', N'Mumbai', N'India'),
(2, N'Priya Nair', N'Kochi', N'India'),
(3, N'Liam Carter', N'Toronto', N'Canada'),
(4, N'Sofia Rossi', N'Milan', N'Italy'),
(5, N'Rohan Mehta', N'Pune', N'India'),
(6, N'Emma Weber', N'Berlin', N'Germany'),
(7, N'Noah Smith', N'Austin', N'USA'),
(8, N'Mei Tanaka', N'Osaka', N'Japan');
CREATE TABLE dbo.Products (
ProductID INT NOT NULL PRIMARY KEY,
ProductName NVARCHAR(150) NOT NULL,
Category NVARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL
);
INSERT INTO dbo.Products (ProductID, ProductName, Category, Price) VALUES
(1, N'Wireless Mouse', N'Accessories', 799),
(2, N'Mechanical Keyboard', N'Accessories', 3499),
(3, N'USB-C Hub', N'Accessories', 1999),
(4, N'27-inch Monitor', N'Displays', 15999),
(5, N'Laptop Stand', N'Accessories', 1299),
(6, N'Webcam HD', N'Cameras', 2799);
CREATE TABLE dbo.Orders (
OrderID INT NOT NULL PRIMARY KEY,
CustomerID INT FOREIGN KEY REFERENCES dbo.Customers(CustomerID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT NOT NULL,
OrderDate DATE NOT NULL
);
INSERT INTO dbo.Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES
(101, 1, 2, 1, '2026-01-05'),
(102, 2, 1, 2, '2026-01-09'),
(103, 3, 4, 1, '2026-01-12'),
(104, 5, 3, 1, '2026-02-02'),
(105, 1, 5, 3, '2026-02-14'),
(106, 6, 6, 1, '2026-03-01'),
(107, 4, 2, 1, '2026-03-18'),
(108, 2, 4, 1, '2026-04-03');
Dialect Differences Explained
Notice that while all four scripts create the exact same three tables, minor dialect differences exist: SQLite uses INTEGER PRIMARY KEY, PostgreSQL uses SERIAL PRIMARY KEY, MySQL uses AUTO_INCREMENT, and SQL Server uses IDENTITY(1,1). The fundamental table structure and SQL querying logic remain universal!
3.7 Try It Yourself: Interactive SQL Editor
Put what you have learned into practice right now! Click any quick query chip below to populate the editor, or write custom queries using SELECT, FROM, WHERE, ORDER BY, LIMIT, and COUNT():
3.8 Common Problems and Fixes
Encountering errors is a natural part of working with databases. Here are six frequent beginner issues and their straightforward solutions:
If the engine shows "Offline Mode", check your internet connection or disable ad-blockers that may block scripts from cdnjs.cloudflare.com.
A syntax error means a keyword was misspelled (e.g. SEELCT instead of SELECT) or written out of order. Verify your spelling against the query template.
Every string literal must have matching opening and closing single quotes (e.g. 'India'). Separate multi-command statements with a semicolon (;).
Errors like no such table: Customer occur when singular names are used instead of plural. Always check the exact schema: Customers, Products, and Orders.
In PostgreSQL or MySQL, verify the database service daemon is running in your OS Task Manager / Services and listening on port 5432 or 3306.
If you lose your local database password, use SQLite (which has no passwords) or run your database engine's official password recovery utility.
3.9 Hands-On Practice Exercises
Test your understanding by writing queries in the sandbox above. Click Insert into Editor to load the query, or toggle Show Solution if you need a hint:
Exercise 1: Sort Customers Alphabetically
Write an SQL query to display CustomerName, City, and Country from the Customers table, sorted in ascending alphabetical order by CustomerName.
Exercise 2: Find Multi-Quantity Orders
Write an SQL query to retrieve OrderID, CustomerID, and Quantity from the Orders table for all orders where Quantity is greater than 1.
Exercise 3: Count Accessories Products
Write an SQL query to count how many items in the Products table belong to the 'Accessories' category.
3.10 Chapter 3 Quiz: Test Your Understanding
Test your knowledge of SQL editor mechanics and relational database keys. Select the best answer for each question:
3.11 Key Takeaways
- Zero Friction Practice: The in-browser WebAssembly editor gives you a complete, risk-free SQL playground with instant execution and reset capability.
- Relational Integrity: The sample AICodeLab Shop schema models realistic e-commerce relations through
Customers,Products, andOrders. - Primary vs Foreign Keys: A Primary Key uniquely identifies rows in a table; a Foreign Key points to a primary key in another table to build multi-table relationships.
- Client vs Server: SQLite operates as a serverless local file engine, while PostgreSQL, MySQL, and SQL Server run as multi-user client-server background daemons.
- Schema Discovery: Every database provides metadata queries—in SQLite,
SELECT name FROM sqlite_master WHERE type = 'table';inspects all available tables.
3.12 Frequently Asked Questions (FAQ)
Do I need to install any database software to complete this tutorial course?
Which database engine should I install first if I want to practice locally?
What is SQLite and why is it so widely used for practice and production apps?
What is a database client tool (GUI)?
Can I break or corrupt the sample database by writing wrong queries in the editor?
Chapter 4: SQL Syntax, Semicolons & Comments
Master SQL statement clauses, execution order, keywords, case rules, single-line (--), and multi-line comments.