At its most basic, SQL is about asking for information. Start out by understanding the SELECT statement.
- [Instructor] The most basic way of asking for information with SQL is to use a select statement. The select keyword tells the database that we want some information returned to us. In fact, we can use it to return information that isn't even part of the database. For example, if I wanted the database engine to send me back some text, I could write, "Select" and a single quote, 'Hello, World!' and end the line with a semi-colon.
If I ran this, I would get that text back, regardless of the data stored in the database. You can see that here in the result window. Try it out yourself. Anything that you put in single quotes will get returned to you as text. We can replace that statement in single quotes with some parameters that will tell the database engine to start sending us information from the database. Let's take a look at what the database looks like. I'll click on the Browse Data tab. So I can see the field names in my database, across the top of this interface.
And each of those field names are something I can use to ask the database for information. So if I wanted to see all of the first names in the database, I could write "Select first _ name from people," the name of the table where I want to see the information form. And then I'll click To Run, and the result here is a listing of every value in the first_name field of my database.
If I wrapped up that value in single quotes, and ran it, I'd get back the text that I typed in, once for each record in the table. I'll get rid of that, and select the last_name table instead. And if I wanted to, I could ask for more than one field by adding commas between the field names. Here, after last_name, I'll write comma, and then, first_name.
When I run that, I get 2 columns. Whatever order these fields are asked for in will be the order of the fields in the output. If we want to get a response that has all of the fields, instead of just listing them one by one, we can use a wildcard operator. The star, or asterisk, which represents all of the fields in a given table. Using the asterisk is useful when you're exploring data, but we'll rarely use it in an app or a report, because it returns a lot of information, and usually we want to do more interesting things with our data, than just dump all of the information into one big result.
Let's use the select statement to get a little more familiar with it. From this Select * statement that I just wrote, I can see the column names in the table that I'm working with, here across the top of the results. Take a few moments and write some select statements with different field names, in different orders. I'll write "Select first_name, last_name, company from people," or I could change that to "Select company, first_name, and quiz_points from people." Oops, I made a typo.
The database is telling me that there's no such column called "quiz point," and it's right. I missed an "s," and I've added that "s" back in, and now if I run the query it works. Selecting data doesn't change anything in the database, so it's a great way to explore.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.