DBeaver & SQL Summary Learning Notes

A step-by-step guide using the Hospital Database worked examples

Learning Roadmap

  1. Understand DBeaver and the SQL editor
  2. Connect to the database and select a schema
  3. Understand tables, columns, keys and constraints
  4. Create tables and insert records
  5. Retrieve data with SELECT
  6. Filter rows with WHERE and operators
  7. Sort and limit results
  8. Summarise data with aggregate functions
  9. Group records with GROUP BY and HAVING
  10. Join related tables
  11. Modify records and table structures
  12. Delete data safely and understand table removal
  13. Write SQL in the correct order and practise

1. DBeaver and the SQL Editor

  • Connect to PostgreSQL database.
  • Use SQL Editor → run queries with Ctrl+Enter.
  • Inspect Results grid and error panel.
  • Use comments: 
 -- Show all doctors SELECT * FROM doctors; /* This query returns all rows and columns */

2. Create and Select a Schema 

CREATE SCHEMA morning; SET search_path TO morning; SHOW search_path;

3. Tables, Columns, Keys and Constraints

Common Data Types

  • INT → whole numbers
  • VARCHAR(n) → text
  • DATE → YYYY-MM-DD
  • NUMERIC(10,2) → decimals
  • SERIAL → auto-increment integer

Constraints

  • PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT

Example 

CREATE TABLE doctors ( doctor_id INT PRIMARY KEY, doctor_name VARCHAR(50) NOT NULL, doctor_gender VARCHAR(20), doctor_specialty VARCHAR(20), doctor_experience_years INT );

4. Insert Records 

INSERT INTO doctors (doctor_id, doctor_name, doctor_gender, doctor_specialty, doctor_experience_years) VALUES (101, 'Dr. Brian Mwangi', 'Male', 'Cardiologist', 12), (102, 'Dr. Sarah Wanjiku', 'Female', 'General Physician', 9); SELECT * FROM doctors;

5. Retrieve Data with SELECT 

-- All columns SELECT * FROM doctors; -- Selected columns SELECT doctor_id, doctor_name FROM doctors; -- Alias SELECT d.doctor_id, d.doctor_name FROM doctors AS d; -- Limit rows SELECT * FROM doctors LIMIT 5;

6. Filtering Rows

Comparison Operators 

SELECT * FROM patients WHERE patient_age = 30; SELECT * FROM patients WHERE patient_age <> 30; SELECT * FROM patients WHERE patient_age BETWEEN 19 AND 30;

Logical Operators 

SELECT * FROM patients WHERE patient_gender = 'Male' AND patient_age > 40; SELECT * FROM patients WHERE patient_gender = 'Male' OR patient_age > 40; SELECT * FROM patients WHERE NOT patient_gender = 'Male';

IN, NOT IN, NULL, DISTINCT 

SELECT * FROM visits WHERE triage_level IN ('Green','Yellow'); SELECT * FROM visits WHERE ward_id IS NULL; SELECT DISTINCT insurance_provider FROM visits;

7. Sorting and Limiting 

SELECT visit_id, visit_date FROM visits ORDER BY visit_date DESC; SELECT patient_id, patient_name, patient_age FROM patients ORDER BY patient_age DESC LIMIT 1;

8. Aggregate Functions 

SELECT COUNT(*) AS total_patients FROM patients; SELECT AVG(patient_age) AS average_age FROM patients; SELECT SUM(total_bill_kes) AS total_billed FROM visits; SELECT MAX(total_bill_kes), MIN(total_bill_kes) FROM visits;

9. GROUP BY and HAVING 

SELECT patient_gender, AVG(patient_age) FROM patients GROUP BY patient_gender; SELECT doctor_id, SUM(total_bill_kes) FROM visits GROUP BY doctor_id HAVING SUM(total_bill_kes) > 100000;

10. Joins

INNER JOIN 

SELECT v.visit_id, v.visit_date, p.patient_name, d.doctor_name, v.total_bill_kes FROM visits v INNER JOIN patients p ON v.patient_id = p.patient_id INNER JOIN doctors d ON v.doctor_id = d.doctor_id ORDER BY v.visit_date;

LEFT JOIN 

SELECT p.patient_id, p.patient_name, v.visit_id FROM patients p LEFT JOIN visits v ON p.patient_id = v.patient_id;

11. Modify Records

UPDATE 

UPDATE doctors SET doctor_name = 'Dr. Peter Ochieng' WHERE doctor_id = 103;

DELETE 

DELETE FROM triage WHERE triage_id = 2; DELETE FROM triage WHERE risk_level IS NULL;

12. Alter Table 

ALTER TABLE doctors ADD COLUMN email VARCHAR(60); ALTER TABLE doctors RENAME COLUMN email TO doctors_email; ALTER TABLE triage RENAME TO dim_triage;

13. DELETE vs TRUNCATE vs DROP

Command Removes Use
DELETE FROM table WHERE… Selected rows Specific records
DELETE FROM table All rows Retain structure
TRUNCATE TABLE All rows efficiently Empty table
DROP TABLE Table + structure Remove table

14. SQL Order

Writing order:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Execution order:

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

15. Practical Patterns 

SELECT patient_id, length_of_stay_days FROM visits WHERE length_of_stay_days > 0; SELECT visit_id, patient_id FROM visits WHERE payment_method = 'Cash'; SELECT insurance_provider, SUM(total_bill_kes) AS total_revenue FROM visits GROUP BY insurance_provider ORDER BY total_revenue DESC LIMIT 1;

16. Common Mistakes

  • Use exact column names (patient_age, not patients_age).
  • Use single quotes for text/date values.
  • Use IS NULL, not = NULL.
  • Always add WHERE in UPDATE/DELETE unless affecting all rows.

17. Suggested Practice Sequence

  1. Display all patients.
  2. Find patients aged ≤ 30.
  3. List visits between 1–10 June 2026.
  4. Count visits and average bill.
  5. Calculate total billed by admission_type.
  6. Find patients with >1 visit.
  7. Join visits with patients and doctors.
  8. Alter a table (add/rename/drop column).
  9. Compare DELETE, TRUNCATE, DROP.

18. Quick Reference

Need to… SQL keyword
Read data SELECT
Choose source table FROM
Filter rows WHERE
Remove duplicates DISTINCT
Sort results ORDER BY
Limit rows LIMIT
Aggregate COUNT, SUM, AVG, MIN, MAX
Group GROUP BY
Filter groups HAVING
Combine tables JOIN
Add rows INSERT INTO
Change rows UPDATE
Remove rows DELETE
Change structure ALTER TABLE
Empty table TRUNCATE
Remove table DROP TABLE