Tuesday, February 10, 2015

SQL Overview / Study Guide

A little bit about everything in SQL! This is more of a study guide/collection of resources for developers with some resources I think are very good. I will give a outline / light overview and show some online resources with great examples. Friends who are new to development are not sure sometimes what the scope is to know about SQL. I would put a heavy importance on the SQL SELECT, UPDATE, INSERT, DELETE and Joins/Unions for a beginner and the rest is also useful but are more specific to certain tasks and depends on what type of development you do (i.e. some companies use SSAS .. others don't.. same for T-SQL and stored procedures).

Most common things you will do: Queries.
Queries let you manipulate data by fetching pieces you need, changing data, deleting it, adding tables.. basically everything you would think to do in an Excel sheet to your data but this is how to use the SQL language to do the manipulating for you.
Every possible (almost?) type of SELECT with table examples: https://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx
INSERT, UPDATE, and DELETE: https://msdn.microsoft.com/en-us/library/bb243852%28v=office.12%29.aspx

If you are new to SQL and used to programming.. keep in mind Strings for column values are in 'single quotes', not "double quotes"!!!!

Some more basics.. how to put data together:
Join: Make a new table out of 2+ tables by finding something in common to "join" on.. and you get to pick the columns you want the new table to have. There are different types of Joins that will determine if you return a table that has matches on all the tables (inner join) or you want to have all the matches on one table to the other one and nulls on the table without data to fill in (outer join). Let's say you have a Person table and an Address table. The Person table has a name and an Id. The address table also has the corresponding Id. You want all of the people's zip code. You would do a join to make a new table that joins on the Id and then now you have access to each person's zip code in a new table.
Union: Grab all the columns you want to make the new table, and find something in common between the 2+ tables to "join" on.

View in SQL. Its a virtual table based on a query you made before that you can save and be able to access easily.

Normalization: This is basically a design pattern for your SQL tables so you can increase your efficiency over the long term. There are different forms of it. You want to tend towards 3NF at most places, but of course it depends on your system and what it's for / size / etc.

Indexing: Think of a book index.. much easier to find what you're looking for in your textbook? Same thing in SQL databses, indexes help you find things quicker!

Primary Key: Guarantees the unique identifier of your table. You don't have to put one, physically.. but you should put one every single time in the case you need it (which is likely). Just make a rule, always make a primary key.
https://msdn.microsoft.com/en-us/library/ms179610.aspx Foreign Key: the key that references a column (usually primary key) of another table and keeps integrity of table.

Stored Procedures: Reuseable code or queries you can pass parameters into. Common practical use is for data validation (integrated into the database) or access control mechanisms

T-SQL: stands for Transact SQL. I think of this as a plugin to SQL.. kinda like how jQuery is to JavaScript or having a GPS built into your car.. y'know.. cool extras. This lets you setup local variables, do if/else statement, other very very very useful things...

SSAS: This is relatively complex to setup, but not bad to use. Usually used with a HUGE HUGE amount of data that could not be handled otherwise by other means. You basically import all of your data from your site.. clicks by date, number of registrations, unique users, anything you like..! You make "cubes" out of them which has dimensions like dates, the mentioned variables (like clicks etc). You can filter your results. And then off to deploy a query which can be plugged into a chart or other form of display.

Great site to practice (free to register, and you can work on practical word problems where you have to enter the correct SQL query!): http://www.sql-ex.com/