SQL Queries in Web-Based Databases

Selecting fields

The SELECT statement retrieves specific columns (fields) from a database table. Use * to select all columns, or list specific column names separated by commas.

-- Select specific columns
SELECT username, email FROM users;

-- Select all columns
SELECT * FROM users;

Incorporating 'group by'

The GROUP BY clause groups rows that share the same values in specified columns. It is commonly used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

-- Count how many users have each role
SELECT role, COUNT(*) AS total FROM users GROUP BY role;

-- Average score per subject
SELECT subject, AVG(score) AS avg_score FROM results GROUP BY subject;

Common SQL queries

Insert data

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Update data

UPDATE users SET email = 'new@example.com' WHERE name = 'Alice';

Delete data

DELETE FROM users WHERE name = 'Alice';

Always use a WHERE clause with UPDATE and DELETE — without it, every row in the table will be affected.

Constraints using WHERE keyword

The WHERE clause filters results based on specified conditions, allowing you to retrieve only the data that matches your criteria.

-- Filter by a numeric condition
SELECT * FROM users WHERE age > 18;

-- Filter by an exact value
SELECT * FROM users WHERE role = 'admin';

-- Combine conditions with AND / OR
SELECT * FROM users WHERE age > 18 AND role = 'student';

Table joins

Joins combine data from multiple tables based on a related column (usually a foreign key). INNER JOIN returns only rows that have a match in both tables.

-- Get each user's name alongside their order
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

A LEFT JOIN returns all rows from the left table, even if there is no matching row in the right table.

-- Show all users, including those with no orders
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Executing SQL from a web script

In a web application, SQL queries are executed from a backend scripting language. The script connects to the database, runs a query, and returns the results to the web page.

Python with SQLite

import sqlite3

conn = sqlite3.connect('school.db')
cursor = conn.cursor()

cursor.execute('SELECT username, email FROM users WHERE role = ?', ('student',))
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

Using ? placeholders (parameterised queries) instead of inserting values directly into the string prevents SQL injection attacks.

Node.js with SQLite

const Database = require('better-sqlite3');
const db = new Database('school.db');

const rows = db.prepare('SELECT username, email FROM users WHERE role = ?').all('student');
console.log(rows);