KevRichardTechnology and Digital Communications

So its been a while again, but push comes to shove and I’m determined to get back on to making a return to this blog. I’m on a bit of a skills development fix at the moment and decided here would be a great spot to report back on what I’ve been doing and to create some documentation on the key learnings.

I recently went through a beginners course on SQL through Code Academy (link to the course here) which I found really interesting and potentially useful for future endeavors.

SUMMARY 

What is SQL? Known as a ‘Standard Query Language’ its a method of organizing and summarizing data. My general thinking when it comes to this is that taking a structured dataset it allows the user to summarize and manipulate the data. In the case of multiple datasets, it connects them through a common tie so the user can get a better understanding of how they interact.

Why is it potentially useful? I’ve experienced SQL in the context of a data tool where the language organizes raw data in such a way that its  a readable output for the end-user. When thinking about it in terms of even a basic excel spreadsheet, the language allows for the data to be summarized and reformatted saving the user from having to do a lot of manual work.

Below is a select set of terms from the course and example code:

TERMS/COMMANDS:

; is used as an ‘End statement’ and is placed at the end of a command. It tells the system to move to the next query statement.

There are three basic functions of the SQL language: To BUILD, ANALYZE or JOIN data tables.

 BUILD:

CREATE TABLE: is a command telling the system to build a new data table.

table_name(***): labels a table for use in queries and joining with other tables.

column_1 data_type: defines what data goes into a particular column (can be text, date, number etc.) These can be layered up to create a series of columns

INSERT INTO: is a formatting command used for define the set columns. Following this, column titles can be added ex: (id, name, age).

VALUES: This is a command used to enter in individual entries. Its formatted as VALUES (row value 1, row value 2…);

UPDATE: used to change entries in a table. Using ‘SET’ and ‘WHERE’ entries, data within the table can be altered

ALTER TABLE: Where as UPDATE alters an entry, ALTER TABLE allows the user to change or add data columns.

DELETE FROM: Deletes rows from a table.

Example Code:

CREAT TABLE table_name (
 column_1 data_type,
 column_2 data_type,
 column_3 data_type
 );

INSERT INTO celebs (id, name, age)

VALUES (1, 'Justin Bieber', 21);

 SELECT *FROM celebs;

UPDATE celebs
 SET age = 22
 WHERE id= 1;
 SELECT *FROM celebs;

ALTER TABLE celebs ADD COLUMN
 twitter_handle TEXT;
 SELECT *FROM celebs;

DELETE FROM celebs WHERE twitter_handle IS NULL;

ANALYZE:

SELECT *FROM ‘table name': displays all data from the labeled table the SQL interface. The * is used to notate ‘select the whole table’. Other commands such as ‘SELECT DISTINCT’ can be used to summarize a table further based on a column or row entry.

BETWEEN: Is a filter option for the use of numerical data.

ORDER BY: organizes a data set. A related command DESC/ASC(aka: Descending/Ascending) can be used to organize numbers by a set parameter.

LIMIT: Sets maximum number of rows pulled from a dataset.

GROUP BY: organizes data by a specific column entry (example: type of apple).

COUNT: summarizes numerical data by a category (example: how many entries contain X)

SUM: Counts up the numerical data of a particular column in a table ( Ex: How many impressions in total).

MAX(): finds the largest value of a criteria.

MIN(): finds the smallest value of a criteria.

AVG(): finds the average of a criteria.

ROUND(): rounds decimal numbers to a specific integer.

EXAMPLE CODE: 

SELECT* FROM movies WHERE imdb_rating >8;

SELECT * FROM movies
 WHERE name LIKE 'Se_en';

SELECT *FROM movies
 ORDER BY imdb_rating DESC;

SELECT price, ROUND(AVG(downloads),2) FROM fake_apps GROUP BY price;

JOIN:

JOIN: creates a common column between two tables which joins the data set together (ex: id_actormovie JOIN ID_Actor)

ON: states the parameter of the join.

Inner Join: joins entries when a join criteria is met.

Left Join: Combines two tables together side by side and provides a NULL value when the join parameters are not met.

AS: When joining a table this allows columns of the joined table to be renamed without affecting the original unjoined tables.

EXAMPLE CODE: 

SELECT

albums.name AS 'Album', albums.year, artists.name AS 'Artist'
 FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year >1990