Choose to either use the existing database or create a new one as you prefer.
If you want to use existing database simply type USE DatabaseName - where DatabaseName is the name of the database that exists within your system that you want to use.
If you want to create a new database simply type CREATE DATABASE DatabaseName - where DatabaseName is the name of the database you seek to create/add. Then follow the same USE command to continue with that database.
Let's create 3 tables and add values under it:
Code to create 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 );
Code to insert values within the 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);
Code to create 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);
Code to insert values within the 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);
Code to create 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) );
Code to insert values within the 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);
At the end of it, you should have 3 tables with data populated under each of it.
Verify if the table is created successfully or not by running:
SHOW TABLES;
If the outcome shows all table names - you're good.
Check if each of the 3 tables have the required data added correct or not by entering:
SELECT * FROM TableName;
where TableName is the name of the table you created. Use it 3 times - once per table to see their values.
Questions to answer:
- How many therapists are there? Answer in one number only.
- How many therapists are there with their first name Tom? Answer in one number only.
- How many therapists are there living in San Jose? Answer in one number only.
- How much is the total session cost in the organization?
- How much total session cost is the organization getting for Physical Therapy?
- How much average session cost is the organization getting?
- How much average session cost is the organization getting for Physical Therapy?
- What is the lowest session cost that the organization is getting?
- What is the lowest session cost that the organization is getting for Physical Therapy?
- What is the highest session cost that the organization is paying for Physical Therapy?
- What is the highest session cost that the organization is getting?
- How many sessions have the highest session cost? Answer in one number only.
- Extract just the month for each of the entries under "session_date" under TherapySessions.
For the next phase of activities - let's create another table and populate it with data:
Creating table Employee
CREATE TABLE Employee
(Name VARCHAR(10),
Gender VARCHAR(10),
Department VARCHAR(10),
Salary INT);
Let's insert data under the table:
INSERT INTO Employee
(Name, Gender, Department, Salary)
VALUES
('Ram', 'Male', 'Sales', 50000),
('Sita', 'Female', 'IT', 70000),
('Hari', 'Male', 'IT', 60000),
('Geeta', 'Female', 'Sales', 50000),
('Hari', 'Male', 'IT', 50000),
('Rita', 'Female', 'Sales', 60000);
Once done, we should have the table as below created:
| Name | Gender | Department | Salary |
|---|---|---|---|
| Ram | Male | Sales | 50,000 |
| Sita | Female | IT | 70,000 |
| Hari | Male | IT | 60,000 |
| Geeta | Female | Sales | 50,000 |
| Hari | Male | IT | 50,000 |
| Rita | Female | Sales | 60,000 |
Let's continue with this to perform further activities.
Top comments (0)