Basic SQL

Lesson:

SQL (Structured Query Language) is an extremely popular programming language that lets people interact with databases.

A database is basically a collection of spreadsheets. Each spreadsheet is referred to as a table, because it stores data in a tabular way: in rows and columns.

In this lesson, we're going to cover the basics for retrieving data from a table. The database receives our query and returns the matching rows.

The most common queries have three parts: a SELECT, a FROM, and a WHERE. They are always in that exact order, so it makes sense to memorize them.

  • The SELECT clause - Tells the computers which columns you want to see. If you want to see more than one column, put a comma between each. For example, if you wanted to see the columns labeled CITY and STATE, you would type Select CITY, State. There's also a shortcut, if you just want to see every column, you can type SELECT *. The * means show me every column of data. It's easy to type, but for really big tables, it will often show you so many columns that you'll have to hunt for the columns you care about.
  • The FROM clause - Tells the computer which table to look at. SQL becomes very powerful when you start combining tables, but we'll just focus on a single table at a time in this lesson.
  • The WHERE clause - Limits which rows can be returned. Typically, this involves comparing the data in a column to something else. The most common comparison uses equal sign (=) to see if the value in a column to something else, like WHERE age = 64. Here are some other common comparisons you should know:
    • > Greater than
    • >= Greater than or equal to
    • < Less than
    • <= Less than or equal to

    It's important to note that if you are comparing a column to something with letters in it, you need to put quotes around it (unless it's a column with only true/false values). For instance, if you are comparing the US_State column to Ohio, you would type WHERE US_State = 'Ohio', but if you're just comparing a column to a number, you should not use quotes. It should look like this: WHERE FAVORITE_NUMBER = 27.

    One fun fact is that you can compare not only numbers, but words too. WHERE STATE > 'OHIO' will retrieve rows that come after (alphabetically) the name Ohio.

Finally, some systems require a semicolon (;) at the end of your SQL, so it's usually a good idea to use it.

As an auditor, you're trying to make sense of a firm's finances, but you're having trouble figuring something out.

Here are the relevant facts:

  • This is the table:
    Table Name: BUYERS
    IDSTATEAGECOMPANYIS_TRUSTED
    1Florida14Romeo Industriesfalse
    2Oregon58Howard corp.true
    3Hawaii3Tango ltd.false
    4West Virginia2Ginny LLCfalse
    5New Jersey18Shady corp.false
    6Oklahoma55Romeo Industriesfalse
    7Nebraska58Llamas, LLCfalse
    8North Dakota53David Co.true
    9Kansas26David Co.false
    10Oklahoma9Charlie LLCfalse
What is the SQL for finding ID for cases in which ID comes after 9?

Answer:

  • select ID from buyers where ID > 9;

Explanation:

  1. Let's type out our standard boiler plate:
    SELECT ?? FROM ?? WHERE ??;
  2. Now we need to figure out what goes where the ?? symbols are.
  3. First, let's take a look at the FOR clause. We were told the name of the table, so let's add it.
    SELECT ?? FROM BUYERS WHERE ??;
  4. Next, let's specify the columns that we want to see.
    SELECT ID FROM BUYERS WHERE ??;
  5. Finally, let's finish the WHERE clause.
    SELECT ID FROM BUYERS WHERE ID > 9;
Random ISC Random in Category Try Again