Syllabus Point
- Apply a web-based database and construct script that executes SQL
Including:
- selecting fields
- incorporating 'group by'
- common SQL queries
- constraints using WHERE keyword
- table joins
Mastering SQL queries enables developers to retrieve, insert, update and delete data from databases, which is fundamental to building dynamic web applications.
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.