SQL Queries

SQL stands for Structured Query Language (to not take anything for granted, query is a term meaning to retrieve data). It's a very useful for programmers when dealing with databases of all sizes.

There are a number of different database management systems that use SQL, I currently use PostrgeSQL and SQLite3. Each management system has their own specifics to how to access the data but for the most part they generally cross over.

For our example we will use SQLite3 and will go over a very simple database and introduce how to access the data so that you can go and traverse even larger and more complex databases in the future.

If you are unsure if you have SQLite3 you can open up your terminal and type

which sqlite3

if the output is

/usr/bin/sqlite3

then you are good to go. If not you can go to https://www.sqlite.org/docs.html
to find out how to install on to your computer

Since we are all set up lets start by making a very simple database. In this example I will create a database but wont go into to much depth as this is more about accessing data than creating it

sqlite3 practice 
(creating a database called practice)

sqlite> CREATE TABLE people(
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        favcolor TEXT
        );
(we've created the table)

sqlite> INSERT INTO people (name, age, favcolor) VALUES
        ('Peggy', 68, 'red'),
        ('Reptar', 27, 'green'),
        ('Link', 33, 'green'),
        ('Marge', 36, 'blue');
 (we've added some data)

Here we see we have created a very simple database with four columns, and we've added four rows. So how do we now access this data?

As you can see in the above example there are words in all caps. These are keywords in SQL. You don't have to write them in all caps but industry standard suggests that you do. Keywords in SQL are easy to use. Here is a list of the possible keywords to use https://www.sqlite.org/lang_keywords.html

in order to get data we will first use the SELECT keyword. SELECT does exactly what it sounds like- it will SELECT something. The next keyword we'll use is FROM, to tell SQL where to select from i.e.

sqlite> SELECT name_of_column FROM name_of_table

'*' is another thing you can use to indicate "all". Its fine with smaller databases but as standard its better to be clear and specific. So we could type something like

sqlite> SELECT * FROM people
(and our output would look like this)

1|Peggy|68|red
2|Reptar|27|green
3|Link|33|green
4|Marge|36|blue

it gives us the id, name, age, and favcolor

Comments

[Chris C]

Thanks for the info Dani.

I will save for reference to SQL work I'm using at work. I am brand new to SQL but sort of familiar with T-SQL