I started learning about Databases from one of my favorite blogs, Schneems. I really appreciate how Richard explains hard-to-grasp concepts with video tutorials. As a kinestatic learner, videos work well for me. It’s just a pity I can’t do the same with this blog as a lot of time and effort is spent on making one video. Blogging is a lot easier. Perhaps next time.
Through Schneems, I chanced upon SQL. Pronounced as “Sequel”, SQL is a standard language for assessing and manipulating databases. You can create new databases, create tables in those databases, insert records, retrieve data, and edit them.
We have a database with several tables, one of them includes the table below, “Customers”. This is assuming we’ve already created the table (formatting not included and is simply for illustration purposes). To learn how to create a table in mysql, you may use this link. By the way, this is the syntax I used for creating the table below.
mysql> create database geekcattysql;
mysql> use geekcattysql;
mysql> create table Customers
-> CustomerID int,
-> CustomerName varchar(255),
-> FavoriteColor varchar(255),
-> UserType varchar(255)
At a glance, the table above contains four records (one for each customer) and four columns (CustomerID, CustomerName, FavoriteColor, and UserType).
Before you even begin running SQL on your terminal, please note these rules while dealing with SQL statements:
- SQL is not case-sensitive (i.e. “select” = “SELECT”)
- We must use a semicolon at the end of each SQL statement
Running some basic commands
SELECT – selects and extracts data from a database, showing you a result table. From the Customer table above, let’s try running this:
SELECT * FROM Customers;
… This will be reflected. Of course, you’ll have to remove all formatting. Your computer isn’t as design-savvy as you’d like it to be.
Now let’s try another command:
SELECT CustomerName,FavoriteColor FROM Customers;
… This will be reflected. Just the two columns you wanted.
What happens if you want to show unique values, a.k.a. distinct, non-duplicate values? Do this:
SELECT DISTINCT UserType FROM Customers;
… And this will be reflected.
SQL commands worth remembering
If you want to practice more SQL, try W3Schools. They’ve got several tutorials, try-it-yourself functions, and a quiz should you want to test your skills. This is a list of the “most important SQL commands”, as suggested by them.
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – deletes a table
- CREATE INDEX – creates an index (search key)
- DROP INDEX – deletes an index
Practice, practice, practice
I would strongly advice you take your SQL skills to the next level through practicing on the actual Terminal tool. Make mistakes, keep Googling, ask around, but don’t give up. From my experience, the best way to learn is simply by doing.