SQL BASICS

WHAT IS SQL

SQL, short for ​Structured Query Language. SQL is the language we use to communicate with databases, SQL can be used within many other languages such as Python or Go to create a link and method of accessing, searching and manipulating the data within the database. SQL is the most popular method of querying / requesting and changing data within a database, if you are handling data then you should be familiar with SQL.

In this section we look at some of the most popular SQL methods.

VIDEO BY: Danielle Thé from Glitch

OPERATORS

Before getting into selecting and manipulating data you should become familiar with some of the most common operators as seen in the table below.

SELECTING DATA FROM THE DATABASE

Using SQL to select data from the database, pulls data from the original database and creates a new report with just the data you have selected. It does not change the original data, changing data will come later in the delete, insert and update methods.

SELECT ALL

Select specified fields of data. Only return the columns that you want from a specified table.

SELECT column1, column2 FROM table

Example:
SELECT gameID, gameName FROM currently_playing

SELECT WHERE

Select data that meets a specified condition. For example select all players with a score greater than 10.

SELECT fields FROM table WHERE field (operator) condition

Example:
SELECT * FROM currently_playing WHERE gameID = 3

SELECT AND / OR

Two make a selection that requires more than one conditional parameter to be met.

SELECT fields FROM table WHERE condition AND condition

Example:
SELECT * FROM currently_playing WHERE gameName = "Pac Land" AND gameID = 3

SELECT BETWEEN

To select data between certain values.

SELECT fields FROM table WHERE field BETWEEN value AND value

Example:
SELECT * FROM currently_playing WHERE gameID BETWEEN 1 AND 3

SELECT WILDCARD

​You can create a search to find a field that contains a value within a string of values by using the wildcard. The wildcard use the % sign or * sign with some DB such as MS Access.

SELECT fields FROM table WHERE field LIKE %condition%

Example:
SELECT * FROM currently_playing WHERE gameName LIKE "%Land%" 

​In this example games such as PacLand should be found.

INSERT DATA INTO THE DATABASE

There are two main ways of inserting data into the database.  If you specify the columns where you want to insert data, then you should specify the data from each of those columns. If you do not specify the columns, then you should specify data for all columns(fields) with in the database.

Method 1: Do not specify the fields:
INSERT INTO table VALUES (‘value1’,’value2’,’value3’)

​Method 2: Specify the fields
If your first field is a Primary key that is auto generated you might not want to insert into that field, using the next method you can pass values into the fields stipulated.
INSERT INTO table ( field2, field3) VALUES (’value2’,’value3’)

Example:
INSERT INTO game_details (gameName, gameDeveloper) VALUES (‘Tetris’, ‘Pajitinov’)

UPDATE DATA IN THE DATABASE

To update a record within a table you can use the following SQL

UPDATE table SET field = value WHERE condition

Example
UPDATE session_details SET score = '2175' WHERE gameID = 'G001'

DELETING DATA FROM THE DATABASE

To delete a record from a table you can use the following SQL

DELETE FROM table WHERE condition

Example:
DELETE FROM game_details WHERE gameName =  'Tetris'