DBeaver SQL Summary Learning Notes

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

---