TyroCity: DATA 210 Database Design & Analytics

SQL aggregate functions activities

Angel Paudel — Sat, 29 Nov 2025 14:14:07 +0000

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:

Table 1: Patients
Table 2: Therapists
Table 3: TherapySessions

Table 1: Patients

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);

Table 2: Therapists

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', 'johndoe@example.com', '555-123-4567', '123 Main St', 'Los Angeles', 'CA', '90001', '2021-01-01', 1), 
('Jane', 'Smith', 'janesmith@example.com', '555-234-5678', '456 Oak Ave', 'San Francisco', 'CA', '94101', '2021-02-15', 1), 
('Tom', 'Wilson', 'tomwilson@example.com', '555-345-6789', '789 Pine St', 'San Diego', 'CA', '92001', '2021-03-15', 1), 
('Emily', 'Jones', 'emilyjones@example.com', '555-456-7890', '321 Elm St', 'Sacramento', 'CA', '95814', '2021-04-01', 1), 
('Mark', 'Lee', 'marklee@example.com', '555-567-8901', '654 Cedar St', 'San Jose', 'CA', '95101', '2021-05-15', 1);

Table 3: TherapySessions

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.

WK2 Activity - Working with queries

Angel Paudel — Thu, 16 May 2024 15:15:55 +0000

Practice table::

Order ID	Customer Name	Order Date	Total Cost	Status
1	Jane Doe	1/1/2022	$100.00	Shipped
2	Jane Doe	1/2/2022	$75.00	Pending
3	Mark Smith	1/3/2022	$50.00	Shipped
4	Emily Gray	1/4/2022	$200.00	Shipped
5	Tom Hill	1/5/2022	$150.00	Pending
6	Sarah Jones	1/6/2022	$25.00	Shipped
7	Mike Brown	1/7/2022	$300.00	Pending
8	Anna Green	1/8/2022	$100.00	Shipped
9	David White	1/9/2022	$50.00	Pending
10	Karen Black	2/10/2022	$175.00	Shipped

Questions to answer:

Retrieve all orders that have been shipped.
Retrieve all orders that are still pending.
Retrieve all orders that have a total cost of $100 or more.
Retrieve all orders that were placed in January 2022.
Retrieve all orders that were placed by customers whose name starts with "J".
Retrieve all orders that were placed by customers whose name contains the letter "a".
Retrieve the total cost of all orders that have been shipped.
Retrieve the average cost of all orders that are still pending.
Retrieve the earliest order date for each customer.
Retrieve the customer who has placed the most orders.

WK3 Assignment

Angel Paudel — Sun, 28 Jan 2024 14:33:15 +0000

To create database:

CREATE DATABASE WK3ASSIGNMENT;

To create Patient table:

CREATE TABLE Patient 
(
    PatientNum  INT,
    LastName    VARCHAR(512),
    FirstName   VARCHAR(512),
    Address VARCHAR(512),
    City    VARCHAR(512),
    State   VARCHAR(512),
    ZipCode INT,
    Balance VARCHAR(512)
);

To input data into Patient table:

INSERT INTO Patient (PatientNum, LastName, FirstName, Address, City, State, ZipCode, Balance) VALUES
    ('1010', 'Koehler', 'Robbie', '119  West Bay Dr.', 'San  Vista', 'TX', '72510', '1,535.15 '),
    ('1011', 'King', 'Joseph', '941  Treemont', 'Oak  Hills', 'TX', '74081', '212.80 '),
    ('1012', 'Houghland', 'Susan', '7841  Lake  Side Dr.', 'Munster', 'TX', '72380', '1,955.40 '),
    ('1013', 'Falls', 'Tierra', '44 Applewood Ave.', 'Palm  Rivers', 'TX', '72511', '1,000.35 '),
    ('1014', 'Odepaul', 'Ben', '546  WCR 150  South', 'Munster', 'TX', '74093', '525.00 '),
    ('1015', 'Venable', 'Isaiah', '37 High School Road', 'Waterville', 'TX', '74183', '432.30 '),
    ('1016', 'Waggoner', 'Brianna', '2691  Westgrove St.', 'Delbert', 'TX', '72381', '714.25 '),
    ('1017', 'Short', 'Tobey', '1928  10th Ave.', 'Munster', 'TX', '72512', '967.60 '),
    ('1018', 'Baptist', 'Joseph', '300  Erin  Dr.', 'Waterville', 'TX', '76658', '1,846.75 '),
    ('1019', 'Culling', 'Latisha', '4238  East  71st  St.', 'San  Vista', 'TX', '74071', '1,988.50 '),
    ('1020', 'Marino', 'Andre', '919  Horton Ave.', 'Georgetown', 'TX', '72379', '688.95 '),
    ('1021', 'Wilson', 'Tammy', '424  October Blvd.', 'Waterville', 'TX', '76658', '2,015.30');

To create another table:

CREATE TABLE Session 
(
    SessionNum  INT,
    SessionDate VARCHAR(512),
    PatientNum  INT,
    LengthOfSession INT,
    TherapistID VARCHAR(512),
    TherapyCode INT
);

And, to input data:

INSERT INTO Session (SessionNum, SessionDate, PatientNum, LengthOfSession, TherapistID, TherapyCode) VALUES
    ('27', '10/10/2021', '1011', '45', 'JR085', '92507'),
    ('28', '10/11/2021', '1016', '30', 'AS648', '97010'),
    ('29', '10/11/2021', '1014', '60', 'SW124', '97014'),
    ('30', '10/12/2021', '1013', '30', 'BM273', '97033'),
    ('31', '10/15/2021', '1016', '90', 'AS648', '98960'),
    ('32', '10/16/2021', '1018', '15', 'JR085', '97035'),
    ('33', '10/17/2021', '1017', '60', 'SN852', '97039'),
    ('34', '10/17/2021', '1015', '45', 'BM273', '97112'),
    ('35', '10/18/2021', '1010', '30', 'SW124', '97113'),
    ('36', '10/18/2021', '1019', '75', 'SN852', '97116'),
    ('37', '10/19/2021', '1020', '30', 'BM273', '97124'),
    ('38', '10/19/2021', '1021', '60', 'AS648', '97535');

To create Therapist table:

CREATE TABLE Therapist 
(
    TherapistID VARCHAR(512),
    LastName    VARCHAR(512),
    FirstName   VARCHAR(512),
    Street  VARCHAR(512),
    City    VARCHAR(512),
    State   VARCHAR(512),
    ZipCode INT
);

To insert data into the table:

INSERT INTO Therapist (TherapistID, LastName, FirstName, Street, City, State, ZipCode) VALUES
    ('AS648', 'Shields', 'Anthony', '5222  Eagle Court', 'Palm  Rivers', 'TX', '72511'),
    ('BM273', 'McClain', 'Bridgette', '385  West Mill St.', 'Waterville', 'TX', '76658'),
    ('JR085', 'Risk', 'Jonathan', '1010  650  North', 'Palm  Rivers', 'TX', '72511'),
    ('SN852', 'Nair', 'Saritha', '25 North Elm St.', 'Livewood', 'TX', '72512'),
    ('SW124', 'Wilder', 'Steven', '7354  Rockville Road', 'San  Vista', 'TX', '72510');

To create Therapies table:

CREATE TABLE Therapies 
(
    TherapyCode INT,
    Description VARCHAR(512),
    UnitOfTime  VARCHAR(512)
);

To insert data into it:

INSERT INTO Therapies (TherapyCode, Description, UnitOfTime) VALUES
    ('90901', 'Biofeedback training by any modality', ''),
    ('92240', 'Shoulder strapping', ''),
    ('92507', 'Treatment of speech', '15'),
    ('92530', 'Knee strapping', ''),
    ('92540', 'Ankle and/or foot strapping', ''),
    ('95831', 'Extremity or trunk muscle testing', ''),
    ('97010', 'Hot or cold pack application', ''),
    ('97012', 'Mechanical traction', ''),
    ('97014', 'Electrical stimulation', ''),
    ('97016', 'Vasopneumatic devices', ''),
    ('97018', 'Paraf n bath', ''),
    ('97022', 'Whirlpool', ''),
    ('97026', 'Infrared', ''),
    ('97032', 'Electrical stimulation', '15'),
    ('97033', 'Iontophoresis', '15'),
    ('97035', 'Ultrasound', '15'),
    ('97039', 'Unlisted  modality', '15'),
    ('97110', 'Therapeutic exercises to develop  strength and endurance, range of motion, and   exibility', '15'),
    ('97112', 'Neuromuscular re-education of movement, balance, coordination, etc.', '15'),
    ('97113', 'Aquatic  therapy with therapeutic exercises', '15'),
    ('97116', 'Gait training', '15'),
    ('97124', 'Massage', '15'),
    ('97139', 'Unlisted  therapeutic procedure', ''),
    ('97140', 'Manual therapy techniques', '15'),
    ('97150', 'Group  therapeutic procedure', '15'),
    ('97530', 'Dynamic activities to improve functional performance, direct (one-on-one) with the patient', '15'),
    ('97535', 'Self-care/home management training', '15'),
    ('97750', 'Physical performance test  or measurement', '15'),
    ('97799', 'Unlisted  physical medicine/rehabilitation service  or procedure', ''),
    ('98941', 'CMT of the spine', ''),
    ('98960', 'Education and training for patient self-management', '30');

P.S. in each of the cases - it's down to your personal discretion to choose the data types for the values and make alterations to them as is required for your case. These data types provided are just placeholder and as such you're recommended to update those as is best.

Questions to answer based on it:

-- 1. List all the information in the patient’s table sorted by city.

-- 2. List the last names of patients whose balance is greater than 1,000.

-- 3. List the city, last name, and balance of patients who live in Waterville and have a balance greater than 1,000.

-- 4. List the last name, city, and balance of patients who live in Waterville or have a balance greater than $2,000.

-- 5. Increase the balance by 2% of any patient whose balance is greater than $2,000.

-- 6. List the session dates and numbers for those sessions scheduled between 10/18/2021 and 10/21/2021.

-- 7. List the full name of the therapist scheduled to work on 10/16/2021.

-- 8. How long are the therapy sessions for the therapist whose last name is Shields? List the length of each session.

-- 9. Which therapies have the word “movement” in their description? List the therapy description only.

-- 10. How many therapies are offered? Answer with one number only.

-- 11. Write an SQL query that displays the therapies and their unit of time for the therapies that include the word bath, hot, or electrical.

-- 12. Write an SQL query to display every therapist’s first name and last name as their full name, but only for those instructors not living in zip code 72511.

DATA 210 WK4 Assignment

Angel Paudel — Sun, 26 Mar 2023 17:01:05 +0000

In the following exercises, you will use the data in the JC Consulting database. Don’t forget to use a copy of the JC Consulting database so any changes you make will not affect future modules.

Examine the JC Consulting data and complete the following case exercises/problems:

What is or are not a benefit(s) of a view within the JC Consulting database?
Which fields from the Projects table is or are not a good candidate to index?
Which security features is or are not available to the JC Consulting database given it is an Access database?
In the Employees table, how could you ensure that no HireDate values were entered prior to 1/1/2019?
To query whether the Clients table has records that do not have any matching records in the Projects table, which join statement should be used?
Write a SQL command that adds a new field named Region to the Clients table.
What feature could you use to create system information about the tables and fields in the JC Consulting database?
Where do you create data macros in the JC Consulting Access database?
According to the article referenced by the Bureau of Labor Statistics, which fundamental competency for a job in database administration is not worth knowing?

Apply key module concepts to the JC Consulting case:

Using Access, open the Relationships window and identify all of the one-to-many relationships in the database as well as the field used in both tables to make the connection using the following pattern found in SQL, when connecting two tables using a WHERE or INNER JOIN clause. An example of one of the relationships is provided as a guide.

Clients.ClientID = Projects.ClientID

Using Access, an employee at JC Consulting tried to delete TaskID CODE01 from the TaskMasterList table and received the following error message: “The record cannot be deleted or changed because table ‘ProjectLineItemsincludes related records.”

Why did the employee receive this error message?

Submit your answers to the Examine and Apply questions in a single Word document.

===

Quick Guide:

Q1: List the benefits of "view" for JC Consulting DB. What does it enable? Think and answer based on it.

Q2: Index refers to primary, foreign key. From the projects table - what is and what is not the index?

Q3: This is a MSAccess DB - so what security limitations does it have? Go general.

Q4: You can do this in either MySQL (trigger to make it no new entries are with that detail) or Access (with criteria)

Q5: Review the relationships of the table - you simply need to answer what type of JOIN is to be used and why (or write the query itself)

Q6: SQL query - general.

Q7: General question, no review of data itself required.

Q8: Macro creation - one of your group presented about it in the class itself. Does not ask you to write a macro itself but rather "where do you create" - as think smart.

Q9: Refer to post here: https://www.bls.gov/ooh/computer-and-information-technology/database-administrators.htm#tab-1 or any other as you see fit. Navigate across different tabs for further info.

Part 2 Q1: Below is the relation chat - one of the answer is already there in question list other such answers.

Part 2 Q2: Why is there the error message - a simple answer would suffice.

Triggers in MySQL

Angel Paudel — Thu, 23 Mar 2023 16:26:06 +0000

Triggers are associated with a table and can not operate on it's own. If it's not specified to a table - it does not exist.

Types of Triggers:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

Trigger general flow / Syntax

In text:

CREATE TRIGGER TriggerName
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        -- trigger body   
        END;

A new terminology: DELIMITER

The default delimiter MySQL considers is semicolon (;). However, in cases where ; might be used before the actual end of the query, command - we need to find a way to get past it. This is where we set the delimiter to be anything else. Often times, it is commonly set to $$, which means that the semicolon (;) used in the trigger code WILL NOT be interpreted as the end of the statement.

Then, after the END statement of the trigger, you need to use the same delimiter ($$) again to tell MySQL that the trigger definition has ended, and reset the delimiter to its default value (;) using the statement DELIMITER ;.

An example of the same as below:

DELIMITER $$
CREATE TRIGGER TriggerName
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        -- trigger body   
        END $$
DELIMITER ;

Let's practice.

First, let's create the database:

CREATE DATABASE triggers;

Now, let's create our first table:

CREATE TABLE `employee` (
  `name` varchar(45) NOT NULL,
  `occupation` varchar(35) DEFAULT NULL,
  `working_date` date DEFAULT NULL,
  `working_hours` varchar(10) DEFAULT NULL,
  `Age` varchar(3) DEFAULT NULL
);

Dumping data for table employee:

INSERT INTO `employee` (`name`, `occupation`, `working_date`, `working_hours`, `Age`) VALUES
('Antonio', 'Business', '2020-10-04', '11', '52'),
('Brayden', 'Teacher', '2020-10-04', '12', '44'),
('Marco', 'Doctor', '2020-10-04', '14', '28'),
('Peter', 'Actor', '2020-10-04', '13', '26'),
('Robin', 'Scientist', '2020-10-04', '12', '29'),
('Warner', 'Engineer', '2020-10-04', '10', '32');

BEFORE INSERT Trigger - 1

DELIMITER $$
CREATE TRIGGER capitalize_name
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
SET NEW.name = CONCAT(UCASE(LEFT(NEW.name, 1)), SUBSTRING(NEW.name, 2));
END $$
DELIMITER ;

Where to find the triggers?

From your phpMyAdmin interface - click on database you're working under. On the page that opens, from top menu bar locate "Triggers" and click on it to find it:

Clicking on it would open interface as below where all the avaiable triggers for the database along with the table those trigger are associated with is displayed:

Ok, so let's get to the first trigger we just created - what does it do and how does it work?

Ignoring the general structure for trigger, and just taking the query from it, that would be:

SET NEW.name = CONCAT(UCASE(LEFT(NEW.name, 1)), SUBSTRING(NEW.name, 2));

Here's a breakdown of what each function does:

LEFT(NEW.name, 1): Returns the leftmost character of the name field, i.e. the first letter.
UCASE((LEFT(NEW.name, 1)): Converts the first letter from left to uppercase.
SUBSTRING(NEW.name, 2): Returns the remainder of the name field starting from the second character.
CONCAT(): Concatenates the capitalized first letter with the rest of the name field.

This will take the first character of the name and capitalize it and then join it with all the other text to insert the name.

Let's test if the trigger work by attempting to insert a name which is in lower case via SQL:

INSERT INTO `employee` (`name`, `occupation`, `working_date`, `working_hours`, `Age`) VALUES
('john', 'Business', '2020-10-04', '11', '52');

The outcome however, has the first letter 'j' of "john" capitalized even if we entered it in all lower case:

This confirms that the query did work as required.

BEFORE INSERT Trigger - 2

Let's test with another trigger for the same table.

DELIMITER $$
CREATE TRIGGER set_min_working_hours
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    IF NEW.working_hours < 8 THEN
        SET NEW.working_hours = 8;
    END IF;
END $$
DELIMITER ;

This trigger do not allow a new employee with working hours less than 8. If it's any lower it'll automatically set it to be 8.

Let's test this by inserting an employee with working hour less than 8:

INSERT INTO employee (name,working_hours) VALUES
('master',3);

The outcome:

As we can see from the above outcome - the name even if was inserted in all lower case has it's first letter capital while the working hour even if was set to be 3 is coming as 8.

This refers that both the triggers are working for the following table.

Let's create 2 tables order and counter to test AFTER INSERT trigger. Below is the code used to create the table and dump values as required. We will only populate one of the two tables as of now:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    customer_name VARCHAR(50),
    total_amount DECIMAL(10, 2)
);

CREATE TABLE counter (
    id INT PRIMARY KEY,
    order_count INT DEFAULT 0
);

INSERT INTO counter (id, order_count) VALUES (1, 0);

Let's now create a trigger to count the number of orders i.e. as any new values are inserted in the table orders - the order_count field under counter table should update as well with the correct count.

AFTER INSERT Trigger - 1

DELIMITER $$
CREATE TRIGGER orders_insert_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE counter SET order_count = order_count + 1;
END $$
DELIMITER ;

Let's try inserting the values to see how it works:

INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-21', 'John Doe', 100.00);
INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-21', 'Ram Bahadur', 300.00);

Here's the outcome:

With 2 new values inserted to order table - the order_count increased accordingly. You can test this further by adding more orders.

AFTER INSERT Trigger - 2

For this, let's try to calculate the total order value. And, for which we will create a table amount_counter

CREATE TABLE amount_counter (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

INSERT INTO amount_counter (total_amount) VALUES (0);

Now, let's create a trigger for the same as below to calculate the total order value (P.S. this will only count for new orders as such if you do already have fields in the table the outcome will be different) :

DELIMITER $$
CREATE TRIGGER orders_amount_trigger 
AFTER INSERT ON orders 
FOR EACH ROW 
BEGIN
  UPDATE amount_counter SET total_amount = total_amount + NEW.total_amount WHERE id = 1;
END $$
DELIMITER ;

Let's test this by inserting two new values:

INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-22', 'Sita Kumari', 200.00);
INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-22', 'RK Bahadur', 400.00);

Now, visiting amount_counter table, we can see that the total_amount is 600 as it should be based on the two values we added:

For learning about BEFORE UPDATE trigger - let's start by first creating the table and inserting dummy data to it:

CREATE TABLE employee_salary (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);

INSERT INTO employee_salary (id, name, salary) VALUES
(1, 'John', 50000),
(2, 'Sarah', 60000),
(3, 'Mike', 45000),
(4, 'Emily', 55000);

Once the table is created - let's now create the trigger for BEFORE UPDATE action. In this trigger, we check for UPDATE action if the salary is anything lower than 40,000 and if it is the case - the salary is auto set to the lowest limit allowed which would be 40,000.

DELIMITER $$
CREATE TRIGGER update_salary
BEFORE UPDATE ON employee_salary
FOR EACH ROW
BEGIN
    IF NEW.salary < 40000 THEN
        SET NEW.salary = 40000;
    END IF;
END $$
DELIMITER ;

Let's test the trigger to action - for this let's try setting the salary for one of the employee to 35,000 and see what happens:

UPDATE employee_salary SET salary = 35000 WHERE id = 3;

We can see that the employee with id 3 has salary set to 40,000 by the system even if we had updated the salary to be 35,000 due to the trigger in place.

The trigger prevented the salary to be set any lower than 40k - anything lower and it's set to 40k automatically.

AFTER UPDATE trigger exercise

Let's create 2 tables product and product_inventory to test AFTER UPDATE trigger. Below is the code used to create the table and dump values as required:

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  quantity INT
);

INSERT INTO product (id, name, price, quantity)
VALUES
  (1, 'Product 1', 10.00, 5),
  (2, 'Product 2', 20.00, 3),
  (3, 'Product 3', 30.00, 7),
  (4, 'Product 4', 15.00, 2);

CREATE TABLE product_inventory (
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (product_id)
);

INSERT INTO product_inventory (product_id, quantity)
VALUES
  (1, 5);

Now, let's create a trigger to update the product_inventory table with the updated stock for product_id 1:

DELIMITER $$
CREATE TRIGGER update_product_inventory
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
  DECLARE inventory INT;

  SET inventory = NEW.quantity - OLD.quantity;

  UPDATE product_inventory
  SET quantity = quantity + inventory
  WHERE product_id = NEW.id;
END $$
DELIMITER ;

Now, to test it let's update quantity for Product 1 from 5 to 8 and see what happens:

UPDATE product
SET quantity = 8
WHERE id = 1;

The outcome as below shows that the product_inventory table does have quantity increased to 8 instead of previous 5 even if the above UPDATE command was only done for product table, confirming the query action:

BEFORE DELETE trigger exercise

For the following exercise, let's create a table products of whose structure and dummy data are as below:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT NOT NULL
);

INSERT INTO products (id, name, price, stock_quantity) VALUES
(1, 'Product 1', 10.50, 0),
(2, 'Product 2', 5.99, -50),
(3, 'Product 3', 20.00, 25),
(4, 'Product 4', 15.75, 75);

Now, let's create a trigger which will not allow the removal of any product if it has any stuck left (meaning - it'll only allow to remove products with 0 or lesser stock_quantity):

DELIMITER $$
CREATE TRIGGER delete_products
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
  IF OLD.stock_quantity > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot delete product, stock quantity still left.';
  END IF;
END $$
DELIMITER ;

Now, let's test the function by removing product_id 1 which has 0 as stock_quantity:

DELETE FROM products WHERE id = 1;

Outcome is successful execution of the same and data removal:

Let's now try removing another product with product_id 3 which has 25 as stock_quantity (which is higher than 0) :

DELETE FROM products WHERE id = 3;

Outcome for this is a failed process:

So, we can see the successful execution of the BEFORE DELETE trigger here which did not allow the operation to complete if there's at least one or more quantity left but did allow the removal of a product if it has 0 or lesser product left to sell.

AFTER DELETE trigger exercise

For the following activity/learning let's create two tables salaries and total_salary_budget and insert dummy data to it as below:

CREATE TABLE salaries (  
    emp_num INT PRIMARY KEY,  
    salary DEC(8 , 2 ) NOT NULL DEFAULT 0  
);

INSERT INTO salaries (emp_num, salary)  
VALUES  
    (101, 50000),  
    (102, 55000),  
    (103, 60000),
    (104, 65000),
    (105, 70000),              
    (106, 75000);

CREATE TABLE total_salary_budget(  
    total_budget DECIMAL(10,2) NOT NULL  
);

INSERT INTO total_salary_budget (total_budget)  
SELECT SUM(salary) FROM salaries;

Now, let's create a trigger to update total_salary_budget table's total_budget field with the updated budget as a employee is fired/leaves the job (here: removed from the database table):

DELIMITER $$    
CREATE TRIGGER after_delete_total_salary  
AFTER DELETE  
ON salaries FOR EACH ROW  
BEGIN  
   UPDATE total_salary_budget SET total_budget = total_budget - old.salary;  
END $$   
DELIMITER ;

Before writing a query to remove an employee - let's take a minute to review the current total_budget where we can see it's 375,000.00:

Now, let's remove emp_num from salaries table using the following command and see what happens:

DELETE FROM salaries WHERE emp_num = 101;

emp_num 101 had a salary of 50,000 as such when the employ is removed from the salary slab the total_budget which was previously 375,000 should come as 325,000 if it was successfully updated. Let's check:

We can indeed see that the total_budget is now 325,000 and as such we can confirm that the trigger did function as was intended.

This is all for Triggers in MySQL. If you have any further concerns or questions - do drop them below in the comments section.

DATA 210 Week 3 Class 3 Answers

Angel Paudel — Fri, 17 Mar 2023 03:38:00 +0000

-- 1. SUM: What is the total amount of all orders?

SELECT SUM(order_total) FROM orders;

-- 2. COUNT: How many customers are there in the database?

SELECT COUNT(*) FROM customers;

-- 3. AVG: What is the average total amount of all orders?

SELECT AVG(order_total) FROM orders;

-- 4. MIN: What is the minimum total amount of all orders?

SELECT MIN(order_total) FROM orders;

-- 5. MAX: What is the maximum total amount of all orders?

SELECT MAX(order_total) FROM orders;

-- 6. GROUP BY: What is the total amount of orders for each customer?

SELECT customer_id, SUM(order_total) FROM orders GROUP BY customer_id;

-- 7. SELECT subquery: What is the first name and last name of the customer with the highest total amount of orders?

SELECT first_name, last_name FROM customers WHERE customer_id = (SELECT customer_id FROM orders GROUP BY customer_id ORDER BY SUM(order_total) DESC LIMIT 1);

-- 8. WHERE subquery: What is the total amount of orders made by customer with last_name in Smith?

SELECT SUM(order_total) FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_name = 'Smith');

-- 9. FROM subquery: What is the total amount of orders made by customers who live in the same city as customer_id 2? (ALTER table to include city field and add values to it with UPDATE)

===========

EXTRA STEPS - Do first

ALTER TABLE customers ADD COLUMN city VARCHAR(255);

UPDATE customers SET city = 'New York' WHERE customer_id = 1;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 2;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 3;

Query:

SELECT SUM(order_total) FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = (SELECT city FROM customers WHERE customer_id = 2));

-- 10. UNION: What are the customer names of customers who have placed an order, as well as customers who have not placed an order?

SELECT first_name, last_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders) UNION SELECT first_name, last_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- 11. UNION ALL: What are the customer names of customers who have placed an order, as well as customers who have not placed an order, and what is the corresponding city for each customer?

SELECT first_name, last_name, city FROM customers WHERE customer_id IN (SELECT customer_id FROM orders) UNION ALL SELECT first_name, last_name, city FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- 12. UPDATE: Change the total amount of order_id 3 to 100.00.

UPDATE orders SET order_total = 100.00 WHERE order_id = 3;

-- 13. INSERT: Add a new order for customer_id 101 with order_total 150.00.

INSERT INTO orders (customer_id, order_total) VALUES (101, 150.00);

-- 14. DELETE: Delete all orders with customer_id more than 100

DELETE FROM orders WHERE customer_id > 100;

DATA 210 Week 3 Class 3 Resources

Angel Paudel — Thu, 16 Mar 2023 15:15:31 +0000

Let's create a database to start with:

CREATE DATABASE WK3CLASS3;

Now, let's create 2 tables (orders & customers) and input data for the table:

CREATE TABLE orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id)
);

INSERT INTO orders (customer_id, order_date, order_total)
VALUES (1, '2022-12-01', 100.50),
       (1, '2022-12-02', 75.25),
       (2, '2022-12-01', 200.00),
       (2, '2022-12-03', 150.75),
       (3, '2022-12-02', 50.00),
       (3, '2022-12-03', 125.50);

CREATE TABLE customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (customer_id)
);

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'jdoe@example.com'),
       ('Jane', 'Smith', 'jsmith@example.com'),
       ('Bob', 'Johnson', 'bjohnson@example.com');

Questions to practice

-- 1. SUM: What is the total amount of all orders?

-- 2. COUNT: How many customers are there in the database?

-- 3. AVG: What is the average total amount of all orders?

-- 4. MIN: What is the minimum total amount of all orders?

-- 5. MAX: What is the maximum total amount of all orders?

-- 6. GROUP BY: What is the total amount of orders for each customer?

-- 7. SELECT subquery: What is the first name and last name of the customer with the highest total amount of orders?

-- 8. WHERE subquery: What is the total amount of orders made by customer with last_name in Smith?

-- 9. FROM subquery: What is the total amount of orders made by customers who live in the same city as customer_id 2? (ALTER table to include city field and add values to it with UPDATE)

Do perform the following action before writing SQL for the question::

ALTER TABLE customers ADD COLUMN city VARCHAR(255);

UPDATE customers SET city = 'New York' WHERE customer_id = 1;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 2;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 3;

-- 10. UNION: What are the customer names of customers who have placed an order, as well as customers who have not placed an order?

Do perform the following action before writing the query:

INSERT INTO customers (first_name, last_name, email)
VALUES ('Happy', 'Says', 'happy@example.com');

-- 11. UNION ALL: What are the customer names of customers who have placed an order, as well as customers who have not placed an order, and what is the corresponding city for each customer?

-- 12. UPDATE: Change the total amount of order_id 3 to 100.00.

-- 13. INSERT: Add a new order for customer_id 101 with order_total 150.00.

-- 14. DELETE: Delete all orders with customer_id more than 100

DATA 210 Week 3 Class 2 Activity 2 Resources

Angel Paudel — Wed, 15 Mar 2023 15:44:02 +0000

Activity 2: Assignment reference activity

Tools used: XAMPP (Apache & MySQL service started)

Start by first starting both Apache and MySQL:

Once that is done, click on "Admin" next to MySQL:

As you do so, it will open phpMyAdmin in your local device. Visit "SQL":

Now, start entering the command to first create the database, and then table and then insert data within it as detailed below.

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', 'johndoe@example.com', '555-123-4567', '123 Main St', 'Los Angeles', 'CA', '90001', '2021-01-01', 1),
('Jane', 'Smith', 'janesmith@example.com', '555-234-5678', '456 Oak Ave', 'San Francisco', 'CA', '94101', '2021-02-15', 1),
('Tom', 'Wilson', 'tomwilson@example.com', '555-345-6789', '789 Pine St', 'San Diego', 'CA', '92001', '2021-03-15', 1),
('Emily', 'Jones', 'emilyjones@example.com', '555-456-7890', '321 Elm St', 'Sacramento', 'CA', '95814', '2021-04-01', 1),
('Mark', 'Lee', 'marklee@example.com', '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.

2 . List the last names of patients whose balance is greater than $1,000.

3 . List the city, last name, and balance of patients who live in Waterville and have a balance greater than $1,000.

4 . List the last name, city, and balance of patients who live in Waterville or have a balance greater than $2,000.

5 . Increase the balance by 2% of any patient whose balance is greater than $2,000.

6 . List the session dates and id for those sessions scheduled between 10/18/2021 and 10/21/2021.

7 . List the full name of the therapist scheduled to work on 10/21/2021.

8 . How long are the therapy sessions for the therapist whose last name is Smith? List the length of each session.

9 . Which therapies have the word “movement” in their description? List the therapy description only.

10 . How many therapies are offered? Answer with one number only.

11 . Display the therapies and their unit of time for the therapies that include the word speech, move, or 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.

DATA 210 Week 3 Class 2 Activity 1 Resources

Angel Paudel — Wed, 15 Mar 2023 15:38:30 +0000

Activity 1: Learn about JOINS

Creating employees table

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT,
  salary DECIMAL(10,2)
);

Insert data for employees table

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
  (1, 'John', 'Doe', 1, 50000.00),
  (2, 'Jane', 'Smith', 2, 60000.00),
  (3, 'Bob', 'Johnson', 1, 55000.00),
  (4, 'Sarah', 'Lee', 3, 65000.00),
  (5, 'Mike', 'Brown', 2, 70000.00),
  (6, 'Karen', 'Davis', 3, 75000.00),
  (7, 'Happy', 'Says', 4, 80000.00);

Creating departments table

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

Insert data for departments table

INSERT INTO departments (department_id, department_name) VALUES
  (1, 'Sales'),
  (2, 'Marketing'),
  (3, 'Engineering'),
  (5, 'Whatever');

Question implementing INNER JOIN

What is the name of each employee and their corresponding department?

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Question implementing LEFT OUTER JOIN

What is the name of each employee and their corresponding department, even if the department has no employees?

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

Question implementing RIGHT OUTER JOIN

What is the name of each employee and their corresponding department, even if the employee has no department assigned?

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;

DATA 210 Week 3 Class 1 Resources

Angel Paudel — Mon, 13 Mar 2023 14:56:50 +0000

Activity 1

Create the table as below (start with table headers and then values) :

StudentID	FirstName	LastName	Gender	Age	City	State	Country
1	John	Doe	M	25	New York	NY	USA
2	Jane	Doe	F	22	Los Angeles	CA	USA
3	Bob	Smith	M	27	Chicago	IL	USA
4	Alice	Johnson	F	21	Houston	TX	USA
5	Tom	Jones	M	24	London	NULL	UK
6	Linda	Smith	F	23	Paris	NULL	France

Need help? Follow the code as below to create a table:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Gender CHAR(1),
  Age INT,
  City VARCHAR(50),
  State VARCHAR(50),
  Country VARCHAR(50)
);

Now, insert values in the table with this:

INSERT INTO Students VALUES
  (1, 'John', 'Doe', 'M', 25, 'New York', 'NY', 'USA'),
  (2, 'Jane', 'Doe', 'F', 22, 'Los Angeles', 'CA', 'USA'),
  (3, 'Bob', 'Smith', 'M', 27, 'Chicago', 'IL', 'USA'),
  (4, 'Alice', 'Johnson', 'F', 21, 'Houston', 'TX', 'USA'),
  (5, 'Tom', 'Jones', 'M', 24, 'London', NULL, 'UK'),
  (6, 'Linda', 'Smith', 'F', 23, 'Paris', NULL, 'France');

Now, answer the following questions:

Select all students from the USA.
Select all students from the USA who are over 25 years old.
Select all students who are either from New York or Los Angeles.
Select all students who are not from the USA.
Select all students who are not from the USA and are over 25 years old.
Select all students from the USA.
Select all students from the USA who are over 25 years old.
Select all students who are either from New York or Los Angeles.
Select all students who are not from the USA.
Select all students who are not from the USA and are over 25 years old.
Select all students and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all students and include a computed field called 'GenderAndAge' which is a concatenation of gender and age, separated by a dash.

Solutions

-- 1. Select all students from the USA.
SELECT * FROM Students WHERE Country = 'USA';

-- 2. Select all students from the USA who are over 25 years old.
SELECT * FROM Students WHERE Country = 'USA' AND Age > 25;

-- 3. Select all students who are either from New York or Los Angeles.
SELECT * FROM Students WHERE City = 'New York' OR City = 'Los Angeles';

-- 4. Select all students who are not from the USA.
SELECT * FROM Students WHERE Country <> 'USA';

-- 5. Select all students who are not from the USA and are over 25 years old.
SELECT * FROM Students WHERE Country <> 'USA' AND Age > 25;

-- 6. Select all students whose age is between 22 and 25 inclusive.
SELECT * FROM Students WHERE Age BETWEEN 22 AND 25;

-- 7. Select all students whose last name starts with 'S'.
SELECT * FROM Students WHERE LastName LIKE 'S%';

-- 8. Select all students whose first name is 'Bob' or 'Tom' or 'Linda'.
SELECT * FROM Students WHERE FirstName IN ('Bob', 'Tom', 'Linda');

-- 9. Select all students and include a computed field called 'FullName' which is the concatenation of first and last name.
SELECT *, CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

-- 10. Select all students and include a computed field called 'GenderAndAge' which is a concatenation of gender and age, separated by a dash.
SELECT *, CONCAT(Gender, '-', Age) AS GenderAndAge FROM Students;

Activity 2
Create a table called Employees with columns for employee ID, name, job title, department ID, and salary.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(50),
  JobTitle VARCHAR(50),
  DepartmentID INT,
  Salary INT
);

Insert some sample data into the Employees table.

INSERT INTO Employees VALUES
  (1, 'John Smith', 'Manager', 1, 80000),
  (2, 'Jane Doe', 'Sales Representative', 2, 60000),
  (3, 'Bob Johnson', 'Software Developer', 3, 70000),
  (4, 'Alice Brown', 'Sales Manager', 2, 90000),
  (5, 'Tom Jones', 'Software Architect', 3, 100000),
  (6, 'Linda Davis', 'HR Specialist', 4, 50000);

Now, work to answer the following questions:

Select all employees from department 3.
Select all employees who make more than $70,000 per year.
Select all employees whose job title contains the word 'Manager'.
Select all employees who work in departments other than department 2.
Select all employees who work in departments other than department 2 and make more than $70,000 per year.
Select all employees whose salary is between $60,000 and $80,000 inclusive.
Select all employees whose name starts with 'J'.
Select all employees whose job title is 'Manager' or 'Sales Representative'.
Select all employees and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all employees and include a computed field called 'DepartmentAndJobTitle' which is a concatenation of department and job title, separated by a comma.

Activity 3

Create table as below:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(50),
  Phone VARCHAR(20),
  Address VARCHAR(100),
  HireDate DATE,
  Salary INT,
  Department VARCHAR(50)
);

Insert the following values:

INSERT INTO Employees VALUES
  (1, 'John', 'Doe', 'john.doe@company.com', '555-1234', '123 Main St, Anytown USA', '2020-01-01', 50000, 'Sales'),
  (2, 'Jane', 'Smith', 'jane.smith@company.com', '555-5678', '456 Oak St, Anytown USA', '2019-05-15', 60000, 'Marketing'),
  (3, 'Bob', 'Johnson', 'bob.johnson@company.com', '555-4321', '789 Pine St, Anytown USA', '2018-09-01', 70000, 'Finance'),
  (4, 'Alice', 'Williams', 'alice.williams@company.com', '555-8765', '321 Maple St, Anytown USA', '2021-03-01', 55000, 'Sales'),
  (5, 'Tom', 'Jones', 'tom.jones@company.com', '555-2468', '654 Elm St, Anytown USA', '2017-01-01', 75000, 'Finance'),
  (6, 'Linda', 'Davis', 'linda.davis@company.com', '555-3698', '987 Cedar St, Anytown USA', '2022-01-01', 80000, 'Marketing');

Now, work to answer the following questions:

Select all employees in the Sales department.
Select all employees in the Finance department with a salary greater than $60,000.
Select all employees whose last name starts with 'D'.
Select all employees hired after January 1st, 2020.
Select all employees whose first name is either 'John' or 'Jane'.
Select all employees whose salary is between $50,000 and $70,000.
Select all employees whose email address contains 'company.com'.
Select all employees and include a computed field called 'FullName' which is the concatenation of first and last name.
Select all employees and include a computed field called 'SalaryPerMonth' which is the employee's annual salary divided by 12.
Select all employees and include a computed field called 'YearsOfService' which is the number of years the employee has been with the company.

DATA 210 WK1 Assignment Resources

Angel Paudel — Thu, 09 Mar 2023 02:38:11 +0000

Attached would be the Excel file that you can make use of to analyze further or to import the details in other DBMS system to extract the answer for the questions as below.

Download File

Table 1: Employees

Table 2: Clients

Table 3: Projects

Table 4: ProjectLineItems

Table 5: TaskMasterList

Examine the JC Consulting data and complete the following case exercises/problems:

Which employee(s) has a salary value of $8,100?
Which client(s) is or are not a government entity?
Which project(s) had an estimated start date prior to 1/1/2020?
List ProjectLineItemID values that are related to ProjectID 3.
List the name of the client related to Project ID 3.
Which task(s) is or are not priced by the project?
For the record with a ProjectLineID value of 7, why does the Quantity field contain 20?
For the record with a ProjectLineID value of 7, why does the Factor field contain 1.3?
What is the cost for an initial meeting with a client, TaskID MEET00?
What is the cost for creating a shopping card, TaskID CODE15?

Apply key module concepts to the JC Consulting case:

JCC needs to be able to contact clients when problems arise concerning an estimate. What other attributes could JCC include in the Clients table to assist in contacting clients?
JCC wants the database to include data on all its employees, not just those who may be involved in projects. What additional entities would the DBA need to include in the database to store this data? What attributes?
What kinds of unstructured data or big data might JCC want to gather in the future?

Guiding points

Q2 asks for
Which client(s) "is or are" not a government entity?

This is asking you to list the client(s) which aren't government entity. Listing ones that are is incorrect.

Q3 mentions
start date prior to 1/1/2020

This refers to date before and not after.

Q4 asks for
ProjectLineItemID values

This refers to you requiring to list the values that are present under "ProjectLineItemID" for project's with "ProjectID" 3.

Q6 mentions
Which task(s) "is or are" not priced by the project

Like for Q2 or point 1 here. List ones that are not priced by project - all.

Q7 asks for "why does the Quantity field contain 20" - this does require you to look on book for definition/answer. The section just below Figure 1-5 -> 3rd last paragraph on that page. Frame your answer based on your understanding there - it is not to be copied.

Q8 asks for "why does the Factor field contain 1.3" - this does require you to look the book for definition/answer. It is the next 2 line after Q7 answer itself. Frame your answer based on your understanding there - it is not to be copied.

Q10 - do not forget to mention the unit.

For "Apply key module concepts to the JC Consulting case" section of questions - give it a new title.

And, then Question number followed by answer to it.

For Q1/Q2 - you just need to list the attributes name to be added.

For Q3 - asks for unstructured data so no specific attribute name required but the category, types of data to be collected like about client feedback and satisfaction for one. You will need to list few and then also explain in a sentence or two of what that will include/cover.

DATA 210 WK2 Assignment Resources

Angel Paudel — Thu, 09 Mar 2023 02:37:47 +0000

Attached would be the Excel file that you can make use of to analyze further or to import the details in other DBMS system to extract the answer for the questions as below.

Download File

The owner of Pitt Fitness knows that the power of the company’s database is in running queries to find out important information for making good business decisions. In the following exercises, use the data in the Pitt Fitness database shown in Figures 1-15, 1-16, 1-17, 1-18, and 1-19 in Module 1. When you use Microsoft Access to respond to these exercises, make a copy of the original database to create the queries. In each step, use QBE to obtain the desired results.

Examine the Pitt Fitness data and complete the following case exercises/problems:

Which customer lives on Negley Avenue?
How many customers were born from 2001 onward?
Which instructors live in zip code 15217?
Which classes (by name) have the highest number of reservations?
How many customers have registered for a class on Wednesdays?
What are the three most popular class names on Saturday?
How many classes did Pablo Brough sign up for?
How many customers have signed up for a class on January 8, 2021?
Which customers prefer the 45-minute class length?
Which instructor teaches the most classes?
Which instructor teaches Zumba?
Of all reservations for classes containing the word Cycle, which customer does not owe another fee besides the class price? (Note: This field is called “OtherFees.”) Create a calculated field to total both costs.
According to the reservations so far, how much money will the Combination classes generate?
Instructor Michael Nguyen is injured and has to cancel his class on Wednesday. Delete that record. How many classes does he have left to teach?
Which instructor will customer Margo Patterson get for her HIIT class?
How many classes are offered on Tuesday at 6 am and last 45 minutes?
Which day of the week has the most classes?

Apply key module concepts to the Pitt Fitness case:

Suppose you want to list information on all the classes that Pitt Fitness offers, including the day of the week, time, location, and length of class. To do this, you could create a query. What table(s) should you add to the query? If you use the Classes table instead of all the tables together, does it make a difference to the output? What if someone had never reserved a specific class?
The owner of Pitt Fitness is considering whether to consolidate his clubs and offer classes in only one location to ensure profitability. To explore his question, what query or queries would you create to answer this business strategy question? In the following exercises, you use data in the Sports Physical Therapy database shown in Figures 1-21 through 1-24 in Module 1. When you use Microsoft Access to answer these questions, make a copy of the original database to create the queries to answer these questions. In each step, use QBE to obtain the desired results.

HINTS (ignore & or and used anywhere - this is also just a hint and not the only way you can solve it):

Q1: Use LIKE & *
Q2: Use >= and COUNT (optional)
Q3: =
Q4: GROUP BY & COUNT
Q5: Use LIKE & *
Q6: GROUP BY, COUNT, WHERE (where "WHERE" is used make sure "Show" field is unchecked i.e. not ticked) and shorting: descending
Q7: AND (just refers to criteria check in 2 locations)
Q8: =, GROUP BY & COUNT
Q9: =
Q10: GROUP BY & COUNT
Q11: GROUP BY & WHERE

More hints:
Q12: Like "*Cycle*"; For OtherFees - you can update Reservations table if you like to make it all 0 and then use "=0" for condition or as is you can check if the field contains null value; here, you will also need to create a calculate field. This is how you can do it:
TotalCost: [Field1]+[Field2]

It is to be added under a new field. "TotalCost" can read anything which you want that field to be called as. While the other elements in the [...] are the field names which you want to add. Update it as is required.