It’s All About That Data
SQL or Structured Query Language is a great skill to learn as a software developer. It is easy to learn, use, and is in-demand at a ton of high-paying companies. Even though SQL has been around for decades, it continues to grow in popularity and necessity when dealing with database manipulation. Why not gain proficiency in such a useful language?
SQL allows you to access and manipulate databases. You can execute queries, retrieve data, insert, update, and delete records. You can also create new databases, tables, views, along with a slew of other useful procedures. Lets look at some of the basics.
Databases are objects that store data in tables. A table is a collection of related data entries and it consists of columns and rows. Every table is broken up into smaller entities called fields.
A row (or record) is each individual entry present in a table.
A column is vertical entity that contains all information associated with a specific field.
A statement is any SQL command such as SELECT, INSERT, UPDATE, DELETE. Lets take a closer look at these…
The SELECT statement is used to (what else) select data from the desired database. This data is returned in what is called a return table, or result-set.
If we wanted to return every record in the Employees Table example we could write something like this:
SELECT * FROM Employees;
You might be wondering what the * means. It is a wild card character (in many languages) meaning all present record.
We can also return specific columns from the Employee Tables. Lets retrieve the phone numbers from each employee:
SELECT Phone FROM Employees;
If we have multiple Employees with duplicate values in a column and we want to return only the unique values we can use:
SELECT DISTINCT SalaryFROM Employees;
The SELECT statement can be combined with the WHERE statement to focus our search queries even further.
The WHERE clause is used to return records that fulfill a specific condition. It uses operators to specify these conditions.
We can look for all Employees who share the same job code (even though none share one in our example):
SELECT * FROM EmployeesWHERE JobCode='ME3';
::NOTE:: SQL shares similar syntax with other languages in that strings must be enclosed in quotes (and mostly double quotes) while integers do not.
The equals sign is just one of the operators we can use with the WHERE statement. Others include…
greater than >less than <greater than or equal to >=less than or equal to <=not equal <> (or != depending on the program)between BETWEEN (a certain rage)search for a pattern LIKE specify multiple values for a column IN
The WHERE statement likewise can be combined with AND, OR, and NOT operators.
AND, OR, and NOT
AND, OR, and NOT operators are used when you need to return records based on multiple conditions.
The AND operator returns a result when all conditions are filled:
SELECT * FROM Employees WHERE Salary=65800 AND Phone=’914/455–2337';
The OR operator returns a result if any conditions are filled:
SELECT * FROM EmployeesWHERE Salary=29860 OR LName=’Parker’;
The NOT operator returns a result if the condition(s) is not true:
SELECT * FROM EmployeesWHERE NOT LName=’Greenwald’;
You can really get specific when combining these operators:
SELECT * FROM EmployeesWHERE JobCode=’ME3' AND (LName=’Chin’ OR LName=’Wood’);
::NOTE:: See how we used ( ) to make the complex expression!
Just the Beginning
Hopefully this brief introduction illustrates how easy and DRY the syntax is in SQL. These are but the beginning steps into a wide world of database drama! Check in next week for my continued quest into SQL syntax.