Basic SQL cheat sheet

I have been looking for a new job for a while now and every job post I see requires SQL. I try to review it every couple of days to stay on top of it, so I figured that creating a cheat sheet for me and others to refer to would be a big help.

First thing that we need to do to start is create a table. This is very easy to do, simply input

CREATE TABLE name_of_table (id INTEGER PRIMARY KEY, name_of_row1 TEXT, name_of_row2 INTEGER)

I think this makes sense but just incase, once you name the table, you have to add the rows and what type of data the rows will hold (integer, text etc.). Usually every item is unique and has some type of unique number or label and that is going to be the primary key column.

Next we want to add data into the table that was created. This is again very simple to do.

INSERT INTO name_of_table VALUES(value_for_id, value_for_name_of_row1, value_for_name_of_row2)

Now we need to figure out how to select things in our SQL database.

SELECT * FROM name_of_table

This will show us the whole database, but most of the time we only want certain columns and in certain orders. To order our list by a certain row we need to use ORDER BY

SELECT * FROM name_of_table ORDER BY name_of_row1

Now we only want the data that is higher than 5 in name_of_row1 but still in order. You can also find things <, =, IN, BETWEEN and NOT.

SELECT * FROM name_of_table WHERE name_of_row1 > 5ORDER BY name_of_row1

Finally is aggregating data and grouping the data. By aggregating the data we can see the SUM, MIN, MAX, AVG, COUNT, and STDEV. Grouping the data allows us to see the data by groups instead of the whole table. Here is an example of where we get the sum of name_of_row2 but grouped into name_of_row1

SELECT name_of_row1 SUM(name_of_row2) FROM name_of_table GROUP BY name_of_row1