Understanding SQL

To achieve these reports, you need to at least have a basic understanding of SQL, ideally you want to be able to create stored procedures etc. In time I may cover more of the basics of SQL as this is one of my areas of expertise with over 5 years’ experience working with Oracle, SQL Server and MySQL. I have created extremely complex SQL statements and stored procedures for many things from fuzzy matching to 1000 line + SQL statements for reporting, implemented data warehouses from scratch and much more.

There are some great learning resources for SQL including w3schools etc. and I won’t want to reinvent the wheel. The basic things you want to do for this are:

• Follow the instructions to install SQL Server or Oracle etc.
• Create a few tables
• Select statements of specific column
• Join a few tables together
• Probably worth creating a few views as easier in python to select from these 

Some sample code of a view / select statement 

	USE [RUNNING]
	GO
	/****** Object: View [dbo].[v_history] Script Date: 28/12/2017 08:52:17 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	ALTER view [dbo].[v_history] as
	select year(track_time) [Run_Year],
	month(track_time) run_month ,
	day(track_time) run_day ,
	g.[file_name],
	g.track_name,
	g.track_time,
	g.track_length,
	g.track_duration,
	g.track_max_speed,
	round(track_duration/60,0) len_min,
	case when [file_name] like '%Run%'
	then 'Run'
	when [file_name] like '%Walk%'
	then 'Walk'
	when [file_name] like '%Ride%'
	then 'Bike'
	end as exercise_type,
	[user]
	from GPX_HISTORY g
	Where 1 = 1
	GO

view raw selectexample.sql hosted with ❤ by GitHub