SQL Query Primer for Software Engineers

Structured Query Language (SQL) is the standard language for interacting with relationshional databases management systems (RDBMS). NoSQL databases exist, but they are not going to replace relational DBs any time soon, so it's important to understand SQL. The most basic way to interact with a relational DB is through a SQL query select statement.

SELECT name, phoneNumber FROM students WHERE class = 2015;

The query above will return the name and phone number of all students in the class of 2015. Select statements return defined data sets from the database. There are several options available for specifying the data you need. You can choose which fields to return.

SELECT name, phoneNumber        // returns only the specified fields  
SELECT *                        // returns all fields  

You can also select which table(s) to return data from using joins. Remember that relational databases have primary keys that are unique to each record in the table and may have foreign keys, primary keys from another table. You can utilize those connections with a join statement.

FROM students  
FROM students LEFT OUTER JOIN major ON students.major = major.id  

Left and right specify the physical location of the tables. In this case, students is left and courses is right. Inner joins return only the records that exist in both tables. Outer joins (or just joins--'outer' is optional) return the records that exist in the specified side's table and the records that exist in both. Records that are returned that do not have both sides will show null for the missing side. Fields used must be identical on both sides. The second line above would return all the students and their majors, if declared. Otherwise the student's major would show as null.

WHERE class = 2015  
WHERE class = 2015 AND GPA >= 2.0  

The where statement can be attached to several types of SQL queries, including insert and update. Where is a set of conditionals and will only return records when they are true. The following operators are a sample of the operators available: =, !=, <>, <, >, and like. You may also use AND, OR and parenthesis to show evaluation order.

This query would return all the data from the student and major table, with null values for students with no major, who are graduating with a passing grade.

SELECT * FROM students LEFT OUTER JOIN major ON students.major = major.id  
    WHERE class = 2015 AND GPA >= 2.0

For more information on joins, click here.