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.
You've been provided with the following information:
- This is the table:
Table Name: CUSTOMER_INFO ID TELEPHONE_NUMBER COMPANY AGE FIRST_NAME 1 (282) 555-(8866) Howard corp. 11 Bob 2 (561) 555-(1997) Tango ltd. 4 Susan 3 (339) 555-(1944) Ultra Biz 14 Patricia 4 (460) 555-(6698) Atomic Industries 75 Jessica 5 (616) 555-(7709) Ginny LLC 39 Alice 6 (782) 555-(8163) Jenny LLC 31 Donald 7 (664) 555-(3542) Howard corp. 69 George 8 (257) 555-(1230) Juliet Corp. 26 Karen 9 (720) 555-(7893) Pepper corp. 43 John 10 (543) 555-(4187) Oscar corp. 70 Bob
Answer:
- select * from customer_info where FIRST_NAME >= 'Bob';
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 CUSTOMER INFO WHERE ??; - Next, let's specify the columns that we want to see.
SELECT * FROM CUSTOMER INFO 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 CUSTOMER INFO WHERE FIRST NAME >= 'BOB';