SQL FOR LAIKIDIOTS

[using sql lite] [[not in html or javascript or css or whatever!!!]]

i don't have or care enough to install sql software so instead i will be representing the data in html tables.

Making a Table

for this example, i will be listing my gaming consoles, my rating of them, and the amount of games that i own for them.
CREATE TABLE systems (id INTEGER PRIMARY KEY, name TEXT, rating TEXT, games INTEGER);
INSERT INTO systems VALUES (1, "3ds", "5 stars", 50);
INSERT INTO systems VALUES (2, "Vita", "5 stars", 35);
INSERT INTO systems VALUES (3, "Ps2", "4 stars", 10);
INSERT INTO systems VALUES (4, "Wii", "5 stars", 20);

here's the basic template:
CREATE TABLE *table name* (*value name* *TYPE*, ect);
INSERT INTO *table nam* VALUES (*first value for value 1*, *first value for value 2*, ect ect);

Querying Tables

[fancy word for fetching data!!]

to fetch all data from the table, use the following command:
SELECT * FROM systems;

to fetch only a certain value, replace the * with that value.
SELECT name FROM systems;

to order the rows by a value, add the ORDER BY command.
SELECT * FROM systems ORDER BY games;

to filter out data who's values exceed certain integers, add the WHERE command.
The below command will tell me what systems i own more than 20 games for.
SELECT * FROM systems WHERE games > 20;
we can also run the following command to sort our systems with more than 20 games by amount of games.
SELECT * FROM systems WHERE games > 20 ORDER BY games;

Aggregating Data

[aka aquiring the total quantities of values and other things!!]

say i wanted to know how many games that i owned for all my systems combined. i could run the following command:
SELECT SUM(games) FROM systems;

if i wanted to know what the maximum amount of games for one system that i owned, i'd run
SELECT MAX(games) FROM systems;

if i wanted to know what about of games i have for systems that share ratings, i could run
SELECT SUM(games) FROM systems GROUP BY rating;

but wait! this doesnt display what rating corrisponds to what amount of games!
i present an easy fix:
SELECT rating, SUM(games) FROM systems GROUP BY rating
but how does this code work? it looks mad confusing!
first sql grouped the systems by rating. then, it proceeded to sum up the number of games in each group.
finally, it selected the first rating value out of each group, but since each group was already sorted by rating, the rating values in each group were the same.
[rating values in '4 stars': 4 stars]
[rating values in '5 stars': 5 stars, 5 stars, 5 stars]

complex queries!!!

more complicated queries await!
for these following examples, let's first make a new table to fit our needs.
note-- autoincrement auto-generates a unique number for each entry added.
this removes the need for us to specify the number.
CREATE TABLE gaminglog
(id INTEGER PRIMARY KEY AUTOINCREMENT,
game TEXT,
minutes INTEGER,
enjoyment INTEGER,
snackssnacked INTEGER);

INSERT INTO gaminglog(game, minutes, enjoyment, snackssnacked) VALUES ("persona 2", 60, 95, 4);
INSERT INTO gaminglog(game, minutes, enjoyment, snackssnacked) VALUES ("catherine", 40, 90, 6);
INSERT INTO gaminglog(game, minutes, enjoyment, snackssnacked) VALUES ("planet laika", 120, 100, 12);
"LAIKA, why did you put all of the table catagories where the 'VALUES' goes?"
it is so i don't have to specify the primary key, or number of value, because the AUTOINCREMENT does it for me!

filtering by 2 or more values! it is possible! [OR, AND, WHERE]

say i want to find out what games i played for more than 30 minutes AND ate more than 5 snacks during. enter the below command!
SELECT * FROM gaminglog WHERE minutes > 30 AND snackssnacked > 5;
the AND lets you add more than one condition to be met!!!! [like an if statement]

OR we could use the 'OR' operator!
if i want to find out what games i either enjoyed more than 95 percent of or ate more than 6 snacks during, i can use the OR operator.
SELECT * FROM gamninglog WHERE enjoyment > 95 OR snackssnacked > 6;
add as many AND or OR as you want!!! go ham!!! keep in mind, AND always takes priority over OR.

querying in subqueries [IN, NOT IN, LIKE]

say i want to find all games by title, so i type the super long command below:
SELECT * FROM gaminglog WHERE game = "persona 2" OR game = "catherine" OR game = "planet laika";
so long!! but we can make it shorter, using the IN operator!
SELECT * FROM gaminglog WHERE game IN ("persona 2", "catherine", "planet laika");
you can also use the NOT IN operator to do the opposite!
the IN and NOT IN operators lets you specify many values in the WHERE operator efficiently.

comparing 2 tables? it's also possible! need new table for this one
CREATE TABLE laikafav (id INTEGER PRIMARY KEY, game TEXT, reason TEXT);
INSERT INTO laikafav(game, reason) VALUES ("catherine", "good game");
INSERT INTO laikafav(game, reason) VALUES ("planet laika", "it has my name, laika, in it");
now i want to see what games from my gaminglog table corrispond with my laikafav table. how do that i?
subqueries!!!!!!!!!!!!!!
SELECT * FROM gaminglog WHERE game IN (SELECT game FROM laikafav);
you put the query for the laikafav into the IN for the gaminglog.

what if i forget the full name of a game, but still want to see if it's in both tables? the LIKE operator!
SELECT * FROM gaminglog WHERE game IN (SELECT game FROM laikafav WHERE game LIKE "%laika%");
the % makes the program look for the word 'laika' in every row.

AS

the AS operator gives an alias, or a nickname, to a column temporarily!!
if i ran this code below, it would rename the column for listeningtime to totallisteningtime, which would make sense,
as for this example, everytime i listen to a song the total time that i listened for is added as new data,
and i want to find out the total time that i've listened to a song for.
SELECT song, SUM(listeningtime) AS totallisteningtime FROM playlist GROUP BY song;
if i don't add the AS totallisteningtime, the column will not be renamed. the operator must be added each time a command is run.

AGGREGATE FUNTIONS [COUNT, MAX, MIN, SUM, AVG]

all of these are self explainitory, except COUNT.
COUNT returns the amount of rows in the criteria.
MIN finds the lowest value in the given criteria. MAX does the opposite.

GROUP BY

a command used to group a set of results! GROUP BY is used with aggregate funtions [COUNT, MAX, MIN, SUM, AVG].
ex. SELECT games, AVG(playtime) AS avgplaytime FROM gamingstats GROUP BY games
this command will calculate my average playtime per game, and output the results sorted by game.

HAVING

HAVING is used instead of WHERE in aggregate functions.
note-- aggregate means to collect multiple values to return as one value.
there is one problem with our example in the AS section above. if i were to filter it to show, say, any songs that i've listened to
for more than an hour [60min], it wouldn't output anything. this is because it processes one row at a time,
and is therefore looking for any one time that i listened to a song for more than an hour, and not the total time.
HAVING can fix that! HAVING looks at the aggregated [grouped] value, and not the preaggregated values like WHERE.
SELECT song, SUM(listeningtime) AS totallisteningtime FROM playlist GROUP BY song HAVING totallisteningtime > 60;

CASE, WHEN, THEN, ELSE, END

CASE is similar to an 'if' from js. WHEN defines the parameters, and THEN creates a new column for these filtered results.
SELECT games, score,
  CASE
    WHEN score > 500 THEN "epic score!"
    WHEN score < 500 THEN "virgin score!"
    ELSE "big 500!!"
  END as "score rating"
FROM gamingstats
the END operator names the columns in which the WHEN and ELSE data is stored.

also notice how i selected both the games and score columns.
this is just so when my data is returned, i can see what game corrisponds to what score.

this is complicated, so below is a representation of what this data would look like in sql with
an imaginary dataset representing my data.

games	score	score rating
planet laika fight 1	550	epic score!
planet laika fight 2	430	virgin score!
planet laika fight 3	330	virgin score!
planet laika fight 4	500	big 500!!

relational queries

JOIN, ON [+ crossjoins]

say i have 2 tables, each about my music collection. one has each song, the file name, and the size of the file, and the other has the each song and it's length.
these are very similar, but also sensical to have splitted up. if i want to see both at once, i can run the following:
SELECT * FROM musicfiles, musicmetadata;
this is called a crossjoin. it tacks on musicfiles' row one to musicmetadata's row one, musicfiles' row two to musicmetadata's row two, ect.
this query will show all the data, even if it repeats.

implicit inner joins are much more useful.
SELECT * FROM musicfiles, musicmetadata;
WHERE musicfiles.song = musicmetadata.title;
the '.song' and '.title' would be replaced with whatever the column in the table with the matching data is named.
this might be efficiant, but still there is a better way.

explicit inner joins!!!. this one actually uses the JOIN operator.
the JOIN operator [with the help of ON] specifies what columns are being matched. this way is the most efficiant.
SELECT * FROM musicfiles
JOIN musicmetadata
ON musicfiles.song = musicmetadata.title;

LEFT OUTER JOINS

left joins show all the rows from the left table, and any matches from the right table.
for this example, i have a table with all the songs in my playlist and another table with ratings for some of the songs.
i want to see the whole list of songs, even if some of the songs don't have ratings.
LEFT OUTER JOIN can achieve this!!
SELECT playlist.songname, playlist.artist
FROM playlist
LEFT OUTER JOIN ratings
ON playlist.songname = ratings.songtitle;
the LEFT tells the program to fetch all results from the left table, and only the matching results from the right.
any left table entry with no matching right table entry will instead read NULL.

RIGHT OUTER JOINS are pretty much the same, but swapped.

FULL OUTER JOINS display both tables completely, matching data where it can and NULL-ing where it can't.

SELF JOINS

a regular join, but from a table to itself!!
we must create an alias for each time we ask the table for itself, lest the program gets confused.
SELECT dudes.firstname, dudes.lastname, friends.firstname
FROM dudes
JOIN dudes friends
ON dudes.friendid = friends.id
the 'friends' above is an alias that i'm giving to the called dudes table, to distinguish it from the calling dudes table.

combining JOINS

JOINS can be combined, just put them one over another.

modifying databases [loserville!!!!]

changing rows [UPDATE and DELETE and SET]

nothing is permanant!!! we can update and delete data in our table real easy!!
UPDATE game_reviews SET review = "this game is the absolute best game that i have ever set my retinas in my entire 17 years on this planet, which will never compare to the planet that is planet laika." WHERE game = "planet laika" AND review_id = 1;
the UPDATE operator tells the program to update some data, and the SET operator tells it what to change the data to.

we can also delete just as easy!!
i really want to delete my old catherine review in my game_reviews table, because it is old.
DELETE FROM game_review WHERE game = "catherine" AND review_id = 2;
reminder to always check that you're only deleting or updating the intended data!! it is very easy to lose data by accidentially deleting it!!!

modifications after creation [ALTER TABLE, DROP TABLE]

after creating our table, we can still change it!!! sneakily!!! using ALTER TABLE!!
ALTER TABLE game_reviews ADD favorite_character TEXT default "undecided";
INSERT INTO game_reviews (game, review_id, review, favorite_character) VALUES ("planet laika",
1, "doggie game rocks!!!", "yolanda and feing");

i can also just delete a whole table!! like the entire thing!!!!
DROP TABLE badgamereviews;
this will result in an error when attempting to access this table.