Sign in

SQL Training uses your LeetCode Foundations account.

Back

SQL Training

← DSA Foundations

1. Why do we need a database?

Imagine a small online coffee store. Amy places two orders, Ben places one. The store needs to keep customer details and orders organized.

🛒 Coffee orders
Amy · $75
Amy · $50
Ben · $120
→
📄 CSV / JSON files
Save the records
or
🗄️ Database
Search, connect and update

Files store information. CSV represents rows and columns; JSON represents structured fields. Both can be saved as files. A database manages information so apps can query, connect and update records reliably.

customers
1 · Amy
2 · Ben
1 → many
orders
101 · customer 1 · $75
102 · customer 1 · $50
103 · customer 2 · $120

Each customer is stored once. Orders link to that customer by ID, so changing Amy's information does not require editing every order.

What is SQL, and why use it?

SQL (Structured Query Language) is how we ask a relational database to store, find, change, and connect information. Instead of manually searching files, we write a short command such as SELECT * FROM customers;. SQL is used by apps, websites, and data analysts.

Deep dive · Which databases use SQL?

SQLite: a small database stored in a file, useful for learning, mobile apps, and local tools. PostgreSQL and MySQL: common database servers behind websites. SQL Server: common in business software. Cloud-hosted databases: managed PostgreSQL/MySQL and other SQL services used by web apps. SQL syntax is largely shared, but each system has differences. A web app usually accesses its database through a server/API, not by exposing database credentials in the browser.

Now try SQL · your first query

Question: Who are our customers?

The store already has a customers table:

customers
idnamecity
1AmySeattle
2BenPortland
3CaraSeattle
4DanTacoma

Step 1. Ask the database for all customers:

SELECT * FROM customers;

Step 2. Go to the SQL editor ↓. The command is already filled in. Press Run SQL.

Step 3. Check the four customer rows. Then choose WHERE to find only Seattle customers. You have just queried a database!

Deep dive · compare CSV, JSON and database tables

CSV file

order_id,customer,city,total
101,Amy,Seattle,75
102,Amy,Seattle,50
103,Ben,Portland,120

Easy to read and exchange, but repeated customer details can become inconsistent.

JSON file

{
  "orders": [
    {"id":101,"customer":"Amy","total":75},
    {"id":102,"customer":"Amy","total":50},
    {"id":103,"customer":"Ben","total":120}
  ]
}

JSON supports structured and nested data. A JSON file alone does not supply database features such as transactions and indexed queries.

Relational database tables

customers
idnamecity
1AmySeattle
2BenPortland
1 → many
orders
idcustomer_idtotal
101175
102150
1032120

Database systems can store JSON too. The important difference is file format versus data management system.

Deep dive · other file formats

Plain text: flexible but often requires custom parsing. Fixed-width: fields occupy fixed character positions; common in older business systems. Binary: compact machine-readable records. Indexed/sequential files (ISAM): file organization with indexes for efficient lookups. XML: structured tagged documents. CSV and JSON: widely used for exchange and storage. JSON and XML became popular after relational databases existed, not as historical predecessors.

Deep dive · short database history
1950s–1960s · File processing

Applications stored records in files, often with custom layouts and indexes.

1960s · Early databases

Hierarchical and network systems made related records easier to manage.

1970 · Relational model

Edgar F. Codd proposed a relational approach that influenced modern SQL databases.

1970s–1980s · SQL

SQL emerged from IBM research and became a standard for relational databases.

1990s–today · Web and cloud

Open-source, document, distributed and managed cloud databases grew alongside SQL systems.

Deep dive · keys, indexes and transactions

Primary key: identifies a row. Foreign key: connects orders to customers. Index: speeds up some lookups. Transaction: groups changes so they succeed or fail together.

Deep dive · other ways to access a database

SQL editor: type SQL directly and view results. Application: a website or program sends a request to a backend that reads or updates the database. Database client: a tool connects to a database server and lets authorized users run queries.

Different databases use different connection tools and permissions. You typically need a database address, credentials, and permission to access its data.

2. Build a database with SQL

A database stores information. A table is like a spreadsheet: columns describe fields, and rows hold records.

Step 1 of 3

1. Create a table

Database

No tables yet.

Deep dive · multiple tables and JOIN

Create an orders table, then connect it to customers.

3. How a real website uses SQL

Imagine the coffee-store order page. The customer clicks My orders. The website asks its backend for that customer's orders, and the backend queries the database.

Coffee store website
My orders
→
Backend / API
→
SQL database
→
Order list on page

Try it: the button below simulates the website requesting Amy's orders from your practice database.

Deep dive · programming and web APIs

A typical website sends an authenticated HTTP request to its backend. The backend checks the user's permissions, runs a parameterized SQL query, and returns JSON. The frontend turns the JSON into cards or a table. Never embed production database passwords in browser JavaScript.

GET /api/my-orders
→ Backend checks logged-in user
→ SELECT id, total FROM orders WHERE customer_id = ?
→ JSON: [{"id":101,"total":75},{"id":102,"total":50}]
→ Website displays the orders

This page uses a browser-only SQLite practice database to demonstrate the data flow; it does not contact a production backend.

4. More SQL practice

Follow these four lessons in order: SELECT → WHERE → ORDER BY → LIMIT. Each starts with a ready-to-run command. Change it and run again to see what happens.

Deep dive · more about this SQL command

Try it:

The query is ready below. Press Run SQL, then edit and try again.

Loading SQL runtime…