Structured Query Language (SQL) is programming language for handling data in a relational database management system. Having at least the basic knowledge of SQL is a very powerful skill set to have and will open doors in a variety of fields.
I am going to give you a quick rundown so you can understand the basics of SQL. Once you have an idea of the basics you will be in a better place to go out and expand your knowledge with the endless resources available online.
DISCLAIMER: you are not going to be an expert after reading this, the goal is to help you have a BASIC understanding so when you start googling, you know what you are looking for and what you are trying to accomplish.
With that said lets begin.
As mentioned earlier, SQL is used to work with databases, so we should probably cover what that is quick. According to Wikipedia, a database is an organized collection of data, generally stored and accessed electronically from a computer system. So basically a bunch of data about whatever you want stored on a computer.
Within a database there are tables, which is a collection of related data. For this article I will use the example of a database for storing university data and we will focus on two tables, one holding student data and one holding data about majors the university offers. (Note: the tables in the example will not follow perfect database design and normalization but will work for the examples).
See below an example of our first table that holds student data called STUDENT. There are four columns in this table FIRST_NAME, LAST_NAME, BIRTHDATE, MAJOR. And three rows, one per student.
The second table we will be looking at is called MAJORS that will have three columns, MAJOR, DEGREE_TYPE, DEPARTMENT_HEAD.
Now that we know what a database is and the fact that it stores the data in tables, we get to the point of the article, SQL. We will use SQL to access the data we have stored.
The first thing you are going to do is view the data that you have. This is done with a SELECT statement. Select statements are formatted as follows
SELECT <COLUMN 1>, <COLUMN 2>, <COLUMN 3>, <COLUMN 4> FROM <TABLE NAME>
Another quick hint is if you know the name of the table and not the columns you want to see, you can use an asterisk “*” rather than listing the columns and this will return all columns in the table, this is often referred to as “select all”. You could write the original query on students as below
SELECT * FROM STUDENT
The select statements I have shown so far are going to return all records in the table. Next we are going to cover ways you can filter down the results. You can filter down select statements with a WHERE clause.
Where clauses are written as follows,
SELECT * FROM <TABLE NAME> WHERE <COLUMN NAME> = ‘SOME VALUE’
You can use all sorts of relational operators in the where cause, using equals is just a quick example.
If you need your results sorted in a specific way, you will use ORDER BY in your query. You can order your queries in ascending (ASC) or descending (DESC) order. This statement will be written as follows
SELECT <COLUMN 1>, <COLUMN 2>, <COLUMN 3>, <COLUMN 4> FROM <TABLE NAME> order by <COLUMN 3> desc
The above query is pulling the four listed columns from the STUDENT table and ordering them by the values in the BIRTHDATE column in descending order.
Now you may be thinking “what if i want to pull data from two tables at once”. That is where a JOIN comes in. There are multiple types of joins, but we are only going to cover INNER JOIN, you get to research the rest on your own once you finish this!
The basic structure of a join is
SELECT * FROM <TABLE 1> INNER JOIN <TABLE 2> on <TABLE 2>.<COLUMN 1> = <TABLE 1>.<COLUMN 4>
The above query written out in plain english would be “select ALL rows from the student table joined with the majors table where the value in the major column in the majors table is equal to the value in the major column in the student table”.
Now that we have covered the basics of retrieving data, I will show you how to get data into the database, modify data, and finally remove data.
To get data into a table, you will use what is called an INSERT statement which is structured as follows:
INSERT INTO <TABLE NAME> (<COLUMN 1>, <COLUMN 2>, <COLUMN 3>) VALUES (<VALUE 1>, <VALUE 2>, <VALUE 3>)
For example, say the university has decided to add a new major, the query to insert into the MAJORS table would look like this
After running this command, I can select all from the MAJORS table and see my new record.
Now that we have our new major in the MAJORS table, we decide to replace the department head so we need to update our database with an UPDATE statement.
To perform an update you need to specify the table you want to update the column(s) that need their values to change and finally a where clause to narrow down what you are updating rather than updating all records in the table!
UPDATE <TABLE NAME> SET <COLUMN 1> = ‘SOME NEW VALUE’ WHERE <COLUMN 2> = ‘SOME SPECIFIC VALUE’
In the above query I am updating the majors table and changing the department_head value to ‘Johnny Bravo’ ONLY for the record where major = ‘Database Design’. That where clause is extremely important unless you want to set all the department heads to the same value.
Finally, we decide that a Database Design major doesn’t generate enough interest so we remove it, we are going to do this with a DELETE statement. Delete statements are simple but can be dangerous if you do not include a WHERE clause, similar to updates.
DELETE FROM <TABLE NAME> WHERE <COLUMN 1> = ‘VALUE THAT I WANT GONE’
This is going to remove the record from MAJORS where MAJOR = ‘Database Design’
At this point we have covered the basics of SQL, and i cannot stress the work BASIC enough. Now that you understand what can be done with SQL it is time to go out and review the almost unlimited resources online. Knowing SQL can be a very powerful tool in almost every field, even if its just a talking point on your resume.