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.
The only relevant information you've discovered is as follows:
- This is the table:
Table Name: MAIN_CUSTOMERS ID PRODUCT IS_TRUSTED TELEPHONE_NUMBER STATE 1 book false (233) 555-(7082) New Hampshire 2 desk false (277) 555-(2332) Florida 3 trumpet true (529) 555-(3079) Maryland 4 bicycle true (729) 555-(6590) Kentucky 5 stool false (560) 555-(7739) Ohio 6 desk false (236) 555-(1696) Delaware 7 pen false (402) 555-(6732) Pennsylvania 8 pencil false (267) 555-(3658) Utah 9 clock true (260) 555-(6831) Arkansas 10 lightbulb false (565) 555-(7741) Nevada
Answer:
- select * from main_customers where STATE <= 'Maryland';
Explanation:
- Let's type out our standard boiler plate:
SELECT ?? FROM ?? WHERE ??; - Now we need to figure out what goes where the ?? symbols are.
- 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 MAIN CUSTOMERS WHERE ??; - Next, let's specify the columns that we want to see.
SELECT * FROM MAIN CUSTOMERS WHERE ??; - Finally, let's finish the WHERE clause. Since we are comparing against a value containing text, we will need quotes around the value.
SELECT * FROM MAIN CUSTOMERS WHERE STATE <= 'MARYLAND';