Introduction
Summary: Learn how to use the SELECT clause to query data in a database table
The SELECT clause queries data from one or more database tables. The two basic ways of querying data from a table are below.
SELECT *
FROM table_name;SELECT column1, column2
FROM table_name;Selecting data from Amazon_Customers
In keeping with our Introduction to SQL lesson and using a database table with customer data called Amazon_Customers.
Note: There is more data in the table, we're just showing the first 10 rows and first 4 columns of data.
We can get all of the records from the table by writing the following.
SELECT *
FROM Amazon_Customers;Let's break down the query:
The first line SELECT * uses the SELECT clause with the * (asterisk) wildcard character to return all of the columns from the table.
The second line FROM Amazon_Customers uses the FROM clause to pull from data from a table. The ; signifies the end of the SQL statement. It tells the SQL engine to stop processing the statement. The ; is not always needed. SQL will run the statement without it. There will be instances where you will need to run multiple SQL statements in sequence; this is where you would terminate multiple SQL statements with a semi-colon.
You would use an * in the SELECT statement because there are times where a database table might have hundreds of columns in a database table. Instead of writing the name for every column, an * would be much faster!
There are also times when we might want a limited number of fields.
A real-world business case might be that you're asked to generate a list of customers for an email marketing campaign and you want to populate the customer's name like "Hello Samantha".
To get the AMAZON_CUSTOMER_ID, FIRST_NAME and EMAIL, we could write the following:
SELECT AMAZON_CUSTOMER_ID, FIRST_NAME, EMAIL
FROM Amazon_Customers;When you run the query above, the database would return the results below:
Notice that there's no trailing comma after the last field, in this case EMAIL, we wanted from the table. If the comma is left in the statement, there will be an error returned from the database.
Below is an example of a SQL statement with a trailing comma and the database returns with an error.
Best Practices with SQL Coding
There's something to be said about best practices when writing SQL and writing code in general. SQL clauses should be written in all capitalized letters like SELECT and FROM. Over time, after years and decades of writing SQL, you might get accustomed to writing SQL in all lowercase letters. While this is generally not done, data analysts and engineers will write code in lowercase. Having to capitalize your clauses over hundreds and thousands of hours of writing code takes a lot of physical and mental energy. It all adds up over time. As your SQL advances, you'll be able to read code in either way. What matters is that it makes sense and executes the results properly.
Below are two examples:
SELECT AMAZON_CUSTOMER_ID, EMAIL, FIRST_NAME
FROM Amazon_Customers;select amazon_customer_id, email, first_name
from amazon_customers;As a beginner in writing SQL, please learn to write SQL by capitalizing all of your SQL clauses and reserved SQL words.
Mind Your Comments
In addition to writing SELECT statements, beginners should also learn how to comment out your code. As your SQL adventures advance, your code will become more complex and you will need to explain your code for others to read or if you're reviewing other people's code.
It's always good to make comments in your code where it makes sense. Make comments that are useful and succinct.
You can write inline comments or multi-line comments.
For inline comments they start with -- (two dashes) and then a comment within the line of the code. I purposely wrote "Not a useful comment" as an example because it doesn't provide any new info that you can't get by reading the code.
-- This is a single line comment.
-- Code below selects data from Amazon_Customers. Not a useful comment.
SELECT AMAZON_CUSTOMER_ID, EMAIL, FIRST_NAME -- Comments can be written inline with code.
FROM Amazon_Customers;For multi-line comments, they start with a /* and end with */. You can write as many lines between the beginning and ending comments.
/*
This is a multi-line comment
Line #2 of the comment
*/
SELECT AMAZON_CUSTOMER_ID, EMAIL, FIRST_NAME
FROM Amazon_Customers;Final Thoughts
In this lesson on SELECT statements, we learned it's best practicies to write your SQL statements/clauses with capitalized letters. While it's recommended to capitalize statements, after years of writing SQL, you have the option to write SQL in lowercase. We can query data in a database table with the * or by write each column from the table. Lastly, we learned to comment out SQL code by using inline or multi-line comments.
In my next lesson, we'll go into the WHERE clause and how to use it in more detail.