SQL 101: Query Basics

SQL is an acronym for Structured Query Language so when getting started with it I think it’s best to think about how it was put together to be able to ask questions about data. With that in mind let’s a take a look at some beginning SQL to understand how to write queries.

Queries can be very simple but they won’t answer very many questions in their most simple forms:

SELECT 'A Static Value';

SELECT *
FROM myTable;

Best practices of course would be to include the specific columns in your select list so the SQL query engine can optimize the return of the necessary data. Selecting * from the table is a lot like eating an entire pizza because you wanted a single slice.

While a table and filter are clearly not required to write a query, the most common use for writing a query will require 3¹ parts to be useful:

• a SELECT-ion of things (columns)
• FROM a a collection of things (a table or view)
• WHERE a criteria is met (filter(s))

I happen to have a database of movie data. Let’s explore some common filter operators by asking some questions about movies. Filters based on mathematical operators are the easiest to start with. We can ask for films with exactly 2 Oscar nominations or more than 4 or less than 6. We make these filters exclusive or inclusive by adding the = operator to the comparison. We can also exclude a specific number by using <> (Microsoft SQL Server will also support != but this is outside the ANSI standard so <> would be best practice as it will be usable in any version of SQL.)

/* films with exactly 2 oscar nominations */
SELECT name, releaseDate
FROM films
WHERE oscarNoms = 2;
/* films with more than 4 oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms > 4;
/* films with 6 or less oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms <= 6;
/* films with any number of oscar nominations except 3 */
SELECT name, releaesDate
FROM films
WHERE oscarNoms <> 3;

We can have multiple conditions in our WHERE clause and these are combined with AND or OR statements. We can ask for films with 1, 2 or 3 Oscar nominations (but we’ll also see we have a new operator IN that is cleaner and accomplishes the same goal).

/* What movies have been nominated for 1, 2 or 3 Oscars? */
SELECT [name], [releaseDate], [oscarNoms]
FROM films
/* if any one of the following conditions is true for a row it will be returned */
WHERE oscarNoms = 1 
    OR oscarNoms = 2 
    OR oscarNoms = 3;

Of course this can be easily re-written with an IN clause:

SELECT name releasedDate
FROM films
WHERE oscarNoms IN (1, 2, 3);

We can check for movies released on or after Jan 1, 2017 and were nominated for at least 1 Oscar:

/* Oscar nominated move is released on or after Jan 1, 2017 */
SELECT name, releaseDate, oscarNoms
FROM films
WHERE releaseDate >= '2017-01-01'
AND OscarNoms >= 1;

Or we can ask about movies released before the year 1950 that made more money than they spent:

SELECT name, releaseDate
FROM films
WHERE releaseDate < '1950-01-01'
    AND boxOffice > budget;

You can combine and seperate WHERE clauses with parenthesis. These can be used to have more complex statements for returning rows: essentially you working to return a true/false statment for returning any given row²

SELECT name, releaseDate
FROM films
WHERE (oscarNoms > 2 
  OR  (releaseDate > '1980-12-31' AND budget >= 100000 ))
    /*  if the previous was true AND the box office was less 
     than $500,000 then the row can be returned */
  AND boxOffice < 500000;

If you’re just getting started with writing SQL queries I hope this was helpful! Next up I’ll cover a few tips for working with dates as well as specific operators for filtering strings.