SQL Server 2019 Samples - Scott M Becker Consulting

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

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