Q&A about the site for artists and crafters
/* GENERAL NOTES ON STYLE:
Developing a style while coding in SQL is something that sort of naturally
occurs. When I started out, Dave encouraged me to follow his style as everyone
on our teams' is similar and it makes it easier to share code with one another.
Everyone has their quirks, but there are some common attributes (like a vertical
progression) that we all adhere to. I've highlighted some things that are not
consistent amongst the team, but are things I've adopted because I think it
makes the code look cleaner.
DROP TABLE IF EXISTS #got
/* NEW SKILL 1b:
When creating a new table it will be important to include a DROP TABLE IF EXISTS
statement to your code. This ensures that when the query is re-run, you won’t get
an error telling you your created table already exists.
/* KATE STYLE NOTE 1:
Some people list all their drop table statements at the top of their code -
I did this too at the beginning, but eventually found that embedding them made
it easier to trouble-shoot things as I was working on a query
Post_Id = p.Id
/* KATE SYTLE NOTE 2:
When it comes to labeling columns I always list an adjusted name in front - I
noticed you did not and that's totally ok. For me I prefer it in front as it's
easier for me to find a datapoint if I have to reference past queries for something
,Tag_Id = t.Id
/* KATE STYLE NOTE 3:
I also do my best, sometimes it is unavoidable, to title columns in a way that
avoids having to use the brackets . It doesn't really matter one way or another,
but I find having to type out the brackets is annoying and I'm sort of lazy, LOL.
An easy option to avoid the brackets, but not have everything run together is to
add an underscore between words
/* NEW SKILL 1a:
To create a table from query results use an INTO statement. This will save the results
of the query you’ve run to a table so that the information can be joined with another
table or manipulated in some way. The most important thing to remember when using an INTO
statement is that no two columns can have the same name.
/* CODING TIP 1:
Temporary tables will be your greatest pal. When I got started I always tried to
get everything I could into one query, but soon found that it's sometimes easier
to build as you go. Inputting results into a temporary table - achieved by using
an INTO statement with a #[name] - is a lifesaver. If you ever need to reference
a temp table outside the immediate query you're working on you have create a 'global'
temp table by using two hastags instead of one.
FROM tags t
JOIN PostTags pt ON pt.TagId = t.Id
/* NEW SKILL 2:
Joining tables is an essential skill and one that I found tricky at first. Here I am using
a standard JOIN (also called an INNER JOIN). INNER JOINs combine the contents of two tables
where they match on a common key. There are many types of joins, but the two we tend to use
most frequently are JOIN and LEFT JOIN. LEFT JOINs (also called LEFT OUTER JOIN) combine the
contents of two tables even when the common key does not match - these are helpful to use in
situations where only some results include a particular parameter. For example, looking at
the Posts table you’ll note that not all posts have a tag. If I wanted to join the Posts table
with the Tags table, but I didn’t want to lose posts that didn’t have a tag, I would use LEFT
JOIN Posts p ON p.Id = pt.PostId
/*KATE STYLE NOTE 4:
I always use 1=1 after a WHERE clause. It's something Dave does and I just picked
up from him. I like it because it creates a nice flow in the look of the code.
It's not necessary though.
t.TagName LIKE '%game%of%thrones%'
t.TagName LIKE '%a%song%of%ice%and%fire%'
/* CODING TIP 2:
Using parenthesis in a WHERE clause can be tricky. You executed this very well!
I generally try to avoid having more than two arguments when I use them since I've
been burned in the past by one to few parenthesis. When I have a number of
arguments to include I will usually use a CASE statement (see below)
DROP TABLE IF EXISTS #ff
/* NEW SKILL 3:
Using an asterisk (*) after a SELECT statement will return all columns from the
requested table. This is handy when you want to see what columns exist in a table
or when you need everything and don’t feel like typing it all out.
FROM #got g
AND CASE WHEN g.Title LIKE '%fan%fic%' THEN 1
/* NEW SKILL 4:
CASE statements are super handy - they can be used in SELECT statements and WHERE clauses.
Here we’re using a CASE statement in the WHERE clause to isolate different ‘populations’ to
include in the final result. This is great when you want to include results that meet
WHEN g.Tags LIKE '%fan%fic%' THEN 1
WHEN g.Body LIKE '%fan%fic%' THEN 1
--210113 ksrosch added, I hadn't initially included a review of the Post's
--body, but it's essentially an elaboration of the title so I think we
--should include it
/* KATE STYLE NOTE 5:
I add alot of notes to my code as I work. Sort of like breadcrumbs to help me
remember why I made a specific change or if I had to look something up to
confirm my understanding. Most people don't do this, but I find it's helpful
since my memory is getting shotty in my old age ;)
END = 1
/* CODING TIP 3:
When searching through a string I like to use to lowest common denominator
(does that work here?) in terms of phrasing. For example, in this scenario,
searching %fan%fiction% would exclude any posts where the more colloquial term
fanfic is used. Strings are tricky since they can really include just about
anything - in real life I'd generally try to avoid using them as a sole means
of identification since there's no guarantee that people all use the same terms
or can spell...HAH
CountofPosts = COUNT(*)
/* NEW SKILL 5b:
For the COUNT function you can count the results of a particular column or the
results of the rows in your overall data. Here I’m counting the rows in my overall
data so I’ve used COUNT(*) instead of referencing a specific column.
,AverageScore = AVG(f.Score)
/* NEW SKILL 5a:
Functions work in SQL much the same way they would in other applications like Excel.
The biggest difference is that when you have a query that has both columns and functions,
you’ll need to include a GROUP BY statement, but we can get into that later.
Here we’re using the AVG function and since we’re hoping to get the cumulative average for
every post in the #ff table I’ve not included any columns. For the AVG functions you
should select a particular column to average that includes numerical values.
FROM #ff f