An Exhausted Students Guide To SQL Basics

If you’re reading this then it’s safe to assume that you are exhausted from learning how to code all day. I can definitely relate and sometimes after spending hours trying to get something down, you just want certain information straight to the point. Well, thats why I created this. I want to give you the SQL basics straight to the point so you can grab what you need and get going.

(Everything covered in this will be done in sqlite3)

Let’s Get Started!

Topics That Will Be Covered:

• Creating A Table
• Alter Table
• Drop Table
• Inserting Data In A Table
• Selecting Data From A Table
• Updating Data
• Deleting Data From A Table
• Aggregate Functions
• Grouping And Sorting Data.
• Access Previous Databases

Creating A Table

Step One

To get into SQLite3, type the following in your terminal. Feel free to use any name that suits your database and table.

sqlite3 (database_name).db

EXAMPLE

sqlite3 avengers.db

This is the database we will store everything in. (The table is not yet made)

Step Two

When we create tables, we need to have column structure such as

CREATE TABLE table_name (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);

(Primary Key means it is auto-incrementing)

All the lowercase words (id, name, age) will be the name of the columns. The CAPITAL words is how it will be defined. The id will be in numbers so we can keep track of how much data there is. The name will be in words which is why TEXT is written next to it and the age will be a number so we put INTEGER to define it. Click Here to learn about the four basic datatypes.

EXAMPLE

CREATE TABLE avengers (
id INTEGER PRIMARY KEY,
realname TEXT,
alias TEXT
);

Step Three

To check if everything worked, type the following in the terminal.

.tables

You will know when everything is done correctly when you see the name of the table show up like so:

You can also check the table structure by typing the following into the terminal.

.schema

Step Four

Now that you have created the table, you can exit sqlite3 by typing

.quit

YAY! You’ve done it. You created your first table in SQL

EXTRA INFORMATION

• There are four basic categories of datatype

TEXT - ALPHANUMERIC CHARACTERS
INTEGER - WHOLE NUMBERS
REAL - DECIMALS 
BLOB - HOLDS BINARY DATA

• To add a row after you have already created a table, type:

ALTER TABLE (table_name) ADD COLUMN (column_name) (text, integer, etc)

EXAMPLE

• To drop a table you have created type the following:

DROP TABLE (table_name)

INSERTING DATA

• To insert data into a table, follow the syntax

INSERT INTO (table_name) (date1, data2, data3) VALUES ('VALUE1', 'VALUE2', 'VALUE3');

EXAMPLE

SELECTING DATA

• To get ALL of the data from the table, type

SELECT * FROM (table_name);

All the data put into the table will be displayed

EXAMPLE

• To select just one or more columns (but not all) follow the syntax

SELECT (column name(s)) FROM (table_name);

EXAMPLE

• Selecting data based on a condition

SELECT * FROM (table_name) WHERE (column) = (value) ;

EXAMPLE

UPDATE DATA

• To update data, follow the syntax

UPDATE (table_name) SET (column) = (new_value) WHERE (column) = (value);

EXAMPLE

DELETING DATA

• To delete data, follow the syntax

DELETE FROM [table name] WHERE [column name] = [value];

EXAMPLE

Aggregate Functions

• To find the Average:

SELECT AVG(column_name) FROM (table_name);

EXAMPLE

• To find the Count:

SELECT COUNT(column_name) FROM (table_name);

EXAMPLE

• To find the Max:

SELECT MAX(column_name) FROM (table_name);

EXAMPLE

• To find the Min:

SELECT MIN(column_name) FROM (table_name);

EXAMPLE

• To find the Sum:

SELECT SUM(column_name) FROM (table_name);

EXAMPLE

Grouping And Sorting Data

• ORDER BY()

SELECT (column_name) FROM (table_name) ORDER BY (column_name) ASC;

EXAMPLE

• LIMIT

SELECT * FROM (insert table_name) ORDER BY(insert column_name) DESC LIMIT (insert number);

EXAMPLE

• ASC/DESC

ACCESS PREVIOUS DATABASES

If at anytime you want to reopen a previously made database, type

.open (database_name).db

Then type

SELECT * FROM (table_name)

and it will show you all the data that was previously stored in that table.

And you are done! YAY! I hope that was helpful and I accomplished my goal of making sure this was as straight to the point as it could be! Hope you do well and good luck on your coding journey :).

Click Here if you are interested in my past article “The 100th Scraping Tutorial You’ll Probably Read”