Activity 2: Assignment reference activity
Tools used: XAMPP (Apache & MySQL service started)
Statements based on MySQL
Creating Database
CREATE DATABASE WK3ACTIVITY2;
Creating Patients table
CREATE TABLE Patients (
patient_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
zip_code VARCHAR(10) NOT NULL,
phone_number VARCHAR(20) NOT NULL,
balance DECIMAL(10,2) NOT NULL
);
Inserting data into Patients table
INSERT INTO Patients (patient_id, first_name, last_name, address, city, state, zip_code, phone_number, balance) VALUES
(1, 'John', 'Doe', '123 Main St', 'Waterville', 'ME', '04901', '555-1234', 500.00),
(2, 'Jane', 'Smith', '456 Elm St', 'Augusta', 'ME', '04330', '555-5678', 1000.00),
(3, 'Bob', 'Johnson', '789 Maple St', 'Waterville', 'ME', '04901', '555-9012', 1500.00),
(4, 'Sarah', 'Lee', '321 Oak St', 'Bangor', 'ME', '04401', '555-3456', 2000.00),
(5, 'Mike', 'Brown', '654 Pine St', 'Portland', 'ME', '04101', '555-7890', 2500.00),
(6, 'Lisa', 'Taylor', '987 Cedar St', 'Waterville', 'ME', '04901', '555-2345', 3000.00);
Creating Therapists table
CREATE TABLE Therapists (
therapist_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
phone VARCHAR(20),
street_address VARCHAR(100),
city VARCHAR(50),
state VARCHAR(2),
zip_code VARCHAR(10),
date_hired DATE,
active TINYINT(1)
);
Inserting into Therapists table
INSERT INTO Therapists (first_name, last_name, email, phone, street_address, city, state, zip_code, date_hired, active)
VALUES
('John', 'Doe', '[email protected]', '555-123-4567', '123 Main St', 'Los Angeles', 'CA', '90001', '2021-01-01', 1),
('Jane', 'Smith', '[email protected]', '555-234-5678', '456 Oak Ave', 'San Francisco', 'CA', '94101', '2021-02-15', 1),
('Tom', 'Wilson', '[email protected]', '555-345-6789', '789 Pine St', 'San Diego', 'CA', '92001', '2021-03-15', 1),
('Emily', 'Jones', '[email protected]', '555-456-7890', '321 Elm St', 'Sacramento', 'CA', '95814', '2021-04-01', 1),
('Mark', 'Lee', '[email protected]', '555-567-8901', '654 Cedar St', 'San Jose', 'CA', '95101', '2021-05-15', 1);
Creating TherapySessions table
CREATE TABLE TherapySessions (
session_id INT NOT NULL PRIMARY KEY,
therapist_id INT NOT NULL,
patient_id INT NOT NULL,
session_date DATE NOT NULL,
session_time TIME NOT NULL,
therapy_description VARCHAR(100) NOT NULL,
session_length INT NOT NULL,
session_cost DECIMAL(10,2) NOT NULL,
FOREIGN KEY (therapist_id) REFERENCES Therapists(therapist_id),
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);
Inserting into TherapySessions table
INSERT INTO TherapySessions (session_id, therapist_id, patient_id, session_date, session_time, therapy_description, session_length, session_cost) VALUES
(1, 1, 1, '2021-10-18', '10:00:00', 'Physical Therapy', 30, 50.00),
(2, 2, 2, '2021-10-19', '11:00:00', 'Occupational Therapy', 45, 75.00),
(3, 3, 3, '2021-10-20', '14:00:00', 'Speech Therapy', 60, 100.00),
(4, 4, 1, '2021-10-21', '12:00:00', 'Movement Therapy', 60, 90.00),
(5, 2, 5, '2021-10-22', '09:00:00', 'Physical Therapy', 30, 50.00),
(6, 3, 6, '2021-10-23', '10:00:00', 'Speech Therapy', 60, 100.00);
Questions to answer (write queries for) based on the above table
1 . List all the information in the patient’s table sorted by city.
SELECT * FROM Patients ORDER BY City;
2 . List the last names of patients whose balance is greater than $1,000.
SELECT last_name FROM Patients WHERE balance > 1000.00;
3 . List the city, last name, and balance of patients who live in Waterville and have a balance greater than $1,000.
SELECT City, last_name, balance FROM Patients WHERE City = 'Waterville' AND balance > 1000.00;
4 . List the last name, city, and balance of patients who live in Waterville or have a balance greater than $2,000.
SELECT last_name, city, balance FROM Patients WHERE city = 'Waterville' OR Balance > 2000.00;
5 . Increase the balance by 2% of any patient whose balance is greater than $2,000.
UPDATE Patients SET balance = balance * 1.02 WHERE balance > 2000.00;
6 . List the session dates and id for those sessions scheduled between 10/18/2021 and 10/21/2021.
SELECT session_date, session_id FROM TherapySessions WHERE session_date BETWEEN '2021-10-18' AND '2021-10-21';
7 . List the full name of the therapist scheduled to work on 10/21/2021.
SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM therapists WHERE therapist_id IN (
SELECT therapist_id FROM TherapySessions WHERE session_date = '2021-10-21'
);
8 . How long are the therapy sessions for the therapist whose last name is Smith? List the length of each session.
SELECT therapy_description, session_length FROM TherapySessions WHERE session_id IN (
SELECT session_id FROM TherapySessions WHERE session_id IN (
SELECT therapist_id FROM Therapists WHERE last_name = 'Smith'
)
);
9 . Which therapies have the word “movement” in their description? List the therapy description only.
SELECT therapy_description FROM TherapySessions WHERE therapy_description LIKE '%movement%';
10 . How many therapies are offered? Answer with one number only.
SELECT COUNT(*) AS session_id FROM TherapySessions;
11 . Display the therapies and their unit of time for the therapies that include the word speech, move, or pat.
SELECT therapy_description, session_length FROM TherapySessions WHERE therapy_description LIKE '%speech%' OR therapy_description LIKE '%move%' OR therapy_description LIKE '%pat%';
12 . Display every therapist’s first name and last name as their full name, but only for those instructors not living in zip code 90001.
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM Therapists
WHERE zip_code != '90001';
Top comments (0)