SQL Server 2019 Samples
I wrote a Database (Hack My Health) in MS SQL Server 2019. I created 3 tables, inserted and queried the data.
You can view more code samples and results on my Github Page – The Project
Create a Database in SQL Server 2019
— Right click on Databases/ select New Database / Name The database / Click Ok
— Creating Oct_Vitals Table
CREATE TABLE dbo.oct_vitals
(
“date” DATE,
“weight” int,
“avg_weight” int,
“bmi” int,
“body_fat” int,
“muscle_mass” int,
“bone_mass” int,
“body_water” int,
“systolic” int,
“diastolic” int,
“heart_rate” int,
“oxygen” int,
“last_sleep” int,
);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-19’, 171.5, 172.8, 25.4, 24.8, 68.0, 8.8, 54.9, 99, 70, 71, 99, 7);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-20’, 172.6, 172.6, 25.6, 25.1, 68.3, 8.9, 54.7, 115, 80, 78, 99, 6.25);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-21’, 172.8, 172.6, 25.6, 24.2, 68.3, 9, 55.3, 121, 84, 78, 97, 8);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-22’, 174.5, 173.6, 25.8, 24, 68.7, 9.2, 55.5, 124, 86, 66, 97, 7);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-23’, 173, 173, 25.6, 24.5, 68.3, 9, 55.1, 113, 76, 73, 97, 7);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-24’, 173.3, 173, 25.7, 24.5, 68.4, 9, 55.1, 111, 78, 75, 97, 7)
SELECT *
FROM dbo.oct_vitals
date | weight | avg_weight | bmi | body_fat | muscle_mass | bone_mass | body_water | systolic | diastolic | heart_rate | oxygen | last_sleep |
10/18/2022 | 172 | 172 | 25 | 25 | 68 | 8 | 54 | 120 | 78 | 72 | 97 | 7 |
10/19/2022 | 171 | 172 | 25 | 24 | 68 | 8 | 54 | 99 | 70 | 71 | 99 | 7 |
10/20/2022 | 172 | 172 | 25 | 25 | 68 | 8 | 54 | 115 | 80 | 78 | 99 | 6 |
10/21/2022 | 172 | 172 | 25 | 24 | 68 | 9 | 55 | 121 | 84 | 78 | 97 | 8 |
10/22/2022 | 174 | 173 | 25 | 24 | 68 | 9 | 55 | 124 | 86 | 66 | 97 | 7 |
10/23/2022 | 173 | 173 | 25 | 24 | 68 | 9 | 55 | 113 | 76 | 73 | 97 | 7 |
10/24/2022 | 173 | 173 | 25 | 24 | 68 | 9 | 55 | 111 | 78 | 75 | 97 | 7 |
— Creating Oct_Rides Table
CREATE TABLE dbo.oct_rides
(
“date” DATE,
“ride_type” varchar(15),
“route” varchar(15),
“time” varchar(10),
“distance” int,
“avg_speed” int,
“max_speed” int,
“avg_cadence” int,
“max_cadence” int,
“avg_heart_rate” int,
“max_heart_rate” int,
“brpm_avg” int,
“brpm_max” int,
“elavation_gain” int,
“avg_power” int,
“max_power” int,
“ftp” int,
“total_work_kj” int,
“load” int,
“calories_burned” int,
);
— (revised the insert statement to be more efficient)
INSERT INTO dbo.oct_rides
(date, ride_type, route, time, distance, avg_speed, max_speed, avg_cadence, max_cadence, avg_heart_rate, max_heart_rate, brpm_avg, brpm_max, elavation_gain, avg_power, max_power, ftp, total_work_kj, load, calories_burned)
VALUES (‘2022-10-01’, ‘ROAD_CYCLING’, ‘DJP’, ‘2:15:31’, 32.14, 14.2, 26.4, 0, 0, 147, 175, 0, 0, 620, 84, 0, 0, 0, 0, 1330),
(‘2022-10-09’, ‘ROAD_CYCLING’, ‘SUN_SKI’, ‘1:52:20’, 30.51, 16.3, 23.2, 0, 0, 158, 179, 0, 0, 354, 108, 0, 0, 0, 0, 1483),
(‘2022-10-13’, ‘STATIONARY_BIKE’, ‘NA’, ‘0:45:01’, 13.5, 0, 0, 0, 0, 140, 163, 0, 0, 0, 0, 0, 0, 0, 0, 459),
(‘2022-10-15’, ‘ROAD_CYCLING’, ‘ZUBE_STOKES’, ‘2:49:06’, 42.99, 15.3, 24.4, 78, 107, 156, 175, 36, 46, 512, 88, 0, 0, 889, 0, 2096),
(‘2022-10-18’, ‘TRAINER’, ‘BARCELONA’, ‘0:52:11’, 13.06, 15, 20, 89, 104, 159, 171, 38, 44, 407, 103, 134, 0, 332, 31, 321),
(‘2022-10-19’, ‘TRAINER’, ‘AMSTERDAM’, ‘1:16:38’, 21.72, 17, 22.6, 95, 114, 164, 175, 0, 0, 157, 102, 218, 0, 472, 101, 471),
(‘2022-10-22’, ‘ROAD_CYCLING’, ‘ZUBE_STOKES’, ‘2:41:32’, 43.51, 16.2, 24.9, 80, 137, 155, 175, 36, 44, 577, 90, 529, 200, 913, 365, 1163),
(‘2022-10-23’, ‘TRAINER’, ‘MILANO’, ‘0:30:21’, 9.68, 19.1, 24.7, 100, 113, 163, 181, 0, 0, 75, 133, 274, 200, 243, 50, 242);
SELECT *
FROM dbo.oct_rides
date | ride_type | route | time | distance | avg_speed | max_speed | avg_cadence | max_cadence | avg_heart_rate | max_heart_rate | brpm_avg | brpm_max | elavation_gain | avg_power | max_power | ftp | total_work_kj | load | calories_burned |
10/1/2022 | ROAD_CYCLING | ZUBE_GROUP | 1:54:52 | 29 | 15 | 21 | 0 | 0 | 77 | 106 | 0 | 0 | 331 | 87 | 0 | 0 | 0 | 0 | 1424 |
10/1/2022 | ROAD_CYCLING | DJP | 2:15:31 | 32 | 14 | 26 | 0 | 0 | 147 | 175 | 0 | 0 | 620 | 84 | 0 | 0 | 0 | 0 | 1330 |
10/9/2022 | ROAD_CYCLING | SUN_SKI | 1:52:20 | 30 | 16 | 23 | 0 | 0 | 158 | 179 | 0 | 0 | 354 | 108 | 0 | 0 | 0 | 0 | 1483 |
10/13/2022 | STATIONARY_BIKE | NA | 0:45:01 | 13 | 0 | 0 | 0 | 0 | 140 | 163 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 459 |
10/15/2022 | ROAD_CYCLING | ZUBE_STOKES | 2:49:06 | 42 | 15 | 24 | 78 | 107 | 156 | 175 | 36 | 46 | 512 | 88 | 0 | 0 | 889 | 0 | 2096 |
10/18/2022 | TRAINER | BARCELONA | 0:52:11 | 13 | 15 | 20 | 89 | 104 | 159 | 171 | 38 | 44 | 407 | 103 | 134 | 0 | 332 | 31 | 321 |
10/19/2022 | TRAINER | AMSTERDAM | 1:16:38 | 21 | 17 | 22 | 95 | 114 | 164 | 175 | 0 | 0 | 157 | 102 | 218 | 0 | 472 | 101 | 471 |
10/22/2022 | ROAD_CYCLING | ZUBE_STOKES | 2:41:32 | 43 | 16 | 24 | 80 | 137 | 155 | 175 | 36 | 44 | 577 | 90 | 529 | 200 | 913 | 365 | 1163 |
10/23/2022 | TRAINER | MILANO | 0:30:21 | 9 | 19 | 24 | 100 | 113 | 163 | 181 | 0 | 0 | 75 | 133 | 274 | 200 | 243 | 50 | 242 |
— Creating Oct_Power_Meter Table
CREATE TABLE dbo.oct_power_meter
(
“date” DATE,
“route” varchar(25),
“left_balance” int,
“right_balance” int,
“total_stress_score” int,
“natural_power” int,
“intensity_factor” varchar(10),
“total_strokes” int,
);
INSERT INTO dbo.oct_power_meter
(date, route, left_balance, right_balance, total_stress_score, natural_power, intensity_factor, total_strokes)
VALUES
(‘2022-10-22’, ‘ZUBE_STOKES’, 60, 40, 84, 109, ‘0.546’, 11691),
(‘2022-10-23’, ‘MILANO’, 65, 35, 13.5, 104, ‘0.519’, 3036);
SELECT *
FROM dbo.oct_power_meter
date | route | left_balance | right_balance | total_stress_score | natural_power | intensity_factor | total_strokes |
10/22/2022 | ZUBE_STOKES | 60 | 40 | 84 | 109 | 0.546 | 11691 |
10/23/2022 | MILANO | 65 | 35 | 13 | 104 | 0.519 | 3036 |
— Multiple Joins
SELECT rides.date
,rides.route
,ride_type
,distance
,avg_speed
,weight
,bmi
,last_sleep
,left_balance as LB
,right_balance as RB
,total_strokes
FROM dbo.oct_rides as rides
INNER JOIN dbo.oct_vitals as vitals
ON
(
rides.date = vitals.date
)
INNER JOIN dbo.oct_power_meter as meter
ON
(
vitals.date = meter.date
)
WHERE vitals.date > ‘2022-10-13’ AND last_sleep > 6
ORDER BY date
date | route | ride_type | distance | avg_speed | weight | bmi | last_sleep | LB | RB | total_strokes |
10/22/2022 | ZUBE_STOKES | ROAD_CYCLING | 43 | 16 | 174 | 25 | 7 | 60 | 40 | 11691 |
10/23/2022 | MILANO | TRAINER | 9 | 19 | 173 | 25 | 7 | 65 | 35 | 3036 |
— 10-26-2022 UPDATE
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-25’, 174.6, 173.5, 25.9, 24.7, 68.7, 9.1, 55.0, 109, 77, 66, 97, 7.5);
INSERT INTO dbo.oct_vitals
(date, weight, avg_weight, bmi, body_fat, muscle_mass, bone_mass, body_water, systolic, diastolic, heart_rate, oxygen, last_sleep)
VALUES
(‘2022-10-26’, 173.3, 173.6, 25.7, 24.7, 68.4, 9, 54.9, 109, 82, 73, 97, 6.75);
INSERT INTO dbo.oct_rides
(date, ride_type, route, time, distance, avg_speed, max_speed, avg_cadence, max_cadence, avg_heart_rate, max_heart_rate, brpm_avg, brpm_max, elavation_gain, avg_power, max_power, ftp, total_work_kj, load, calories_burned)
VALUES (‘2022-10-01’, ‘TRAINER’, ‘LOMBARDY2’, ‘1:18:30’, 23.1, 17.7, 29.3, 93, 138, 170, 181, 0, 0, 394, 114, 195, 200, 536, 90, 536);
SELECT * FROM dbo.oct_rides
— I didn’t enter the correct date on the ride, so I needed to write an update script.
UPDATE dbo.oct_rides
SET date = ‘2022-10-26’
WHERE route = ‘LOMBARDY2’
INSERT INTO dbo.oct_power_meter
(date, route, left_balance, right_balance, total_stress_score, natural_power, intensity_factor, total_strokes)
VALUES
(‘2022-10-26’, ‘LOMBARDY2’, 0, 0, 45.4, 118, ‘0.589’, NULL);
SELECT * FROM dbo.oct_power_meter
To hire me click HERE
MY BRANDS:
To learn more about the Freelance Data Analyst and Cloud Engineering work I do, please visit XL Mobile Media
To learn more about the Digital Marketing and Analytics work I do, please visit XL Digital Media