Phase 5 — Basic SQL Queries (q01–q20)¶

This section documents the basic query set used in the application backend.

Query Catalog¶

Returns the total number of users registered in the system.

SELECT COUNT(*) AS total_users FROM User;

Retrieves the ten most recently created user records ordered by creation date.

SELECT user_id, first_name, last_name, email, phone_number, created_at
FROM User
ORDER BY created_at DESC
LIMIT 10;

Searches for users whose email addresses match a specified keyword or pattern.

SELECT user_id, first_name, last_name, email
FROM User
WHERE email LIKE CONCAT('%', ?, '%')
ORDER BY user_id DESC;

Calculates the total number of family member records stored in the database.

SELECT COUNT(*) AS total_family_members FROM FamilyMember;

Retrieves a simple list of the most recently added family members.

SELECT member_id, user_id, first_name, last_name, relationship, date_of_birth
FROM FamilyMember
ORDER BY member_id DESC
LIMIT 15;

Displays all family members associated with a specific user account.

SELECT member_id, first_name, last_name, relationship
FROM FamilyMember
WHERE user_id = ?
ORDER BY member_id DESC;

Returns the total number of health events recorded in the system.

SELECT COUNT(*) AS total_health_events FROM HealthEvent;

Retrieves recent health events together with their associated member, condition, severity, and date.

SELECT event_id, member_id, condition_id, severity, event_date
FROM HealthEvent
ORDER BY event_date DESC
LIMIT 15;

Filters health events based on a specified severity level.

SELECT event_id, member_id, condition_id, severity, event_date
FROM HealthEvent
WHERE severity = ?
ORDER BY event_date DESC;

Returns all health conditions stored in the database in alphabetical order.

SELECT condition_id, condition_name
FROM HealthCondition
ORDER BY condition_name;

Retrieves recent medical history records ordered by event date.

SELECT event_id, member_id, condition_id, event_date
FROM MedicalHistory
ORDER BY event_date DESC
LIMIT 15;

Displays the medical history details of a specific family member.

SELECT event_id, member_id, condition_id, event_date, diagnosis, treatment, outcome
FROM MedicalHistory
WHERE member_id = ?
ORDER BY event_date DESC;

Retrieves the latest risk alerts generated in the system.

SELECT alert_id, member_id, risk_level, status, created_date
FROM RiskAlert
ORDER BY created_date DESC
LIMIT 15;

Returns all risk alerts associated with a selected family member.

SELECT alert_id, member_id, risk_level, status, created_date
FROM RiskAlert
WHERE member_id = ?
ORDER BY created_date DESC;

Groups risk alerts by status and counts the number of alerts in each category.

SELECT status, COUNT(*) AS total
FROM RiskAlert
GROUP BY status
ORDER BY total DESC;

Retrieves all health events linked to a specific family member.

SELECT event_id, condition_id, severity, event_date
FROM HealthEvent
WHERE member_id = ?
ORDER BY event_date DESC;

Lists only health events marked as high severity.

SELECT event_id, member_id, condition_id, event_date
FROM HealthEvent
WHERE severity = 'High'
ORDER BY event_date DESC;

Retrieves alerts classified as high risk.

SELECT alert_id, member_id, status, created_date
FROM RiskAlert
WHERE risk_level = 'High'
ORDER BY created_date DESC;

Lists users ordered alphabetically by last name and first name.

SELECT user_id, first_name, last_name, email
FROM User
ORDER BY last_name, first_name
LIMIT 20;

Lists family members in alphabetical order.

SELECT member_id, first_name, last_name, relationship
FROM FamilyMember
ORDER BY last_name, first_name
LIMIT 20;

? indicates a parameterized value.
Query IDs map directly to backend query constants for implementation traceability.
The query descriptions in this section are aligned with the Phase 5 report appendix.