Join Bill Weinman for an in-depth discussion in this video Using the basic SELECT statement, part of SQL Essential Training.
In this lesson I'm going to show you the SELECT statement. The SELECT statement is used for most data retrieval in SQL. Here I have the SID application open in my browser. And I'm using the world.db database. I have that selected over here. And I'm going to type some SQL into the box. We'll start with a very simple SELECT statement. This will select all of the rows from the Country table. And I'll go ahead and press Go here. And here in the world.db database, the Country table looks like this.
If I scroll horizontally, you see it as a few more columns than what fit on the screen here. And this is all of the rows and all of the columns. You'll notice up here in the status bar it says 239 rows were returned and it took 8.9 milliseconds to do that. And there are the rows and you can see there are a lot of them. Here, the keyword SELECT means that this statement will return rows. And the asterisk means that all of the columns will be returned in this query.
And the FROM clause designates the source of the query in this case a table named Country. So, the database returned 239 rows and you'll notice that it's not in any particular order. We haven't specified an order, so it may or may not be in any particular order, it may even be in a different order on your system. And it may or may not even be in the same order when you try it again. SQL does not guarantee that data is returned in any specific order unless you specify an order. So, let's go ahead and specify order. So, I'm going to put my cursor there before the semicolon, the semicolon terminates the SQL statement.
And I'm going to say ORDER BY. And I'm going to specify the name column which you see there. So, I'm going to type it just like it is with a capital N. And, I'll press Go. And now, you'll notice that they are sorted in order by name in alphabetical order. The order actually depends on the collation algorithm that your database engine uses. In this case SQLite uses a simple binary value comparison. It's very fast and it's mostly alphabetical, as long as there aren't characters that are outside of the normal Roman character set.
If you need more specific collation, most databases systems allow you to define collation and comparison functions. We can specify what columns we want to return by replacing this asterisk with the names of particular columns. We can say name and say Life Expectancy. And when I press Go, you see we just get those two columns. I can specify that a column have a different label on it when it's returned. So I can say as and put this in quotes, Life Expectancy with a space in it.
And you notice that SQL uses single quotation marks as the standard quotation marks. Some databases allow you to use double quotes under certain circumstances and that's database engine specific. We're just looking at the standard here and the standard is to use the single quote marks. So I'm going to say Go, and you see now that our Column heading says Life Expectancy with a space in it. You don't actually have to select a source. You can just say SELECT Hello, World like this and put in a semi colon.
And now we get a column called Hello World and it has one row with the one value in it that says Hello World. You can actually use any expression. Here, I'm just going to put in a small expression. I'll say 1 plus 2 and I say Go. It returns one row with a column heading that says 1 plus 2 and the value is 3. If you remember our original query, we said select asterisk from country and we got all of these results.
Instead of actually returning the results, we can simply return the count of the results by using the COUNT function. SELECT COUNT FROM Country. And again, COUNT is another expression, but this one actually takes input so that it comes from the table. And it counts the number of rows returned and they are 239. So the SELECT statement is how you get a result from an SQL query. In this lesson, I've showed you the SELECT statement, what it is, how it's used to read data from a table, how to use a function with SELECT.
These basic concepts will be used in most everything you do with SQL. And I'll show you more details about SELECT later in this course.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.