Skip to main content

SQL Resources, or Show Me The Data!

As I am pretty sure was said, loudly and repeatedly in the movie Jerry Maguire: “Show Me The Data!”
It is the Data Scientists Mantra...
Data is in our name and really you need data to do your work, and we often have a singular place to store and find all our data (a data warehouse) for the work we do. But of course, the better the tools you have to easily group it, subset it, order it and otherwise transform it, the more insights you can reveal with the rest of your skills.
To command all the power that the data warehouse offers and that your analytical work deserves, having a solid grip on SQL (Structured Query Language) is key. It will make your life easier, happier, and simply more wonderful.
So, here are some tools to aid in that quest.

Books

First of all, I would like to introduce you to O’Reilly books, mostly because of 2 titles I have found repeatedly useful:
  • SQL Pocket Guide – A Guide to SQL Usage, this slim volume helps with the details of syntax and all the details I can’t remember. Less than 200 pages with a good index. Current edition is the 3rd, and it’s getting a little dog-eared.
  • SQL Cookbook – This provides a list of various tasks you’re likely to need to accomplish and for each, the ways to get it done. Examples of some of the task descriptions: Retrieving a Subset of Rows from a Table; Finding Rows That Satisfy Multiple Conditions; Concatenating Column Values; Transforming Nulls into Real Values; Searching for Patterns
O’Reilly offers a good deal if you want both the paper and eBook format of a title, and they are very well edited and geared towards answering questions.


Online tutorials

There are a number of online resources which offer training in a variety of formats (multimedia, written word, etc.) and the best also include support for actually working through problems online.
  • Khan Academy offers audio/video training with exercises at: https://www.khanacademy.org/computing/computer-programmi ng/sql – These have the trademark depth and quality of all the Khan academy offerings along with exercises and grading and progress tracking.


  • Tutorialspoint offers a written presentation along with diagrams and a coding ground  to practice what you are learning. http://www.tutorialspoint.com/sql/index.htm
  • This is more of a show it, do it tutorial arrangement, small bites of information with the chance to try out each thing presented in-line. http://sqlzoo.net/wiki/SQL_Tutorial
  • A SQL tutorial, from a data scientists perspective for data scientists: http://downloads.bensresearch.com/SQL.pdf

Ephemera

These are some resources I came across that talk about the intersections of R and SQL
  • SQL and R  are allies: https://www.simple-talk.com/sql/reporting-services/makin g-data-analytics-simpler-sql-server-and-r/
  • Reaching into R dataframes with SQL: http://www.burns-stat.com/translating-r-sql-basics/
  • There is a SQL Playground on-line where you can try out creating databases and using a range of SQL manipulations at http://sqlfiddle.com/. It’s got a lot in common with R Fiddle (at http://www.r-fiddle.org/#/)

Comments

Post a Comment

Popular posts from this blog

Let's Not Mess Around with Security on our Personal Systems Either!

Essential Security Practices for Your Personal Systems Ensuring a minimal level of cybersecurity, privacy, and availability on your personal systems means you need to manage the following essential practices. This is a brief overview of recommendations from sources like CISA, NSA, etc., focused on personal laptop, phone, and other systems' security. Anti-virus  I've found you'll get the best anti-virus protection and usability from a paid product - I've always had good luck with Norton labeled products. If you are looking for current vendor offerings see:  https://www.pcmag.com/picks/the-best-antivirus-protection Regardless of whether you choose to use a commercial product or open-source anti-virus tool, it is absolutely something you need to use. This is the minimally needed level of system security. Once installed, ideally, it should be invisible until there's a security problem it can't prevent or solve.   Backups You need to have at least a minimal level of ...
Post a Comment
Read more

RACI, Cybersecurity and NICE Framework

The NICE framework from a RACI point of view The NICE framework ( NIST SP 800-181 rev. 1) established a standard approach for describing cybersecurity work, in order to help stakeholders share a common language and ideally improve how to identify, recruit, develop and retain talent. It breaks down cybersecurity work role categories into: Oversight and Governance; Design and Development; Implementation and Operation; Protection and Defense; Investigation.  Which is very cybersecurity-centric and not related to common tools for project management within companies. Especially smaller enterprises that do not have dedicated people to mange and coordinate cybersecurity needs. A  RACI chart  is   a project management tool used to define and clarify roles and responsibilities within a project team.   It stands for Responsible, Accountable, Consulted, and Informed, and visually represents who is responsible for what, who is accountable for the outcome, who needs to be c...
Post a Comment
Read more

Typescript - It might not be easier, but but it's surely different

Typescript is a statically typed language, that is a superset of JavaScript. I've had the discussions and debates about that aspect of the language. I am all for static typing. Any way my tools can help me be better is alright by me. So I avoid the ' any ' type designation and make sure I have guards on ' unknown ' types, as much as I can.  Any  does not carry any useful type information, while unknown does, and allows it to enforce type checking.  Anything can be assigned to a variable of type unknown , but an unknown value cannot be assigned to variables of other types without explicit type assertion or narrowing. Similarly, no operations are permitted on an unknown value until its type is refined. This behavior ensures type safety and prevents runtime errors. (Refined with help from google). I bring this up because I was arguing with the compiler recently because I'd assumed both made no use on any type information in any circumstance - because I haven't ...
Post a Comment
Read more